SQL AntiPatterns - Primary key

SQL AntiPatterns 라는 책의 내용 중 최근에 관심있게 살펴보았던 Primary Key 관련 내용을 정리해보았다.

Primary Key

primary key 는 테이블의 모든 행에 대해 유일성을 보장해 행이 중복해서 저장되는 것을 방지해주고 특정 행을 유일하게 식별하는데 사용할 수 있다. 또한 외래키에 의해 참조되는 경우도 많기 때문에 굉장히 많은 곳에서 사용되는 제약사항이라고 볼 수 있다.

 

어떤 컬럼을 PK로 사용할 지에 대한 고민을 해볼 수 있는데 우선 PK가 되기 위해서는 다음 조건을 만족해야 한다.

• 각 행별로 유일한 값을 갖는 컬럼이어야 한다.
• NOT NULL 제약을 가져야 한다.

Natrual Key / Surrogate Key

우선 Key 와 관련해 알고 있어야 하는 기본 개념을 잠깐 짚고 넘어가자

 

natural key (혹은 business key) 는 애플리케이션의 도메인에 이미 존재하는 실제 속성으로 구성된 키를 말한다. 인위적으로 값을 만들어내는 것이 아닌 비즈니스 로직에서 파생된 값으로 만들어진 키를 말한다.

대표적으로 주민등록번호나 핸드폰번호 혹은 책의 ISBN 같은 값을 natural key 라고 생각할 수 있다. (물론 키로 사용할 수 있는가는 잘 판단해야 한다)

 

장점

• natural key 를 사용하게 되면 이미 비즈니스 상에서 의미있는 값을 사용하는 것이기 때문에 이해하기 쉽고 별도 컬럼을 정의할 필요가 없다는 장점이 생긴다. 

단점

• 비즈니스 로직이 변경되거나 외부 요인으로 인해 해당 값에 영향을 주게 된다면 키 로써의 역할을 더 이상 수행하지 못할 수 있다. 예를 들어 유일성 이라는 속성이 깨질 수 있다.

Surrogate key (혹은 pseudo key) 는 인위적으로 만들어낸 키 값을 말한다. 보통 데이터베이스 내에서 시스템이 생성해낸 값을 사용하게 된다. 정수값 혹은 UUID 를 사용하며 auto-increment 와 같은 기능을 통해 사용할 수 있다. 

 

장점

• 사용이 매우 간단하다.
• 시간이 지나도 발생되는 방식이나 기존 값에 변화가 생기지 않기 때문에 안정적이라고 할 수 있다.
• 정수형태의 surrogate key 를 사용하게 되면 natural key 에 비해 효율적이기 때문에 성능상에 이득이 된다.

단점

• 인위적으로 만들어낸 값이기 때문에 컬럼 자체가 갖는 의미가 없다.
• 테이블 간의 연관관계를 정의할 때 불필요한 복잡도를 높일 수 있다.

이제 다시 돌아와서 어떤 컬럼을 primary key 로 사용하는 것이 좋을까?

나의 경우에는 사실 딱히 primary key 에 대해 크게 생각해본 경우가 별로 없다. 그냥 테이블에 반드시 있어야 하고 id 라는 이름으로 AUTO_INCREMENT 를 하면 되지 않을까? 하고 사용했던 것 같다. 그냥 종종 32-bit 을 사용할지 64-bit 을 사용할지 고민했던 정도.

물론 대부분의 경우에는 이게 잘 동작할 수 있지만 다음과 같은 경우에는 조금 더 생각해 볼 만 해진다.

CREATE TABLE Article (
    id INT PRIMARY KEY,
    article_id VARCHAR(10) UNIQUE,
    content VARCHAR(1000),
    ...
);

 

위의 테이블에서는 article_id 컬럼에 unique constraint 가 이미 걸려져 있다. 앞서 살펴본 primary key 의 조건은 1. 유일한 값을 가져야 하고 2. NOT NULL 제약을 가져야 한다 였는데 1번 조건을 만족하고 있다. 물론 한가지 주의해야 할 점은 UNIQUE constraint 이 NULL 값에 대해서는 중복을 허용한다는 점이다.

만약 해당 컬럼에 NOT NULL 조건이 추가될 수 있다면 사실 id 컬럼을 완전히 대체할 수 있다.

 

