새 아파트로 이사했을 때를 떠올려보자. 아마 모든 것을 어디에 둘지 계획하는 데 시간을 보냈을 것이다. 부엌용품은 스토브 근처에, 책은 책장에 정리하고, 옷은 옷장에 분류하는 것처럼 말이다. 데이터베이스 스키마 설계도 놀라울 정도로 이와 유사하다. 데이터를 합리적이고 유지 관리하기 쉬우며 필요에 따라 확장할 수 있는 방식으로 구성하는 것이다.
좋은 스키마 설계는 규칙을 암기하거나 엄격한 공식을 따르는 것이 아니다. 데이터베이스가 잘 작동하도록 만드는 원칙을 이해하고 특정 상황에 맞게 신중하게 적용하는 것이다.
이 글에서는 스키마 설계의 핵심 원칙을 안내하고, 일상적인 개발에서 실제로 중요한 것이 무엇인지 다룰 것이다. 학문적인 내용은 건너뛰고, 경력 내내 반복해서 사용하게 될 패턴에 집중할 것이다.
스키마 설계가 생각보다 중요한 이유
몇 년 전, 이전 팀이 데이터베이스 구조에 대해 별 생각 없이 "코딩부터 시작"한 프로젝트를 물려받은 적이 있다. 유지보수를 시작한 지 6개월이 지나자, 몇 시간이면 끝났어야 할 간단한 기능이 며칠씩 걸렸다. 데이터베이스는 정보를 찾으려면 7개의 테이블을 조인해야 하는 미로가 되었고, 새로운 기능을 추가하려면 애플리케이션의 쿼리 절반을 다시 작성해야 했다.
잘못된 스키마 설계의 비용은 시간이 지남에 따라 복리로 증가한다. 프로젝트 초기의 작은 지름길처럼 보였던 것이 나중에는 엄청난 기술 부채가 된다. 반면에 잘 설계된 스키마는 개발 속도를 높이고, 쿼리 효율성을 높이며, 유지보수를 훨씬 쉽게 만든다.
좋은 스키마 설계가 제공하는 이점은 다음과 같다:
- 자연스럽게 확장되는 성능. 테이블이 제대로 구조화되면 데이터베이스는 인덱스를 효과적으로 사용할 수 있다. 잘못 설계된 스키마에서 몇 분이 걸리는 쿼리가 잘 설계된 스키마에서는 몇 밀리초가 걸릴 수 있다.
- 이해하기 쉬운 코드. 데이터베이스 구조가 비즈니스 도메인을 명확하게 나타내면 개발자는 테이블이 무엇을 의미하는지 해독하는 데 시간을 덜 들이고 기능을 구축하는 데 더 많은 시간을 할애할 수 있다. 새로운 팀원은 스키마를 보고 애플리케이션이 무엇을 하는지 이해할 수 있다.
- 미래 변경에 대한 유연성. 비즈니스 요구사항은 끊임없이 변경된다. 견고한 스키마 설계는 모든 것을 다시 작성할 필요 없이 적응할 수 있는 여지를 제공한다. 구축한 구조에 맞서 싸우는 대신 기존 패턴을 확장하여 새로운 기능을 추가할 수 있다.
기초: 먼저 도메인 이해하기
데이터베이스 도구를 만지기 전에 무엇을 만들고 있는지 이해해야 한다. 당연하게 들리지만, 너무 많은 개발자가 도메인을 충분히 생각하지 않고 바로 CREATE TABLE 문으로 뛰어드는 것을 보았다.
먼저 엔티티를 식별하는 것부터 시작하자. 이것은 애플리케이션이 관심을 갖는 "사물"이다. 전자상거래 시스템에서는 고객, 제품, 주문, 배송이 있을 수 있다. 블로그 플랫폼에서는 사용자, 게시물, 댓글, 태그가 있을 것이다. 이것들을 적어보자. 화이트보드에 상자를 그리자. 각 엔티티가 무엇을 나타내는지 팀과 이야기하자.
다음으로, 이러한 엔티티 간의 관계를 파악하자. 고객은 여러 주문을 가질 수 있는가? 주문에 여러 제품이 포함될 수 있는가? 제품이 여러 카테고리에 속할 수 있는가? 이러한 관계는 전체 스키마 설계를 형성할 것이다.
실용적인 예를 들어보자.
강의 관리 시스템을 구축한다고 가정해보자.
- 학생은 강의를 수강한다.
- 강사는 강의를 가르친다.
- 강의에는 여러 강의가 있다.
- 학생은 강의에 대한 과제를 제출한다.
- 과제는 성적을 받는다.
이미 관계가 형성되는 것을 볼 수 있다. 학생은 여러 번의 수강 신청을 한다. 강의에는 한 명의 강사와 여러 명의 학생이 있다. 과제는 한 명의 학생과 한 개의 강의에 속한다. 이러한 관계는 테이블이 어떻게 연결되어야 하는지를 정확하게 알려준다.
여기서 핵심 통찰력은 스키마가 비즈니스 도메인을 반영해야 한다는 것이다. 누군가 "학생이 같은 강의를 두 번 수강할 수 있나요?"라고 물으면 스키마를 보고 답할 수 있어야 한다. 스키마가 현실을 정확하게 반영하면 다른 모든 것이 쉬워진다.
기본 키: 데이터의 신원 시스템
모든 테이블에는 각 행을 고유하게 식별하는 방법이 필요하다. 이것이 기본 키가 하는 일이며, 여기서 내리는 결정은 전체 애플리케이션에 파급 효과를 미친다.
가장 일반적인 논쟁은 자연 키와 대리 키 사이에서 벌어진다. 자연 키는 사용자의 이메일 주소나 책의 ISBN과 같이 엔티티에 내재된 것이다. 대리 키는 일반적으로 자동 증가 정수 또는 UUID와 같이 인위적으로 만드는 식별자이다.
실제로 나는 거의 항상 대리 키를 권장한다. 이유는 다음과 같다.
이메일 주소는 사용자의 완벽한 자연 키처럼 보인다. 고유하고 모든 사용자가 하나씩 가지고 있기 때문이다. 하지만 사용자가 이메일 주소를 변경하고 싶어한다. 이제 그 이메일이 참조되는 모든 곳, 즉 수십 개의 테이블에서 이메일을 업데이트해야 한다. 또는 제품 SKU를 생각해보자. 동일한 SKU 형식을 사용하는 다른 회사와 합병하기 전까지는 고유하다. 갑자기 "고유한" 키가 더 이상 고유하지 않게 된다.
대리 키는 이러한 문제를 완전히 피한다. 절대 변경되지 않고 충돌하지 않는 의미 없는 숫자(또는 UUID)일 뿐이다.
이 디자인은 여러 가지 이점을 제공한다. 첫째, 다른 테이블의 외래 키는 변경 불가능한 id 필드를 가리키기 때문에 사용자는 아무것도 깨뜨리지 않고 이메일을 변경할 수 있다. 둘째, 정수 키는 문자열 비교보다 조인을 더 빠르게 만든다. 셋째, 이메일에 대한 고유성 제약 조건은 여전히 유지되므로 사용자는 중복된 주소로 등록할 수 없다.
기본 키 유형에는 일반적으로 자동 증가 정수(PostgreSQL의 SERIAL, MySQL의 AUTO_INCREMENT), UUID 또는 스노우플레이크 ID의 세 가지 선택지가 있다. 각각 장단점이 있다.
자동 증가 정수는 간단하고 빠르며 대부분의 애플리케이션에 적합하다. 주요 단점은 순차적이고 예측 가능하다는 것이다. /api/users/1234가 있으면 사람들은 /api/users/1235가 존재할 것이라고 추측할 수 있다. 내부 시스템이나 적절한 권한 부여와 결합된 경우 일반적으로 문제가 되지 않는다.
UUID는 예측 가능성 문제를 해결하고 여러 데이터베이스가 독립적으로 ID를 생성할 수 있는 분산 시스템에서 잘 작동한다. 단점은 BIGINT의 64비트에 비해 128비트로 더 크다는 점이며, 이는 인덱스가 더 커지고 조인이 약간 느려진다는 것을 의미한다. 대부분의 애플리케이션에서 이 성능 차이는 무시할 수 있지만 대규모에서는 중요할 수 있다.
종종 간과되는 또 다른 중요한 UUID 성능 문제가 있다. 대부분의 UUID 라이브러리는 기본적으로 v4(랜덤) UUID를 생성하는데, 이는 심각한 B-트리 인덱스 단편화를 유발한다. 랜덤 UUID는 인덱스의 임의 위치에 삽입되어 페이지 분할 및 참조 지역성 저하를 유발한다. 이로 인해 테이블이 커짐에 따라 삽입 성능이 몇 배나 저하될 수 있다. UUID가 필요한 경우 다음 대안을 고려하자.
- UUID v7: 시간 순서에 따라 정렬되는 UUID. Node.js용 uuid 패키지 v10+에서 사용 가능(2024년 6월 출시).
- ULID: 사전순으로 정렬 가능하고 시간 순서에 따르며 더 간결한 문자열 표현(UUID의 36자에 비해 26자)을 가진다.
JavaScript에서는 쓰기 집약적인 테이블의 기본 키에 crypto.randomUUID()(v4 생성)를 사용하지 말자.
대신 이렇게:
// Good: Time-ordered options
import { v7 as uuidv7 } from 'uuid'; // UUID v7 (requires uuid@10+)
import { ulid } from 'ulid'; // ULID
const id1 = uuidv7(); // Time-ordered UUID
const id2 = ulid(); // Lexicographically sortable
// Bad: Random UUID v4 (default)
import { randomUUID } from 'crypto';
const id = randomUUID(); // Causes index fragmentation at scale
차이점은 대규모 테이블(>1M 행) 및 쓰기 집약적인 워크로드에서 눈에 띄게 나타난다. 소규모 애플리케이션의 경우 UUID v4도 잘 작동하지만 나중에 마이그레이션의 고통을 피하기 위해 v7 또는 ULID로 시작하는 것이 좋다.
Snowflake ID(트위터에서 대중화)는 두 가지 장점을 모두 제공한다. 생성 시간별로 대략 정렬 가능하고, 분산 시스템 전반에서 고유하며, UUID보다 간결하다. 대부분의 애플리케이션에는 과하지만 대규모 분산 시스템을 구축하는 경우 알아둘 가치가 있다.
여기서 원칙은 간단하다.
안정적이고 의미 없으며 효율적인 Primary keys를 선택하자.
- 안정성은 절대 변경되지 않음을 의미한다.
- 의미 없음은 유효하지 않게 될 수 있는 비즈니스 정보를 인코딩하지 않음을 의미한다.
- 효율성은 비교가 빠르고 공간을 낭비하지 않음을 의미한다.
외래 키: 데이터 무결성 유지
외래 키는 테이블을 함께 연결하는 방법이며, 데이터베이스 수준에서 이를 강제할지 여부는 열띤 논쟁을 불러일으키는 결정 중 하나이다. 소음을 뚫고 말하자면: 외래 키 제약 조건을 사용하자.
나는 그것에 반대하는 모든 주장을 들어봤다. "쓰기 속도를 늦춘다." "스키마 변경을 더 어렵게 만든다." "애플리케이션 코드에서 참조 무결성을 처리한다." 이러한 주장은 요점을 놓치고 있다. 데이터베이스는 데이터의 단일 진실 공급원이다. 약간 더 빠른 쓰기를 원했기 때문에 데이터가 일관성 없는 상태가 되도록 내버려두는 것은 소탐대실이다.
외래 키 제약 조건이 없으면 어떻게 될까? 애플리케이션 코드는 모든 주문이 유효한 사용자를 참조하도록 보장해야 한다. 하지만 누군가 데이터를 대량으로 가져오는 스크립트를 작성하면서 사용자 ID 유효성 검사를 잊어버린다. 또는 새로운 기능을 작업하는 개발자가 실수로 주문을 처리하지 않고 사용자를 삭제한다. 또는 최악의 경우, 동일한 데이터베이스에 여러 애플리케이션이 쓰기를 하는데, 모든 애플리케이션이 동일한 유효성 검사 논리를 가지고 있지 않다.
외래 키 제약 조건이 있으면 이 중 어느 것도 일어날 수 없다. 데이터베이스는 단순히 고아 레코드를 생성하도록 허용하지 않는다. 이를 올바르게 구현하는 방법은 다음과 같다.
-- 부모 테이블부터 시작
CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
stock_quantity INTEGER NOT NULL DEFAULT 0
);
-- 외래 키 및 명시적인 참조 동작이 있는 자식 테이블
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10, 2) NOT NULL,
status VARCHAR(50) NOT NULL DEFAULT 'pending',
-- CASCADE 삭제가 있는 외래 키 - 고객이 삭제되면 주문도 삭제됨
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10, 2) NOT NULL,
-- RESTRICT가 있는 외래 키 - 항목이 있는 주문은 삭제할 수 없음
CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id)
REFERENCES orders(id)
ON DELETE RESTRICT,
-- RESTRICT가 있는 외래 키 - 주문에서 참조되는 제품은 삭제할 수 없음
CONSTRAINT fk_order_items_product
FOREIGN KEY (product_id)
REFERENCES products(id)
ON DELETE RESTRICT
);
ON DELETE 및 ON UPDATE 절을 주목하자. 이는 참조된 행이 변경될 때 발생하는 일을 정의한다. CASCADE(변경 전파), RESTRICT(변경 방지), SET NULL(외래 키를 null로 설정) 또는 SET DEFAULT(기본값으로 설정) 중에서 선택할 수 있다. 비즈니스 규칙에 따라 선택하자.
주문-고객 관계의 경우 CASCADE가 합리적이다. 고객을 삭제하면(아마도 해서는 안 되지만 나중에 자세히 설명) 주문도 삭제되어 일관성이 유지된다. 주문-제품 관계의 경우 RESTRICT가 더 합리적이다. 기존 주문에서 참조되는 제품을 실수로 삭제하고 싶지 않을 것이다.
외래 키의 성능 영향은 실제이지만 대개 과장된다. 예, 데이터베이스는 모든 삽입 및 업데이트 시 제약 조건을 확인한다. 하지만 데이터가 일관성 없게 될 때 발생하는 성능 재앙에 비하면 오버헤드는 미미하다. 게다가 쿼리 성능을 위해 어쨌든 외래 키 열에 인덱스가 필요하며, 동일한 인덱스가 제약 조건 확인을 빠르게 만든다.
명명 규칙: 유지 관리 가능한 스키마의 숨은 영웅
좋은 명명 규칙은 사소한 세부 사항처럼 보일 수 있지만, 작업하기 쉬운 스키마와 개발자를 미치게 만드는 스키마의 차이를 만든다. 일관성은 선택하는 특정 규칙보다 더 중요하지만 일부 규칙은 다른 규칙보다 더 잘 작동한다.
모든 것에 snake_case를 사용하자. camelCase도, PascalCase도, kebab-case도 아니다. snake_case는 모든 데이터베이스 시스템에서 작동하고, 읽기 쉬우며, SQL에서 따옴표를 사용할 필요가 없다. 이는 user_profiles와 같은 테이블 이름, first_name과 같은 열 이름, pk_users와 같은 제약 조건 이름을 의미한다.
테이블 이름은 복수형이어야 한다. 이것은 논란의 여지가 있으며 일부 개발자는 단수형을 선호한다. 나는 테이블이 엔티티의 모음이기 때문에 복수형을 사용한다. users 테이블에는 여러 사용자 레코드가 포함된다. orders 테이블에는 여러 주문이 포함된다. 이것은 대부분의 쿼리에서 자연스럽게 읽힌다. SELECT * FROM users WHERE age > 18은 "18세 이상인 사용자를 가져오기"처럼 읽힌다.
열 이름은 단수형이고 설명적이어야 한다. users 테이블의 first_name 열은 명확하다. 무엇을 포함하는지 알려주지 않는 name이나 value와 같은 일반적인 이름은 피하자. user_name과 같은 접두사를 추가하고 싶다면 해당 열이 잘못된 테이블에 있을 수 있다는 신호이다.
외래 키 열에는 참조된 테이블 이름을 포함해야 한다. 외래 키에 대해 그냥 id 대신 user_id, product_id, category_id를 사용하자. 이렇게 하면 조인이 자체 문서화된다. orders.user_id = users.id를 보면 즉시 관계를 이해할 수 있다.
부울 열은 예/아니오 질문으로 표현되어야 한다. is_active, has_discount, is_verified와 같은 이름을 사용하자. 이렇게 하면 쿼리가 자연스럽게 읽힌다. WHERE is_active = true가 WHERE active = true보다 명확하다.
타임스탬프 열은 무엇을 나타내는지 표시해야 한다. 그냥 created나 date가 아닌 created_at과 updated_at을 사용하자. _at 접미사는 이것이 타임스탬프임을 명확하게 한다. 날짜만 있는 필드의 경우 birth_date나 published_on과 같이 _on 또는 _date를 사용하자.
다음은 이러한 규칙을 실제로 보여주는 전체 예이다.
-- 규칙을 따르는 잘 명명된 스키마
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT true,
is_verified BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
slug VARCHAR(100) NOT NULL UNIQUE,
parent_category_id BIGINT REFERENCES categories(id), -- 자기 참조 FK
display_order INTEGER NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT true
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
category_id BIGINT NOT NULL REFERENCES categories(id),
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
is_available BOOLEAN NOT NULL DEFAULT true,
stock_quantity INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 목적을 명확하게 나타내는 제약 조건 이름
ALTER TABLE products
ADD CONSTRAINT chk_products_positive_price
CHECK (price >= 0),
ADD CONSTRAINT chk_products_non_negative_stock
CHECK (stock_quantity >= 0);
-- 무엇을 최적화하는지 명확하게 보여주는 명확한 이름의 인덱스
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_is_available ON products(is_available);
CREATE INDEX idx_users_email ON users(email);
이 스키마가 얼마나 읽기 쉬운지 주목하자. 어떤 테이블을 보든 즉시 어떤 데이터가 들어 있는지 이해할 수 있다. 외래 키는 명백하다. 제약 조건은 자체 문서화된다. 프로젝트에 새로 합류한 개발자는 암호 같은 약어나 일관성 없는 명명 패턴을 해독하는 데 시간을 낭비하지 않을 것이다.
제약 조건 이름에는 유형을 나타내는 접두사를 사용한다. pk_는 기본 키, fk_는 외래 키, uk_는 고유 제약 조건, chk_는 체크 제약 조건, idx_는 인덱스이다. 이렇게 하면 각 제약 조건이 무엇을 하는지 즉시 명확해지며, 디버깅하거나 데이터베이스에서 오류 메시지를 표시할 때 매우 유용하다.
시간과 타임스탬프를 올바르게 처리하기
시간은 보기보다 어렵다. 나는 타임스탬프 및 시간대와 관련된 버그를 스키마 설계의 거의 다른 어떤 측면보다 더 많이 보았다. 내가 겪었던 고통에서 당신을 구해주겠다.
첫 번째 규칙: 항상 타임스탬프를 UTC로 저장하자. 항상. 데이터베이스는 어떤 일이 일어난 객관적인 순간을 저장해야 하며, 그 일이 일어난 현지 시간을 저장해서는 안 된다. 사용자에게 표시할 때 애플리케이션 계층에서 현지 시간대로 변환할 수 있지만 데이터베이스는 시간대에 구애받지 않아야 한다.
두 번째 규칙: 날짜만 필요한지, 시간만 필요한지, 아니면 전체 타임스탬프가 필요한지 일찍 결정하자. 필요에 가장 적합한 가장 구체적인 유형을 사용하자. 누군가의 생년월일을 저장하는 경우 TIMESTAMP가 아닌 DATE를 원할 것이다. 대부분의 애플리케이션에서 그들이 하루 중 몇 시에 태어났는지는 신경 쓰지 않는다. 사용자가 로그인한 시간을 기록하는 경우 정확한 순간이 중요하기 때문에 TIMESTAMP가 필요하다.
세 번째 규칙: 모든 테이블에 항상 created_at 및 updated_at 타임스탬프를 포함시키자. 미래의 당신은 현재의 당신에게 감사할 것이다. 이러한 필드는 디버깅, 감사 및 데이터 기록을 이해하는 데 매우 유용하다. 항상 채워지도록 기본값이 있는 null을 허용하지 않도록 만들자.
이를 올바르게 구현하는 방법은 다음과 같다.
-- 적절한 타임스탬프 처리가 포함된 PostgreSQL 예제
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
-- 시스템에서 이벤트가 발생하는 시점에 TIMESTAMP 사용
event_date TIMESTAMP NOT NULL,
-- 원래 시간대를 보존해야 하는 경우 TIMESTAMPTZ 사용
scheduled_at TIMESTAMPTZ NOT NULL,
-- 시간 산술이 필요한 경우 기간을 간격으로 저장
duration INTERVAL NOT NULL DEFAULT '1 hour',
-- 모든 테이블에 있어야 하는 감사 필드
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 타임스탬프를 사용하는 소프트 삭제 패턴
deleted_at TIMESTAMP NULL
);
-- updated_at을 자동으로 업데이트하는 트리거
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_events_updated_at
BEFORE UPDATE ON events
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
deleted_at 필드는 중요한 패턴인 소프트 삭제를 도입한다. 실제로 행을 삭제하는 대신(감사를 불가능하게 하고 기록 쿼리를 깨뜨릴 수 있음) 레코드가 "삭제"된 시점을 나타내는 타임스탬프를 설정한다. 애플리케이션은 deleted_at 값이 null이 아닌 행을 삭제된 것으로 처리하지만 감사 및 복구를 위해 데이터베이스에 남아 있다.
시간 범위를 작업할 때 시작 및 종료 타임스탬프를 별도로 저장하고 싶을 수 있다. 그것도 작동하지만 PostgreSQL 및 일부 다른 데이터베이스는 강력한 쿼리 기능을 제공하는 범위 유형을 제공한다.
-- 기간에 범위 유형 사용
CREATE TABLE bookings (
id BIGSERIAL PRIMARY KEY,
room_id BIGINT NOT NULL REFERENCES rooms(id),
user_id BIGINT NOT NULL REFERENCES users(id),
-- 예약 기간을 범위로 저장
booking_period TSTZRANGE NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 동일한 방에 대한 중복 예약 방지
CONSTRAINT no_overlapping_bookings
EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&)
);
-- 범위로 쿼리하는 것은 우아하다
-- 특정 기간과 겹치는 모든 예약 찾기
SELECT * FROM bookings
WHERE booking_period && '[2025-10-01 00:00:00+00, 2025-10-07 00:00:00+00)'::tstzrange;
-- 특정 기간에 사용 가능한 방 찾기
SELECT * FROM rooms
WHERE id NOT IN (
SELECT room_id FROM bookings
WHERE booking_period && '[2025-10-01 00:00:00+00, 2025-10-07 00:00:00+00)'::tstzrange
);
여기서 EXCLUDE 제약 조건은 특히 강력하다. PostgreSQL의 GiST 인덱스를 사용하여 동일한 방에 대한 기존 예약과 겹치는 예약을 삽입하는 것을 방지한다. 이것은 그렇지 않으면 애플리케이션 코드에서 처리해야 하는 것이며, 여러 요청이 동시에 들어올 때 잘못 처리하기 쉽다.
외래 키를 넘어선 데이터 무결성 설계
외래 키는 데이터 무결성을 유지하는 시작에 불과하다. 데이터베이스는 제약 조건을 통해 많은 비즈니스 규칙을 직접 적용할 수 있으며, 그렇게 해야 한다. 데이터베이스에 인코딩하는 모든 규칙은 누군가 잘못된 애플리케이션 코드를 작성할 때 버그가 발생할 기회를 하나 줄여준다.
체크 제약 조건은 첫 번째 방어선이다. 열 값이 특정 기준을 충족하는지 확인한다. 항상 참이어야 하는 비즈니스 규칙에 자유롭게 사용하자.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
-- 가격은 양수여야 함
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
-- 할인율은 0에서 100 사이여야 함
discount_percentage INTEGER CHECK (discount_percentage BETWEEN 0 AND 100),
-- 재고 수량은 음수가 될 수 없음
stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
-- 상태는 이러한 특정 값 중 하나여야 함
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'active', 'discontinued', 'out_of_stock')),
-- 게시 날짜는 미래가 될 수 없음
published_at TIMESTAMP CHECK (published_at <= CURRENT_TIMESTAMP),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
고유 제약 조건은 의미 없는 중복 데이터를 방지한다. 때로는 고유성이 여러 열에 함께 의존한다.
CREATE TABLE course_enrollments (
id BIGSERIAL PRIMARY KEY,
student_id BIGINT NOT NULL REFERENCES students(id),
course_id BIGINT NOT NULL REFERENCES courses(id),
enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 학생은 같은 과정에 두 번 등록할 수 없음
CONSTRAINT uk_student_course UNIQUE (student_id, course_id)
);
CREATE TABLE product_reviews (
id BIGSERIAL PRIMARY KEY,
product_id BIGINT NOT NULL REFERENCES products(id),
user_id BIGINT NOT NULL REFERENCES users(id),
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 사용자는 각 제품을 한 번만 리뷰할 수 있음
CONSTRAINT uk_user_product_review UNIQUE (product_id, user_id)
);
부분 고유 인덱스를 사용하면 조건부로 고유성을 강제할 수 있다. 이는 한 행만 "기본" 또는 "기본값"으로 표시되도록 보장하는 것과 같은 패턴에 매우 유용하다.
CREATE TABLE user_addresses (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
address_line1 VARCHAR(255) NOT NULL,
address_line2 VARCHAR(255),
city VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
is_primary BOOLEAN NOT NULL DEFAULT false
);
-- 사용자당 하나의 주소만 기본 주소일 수 있음
CREATE UNIQUE INDEX idx_user_addresses_primary
ON user_addresses (user_id)
WHERE is_primary = true;
이 인덱스는 주어진 user_id에 대해 is_primary = true인 행이 하나만 있을 수 있도록 보장한다. 첫 번째 주소를 해제하지 않고 두 번째 주소를 기본 주소로 설정하려고 하면 데이터베이스가 작업을 거부한다. 이렇게 하면 기본 주소를 관리하기 위한 복잡한 애플리케이션 논리를 구현할 필요가 없다.
때로는 간단한 제약 조건이 표현할 수 있는 것보다 더 복잡한 규칙이 필요하다. 이때 트리거가 사용된다. 스키마를 이해하기 어렵게 만들 수 있으므로 드물게 사용해야 하지만 특정 작업을 자동화하는 데 유용하다.
-- 주문 총액 자동 계산
CREATE OR REPLACE FUNCTION calculate_order_total()
RETURNS TRIGGER AS $$
BEGIN
NEW.total_amount := (
SELECT COALESCE(SUM(quantity * unit_price), 0)
FROM order_items
WHERE order_id = NEW.id
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_order_total
AFTER INSERT OR UPDATE ON order_items
FOR EACH ROW
EXECUTE FUNCTION calculate_order_total();
이 트리거는 항목이 추가되거나 업데이트될 때마다 주문 총액을 다시 계산한다. 애플리케이션 코드에서 이를 계산할 수도 있지만 데이터베이스 수준에 있으면 어떤 애플리케이션이나 스크립트가 데이터를 수정하든 항상 정확하다.
데이터베이스 스키마를 위한 버전 관리
데이터베이스 스키마는 코드이다. 버전 관리에 있어야 하고, 배포 전에 검토해야 하며, 애플리케이션 코드와 동일한 품질 표준을 가져야 한다. 하지만 애플리케이션 코드와 달리 데이터베이스 스키마는 그냥 다시 배포할 수 없다. 이전 버전에서 새 버전으로 마이그레이션해야 하며, 해당 마이그레이션은 프로덕션 데이터에서 작동해야 한다.
마이그레이션 기반 접근 방식이 업계 표준이다. 스키마를 비교하여 마이그레이션을 자동으로 생성하려고 시도하는 대신, 버전 N에서 버전 N+1로 가는 방법을 설명하는 명시적인 마이그레이션을 작성한다. 각 마이그레이션은 "up"(변경 적용)과 "down"(변경 되돌리기)의 두 부분으로 구성된 스크립트이다.
마이그레이션을 구성하는 방법은 다음과 같다.
-- migrations/001_create_users_table.sql
-- UP
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_users_email ON users(email);
-- DOWN
DROP TABLE IF EXISTS users CASCADE;
-- migrations/002_add_user_roles.sql
-- UP
CREATE TABLE roles (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
description TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_roles (
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id)
);
-- 기본 역할 삽입
INSERT INTO roles (name, description) VALUES
('admin', 'Full system access'),
('user', 'Standard user access'),
('guest', 'Limited read-only access');
-- DOWN
DROP TABLE IF EXISTS user_roles CASCADE;
DROP TABLE IF EXISTS roles CASCADE;
마이그레이션에 순차적으로 번호를 매기자. 설명적인 이름을 사용하자. 필요한 경우 롤백할 수 있도록 양방향(up 및 down)을 모두 포함하자. down 마이그레이션이 실제로 작동하는지 테스트하자.
Flyway, Liquibase 또는 언어별 솔루션(Node의 경우 Knex.js, Python의 경우 Alembic 등)과 같은 도구는 이러한 마이그레이션을 순서대로 적용하고 어떤 마이그레이션이 실행되었는지 추적한다. 이를 기록하기 위해 데이터베이스에 마이그레이션 기록 테이블을 만든다.
프로덕션 마이그레이션의 중요한 원칙은 가능한 경우 변경 사항을 하위 호환 가능하게 만드는 것이다. 열을 삭제하고 다시 만드는 대신, 새 열을 추가하고, 데이터를 마이그레이션하고, 애플리케이션 코드를 업데이트한 다음, 나중에 마이그레이션에서 이전 열을 제거하자. 이렇게 하면 다운타임 없이 데이터베이스 변경 사항을 배포할 수 있다.
-- Bad: 다운타임이 필요한 주요 변경
-- migration_003_bad.sql
ALTER TABLE users RENAME COLUMN first_name TO full_name;
-- Good: 여러 마이그레이션에 걸친 비파괴적 변경
-- migration_003_add_full_name.sql
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
-- 기존 데이터 업데이트
UPDATE users SET full_name = first_name || ' ' || last_name;
-- 데이터 채워진 후 null을 허용하지 않도록 설정
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
-- migration_004_remove_name_parts.sql (애플리케이션 코드 업데이트 후 배포)
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;
마이그레이션 003과 004 사이에서 애플리케이션 코드는 이전 열 또는 새 열로 작업할 수 있다. 즉, 데이터베이스 변경을 배포하고, 작동하는지 확인하고, 애플리케이션 코드 변경을 배포하고, 작동하는지 확인한 다음, 최종 정리 마이그레이션을 배포할 수 있다.
팀의 경우 스키마 변경에 대한 명확한 프로세스를 설정하자. 모든 변경 사항은 코드 검토를 거쳐야 한다. 프로덕션에 배포하기 전에 스테이징에서 프로덕션 데이터의 복사본에 대해 마이그레이션을 실행하자. 모든 마이그레이션에 대한 롤백 계획을 세우자. 주요 변경 사항을 눈에 띄게 문서화하자.
스키마를 위한 인덱싱 전략
인덱스는 쿼리를 빠르게 만들지만 쓰기 속도를 늦추고 디스크 공간을 사용한다. 어떤 인덱스를 만들지 아는 것이 기술이다. 명백한 것부터 시작한 다음, 관찰하는 실제 쿼리 패턴에 따라 더 많이 추가하자.
항상 외래 키 열에 인덱스를 생성하자. 항상. 대부분의 쿼리는 이러한 열에서 테이블을 조인하며, 인덱스가 없으면 이러한 조인은 성능을 저하시키는 테이블 스캔이 된다.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) NOT NULL
);
-- 외래 키 인덱스 (중요)
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- 쿼리 필터 인덱스
CREATE INDEX idx_orders_status ON orders(status);
-- 일반적인 쿼리 패턴을 위한 복합 인덱스
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
-- 시간 기반 쿼리 (대시보드에서 일반적)
CREATE INDEX idx_orders_order_date ON orders(order_date DESC);
복합 인덱스 idx_orders_customer_status는 고객별 또는 고객 및 상태별로 필터링하는 쿼리를 충족할 수 있다. 상태별로만 필터링하는 쿼리를 효율적으로 충족할 수는 없다(이를 위해서는 상태 전용 인덱스가 필요하다). 복합 인덱스의 열 순서가 중요하다. 가장 선택적인 열을 먼저 배치하고, 필터링하는 열을 정렬만 하는 열보다 먼저 배치하자.
텍스트 검색의 경우 일반 인덱스는 별로 도움이 되지 않는다. PostgreSQL은 이를 위한 특수 인덱스를 제공한다.
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
content TEXT NOT NULL,
published_at TIMESTAMP NOT NULL
);
-- 전체 텍스트 검색을 위한 GIN 인덱스
CREATE INDEX idx_articles_content_search
ON articles
USING GIN (to_tsvector('english', title || ' ' || content));
-- 전체 텍스트 검색으로 쿼리
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database & design');
부분 인덱스는 특정 쿼리 패턴을 최적화하는 데 강력하다. 활성 사용자에 대한 쿼리는 자주 하지만 비활성 사용자에 대한 쿼리는 거의 하지 않는 경우 활성 사용자만 인덱싱하자.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT true,
last_login TIMESTAMP
);
-- 활성 사용자만을 위한 부분 인덱스
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;
-- 최근에 로그인하지 않은 사용자를 위한 부분 인덱스
CREATE INDEX idx_users_inactive ON users(id)
WHERE is_active = true AND (last_login < CURRENT_TIMESTAMP - INTERVAL '30 days' OR last_login IS NULL);
이러한 부분 인덱스는 WHERE 조건을 충족하는 행만 포함하므로 전체 인덱스보다 작고 빠르다. 쿼리 최적화 프로그램은 적절한 경우 자동으로 이를 사용한다.
시간이 지남에 따라 인덱스를 모니터링하자. 사용되지 않는 인덱스는 공간을 낭비하고 쓰기 속도를 늦춘다. 대부분의 데이터베이스에서는 인덱스 사용 통계를 확인할 수 있다. 사용되지 않는 인덱스는 삭제하자.
-- 사용되지 않는 인덱스를 찾는 PostgreSQL 쿼리
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE 'pk_%' -- 기본 키는 삭제하지 말 것!
ORDER BY schemaname, tablename;
계층적 데이터 처리
계층 구조는 어디에나 나타난다. 카테고리 트리, 조직도, 댓글 스레드, 파일 시스템. 관계형 데이터베이스에서 이를 모델링하는 방법에는 여러 가지가 있으며, 각각 다른 장단점이 있다.
인접 목록은 가장 간단한 접근 방식이다. 각 행은 부모에 대한 참조를 저장한다.
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id BIGINT REFERENCES categories(id),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 샘플 계층 구조 삽입:
-- Electronics
-- Computers
-- Laptops
-- Desktops
-- Phones
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Desktops', 2),
(5, 'Phones', 1);
이것은 이해하고 수정하기 쉽다. 하위 트리를 이동하는 것은 하나의 parent_id를 업데이트하는 것을 의미한다. 단점은 전체 트리나 모든 조상을 쿼리하려면 재귀 쿼리가 필요하며, 이는 느릴 수 있다는 것이다.
-- 재귀 CTE를 사용하여 "Laptops"의 모든 조상 가져오기
WITH RECURSIVE ancestors AS (
SELECT id, name, parent_id FROM categories WHERE id = 3
UNION ALL
SELECT c.id, c.name, c.parent_id
FROM categories c
INNER JOIN ancestors a ON a.parent_id = c.id
)
SELECT * FROM ancestors;
-- "Computers" 아래의 전체 하위 트리 가져오기
WITH RECURSIVE subtree AS (
SELECT id, name, parent_id FROM categories WHERE id = 2
UNION ALL
SELECT c.id, c.name, c.parent_id
FROM categories c
INNER JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;
계층 구조를 수정하는 것보다 더 많이 읽는 경우 구체화된 경로 접근 방식이 더 잘 작동한다. 각 노드에 대한 전체 경로를 문자열로 저장하자.
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
path VARCHAR(500) NOT NULL UNIQUE, -- 예: 중첩된 ID의 경우 "1.2.3"
level INTEGER NOT NULL, -- 트리 내 깊이 (0 = 루트)
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_categories_path ON categories(path);
-- 동일한 계층 구조 삽입
INSERT INTO categories (id, name, path, level) VALUES
(1, 'Electronics', '1', 0),
(2, 'Computers', '1.2', 1),
(3, 'Laptops', '1.2.3', 2),
(4, 'Desktops', '1.2.4', 2),
(5, 'Phones', '1.5', 1);
-- "Laptops"의 모든 조상 가져오기 (훨씬 빠름)
SELECT * FROM categories
WHERE '1.2.3' LIKE path || '%'
ORDER BY level;
-- "Computers" 아래의 전체 하위 트리 가져오기 (또한 훨씬 빠름)
SELECT * FROM categories
WHERE path LIKE '1.2%'
ORDER BY path;
재귀가 필요 없기 때문에 쿼리가 훨씬 빠르다. 단점은 하위 트리를 이동하려면 모든 하위 노드에 대한 경로를 업데이트해야 한다는 것이다. 제품 카테고리와 같이 비교적 안정적인 계층 구조의 경우 이 절충안은 일반적으로 의미가 있다.
중첩 집합 모델은 읽기 집약적인 워크로드에는 훌륭하지만 유지 관리가 복잡한 또 다른 옵션이다. 각 노드에 깊이 우선 순회에서의 위치를 나타내는 왼쪽 및 오른쪽 값을 할당한다.
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
lft INTEGER NOT NULL,
rgt INTEGER NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT check_lft_rgt CHECK (lft < rgt)
);
CREATE INDEX idx_categories_lft ON categories(lft);
CREATE INDEX idx_categories_rgt ON categories(rgt);
-- 중첩 집합 값이 있는 동일한 계층 구조
INSERT INTO categories (id, name, lft, rgt) VALUES
(1, 'Electronics', 1, 10),
(2, 'Computers', 2, 7),
(3, 'Laptops', 3, 4),
(4, 'Desktops', 5, 6),
(5, 'Phones', 8, 9);
-- 모든 조상 가져오기 (매우 빠름)
SELECT * FROM categories
WHERE 3 BETWEEN lft AND rgt
ORDER BY lft;
-- 전체 하위 트리 가져오기 (또한 매우 빠름)
SELECT * FROM categories
WHERE lft BETWEEN 2 AND 7
ORDER BY lft;
중첩 집합 모델은 읽기를 엄청나게 빠르게 만들지만 삽입 및 삭제에는 많은 노드의 번호를 다시 매겨야 한다. 나는 일반적으로 탐색 메뉴나 분류 체계와 같이 거의 변경되지 않지만 지속적으로 쿼리되는 계층 구조에만 권장한다.
자주 업데이트되는 댓글 스레드와 같은 것의 경우 인접 목록을 고수하고 재귀 쿼리의 비용을 감수하자. 최신 데이터베이스는 특히 적절한 인덱스를 사용하여 이를 잘 처리한다.
다형성 연관 처리
때로는 여러 다른 테이블과 관련될 수 있는 테이블이 필요하다. 예를 들어, 블로그 게시물과 사진 모두에 나타날 수 있는 댓글이나 다양한 콘텐츠 유형에 첨부될 수 있는 태그가 있다.
데이터베이스 순수주의자 접근 방식은 각 연관에 대해 별도의 테이블을 만드는 것이다.
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
content TEXT NOT NULL
);
CREATE TABLE photos (
id BIGSERIAL PRIMARY KEY,
url VARCHAR(500) NOT NULL,
caption TEXT
);
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
content TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 각 관계에 대한 별도의 조인 테이블
CREATE TABLE post_comments (
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
comment_id BIGINT NOT NULL REFERENCES comments(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, comment_id)
);
CREATE TABLE photo_comments (
photo_id BIGINT NOT NULL REFERENCES photos(id) ON DELETE CASCADE,
comment_id BIGINT NOT NULL REFERENCES comments(id) ON DELETE CASCADE,
PRIMARY KEY (photo_id, comment_id)
);
이 접근 방식은 유형에 안전하며 적절한 외래 키를 사용할 수 있다. 단점은 모든 댓글 유형에 대해 새 테이블이 필요하고 사용자에 대한 모든 댓글을 쿼리하려면 모든 조인 테이블에서 UNION 쿼리가 필요하다는 것이다.
실용적인 접근 방식은 유형 판별자가 있는 단일 다형성 테이블을 사용하는 것이다.
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
commentable_type VARCHAR(50) NOT NULL, -- 'post' 또는 'photo'
commentable_id BIGINT NOT NULL, -- 해당 테이블의 ID
content TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 유형이 허용된 값 중 하나인지 확인
CONSTRAINT check_commentable_type
CHECK (commentable_type IN ('post', 'photo'))
);
-- 효율적인 조회를 위한 복합 인덱스
CREATE INDEX idx_comments_commentable
ON comments(commentable_type, commentable_id);
-- 특정 게시물의 모든 댓글 쿼리
SELECT * FROM comments
WHERE commentable_type = 'post' AND commentable_id = 123;
여기서 단점은 참조 무결성을 잃는다는 것이다. 데이터베이스는 commentable_id가 실제로 posts 또는 photos 테이블에 존재하는지 강제할 수 없다. 일관성을 유지하기 위해 애플리케이션 코드에 의존하고 있다.
체크 제약 조건 및 의사 외래 키 역할을 하는 부분 인덱스를 사용하여 두 가지 접근 방식의 이점을 일부 얻을 수 있다.
-- 가능한 모든 부모에 대한 열 추가
ALTER TABLE comments
ADD COLUMN post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
ADD COLUMN photo_id BIGINT REFERENCES photos(id) ON DELETE CASCADE;
-- 정확히 하나의 부모가 설정되었는지 확인
ALTER TABLE comments
ADD CONSTRAINT check_exactly_one_parent
CHECK (
(post_id IS NOT NULL AND photo_id IS NULL) OR
(post_id IS NULL AND photo_id IS NOT NULL)
);
-- commentable_type 및 commentable_id 자동 설정
CREATE OR REPLACE FUNCTION set_commentable_fields()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.post_id IS NOT NULL THEN
NEW.commentable_type := 'post';
NEW.commentable_id := NEW.post_id;
ELSIF NEW.photo_id IS NOT NULL THEN
NEW.commentable_type := 'photo';
NEW.commentable_id := NEW.photo_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_commentable_fields_trigger
BEFORE INSERT OR UPDATE ON comments
FOR EACH ROW
EXECUTE FUNCTION set_commentable_fields();
이렇게 하면 쿼리에 대한 유형/ID 패턴의 편의성을 유지하면서 적절한 외래 키 제약 조건을 제공한다. 설정하기는 더 복잡하지만 더 간단한 다형성 설계에서 발생하는 데이터 불일치를 방지한다.
내 권장 사항: 유형이 두세 개뿐인 경우 명시적 외래 키 접근 방식을 사용하자. 유형이 많거나 시간이 지남에 따라 유형이 늘어날 경우 무결성을 유지하기 위해 가능한 한 많은 제약 조건이 있는 다형성 패턴을 사용하자.
다중 테넌시를 위한 스키마 설계
여러 고객이 동일한 시스템을 공유하는 다중 테넌트 애플리케이션에는 신중한 스키마 설계가 필요하다. 각각 다른 격리 및 확장 특성을 가진 세 가지 주요 접근 방식이 있다.
테넌트 ID가 있는 공유 스키마가 가장 간단하다. 모든 테이블에 tenant_id 열이 생긴다.
CREATE TABLE tenants (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
subdomain VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
tenant_id BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
email VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 이메일은 테넌트 내에서 고유해야 함
CONSTRAINT uk_users_tenant_email UNIQUE (tenant_id, email)
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
tenant_id BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 중요: 모든 테이블에 tenant_id 인덱스 생성
CREATE INDEX idx_users_tenant_id ON users(tenant_id);
CREATE INDEX idx_products_tenant_id ON products(tenant_id);
모든 쿼리는 tenant_id로 필터링해야 한다. 필터를 잊어버리면 한 테넌트의 데이터가 다른 테넌트에게 노출되기 때문에 오류가 발생하기 쉽다. 행 수준 보안을 사용하여 데이터베이스 수준에서 이를 강제하자.
-- users 테이블에서 행 수준 보안 활성화
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- tenant_id로 필터링하는 정책 생성
-- 애플리케이션이 current_tenant_id 세션 변수를 설정한다고 가정
CREATE POLICY tenant_isolation_policy ON users
USING (tenant_id = current_setting('app.current_tenant_id')::BIGINT);
-- 애플리케이션에서 각 세션에 대한 테넌트 설정
-- 이 예는 개념을 보여줌 (실제 구문은 드라이버에 따라 다름)
-- SET LOCAL app.current_tenant_id = 123;
이제 애플리케이션 코드가 테넌트로 필터링하는 것을 잊어버려도 데이터베이스가 이를 강제한다. 이 공유 스키마 접근 방식은 간단하고 효율적이지만 제한된 격리를 제공한다. 한 테넌트의 무거운 쿼리가 다른 테넌트에 영향을 미칠 수 있다.
테넌트당 스키마는 더 나은 격리를 제공한다. 각 테넌트는 데이터베이스 내에 자체 스키마를 갖는다.
-- 각 테넌트에 대한 스키마 생성
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_widgetco;
-- 각 스키마에는 동일한 테이블 구조가 있음
CREATE TABLE tenant_acme.users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE tenant_widgetco.users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 애플리케이션은 테넌트에 따라 스키마를 전환
-- SET search_path = tenant_acme;
-- SELECT * FROM users; // tenant_acme.users 쿼리
이 접근 방식은 데이터를 더 잘 격리하고 개별 테넌트를 백업하거나 복원하기 쉽게 만든다. 단점은 많은 동일한 스키마를 관리하고 모든 스키마에 대해 마이그레이션을 실행하는 것이다. 또한 각 테넌트가 자체 연결이 필요할 수 있으므로 데이터베이스 연결 제한에 더 빨리 도달한다.
테넌트당 데이터베이스는 최대 격리를 제공한다.
-- 각 테넌트는 자체 데이터베이스를 가짐
CREATE DATABASE tenant_acme;
CREATE DATABASE tenant_widgetco;
-- 테넌트에 따라 적절한 데이터베이스에 연결
-- 애플리케이션은 테넌트당 연결 풀을 유지
이것은 가장 강력한 격리, 가장 쉬운 백업/복원, 그리고 테넌트를 다른 서버로 이동할 수 있는 기능을 제공한다. 하지만 마이그레이션 및 교차 테넌트 쿼리(관리 대시보드와 같은)에 대해 관리하기가 가장 복잡하다.
대부분의 애플리케이션이 시작할 때 행 수준 보안이 있는 공유 스키마 접근 방식을 권장한다. 간단하고 효율적이며, 더 강력한 격리가 필요한 경우 나중에 테넌트당 스키마 또는 데이터베이스로 마이그레이션할 수 있다. 출시하기 전에 테넌트 필터링에 대한 좋은 테스트 커버리지를 확보해야 한다.
소프트 삭제 및 감사 추적 처리
하드 삭제는 데이터를 영구적으로 제거한다. 소프트 삭제는 데이터를 삭제된 것으로 표시하지만 데이터베이스에 보관한다. 각 접근 방식에는 사용 사례가 있지만 소프트 삭제는 비즈니스 애플리케이션에서 점점 더 표준이 되고 있다.
기본 소프트 삭제 패턴은 deleted_at 타임스탬프를 추가한다.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL -- NULL은 삭제되지 않았음을 의미
);
-- 삭제된 레코드 필터링을 위한 인덱스
CREATE INDEX idx_products_not_deleted ON products(id) WHERE deleted_at IS NULL;
-- 대부분의 쿼리는 삭제된 레코드를 제외
SELECT * FROM products WHERE deleted_at IS NULL;
-- 타임스탬프를 설정하여 "삭제"
UPDATE products SET deleted_at = CURRENT_TIMESTAMP WHERE id = 123;
-- 타임스탬프를 지워 복원
UPDATE products SET deleted_at = NULL WHERE id = 123;
이것은 간단하지만 외래 키 및 고유 제약 조건에 영향을 미친다. 제품이 소프트 삭제된 경우 동일한 SKU로 새 제품을 만들 수 있을까? 아마도 아닐 것이다. 부분 고유 인덱스를 사용하자.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(100) NOT NULL,
name VARCHAR(255) NOT NULL,
deleted_at TIMESTAMP NULL
);
-- SKU는 삭제되지 않은 제품 중에서 고유해야 함
CREATE UNIQUE INDEX uk_products_sku
ON products(sku)
WHERE deleted_at IS NULL;
외래 키 처리가 더 까다로워진다. 카테고리를 삭제하면 모든 제품이 소프트 삭제되어야 할까? 제품이 있는 카테고리를 삭제하는 것을 막아야 할까?
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
deleted_at TIMESTAMP NULL
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
category_id BIGINT NOT NULL REFERENCES categories(id),
name VARCHAR(255) NOT NULL,
deleted_at TIMESTAMP NULL,
-- 삭제되지 않은 제품이 있는 카테고리 삭제 방지
CONSTRAINT check_category_not_deleted
CHECK (
deleted_at IS NOT NULL OR
NOT EXISTS (
SELECT 1 FROM categories
WHERE id = category_id AND deleted_at IS NOT NULL
)
)
);
완전한 감사 추적을 위해 누가 무엇을 언제 변경했는지 추적하자. 모든 테이블에 감사 열을 추가하자.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
-- 이 레코드를 만든 사람
created_by BIGINT NOT NULL REFERENCES users(id),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 이 레코드를 마지막으로 업데이트한 사람
updated_by BIGINT REFERENCES users(id),
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 이 레코드를 삭제한 사람 (소프트 삭제된 경우)
deleted_by BIGINT REFERENCES users(id),
deleted_at TIMESTAMP NULL
);
완전한 기록 추적을 위해 모든 변경 사항을 기록하는 별도의 감사 테이블을 사용하자.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products_audit (
audit_id BIGSERIAL PRIMARY KEY,
product_id BIGINT NOT NULL,
action VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
changed_by BIGINT NOT NULL REFERENCES users(id),
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- 변경 시점의 모든 제품 필드 스냅샷
name VARCHAR(255),
price DECIMAL(10, 2)
);
-- 감사 테이블을 채우는 트리거
CREATE OR REPLACE FUNCTION audit_product_changes()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO products_audit (product_id, action, changed_by, name, price)
VALUES (OLD.id, 'DELETE', current_setting('app.current_user_id')::BIGINT,
OLD.name, OLD.price);
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO products_audit (product_id, action, changed_by, name, price)
VALUES (NEW.id, 'UPDATE', current_setting('app.current_user_id')::BIGINT,
NEW.name, NEW.price);
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO products_audit (product_id, action, changed_by, name, price)
VALUES (NEW.id, 'INSERT', current_setting('app.current_user_id')::BIGINT,
NEW.name, NEW.price);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER product_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION audit_product_changes();
이렇게 하면 완전한 기록을 얻을 수 있다. 누가 무엇을 언제 변경했는지 쿼리하거나 특정 시점의 레코드가 어떻게 생겼는지 재구성할 수도 있다. 비용은 추가 저장 공간과 쓰기 오버헤드이지만 비즈니스에 중요한 데이터의 경우 절충안은 일반적으로 가치가 있다.
JSON 및 반구조화된 데이터
최신 데이터베이스는 JSON 열을 지원하므로 구조화된 데이터와 함께 유연하고 스키마 없는 데이터를 저장할 수 있다. 이것은 강력하지만 오용하기 쉽다.
각 레코드에 다른 필드가 있을 수 있는 진정으로 가변적인 데이터 구조에 JSON을 사용하자.
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
-- 유형에 따라 다른 제품별 속성
attributes JSONB NOT NULL DEFAULT '{}'::JSONB,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 전자제품에는 이러한 속성이 있을 수 있음
INSERT INTO products (name, price, attributes) VALUES
('Laptop', 999.99, '{
"brand": "TechCorp",
"screenSize": "15.6",
"processor": "Intel i7",
"ram": "16GB",
"storage": "512GB SSD"
}'::JSONB);
-- 의류에는 완전히 다른 속성이 있음
INSERT INTO products (name, price, attributes) VALUES
('T-Shirt', 19.99, '{
"size": "L",
"color": "Blue",
"material": "Cotton",
"washInstructions": "Machine wash cold"
}'::JSONB);
-- JSON 필드로 쿼리 (성능을 위해 GIN 인덱스 필요)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
SELECT * FROM products
WHERE attributes @> '{"brand": "TechCorp"}'::JSONB;
SELECT * FROM products
WHERE attributes->>'color' = 'Blue';
JSONB 유형(바이너리 JSON)은 거의 항상 JSON보다 낫다. 쿼리가 더 빠르고 인덱싱을 지원한다
정확한 형식이나 키 순서를 유지해야 하는 경우에만 JSON을 사용하자
단순히 가능하다고 해서 모든 것을 JSON에 담지 말자.
이 필드를 쿼리에 사용하나? 유효성 검사 규칙이 있나? 둘 중 하나라도 있다면 해당 필드는 적절한 Column으로 있어야 할 것이다.
-- Bad: everything in JSON
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
data JSONB NOT NULL
);
-- Good: structured data in columns, flexible data in JSON
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE, -- Needs uniqueness constraint
first_name VARCHAR(100) NOT NULL, -- Will be queried often
last_name VARCHAR(100) NOT NULL, -- Will be queried often
is_active BOOLEAN NOT NULL DEFAULT true, -- Has business rules
-- Optional profile data that varies by user type
profile_data JSONB DEFAULT '{}'::JSONB,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
JSON은 개별 항목을 효율적으로 쿼리할 필요가 없을 때 값 배열을 저장하는 데도 유용하다.
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
content TEXT NOT NULL,
-- Tags as a simple array
tags TEXT[] NOT NULL DEFAULT '{}',
-- Complex metadata as JSONB
metadata JSONB DEFAULT '{}'::JSONB
);
-- Query by array containment
SELECT * FROM articles WHERE tags @> ARRAY['database', 'design'];
-- Query by JSON field
SELECT * FROM articles WHERE metadata->>'category' = 'tutorial';
시간이 지남에 따라 구조가 변경되는 데이터의 경우 JSON을 사용하면 지속적인 스키마 마이그레이션을 방지할 수 있다. 하지만 JSON 구조를 어딘가에 문서화하고, 애플리케이션 코드에서 유효성을 검사하고, 데이터베이스에서 지원하는 경우 JSON 스키마 유효성 검사 함수를 고려해야 할 것이다.
Putting It All Together: A Complete Example
위에서 본 내용을 종합한 Schema를 보자면 다음과 같다.
-- Start with lookup/reference tables
CREATE TABLE roles (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
description TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Core entity: users with proper constraints
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT true,
is_email_verified BOOLEAN NOT NULL DEFAULT false,
-- Audit fields
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL
);
CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_active ON users(is_active) WHERE deleted_at IS NULL;
-- User roles (many-to-many)
CREATE TABLE user_roles (
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
assigned_by BIGINT REFERENCES users(id),
PRIMARY KEY (user_id, role_id)
);
-- Courses with proper constraints
CREATE TABLE courses (
id BIGSERIAL PRIMARY KEY,
instructor_id BIGINT NOT NULL REFERENCES users(id),
title VARCHAR(500) NOT NULL,
description TEXT,
difficulty_level VARCHAR(20) NOT NULL DEFAULT 'beginner'
CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced')),
max_students INTEGER CHECK (max_students > 0),
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
is_published BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL
);
CREATE INDEX idx_courses_instructor ON courses(instructor_id);
CREATE INDEX idx_courses_published ON courses(is_published) WHERE deleted_at IS NULL;
-- Lessons using materialized path for hierarchy
CREATE TABLE lessons (
id BIGSERIAL PRIMARY KEY,
course_id BIGINT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL,
content TEXT NOT NULL,
video_url VARCHAR(500),
duration_minutes INTEGER CHECK (duration_minutes > 0),
-- For ordering and hierarchy
path VARCHAR(500) NOT NULL,
display_order INTEGER NOT NULL DEFAULT 0,
-- Allow JSON for flexible lesson metadata
metadata JSONB DEFAULT '{}'::JSONB,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_lessons_course ON lessons(course_id);
CREATE INDEX idx_lessons_path ON lessons(path);
CREATE INDEX idx_lessons_order ON lessons(course_id, display_order);
-- Course enrollments with business rules
CREATE TABLE enrollments (
id BIGSERIAL PRIMARY KEY,
student_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
course_id BIGINT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
completed_at TIMESTAMP NULL,
progress_percentage INTEGER NOT NULL DEFAULT 0
CHECK (progress_percentage BETWEEN 0 AND 100),
-- Student can only enroll once per course
CONSTRAINT uk_enrollment_student_course UNIQUE (student_id, course_id)
);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
-- Assignments with proper relationships
CREATE TABLE assignments (
id BIGSERIAL PRIMARY KEY,
lesson_id BIGINT NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL,
description TEXT NOT NULL,
due_date TIMESTAMP NOT NULL,
max_points INTEGER NOT NULL CHECK (max_points > 0),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_assignments_lesson ON assignments(lesson_id);
-- Student submissions with audit trail
CREATE TABLE submissions (
id BIGSERIAL PRIMARY KEY,
assignment_id BIGINT NOT NULL REFERENCES assignments(id) ON DELETE CASCADE,
student_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
content TEXT NOT NULL,
submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- Grading information
points_earned DECIMAL(5, 2) NULL,
feedback TEXT NULL,
graded_by BIGINT REFERENCES users(id),
graded_at TIMESTAMP NULL,
-- Student can submit once per assignment (can be updated)
CONSTRAINT uk_submission_assignment_student
UNIQUE (assignment_id, student_id),
-- Points can't exceed max points
CONSTRAINT check_points_valid
CHECK (points_earned IS NULL OR
points_earned >= 0 AND
points_earned <= (SELECT max_points FROM assignments WHERE id = assignment_id))
);
CREATE INDEX idx_submissions_assignment ON submissions(assignment_id);
CREATE INDEX idx_submissions_student ON submissions(student_id);
-- Polymorphic comments (can comment on courses or lessons)
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
course_id BIGINT REFERENCES courses(id) ON DELETE CASCADE,
lesson_id BIGINT REFERENCES lessons(id) ON DELETE CASCADE,
parent_comment_id BIGINT REFERENCES comments(id) ON DELETE CASCADE,
content TEXT NOT NULL,
is_instructor_reply BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
-- Must comment on either a course or lesson, not both
CONSTRAINT check_comment_target
CHECK (
(course_id IS NOT NULL AND lesson_id IS NULL) OR
(course_id IS NULL AND lesson_id IS NOT NULL)
)
);
CREATE INDEX idx_comments_user ON comments(user_id);
CREATE INDEX idx_comments_course ON comments(course_id) WHERE course_id IS NOT NULL;
CREATE INDEX idx_comments_lesson ON comments(lesson_id) WHERE lesson_id IS NOT NULL;
CREATE INDEX idx_comments_parent ON comments(parent_comment_id) WHERE parent_comment_id IS NOT NULL;
-- Triggers for updated_at timestamps
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_users_timestamp BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_courses_timestamp BEFORE UPDATE ON courses
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
CREATE TRIGGER update_lessons_timestamp BEFORE UPDATE ON lessons
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
이 스키마에서 보여주는 것을 요약하면 다음과 같다.
- 전반적으로 명확한 이름 규칙(Naming conventions) 사용
- 참조 무결성을 갖춘 적절한 기본 키(Primary keys) 및 외래 키(Foreign keys) 설정
- 비즈니스 규칙 적용을 위한 Check 제약 조건
- 필요한 곳에 설정된 Unique(고유) 제약 조건
- 전략적인 인덱스(Indexes) 사용
- 타임스탬프를 포함한 감사 추적(Audit trails)
- 소프트 삭제(Soft delete) 기능
- 적절하게 구현된 다형성 연관(Polymorphic associations)
- 단순 외래 키와 복합 고유 제약 조건 모두 사용
- 진정한 유연성을 제공하는 JSONB 데이터 타입 활용
피해야 할 일반적인 실수 (Common Pitfalls)
숙련된 개발자조차도 이러한 실수를 한다. 이 실수들로부터 배워서 동일한 실수를 반복하지 않도록 하는 것이다.
- 길이 제한 없이 VARCHAR 사용하기 항상 최대 길이를 지정해야 한다. VARCHAR(255)는 합리적인 제한에 대해 고민하게 만든다. 길이 제한 없는 VARCHAR는 TEXT가 되는데, 이는 원하는 바가 아닐 수 있으며 쿼리 속도를 저하시키는 거대한 문자열을 실수로 저장하는 원인이 될 수 있다.
- NULL 처리 계획을 세우지 않기 모든 컬럼은 비즈니스 규칙에 따라 명시적으로 NOT NULL 또는 NULL이 되어야 한다. 컬럼을 기본적으로 'nullable'(NULL을 허용)하게 만들면, 모든 곳에서 NULL을 확인하는 방어적인 코딩을 하게 된다. 대부분의 컬럼은 적절한 기본값(default)과 함께 NOT NULL이 되어야 한다.
- 과도한 정규화 또는 부족한 정규화 양 극단은 모두 문제를 일으킨다. 과도한 정규화는 간단한 쿼리에도 10개의 테이블을 조인(join)하게 만든다. 부족한 정규화는 데이터 중복과 업데이트 이상(update anomalies)을 초래하기도 한다. 본인의 사용 사례에 맞는 균형점을 찾아야 한다..
- 문자 인코딩(Character encoding) 무시하기 매우 특별한 이유가 없다면 항상 UTF-8(MySQL의 경우 UTF8MB4)을 사용하자. 이는 이모지(emoji), 국제 문자 및 현대 웹 콘텐츠 관련 문제를 방지해 준다.
- 계산된 값을 계산하는 대신 저장하기 만약 값이 다른 컬럼으로부터 파생될 수 있다면(예: total = subtotal + tax), 그것을 저장하지 마라. 쿼리나 애플리케이션 코드에서 계산하자. 저장된 계산 값은 필연적으로 동기화가 깨지게 된다.
- 설계 시 쿼리 패턴을 고려하지 않기 이론적인 순수함만이 아니라, 실제로 데이터를 어떻게 쿼리할 것인지에 기반하여 스키마를 설계하자. 만약 항상 사용자와 그의 역할을 함께 쿼리한다면, 매번 조인하는 대신 역할 이름을 users 테이블에 비정규화(denormalize)하는 것을 고려할 수 있다.
다음 단계로 나아가기 (Moving Forward)
스키마 설계는 과학인 동시에 예술이다. 과학은 패턴, 제약 조건, 데이터베이스의 기능을 아는 것이다. 예술은 그것들을 언제 적용하고 언제 규칙을 깨야 할지 아는 것이다.
견고한 기본에서 시작하자: 적절한 키(keys), 좋은 이름 규칙(naming), 참조 무결성, 그리고 적절한 인덱스(indexes). 실제 사용 패턴이 최적화를 이끌도록 해야한다. 쿼리 성능을 모니터링하고 필요에 따라 조정해야한다. 가장 중요한 것은, 스키마를 버전 관리하고 검증된 마이그레이션(migrations)을 통해 점진적으로 변경하는 것이다.
데이터베이스 스키마는 애플리케이션이 성장함에 따라 진화할 것이다. 스스로를 궁지에 몰아넣지 않으면서(without painting yourself into corners) 그 성장을 지원할 수 있을 만큼 충분히 잘 설계해야한다. 확장을 위한 여지를 남기고, 결정 사항을 문서화하며, 현재 설계의 고통이 변경 비용을 넘어설 때는 리팩토링(refactor)하는 것을 두려워하지 말아야 한다.
잘 설계된 스키마와 잘못 설계된 스키마의 차이는 시간이 지남에 따라 복리처럼 불어난다. 올바른 설계를 위해 초기에 노력을 투자해야한다. 그러면 미래의 당신과 당신의 동료들이 고마워할 것이다.
'최신 IT' 카테고리의 다른 글
| MSA(Micro Service Architecture)로의 전환 (0) | 2025.11.11 |
|---|---|
| REST API 기본 및 모범 사례 설명 (0) | 2025.11.03 |
| 확장을 고려하는 Scale Up 방법들 : (Servers, DBs, LBs, SPOFs) (0) | 2025.10.26 |
| 개발자가 알아야 할 20가지 시스템 설계 개념 (Node.js 예제기반) (0) | 2025.10.11 |
| N8N 사용법 - (예시)뉴스 RSS 피드 엑셀 저장 (0) | 2025.10.08 |
