프로젝트의 목표
지금까지는 SQL 문법, 테이블 설계, 조인, 집계, 인덱스, 트랜잭션, 백업과 운영을 각각 따로 배웠다. 이 마지막 장에서는 그 지식을 하나의 작은 시스템에 연결한다. 예제로 만들 시스템은 미니 커머스다. 회원이 상품을 주문하고, 재고가 줄고, 결제가 승인되며, 배송 상태가 기록된다. 관리자는 매출을 조회하고, 고객센터는 회원과 주문을 검색한다.
이 프로젝트의 목표는 많은 기능을 만드는 것이 아니다. 데이터베이스 관점에서 다음 흐름을 끝까지 경험하는 것이다.
업무 문장을 테이블과 제약 조건으로 바꾼다.
데이터 변경을 트랜잭션으로 안전하게 묶는다.
조회 쿼리를 작성하고 실행 계획으로 검증한다.
권한, 백업, 느린 쿼리 로그까지 운영 준비를 점검한다.
더 큰 시스템으로 확장할 때 무엇을 바꿔야 하는지 판단한다.
완성 기준은 화면이 아니라 데이터베이스다. 잘못된 주문이 들어가지 않고, 동시에 주문해도 재고가 음수가 되지 않으며, 필요한 보고서를 설명 가능한 비용으로 조회할 수 있으면 이 프로젝트는 성공이다.
AI 생성 이미지
미니 커머스 데이터 모델 설계
먼저 핵심 엔터티를 정한다. 회원, 상품, 재고, 주문, 주문 상세, 결제, 배송이 필요하다. 상품과 재고를 분리하는 이유는 재고 행을 명확히 잠그기 위해서다. 주문 상세에는 상품명과 주문 당시 가격을 따로 저장한다. 상품명이 나중에 바뀌어도 과거 주문서의 의미가 변하면 안 되기 때문이다.
CREATE SCHEMA IF NOT EXISTS commerce;
CREATE TABLE commerce.members (
member_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL,
status text NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'blocked', 'withdrawn')),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE commerce.products (
product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL UNIQUE,
name text NOT NULL,
price numeric(12, 2) NOT NULL CHECK (price >= 0),
status text NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'hidden', 'sold_out')),
attributes jsonb NOT NULL DEFAULT '{}',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE commerce.product_stock (
product_id bigint PRIMARY KEY
REFERENCES commerce.products(product_id),
quantity integer NOT NULL CHECK (quantity >= 0),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE commerce.orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no text NOT NULL UNIQUE,
member_id bigint NOT NULL REFERENCES commerce.members(member_id),
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'cancelled', 'shipped')),
total_amount numeric(14, 2) NOT NULL DEFAULT 0 CHECK (total_amount >= 0),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE commerce.order_items (
order_id bigint NOT NULL REFERENCES commerce.orders(order_id),
product_id bigint NOT NULL REFERENCES commerce.products(product_id),
product_name text NOT NULL,
unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
quantity integer NOT NULL CHECK (quantity > 0),
line_amount numeric(14, 2)
GENERATED ALWAYS AS (unit_price * quantity) STORED,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE commerce.payments (
payment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL UNIQUE REFERENCES commerce.orders(order_id),
status text NOT NULL
CHECK (status IN ('requested', 'approved', 'failed', 'cancelled')),
amount numeric(14, 2) NOT NULL CHECK (amount >= 0),
approved_at timestamptz
);
CREATE TABLE commerce.shipments (
shipment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL UNIQUE REFERENCES commerce.orders(order_id),
status text NOT NULL DEFAULT 'ready'
CHECK (status IN ('ready', 'shipping', 'delivered', 'returned')),
address text NOT NULL,
shipped_at timestamptz,
delivered_at timestamptz
);이 설계에서 중요한 점은 제약 조건이 업무 규칙을 직접 표현한다는 것이다. 회원 이메일은 중복될 수 없고, 가격과 수량은 음수가 될 수 없고, 주문 상태는 허용된 값만 저장된다. 애플리케이션에서도 검증해야 하지만, 데이터베이스가 마지막 방어선이 되어야 한다.
상태값을 text와 CHECK 제약 조건으로 표현한 것은 학습용 프로젝트에 맞춘 선택이다. 상태가 여러 테이블에서 재사용되거나 상태 전이 규칙이 복잡해지면 별도 코드 테이블, enum 타입, 애플리케이션 상태 머신을 함께 검토할 수 있다. 하지만 출발점은 단순해도 괜찮다. 중요한 것은 아무 값이나 들어가지 못하게 막는 것이다.
주문 생성과 결제 확정을 하나의 트랜잭션으로 묶기
주문은 여러 테이블을 함께 바꾸는 작업이다. 주문 머리 행을 만들고, 재고를 차감하고, 주문 상세를 저장하고, 결제 상태를 기록해야 한다. 중간에 하나라도 실패하면 전체가 실패해야 한다.
아래 예제는 상품 하나를 2개 주문하는 단순한 흐름이다. 실서비스에서는 외부 결제 API 호출을 데이터베이스 트랜잭션 안에서 오래 기다리지 않는다. 외부 결제 승인 결과를 받은 뒤, 데이터베이스의 상태 변경만 짧은 트랜잭션으로 묶는 방식이 안전하다.
예제를 psql에서 직접 따라 한다면 gset으로 RETURNING 값을 변수에 담을 수 있다. 애플리케이션 코드에서는 같은 역할을 코드의 변수와 영향받은 행 수 검사로 처리하면 된다. 아래 흐름에서 어떤 단계가 0행을 반환하면 더 진행하지 말고 ROLLBACK해야 한다.
BEGIN;
INSERT INTO commerce.orders (order_no, member_id)
VALUES ('ORD-20260530-0001', 1)
RETURNING order_id AS new_order_id
gset
-- 상품이 판매 중이고 재고가 충분할 때만 재고를 차감한다.
WITH selected_product AS (
SELECT product_id, name, price
FROM commerce.products
WHERE product_id = 101
AND status = 'active'
), stock_updated AS (
UPDATE commerce.product_stock s
SET quantity = s.quantity - 2,
updated_at = now()
WHERE s.product_id = 101
AND s.quantity >= 2
AND EXISTS (
SELECT 1
FROM selected_product p
WHERE p.product_id = s.product_id
)
RETURNING s.product_id
)
INSERT INTO commerce.order_items (
order_id, product_id, product_name, unit_price, quantity
)
SELECT :new_order_id, p.product_id, p.name, p.price, 2
FROM selected_product p
JOIN stock_updated s ON s.product_id = p.product_id
RETURNING order_id, product_id;
-- 위 INSERT가 0행이면 상품이 비활성 상태이거나 재고가 부족한 것이다.
-- 이때는 이어서 진행하지 말고 ROLLBACK한다.
UPDATE commerce.orders o
SET total_amount = s.total_amount
FROM (
SELECT order_id, sum(line_amount) AS total_amount
FROM commerce.order_items
WHERE order_id = :new_order_id
GROUP BY order_id
) s
WHERE o.order_id = s.order_id
RETURNING o.order_id, o.total_amount;
INSERT INTO commerce.payments (order_id, status, amount, approved_at)
SELECT order_id, 'approved', total_amount, now()
FROM commerce.orders
WHERE order_id = :new_order_id
RETURNING payment_id, amount;
UPDATE commerce.orders
SET status = 'paid'
WHERE order_id = :new_order_id
RETURNING order_id, status;
COMMIT;핵심은 재고 차감 문장이다. quantity >= 2 조건이 같은 UPDATE 안에 들어 있다. 동시에 두 사용자가 마지막 재고를 주문해도 PostgreSQL은 같은 재고 행에 대한 변경을 순서대로 처리한다. 기본 격리 수준인 READ COMMITTED에서는 뒤의 트랜잭션이 먼저 잡힌 행 잠금이 풀릴 때까지 기다린다. 앞선 트랜잭션이 커밋하면 뒤의 트랜잭션은 갱신된 값을 기준으로 조건을 다시 확인하고, 조건을 만족하지 않으면 0행을 변경한다.
동시성 테스트는 psql 창 두 개로 해볼 수 있다. 재고를 1로 맞춘 뒤 두 세션에서 동시에 다음 문장을 실행한다.
BEGIN;
UPDATE commerce.product_stock
SET quantity = quantity - 1,
updated_at = now()
WHERE product_id = 101
AND quantity >= 1
RETURNING quantity;한쪽 세션은 재고 차감에 성공하고, 다른 쪽 세션은 같은 행의 잠금이 풀릴 때까지 기다린다. 성공한 세션이 COMMIT하면 대기하던 세션은 조건을 다시 확인한다. 이미 재고가 0이 되었다면 0행을 반환한다. 이 테스트를 통해 트랜잭션은 문법이 아니라 데이터 경합을 제어하는 도구라는 점을 확인할 수 있다.
여러 상품을 한 주문에서 차감한다면 항상 같은 순서로 재고 행을 처리해야 한다. 예를 들어 모든 주문에서 product_id 오름차순으로 재고를 잠그면 교착 상태 가능성을 줄일 수 있다. 교착 상태가 완전히 사라지는 것은 아니므로, 애플리케이션에서는 교착 상태나 직렬화 실패가 발생했을 때 짧게 재시도하는 기준도 정해 두는 것이 좋다.
조회 쿼리 작성과 인덱스 개선
운영 시스템에서 중요한 조회는 크게 세 가지다. 관리자 매출 리포트, 고객 검색, 상품 필터링이다. 먼저 쿼리를 작성하고, 그다음 EXPLAIN (ANALYZE, BUFFERS)로 실제 계획을 확인한다. 인덱스는 쿼리를 본 뒤 만든다.
관리자 일별 매출 리포트는 결제 완료 주문만 집계한다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', created_at) AS sales_day,
count(*) AS order_count,
sum(total_amount) AS sales_amount
FROM commerce.orders
WHERE status = 'paid'
AND created_at >= timestamptz '2026-05-01'
AND created_at < timestamptz '2026-06-01'
GROUP BY date_trunc('day', created_at)
ORDER BY sales_day;이 쿼리가 큰 주문 테이블에서 자주 실행된다면 결제 완료 주문과 생성일을 기준으로 부분 인덱스를 검토한다.
CREATE INDEX orders_paid_created_idx
ON commerce.orders (created_at)
WHERE status = 'paid';부분 인덱스는 모든 주문이 아니라 status = 'paid'인 행만 담는다. 취소, 대기 주문이 많고 매출 리포트가 결제 완료 주문만 본다면 인덱스 크기를 줄일 수 있다. 단, 쿼리의 조건이 부분 인덱스의 조건과 맞아야 효과가 난다. 리포트 쿼리가 status = 'paid' 조건을 빼고 실행된다면 이 인덱스를 사용할 수 없다.
고객센터의 회원 검색은 이메일 정확 검색이 많다고 가정한다. 대소문자를 무시하려면 표현식 인덱스를 사용할 수 있다.
CREATE INDEX members_email_lower_idx
ON commerce.members (lower(email));
SELECT member_id, email, name, status, created_at
FROM commerce.members
WHERE lower(email) = lower('[email protected]');이 인덱스는 검색을 빠르게 하기 위한 인덱스다. 만약 정책상 Customer@example.com과 customer@example.com을 같은 이메일로 보아야 한다면 단순 인덱스가 아니라 lower(email)에 대한 유니크 인덱스나 citext 확장도 함께 검토해야 한다.
상품 목록은 상태, 가격, 속성 조건을 함께 받을 수 있다.
SELECT product_id, sku, name, price
FROM commerce.products
WHERE status = 'active'
AND price BETWEEN 10000 AND 50000
AND attributes @> jsonb_build_object('color', 'black')
ORDER BY price, product_id
LIMIT 20;기본 필터와 정렬에는 B-tree 인덱스를, JSON 포함 검색에는 GIN 인덱스를 검토한다.
CREATE INDEX products_status_price_id_idx
ON commerce.products (status, price, product_id);
CREATE INDEX products_attributes_gin_idx
ON commerce.products USING gin (attributes);인덱스를 만든 뒤에는 반드시 같은 쿼리로 실행 계획을 다시 본다. 확인할 것은 세 가지다. 첫째, 예상 행 수와 실제 행 수가 크게 다르지 않은가. 둘째, 읽은 버퍼 수가 줄었는가. 셋째, 전체 실행 시간이 안정적으로 줄었는가. 인덱스가 생겼다는 사실보다 이 세 가지 결과가 더 중요하다.
운영 준비 체크리스트
프로젝트가 개발 환경에서 동작한다고 끝난 것은 아니다. 최소한 백업, 권한, 로그 설정을 확인해야 한다.
먼저 읽기 전용 계정을 만든다. 관리자 리포트나 분석 도구가 실수로 데이터를 바꾸지 않게 하기 위해서다.
CREATE ROLE commerce_readonly NOLOGIN;
GRANT CONNECT ON DATABASE shop TO commerce_readonly;
GRANT USAGE ON SCHEMA commerce TO commerce_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA commerce TO commerce_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA commerce
GRANT SELECT ON TABLES TO commerce_readonly;
CREATE ROLE report_app LOGIN PASSWORD 'change_me';
GRANT commerce_readonly TO report_app;ALTER DEFAULT PRIVILEGES는 이 명령을 실행한 역할이 앞으로 만드는 테이블에 적용된다. 운영에서 테이블을 만드는 역할이 여러 개라면 각 소유자 역할 기준으로 기본 권한을 점검해야 한다. 또한 운영에서는 비밀번호를 SQL 파일에 그대로 남기지 말고 시크릿 관리 도구나 배포 환경 변수로 관리한다. 애플리케이션 계정도 읽기, 쓰기, 배치, 마이그레이션 용도를 분리한다.
다음은 백업과 복구 리허설이다. 백업 파일이 있다는 사실만으로는 부족하다. 실제로 복구해 봐야 한다.
pg_dump -Fc -d shop -f backups/shop_20260530.dump
createdb shop_restore
pg_restore -d shop_restore backups/shop_20260530.dump복구 후에는 주요 테이블 행 수, 시퀀스 값, 외래 키 오류, 대표 조회 쿼리를 확인한다. 주문 수와 결제 수가 맞지 않거나, 복구된 데이터베이스에서 애플리케이션 대표 쿼리가 실패하면 백업 절차를 다시 점검해야 한다.
느린 쿼리 로깅도 켜 둔다. 처음부터 너무 낮은 기준을 잡으면 로그가 과도하게 쌓인다. 예를 들어 500ms 이상 걸린 SQL부터 기록하고, 상황에 따라 조정한다.
ALTER DATABASE shop SET log_min_duration_statement = '500ms';이 설정은 새 연결부터 적용된다. 운영에서는 로그 저장 위치, 보관 기간, 개인정보 포함 여부도 함께 점검해야 한다. 느린 쿼리가 발견되면 바로 인덱스를 추가하지 말고 반복 빈도와 총 소요 시간을 먼저 본다. 이때 pg_stat_statements가 도움이 된다.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;직접 운영하는 PostgreSQL에서는 pg_stat_statements를 사용하려면 설정 파일의 shared_preload_libraries에 미리 등록하고 재시작해야 할 수 있다. 관리형 데이터베이스에서는 이미 켜져 있거나 별도 옵션으로 제공되는 경우가 많으므로 운영 환경의 방식을 확인한다. 반복적으로 비용이 큰 쿼리를 찾은 뒤에는 개별 쿼리를 EXPLAIN (ANALYZE, BUFFERS)로 확인한다.
마지막으로 다음 항목을 체크한다.
애플리케이션 계정이 필요한 권한만 가지고 있는가.
주문 생성 트랜잭션 실패 시 재시도 기준이 정해져 있는가.
장시간 열린 트랜잭션이 없는지 확인하는 쿼리가 있는가.
autovacuum이 큰 테이블에서 제때 동작하는가.
백업 파일을 다른 서버나 스토리지에 보관하는가.
복구 목표 시간과 복구 목표 시점이 문서화되어 있는가.
확장 과제
기본 프로젝트가 완성되면 다음 과제를 하나씩 추가해 본다.
첫째, 주문 테이블을 월별 파티셔닝한다. 주문이 수천만 행 이상으로 커지고 조회 조건이 대부분 기간 기준이라면 created_at 범위 파티셔닝이 도움이 될 수 있다. 단, 파티션 키가 쿼리 조건에 들어가지 않으면 효과가 작다. 파티션이 너무 잘게 쪼개지면 관리 비용도 늘어난다.
둘째, 분석 데이터베이스로 논리 복제를 구성한다. 운영 DB의 일부 테이블을 읽기 전용 분석 DB로 보내면 무거운 리포트 쿼리를 분리할 수 있다. 이때 복제 지연, 기본 키 또는 replica identity 존재 여부, 스키마 변경 절차를 함께 점검한다.
셋째, 상품 속성을 JSONB로 확장한다. 의류는 색상과 사이즈, 전자제품은 제조사와 보증 기간처럼 상품군마다 속성이 다를 수 있다. 다만 가격, 상태, 카테고리, 재고처럼 핵심 조회와 제약 조건이 필요한 값은 일반 열로 유지한다.
넷째, 상품명과 설명에 전문 검색을 붙인다. 단순 ILIKE 검색으로 충분하지 않다면 tsvector, tsquery, GIN 인덱스를 사용해 검색어 토큰화와 순위 계산을 실험한다.
다섯째, 주문 생성 부하 테스트를 해 본다. 동시에 10명, 100명, 500명이 같은 상품을 주문할 때 성공 수와 실패 수, 잠금 대기 시간, 재고 정합성을 확인한다. 결과가 예상과 다르면 트랜잭션 범위, 인덱스, 잠금 순서를 다시 본다.
마무리 기준
PostgreSQL을 실무 도구로 익힌다는 것은 문법을 많이 외우는 일이 아니다. 데이터가 어떤 규칙을 가져야 하는지 설계하고, 변경 작업을 안전하게 묶고, 조회 성능을 실행 계획으로 확인하고, 장애가 나도 복구할 수 있게 준비하는 일이다.
이 장의 미니 커머스 프로젝트를 직접 입력하고 수정해 보자. 일부러 재고 부족, 중복 이메일, 잘못된 주문 상태, 결제 실패, 느린 검색 쿼리를 만들어 보면 더 좋다. PostgreSQL은 성공한 쿼리보다 실패한 쿼리를 분석할 때 더 빠르게 익숙해진다. 최종 목표는 모든 기능을 한 번에 완성하는 것이 아니라, 새 요구사항이 들어왔을 때 어떤 테이블을 바꾸고, 어떤 쿼리를 검증하고, 어떤 운영 위험을 확인해야 하는지 스스로 판단하는 것이다.
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.