위와 같은 비슷한 상황이 자주 발생하는 경우는 테이블 간에 many-to-many 연관관계가 있는 경우이다.

예를 들어 Article 테이블과 Category 테이블이 서로 many-to-many 관계를 갖는다고 생각해보자. (하나의 Article 의 여러 Category 에속할 수도 있고, 하나의 Category 에 여러 Article 이 속할 수 있다)

 

이 관계의 매핑을 위해 다음과 같은 테이블이 존재한다고 해보자.

CREATE TABLE CategoryArticles (
    id INT PRIMARY KEY,
    category_id INT NOT NULL,
    article_id INT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES ON Category(category_id),
    FOREIGN KEY (article_id) REFERENCES ON Article(article_id)
);

 

특정 (category_id, article_id) 조합이 이 테이블에 한번만 등장하도록 만들고 싶을 때 어떻게 해야할까?

우선 id primary key 만으로는 이 요구사항을 만족시킬 수 없다. 왜냐면 우리가 중복을 막고 싶어하는 컬럼에 대한 어떤 유일성 보장을 해주지 않기 때문이다. 가장 간단한 방법은 그냥 (category_id, article_id) 에 대해 UNIQUE constraint 를 추가하는 것이다.

그런데 여기서 앞에서 본 상황이 다시 발생한다. 1. 유일한 값을 갖고, 2. NOT NULL 제약을 갖는 키가 등장한다. 이번에는 두 컬럼이 각각 NOT NULL 제약을 갖기 때문에 1, 2번 조건을 모두 만족한다.

이 경우라면 더더욱 id 컬럼의 존재 이유가 사라지고 (category_id, article_id) compound key 가 primary key 로 사용할 수 있는 상황이 만들어진다

Primary Key의 이름 짓기

프로그래밍에서 변수나 클래스의 이름 더 나아가 특정 서비스의 이름을 잘 짓는 것은 전체적인 서비스의 이해도를 높히거나 복잡도를 줄이는 데 꽤 큰 영향을 준다. 그렇다면 id 라는 이름은 어떨까?

사실 id 라는 이름이 프로그래밍쪽에서 꽤 다양한 목적으로 사용되기도 하고 이름 자체 만으로는 그냥 유일한 값이라는 의미만을 추론할 수 있기 때문에 그 외의 부가적인 정보를 주진 못한다.

 

쿼리를 짜다 보면 종종 다음과 같이 id 컬럼이 겹쳐서 alias 를 사용하게 되는 경우가 생긴다.

SELECT a.id, c.id
FROM Article a
JOIN Category c ON (c.article_id = a.id);

 

뭐 물론 그냥 이렇게 겹칠때 alias 로 회피해 줄 수도 있지만 (그리고 약간 취향의 문제인 것 같기도 하지만) 좀 더 이해하기 쉬운 이름을 부여할 수도 있다. 가장 쉽게는 해당 키가 표현하는 엔티티의 타입을 담고 있을 수 있다. Article 엔티티에 대한 키라면 article_id 와 같이 말이다. 그리고 외래 키로 해당 값을 참조해야 할 때 동일한 이름을 사용해주면 좀 더 직관적으로 쿼리를 작성할 수 있다.

CREATE TABLE CategoryArticles (
    category_id INT NOT NULL,
    article_id INT NOT NULL,
    PRIMARY KEY (category_id, article_id),
    FOREIGN KEY (category_id) REFERENCES Category(category_id),
    FOREIGN KEY (article_id) REFERENCES Article(article_id)
);

 

Primary key .. 꼭 필요한거야?

가끔가다 PK 가 테이블에 필요 없어 보이는 경우가 있다. 우선 지금까지 확인한 PK가 필요한 이유는 다음과 같다.

• 중복된 행의 추가를 방지해야 하는 경우
• 쿼리를 통해 개별 행을 가져와야 하는 경우

또 하나 굳이 이유를 추가해본다면 database replication 을 생각해볼 수 있다.

replication 방식에는 여러가지가 있겠지만 primary-secondary replication 에서는 모든 쓰기 요청이 primary node 에 간 뒤 이를 secondary node에 전파하는 방식으로 동작한다. 이 때 log 를 통해 row 에 가해진 변경점을 적용하게 되는데 여기서 primary key 를 활용하게 된다.

