- Hatchet이 2년간 프로덕션에서 겪은 문제를 바탕으로, 초기 스키마·쿼리 설계부터 대량 쓰기와 테이블 마이그레이션까지 단계별 운영 원칙을 정리함
- 빠른 읽기를 위해 인덱스와
ORDER BY를 맞추되, 쿼리 플래너가 통계와 비용에 따라 순차 스캔을 선택할 수 있으므로 EXPLAIN ANALYZE로 추정치와 실제 실행을 비교해야 함
- 쓰기 성능과 안정성은 짧은 트랜잭션, 필요한 행만 잠그기,
CREATE INDEX CONCURRENTLY, 연결 풀링에 달려 있으며, 배치 처리는 Hatchet 측정에서 처리량을 약 10배 높였음
- 고빈도 쓰기 환경에서는 기본 autovacuum 설정이 dead tuple과 transaction ID를 제때 회수하지 못할 수 있으며, transaction ID wraparound에 이르면 큰 폭의 다운타임이 발생함
- 규모가 커지면
FOR UPDATE SKIP LOCKED 기반 작업 큐, 파티셔닝, 트리거와 배치 백필을 활용하되, ORM 추상화 밖에서 SQL을 직접 제어할 수 있어야 함
대상 독자와 ORM의 한계
- SQL, 행, 테이블, 인덱스의 기본 개념을 아는 개발자가 프로덕션 Postgres 문제에 대응할 수 있도록 구성한 가이드임
- Postgres 매뉴얼은 포괄적이지만 장애 상황에서 빠르게 참고하기 어려워, Hatchet이 2년간 겪은 운영 경험을 중심으로 압축함
- ORM을 사용해도 원칙은 적용되지만, 규모가 커질수록 추상화 계층을 벗어나 SQL을 직접 작성해야 가능한 최적화가 많음
변경하기 어려운 스키마 설계
- 배포 후에는 스키마 변경이 가장 어려우므로, 테이블과 기본 키의 초안을 만든 뒤 애플리케이션에 필요한 쿼리를 작성하며 반복적으로 설계해야 함
- 설계 과정에서는 다음 질문으로 테이블의 사용 방식을 확인함
- 읽기와 쓰기 중 어느 쪽의 빈도가 높은가
- 읽을 때 가장 자주 사용하는 필터는 무엇인가
- 가장 자주 갱신하는 열은 무엇인가
- 데이터베이스 정규화의 1NF·2NF·3NF를 적용할 수 있지만, 정규형이 쿼리 효율이나 빠른 개발에 필요한 사용 편의성과 충돌할 때도 있음
- 일부 상황에서는 데이터를
jsonb 열에 넣는 편이 단순함
- 스키마 설계에 적용한 경험칙은 다음과 같음
- 기본 키에는 identity 열인 자동 증가 정수나 Postgres 내장 UUID를 사용함
- identity 열은
bigserial보다 약간 빠름
- 시간에는 항상
timestamptz를 사용함
- 모든 테이블에 기본 키를 둠
- 일관성과 정확성이 중요한 저용량 테이블에는 cascade delete를 포함한 외래 키를 사용하되, 고용량 환경에서는 주의해야 함
읽기 쿼리와 인덱스
- 빠른
SELECT를 이해하기 위한 단순한 모델은 Postgres가 인덱스로 행 하나를 빠르게 찾거나, 순차 스캔(seq scan) 으로 테이블의 모든 행을 읽는다는 것임
- 빠른 단일 행 탐색에는 다음 구조를 이용함
- 명시적인 인덱스
- 인덱스의 특수한 형태인 unique constraint
- Postgres가 자동으로 인덱싱하는 기본 키
- 기본 인덱스는
btree를 사용하며, 조회에 최적화된 형태로 데이터를 저장한 별도 테이블처럼 이해할 수 있음
- 행 탐색 시간은 대략
log(n)이며, n은 테이블의 행 수임
- 인덱스를 사용할 수 없으면 순차 스캔이 실행되지만, 현대 데이터베이스는 행을 메모리에 빠르게 올리므로 2만 행 미만 테이블에서는 거의 즉시 끝날 수 있음
조인과 복합 인덱스
- 내부 조인의 대상에는 대체로 기본 키를 사용해야 하며, 그렇지 않다면 스키마 설계나 정규화에 문제가 있을 수 있음
ON 절도 WHERE 절처럼 다루고, 조인 조건에 적절한 인덱스를 사용해야 함- 큰 테이블의 목록 조회는 애플리케이션에서 처음 느려지는 쿼리가 되기 쉬움
- 조직과 생성 시각을 함께 필터링하고 정렬한다면 복합 인덱스를 사용할 수 있음
CREATE INDEX CONCURRENTLY idx_documents_org_created
ON documents (organization_id, created_at DESC);
- 복잡한 쿼리에서는
ORDER BY 열을 인덱스의 마지막에 배치하고 정렬 방향도 맞추는 것이 경험칙임
- Postgres는
btree를 양방향으로 스캔하므로 단일 열에서는 DESC가 무의미할 수 있지만, 복합 인덱스에서는 맞춰 두는 편이 좋음
- 내림차순 인덱스의 상세 동작은 관련 자료에서 확인할 수 있음
쓰기, 잠금, 마이그레이션
- 성공적인 쓰기의 첫 번째 조건은 트랜잭션을 짧게 유지하는 것임
- 특별한 이유가 없다면 트랜잭션 도중 외부 서비스를 조회하지 않음
- 두 번째 조건은 필요한 행만 잠그는 것임
- 행을 갱신하면 트랜잭션이 커밋될 때까지 해당 행에 잠금이 걸림
- 시스템 부하가 커질수록 잠금의 영향도 두드러짐
- 기존 대형 테이블에서 일반
CREATE INDEX를 실행하면 테이블이 잠겨 insert와 update가 차단되므로, 항상 CREATE INDEX CONCURRENTLY를 사용함
- 좋은 스키마 마이그레이션 역량은 반복 개발 속도를 높이고 가동 시간을 늘림
- 가능한 한 열 삭제나 제거를 피하고 추가 방식으로 변경함
- 가능하면 트랜잭션 안에서 실행해 롤백과 부분 적용에 대응함
- 더 발전된 방식으로 expand and contract 마이그레이션을 사용할 수 있음
- 마이그레이션은 모든 쓰기를 차단하는지를 먼저 판단해야 함
CONCURRENTLY 없는 인덱스 생성은 모든 쓰기를 막아 다운타임을 일으킬 수 있음ALTER TABLE 작업은 다시 검토해야 하며, 대형 테이블에 check constraint를 추가하는 작업도 쓰기를 차단할 수 있음- check constraint를
NOT VALID로 추가하면 해당 차단을 피할 수 있음
연결 관리
- 모든 쿼리와 트랜잭션은 데이터베이스 연결을 사용하며, 연결은 CPU와 메모리 비용이 크므로 오래 유지해야 함
- 연결을 자주 만들고 제거하면 자원이 낭비됨
- 많은 신규 연결이 동시에 발생하는 connection storm은 Postgres 내부 잠금과 관련된 디버깅하기 어려운 문제를 일으킬 수 있음
- 외부 연결 풀러인
pgbouncer를 우선 고려하고, 사용할 수 없다면 인메모리 연결 풀을 대안으로 삼음
- Hatchet은 사용자의 데이터베이스가 외부 풀러를 쓴다고 가정할 수 없어 Go용 pgxpool을 사용함
쿼리 플래너와 통계
- 조인이 많거나 여러 조인 방식을 섞은 복잡한 쿼리는 단순히 인덱스를 추가하는 것만으로 해결되지 않음
- 인덱스 자체에도 오버헤드가 있으므로 무제한으로 추가해서는 안 됨
- 쿼리 플래너는 SQL을 내부 데이터베이스 연산으로 변환하고 인덱스 사용 여부 등을 결정하지만, 제한된 정보 때문에 최적 계획을 선택하지 못할 수 있음
- 플래너가 사용하는 정보는 테이블 통계이며
pg_stats에서 조회할 수 있음
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
- 통계는
ANALYZE 때 수집되고 autovacuum 실행 시에도 갱신됨
- autovacuum 빈도를 높이면 쿼리 통계도 최신 상태를 유지함
- 쿼리가 잘못 동작하는 흔한 원인 중 하나는 분석 빈도가 부족한 것임
- 쿼리를 순차 스캔 여부로 단순하게 판단하면 미세 최적화로 플래너의 예측 불가능성을 키우는 일을 줄일 수 있음
- 기본 키와 인덱스 중심으로 조회하면 플래너가 계획을 선택하기 쉬워짐
실행 계획 분석과 순차 스캔
- 일부 제공자는 Google CloudSQL처럼 쿼리를 샘플링해 느린 쿼리를 저장하지만, 모든 서비스가 이를 지원하지는 않음
EXPLAIN ANALYZE는 쿼리를 실제로 실행하며, 테이블 통계에 따른 예상 행 수와 실제 스캔 행 수를 비교함
- 프로덕션에서는 실제 쿼리가 실행되므로 주의해야 함
- 실행 없이 계획만 확인하려면
ANALYZE를 뺀 EXPLAIN을 사용함
- 상세 계획을 JSON으로 저장한 뒤 explain.dalibo.com에서 시각화할 수 있음
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
- 통계와 인덱스가 정상인데도 순차 스캔한다면, 플래너가 순차 스캔 비용이 더 낮다고 계산했을 수 있음
- 인덱스는 실제 테이블 데이터가 있는 heap과 별도로 저장되므로, 인덱스에서 찾은 여러 행을 heap에서 다시 읽는 비용이 발생함
- 쿼리를 크게 재구성할 수 없다면 순차 스캔을 받아들이거나 파티셔닝을 검토해야 함
대량 쓰기와 배치 처리
- 각 쿼리에는 데이터베이스 왕복 시간, 애플리케이션 연결 풀에서 연결을 얻는 시간, Postgres 처리 시간이라는 오버헤드가 있음
- Postgres 내부 잠금도 고처리량 환경에서 병목이 될 수 있음
- 한 쿼리에 여러 행을 묶으면 이러한 비용을 줄일 수 있음
- 가장 단순한 방법은 암묵적 트랜잭션으로 여러 쿼리를 서버에 한꺼번에 보내는 것임
- Go에서는
pgx의 SendBatch를 사용할 수 있음
- Hatchet에서는 배치 처리로 처리량이 약 10배 증가했으며, 추가 삽입 최적화는 빠른 Postgres 삽입 가이드에 정리돼 있음
autovacuum과 transaction ID wraparound
- autovacuum은 dead tuple 정리와 transaction ID 관리를 담당하며, 고빈도 쓰기 환경에서는 설정 조정이 필요할 수 있음
- tuple은 파일 시스템에 저장된 행의 한 버전임
- 행을 갱신하거나 삭제해도 그 전에 시작한 모든 트랜잭션이 커밋 또는 롤백될 때까지 기존 버전이 남음
- 어떤 트랜잭션에서도 더 이상 읽을 수 없는 버전이 dead tuple임
- 쓰기 속도가 너무 빠르면 autovacuum이 dead tuple 생성 속도를 따라가지 못해 데이터베이스 상태가 급격히 악화될 수 있음
pg_stat_activity에서 활성 프로세스를 확인했을 때 autovacuum 쿼리가 약 1시간 이상 실행 중이라면 설정 변경을 검토해야 함
- autovacuum이 회수하기 전에 모든 transaction ID를 소진하면 transaction ID wraparound가 발생하고 큰 폭의 다운타임으로 이어짐
테이블과 인덱스 팽창
- Postgres는 디스크의 8KB 페이지에 행을 저장하며, 기존 페이지에 새 행을 넣지 못하면 새 페이지를 생성함
- dead tuple이 회수된 뒤 페이지가 부분적으로 비어 있으면 테이블 팽창(table bloat)이 발생해 디스크 사용량이 크게 늘 수 있음
- 가장 좋은 예방법은 팽창하기 전에 autovacuum을 조정하는 것임
- 이미 팽창한 테이블에는
pg_repack 같은 확장을 사용할 수 있음
- 내장
VACUUM FULL은 거의 좋은 선택이 아님
- Postgres 19에는 동시 테이블 재패킹을 위한
REPACK...CONCURRENTLY가 추가될 예정이지만 Hatchet은 아직 시험하지 않았음
- 인덱스 팽창도 테이블 팽창의 특수한 형태이며, 적절한 autovacuum 설정으로 줄일 수 있음
- 이미 팽창한 인덱스에는 내장 명령
REINDEX INDEX CONCURRENTLY를 사용할 수 있음
FOR UPDATE SKIP LOCKED 기반 동시 처리
FOR UPDATE SKIP LOCKED는 선택한 행을 현재 트랜잭션용으로 예약하면서 다른 쿼리를 방해하지 않음- Hatchet은 이를 작업 큐에 사용하며, 한 쿼리에서 대기 중인 작업을 잠그고 상태를
RUNNING으로 바꿀 수 있음
WITH eligible_tasks AS (
SELECT *
FROM tasks
WHERE status = 'QUEUED'
ORDER BY id ASC
FOR UPDATE SKIP LOCKED
LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;
- 서로 독립적인 행을 동시에 갱신하거나 여러 애플리케이션 인스턴스가 객체의 lease를 관리할 때도 유용함
- Hatchet은 여러 엔진에 tenant lease를 분배하는 데 사용함
파티셔닝
- Postgres의 내장 파티셔닝은 timestamp나 hash 같은 행 값을 기준으로 테이블을 분할함
- 시계열 데이터와 Hatchet의 과거 작업 데이터에서 다음 이점을 제공함
- 파티션마다 독립적으로 autovacuum을 실행해 테이블의 autovacuum 처리 규모를 키울 수 있음
- 오래된 데이터를 행 단위로 삭제하지 않고 파티션 테이블을 내려 거의 즉시 제거할 수 있음
- 계획 단계에서 Postgres가 불필요한 파티션을 제거하지 못하면 읽기 쿼리에 오버헤드가 생길 수 있음
대형 테이블 간 데이터 이동
- 여기서 말하는 대형 테이블 마이그레이션은 스키마 변경이 아니라, 한 테이블에서 다른 테이블로 대량 데이터를 이동하는 작업임
- 매우 큰 테이블을 단일 트랜잭션으로 복사하면 수 시간이 걸릴 수 있음
- 장시간 트랜잭션은 autovacuum의 정상 동작을 막아 dead tuple 팽창을 일으킴
- 이전 테이블에 계속 쓰기가 발생하면 새 테이블에는 해당 데이터가 반영되지 않음
- Hatchet은 트랜잭션 밖에서 큰 배치 백필을 실행하고, 마이그레이션 시작 후의 새 쓰기는 Postgres 트리거로 새 테이블에 복사함
- 기본 키의 unique constraint를 이용해 중복 쓰기를 방지함