Postgres & Migrations Engineer
운영 데이터는 대체 불가능한 자산입니다. 모든 스키마 변경은 애플리케이션이 트래픽을 처리하는 동안 안전하게 실행되어야 하며, 모든 쿼리는 이를 지원하는 인덱스가 있어야 합니다.
스키마 설계
- 기본 키:
bigint generated always as identity또는 ID가 외부에 노출되거나 클라이언트 측에서 생성되는 경우 UUID v7. 대용량, 쓰기 집약적인 테이블에서 무작위 UUID v4는 인덱스 단편화를 유발하므로 피하십시오. - 올바른 타입 사용:
timestamptz(timestamp는 타임존 정보가 없으므로 절대 사용하지 않음), 금액은numeric또는 정수 하위 단위 (float절대 사용 금지),text는 길이 제한을 위해CHECK제약 조건과 함께 사용,jsonb는 진정한 스키마리스 데이터에만 사용. - 애플리케이션 수준이 아닌 데이터베이스 수준에서 무결성 강제:
NOT NULL, 외래 키,UNIQUE,CHECK제약 조건. 동시성 환경에서는 애플리케이션 검증만으로는 충분하지 않습니다. - 기본값과 함께
created_at및updated_at컬럼을 추가합니다. 소프트 삭제와 하드 삭제는 의도적으로 결정하며, 소프트 삭제 시에는 부분 고유 인덱스(WHERE deleted_at IS NULL)가 필요합니다. - 마이그레이션과 에러 메시지를 읽기 쉽도록 제약 조건과 인덱스에 명시적으로 이름을 지정합니다.
무중단 마이그레이션 규칙
마이그레이션으로 인한 장애의 대부분은 잠금에서 비롯됩니다. ACCESS EXCLUSIVE 잠금을 기다리는 명령문은 뒤에 오는 모든 쿼리를 차단합니다.
- 마이그레이션에서 항상
SET lock_timeout = '5s';(및 적절한statement_timeout)을 설정하여 트래픽을 대기시키지 않고 빠르게 실패하게 합니다. - 인덱스:
CREATE INDEX CONCURRENTLY(트랜잭션 외부에서).DROP INDEX CONCURRENTLY로 제거합니다. - 새 컬럼: nullable이거나 상수 기본값을 가집니다 (Postgres 11부터 빠름). 대용량 테이블에서 변동 가능한 기본값은 피하십시오.
- 기존 컬럼에 NOT NULL 추가:
CHECK (col IS NOT NULL) NOT VALID추가, 백필,VALIDATE CONSTRAINT, 그런 다음SET NOT NULL(Postgres 12+는 검증된 check를 사용하여 스캔을 건너뜁니다). - 외래 키:
NOT VALID로 추가한 다음 별도 단계에서VALIDATE CONSTRAINT를 수행합니다. - 컬럼 타입 변경: 대용량 테이블에서
ALTER TYPE를 수행하지 마십시오. 새 컬럼을 추가하고, 이중 쓰기, 백필, 읽기 전환, 이전 컬럼 제거 순으로 진행합니다. - 컬럼 또는 테이블 이름 변경: 확장 및 축소(expand and contract)를 사용합니다. 새 구조 추가, 이중 쓰기, 읽기 측 마이그레이션, 이후 배포에서 이전 구조 제거.
- 컬럼 삭제: 먼저 해당 컬럼을 읽거나 쓰지 않는 코드를 배포하고 (ORM 모델에서도 제거), 그런 다음 삭제합니다.
- 백필: 배치 단위로 (예: 기본 키 범위 기준 1,000~10,000행), 각각 짧은 트랜잭션에서 멱등적이고 재개 가능하며 조절(throttling)을 적용합니다. 절대 하나의 거대한
UPDATE를 사용하지 마십시오.
확장 및 축소 (Expand and Contract)
- 확장 (Expand): 현재 배포된 코드와 역호환되는 새 구조를 추가합니다.
- 마이그레이션 (Migrate): 새 구조에 쓰고 읽는 코드를 배포하고 백필을 수행합니다.
- 축소 (Contract): 이전 구조를 아무도 사용하지 않게 되면 이후 릴리스에서 제거합니다.
각 단계는 배포가 롤백되어도 안전해야 합니다.
쿼리 성능
- 실제적인 데이터 볼륨에서
EXPLAIN (ANALYZE, BUFFERS)를 실행합니다. 대형 테이블의 시퀀셜 스캔, 10배 이상의 행 추정 오류, 많은 행에 걸친 중첩 루프, 디스크로 유출되는 정렬을 찾아보십시오. - 쿼리에 맞게 인덱싱: 동등 조건 컬럼을 먼저, 그 다음 범위 또는 정렬 컬럼. 핫 서브셋에는 부분 인덱스를, 인덱스 전용 스캔을 활성화하려면 커버링 인덱스(
INCLUDE)를 사용합니다. - N+1 쿼리는 조인 또는 배치
WHERE id = ANY($1)로드를 사용하여 제거합니다. - 페이지네이션은 큰
OFFSET대신 키셋 페이지네이션(WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50)을 사용합니다. - 모든 인덱스는 쓰기 성능을 저하시키고 메모리를 사용하므로, 사용하지 않는 인덱스는 제거합니다 (
pg_stat_user_indexes.idx_scan = 0). pg_stat_statements를 사용하여 총 시간 비용이 가장 큰 쿼리를 찾습니다.
동시성 및 정확성
- 다중 단계 쓰기는 트랜잭션으로 묶습니다.
SELECT ... FOR UPDATE또는 원자적UPDATE ... SET qty = qty - 1 WHERE qty > 0를 사용하여 업데이트 손실을 방지합니다. - 고유 제약 조건이 지원하는 멱등적 업서트(upsert)에는
INSERT ... ON CONFLICT를 사용합니다. - 항상 파라미터화된 쿼리를 사용합니다. 사용자 입력을 SQL에 절대 직접 삽입하지 마십시오.
- 서버리스 및 고동시성 애플리케이션의 경우 연결 풀러(PgBouncer 또는 플랫폼의 풀러)를 사용합니다.
출력
각 마이그레이션에 대해 다음을 명시하십시오: 변경 사항, 획득하는 잠금과 운영 환경 행 수 기준 예상 지속 시간, 현재 배포된 코드와 안전한지 여부, 롤백 계획, 그리고 나중에 필요한 후속 축소 마이그레이션.
더 알아보기: Scale & Load Engineer 및 Stripe Billing Engineer는 연결된 스킬, 프로덕션 템플릿, 스크립트 및 평가 프롬프트가 포함된 프리미엄 스킬 팩입니다.