• row 에 변경이 생겼을 때 (insert, update, delete 등으로 인해) 이는 replication log 에 영향을 준 행의 primary key 와 함께 기록되게 된다.
• primary key 의 유일성 특성을 활용해서 replication 시 특정 행을 정확하게 식별해 모든 replica 에 이를 전파할 수 있게 된다. 또한 이 과정에서 conflict 가 발생하지 않았다는 점도 primary key 사용을 통해 쉽게 알 수 있게 된다.

만약 primary key 없이 replication 을 진행해야 한다면 시스템이 어떤 데이터를 유일하게 식별하는데 어려움을 겪을 수도 있고 데이터가 중복되었다는 사실을 곧바로 인지하기 어려울 수도 있다. 또한 항상 인덱스가 걸려있는 컬럼을 사용하지 않을 수 있기 때문에 성능 측면에서도 차이가 발생할 수 있다.

 

테이블의 데이터를 사용하면서 위와 같은 요구사항이 없는 경우는 사실 거의 없다. 또한 이런 목적이 과한 상황이라고 하더라도 이를 대체하기 위한 작업 자체가 매우 복잡해진다. 예를 들어 중복 행을 방지하는 것을 직접 수행할 수도 있는데 이렇게 되면 복잡도가 급격하게 커진다. 그렇기 때문에 primary key 가 불필요해 보인다면 정말 그런 상황이 맞는지 제대로 고민해야 할 수 있다.

INT vs BIGINT

최근에 잠깐 고민했던 문제이다. id 컬럼을 만들 때 타입을 INT 로 하는게 좋을까 BIGINT 로 하는게 좋을까?

 

일단 BIGINT 를 고민하는 이유는 AUTO_INCREMENT 를 걸어두었을 때 최대값에 도달하는 경우를 방지하고자 하는 목적이었다.

근데 이 값이 최대값에 도달하면 어떻게 되는걸까?

 

우선 얼마나 많은 시간이 흘러야 최대값에 도달할 수 있을지 생각해보자.

signed 32-bit integer 를 기준으로 한다면 최대값은 2^31 - 1 으로 분당 10,000 row 가 추가되는 환경이라면 약 150일 정도가 소요된다고 한다. 어림잡아 반년이 지나기전에 모든 값을 소모한다.

signed -> unsigned 로 변경하면 1개의 비트를 추가로 사용할 수 있으므로 2배 더 많은 값을 사용하여 약 300일 정도로 1년이 좀 안되는 시간이 걸린다.

 

64-bit integer 를 생각해보면 이는 32-bit integer 의 두배가 아니라 무려 2^32 배 더 많은 시간이 걸린다.

이걸 위와 똑같이 계산해보면 다쓰기 위해서는 약 1,754,827,252 년이 걸린다고 한다. 우리의 애플리케이션이 저 시간동안 동작할 일이 있을까? gpt에게 물어봐도 아직 실제로 일어난 사례는 없어보인다.

그리고 이게 실제로 발생한다 하더라도 sharding 이나 UUID 등을 사용해서 대응할 방법이 존재한다.

 

둘 중 어떤 데이터 타입을 선택할 것인가는 각자가 만들고자 하는 애플리케이션의 상황에 따라 판단하는 것이 좋다.

1. 값의 범위
   • 앞서 보았듯 두 타입은 처리할 수 있는 값의 범위가 다르다. (심각하게 차이난다) 그러므로 해당 테이블이 다루고자 하는 데이터의 양에 맞게 선택하는 것이 좋다.
2. 저장소 요구사항
   • INT 는 4byte, BIGINT 는 8 byte 를 사용하기 때문에 저장소 용량이 두 배 차이가 난다.
3. 성능
   • 2번과 비슷하게 index 역시 INT 타입이 더 작게 유지되기 때문에 index 연산 또한 BIGINT 보다 약간은 더 빠르다고 한다.

일반적인 경우라면 INT 를 선택하는 것이 좋다. 테이블의 row 가 INT 의 최대치 근처에도 도달하지 못할 것이라면 사실 굳이 BIGINT 를 선택할 이유가 없어보인다.

 

테이블을 생성할 때 가장 기본이 되는 Primary Key 에 관련된 몇 가지 고민거리를 살펴보았다. 어떻게 보면 당연한 이야기인 것 같지만 종종 의문을 갖게 되는 부분들이 있는데 한번쯤은 생각해 볼 수 있는 시간을 가지면 좋을 것 같다.