Postgres에서 흔히 발생하는 DB 스키마 변경 실수

 (postgres.ai)
5P by GN⁺ | ★ favorite | 댓글 1개
  • Postgres 스키마 마이그레이션은 잠금·테이블 재작성·복제 지연이 운영 장애로 이어질 수 있어, 대형 OLTP 환경에서 특히 위험도가 높음
  • 위험은 DEFAULT와 NOT NULL 동시 추가, CONCURRENTLY 없는 인덱스 생성, 즉시 컬럼 삭제, 안전하지 않은 타입 변경, 검증 없는 외래 키 추가처럼 전체 스캔과 긴 잠금을 유발하는 작업에 집중됨
  • PostgreSQL 11부터 일부 컬럼 추가 비용은 줄었지만, 인덱스는 CREATE INDEX CONCURRENTLY, 외래 키는 NOT VALID 후 VALIDATE CONSTRAINT처럼 운영 영향을 낮추는 절차가 필요함
  • 대량 변경은 작은 배치로 나누고, 읽기 복제본·복제 지연·의존 객체·기존 애플리케이션 인스턴스의 컬럼 참조 여부까지 함께 확인해야 함
  • 프로덕션 규모 데이터에서 사전 테스트하고, 파괴적 작업은 다단계 배포와 검증된 롤백 계획을 갖춘 뒤 진행해야 함

스키마 마이그레이션의 전제

  • 여기서 DB 마이그레이션은 DBMS 전환이 아니라 DB 스키마 변경을 뜻함
  • 대상 변경은 세 가지 성격을 가짐
    • 각 변경에 고유 식별자와 자동화된 적용 절차가 있는 버전 관리형 변경
    • 프로덕션 적용 뒤 수정하지 않고 새 변경만 추가하는 불변 변경
    • 데이터베이스 스키마가 단계적으로 진화하는 증분 변경
  • 초점은 모바일·웹 애플리케이션 같은 OLTP 사용 사례이며, 1초를 넘는 쿼리 실행은 보통 너무 느린 것으로 간주됨
  • 작은 데이터베이스와 낮은 활동량에서는 일부 문제가 잘 드러나지 않지만, 약 10TiB 규모와 초당 10⁴~10⁵ 트랜잭션 부하에서는 대부분의 문제가 나타날 수 있음
  • Database Lab Engine은 얇은 클론으로 개발과 테스트에 쓰이며, 10TiB 데이터베이스를 10초 안에 클론해 스키마 변경 위험을 배포 전에 확인할 수 있음
  • GitLab Migration Style Guide는 다수의 Postgres 스키마 변경을 자동 배포한 경험을 담은 참고 자료임

컬럼 추가와 테이블 재작성

  • DEFAULT와 NOT NULL을 동시에 가진 컬럼 추가는 구버전 PostgreSQL에서 특히 위험함
    • PostgreSQL 11 이전에는 전체 테이블 재작성이 필요함
    • 큰 테이블에서는 몇 시간 또는 며칠이 걸릴 수 있고, 그동안 쓰기 잠금이 발생함
  • 위험한 예시는 다음과 같음
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • 더 안전한 절차는 컬럼 추가, 데이터 갱신, 제약 추가를 나누는 방식임
    • 먼저 NOT NULL 없이 컬럼을 추가함
    • 필요하면 기존 행을 갱신함
    • 이후 NOT NULL 제약을 추가함
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • PostgreSQL 11 이상에서는 비휘발성 DEFAULT 값을 가진 컬럼 추가가 더 이상 테이블 재작성을 요구하지 않음

인덱스 생성과 외래 키 추가

  • CONCURRENTLY 없이 인덱스를 만들면 표준 인덱스 생성이 테이블에 배타 잠금을 잡음
    • 인덱스 생성이 끝날 때까지 모든 쓰기와 일부 읽기가 막힐 수 있음
  • 위험한 예시는 다음과 같음
CREATE INDEX idx_users_email ON users(email);
  • 운영 중에는 CREATE INDEX CONCURRENTLY 사용이 더 안전함
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY에는 제약이 있음
    • 더 오래 걸리지만 테이블 접근을 막지 않음
    • 트랜잭션 블록 안에서 사용할 수 없음
    • 실패하면 삭제가 필요한 유효하지 않은 인덱스를 남길 수 있음
  • 큰 테이블에 외래 키 제약을 직접 추가하면 기존 데이터 검증을 위해 전체 테이블을 스캔하고 긴 잠금을 유발함
  • 더 안전한 절차는 먼저 NOT VALID로 제약을 추가한 뒤, 트래픽이 낮은 시점에 검증하는 방식임
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

컬럼 삭제와 타입 변경

  • 프로덕션에서 컬럼을 바로 삭제하면 애플리케이션 코드가 여전히 해당 컬럼을 참조할 때 애플리케이션 오류가 발생할 수 있음
  • 컬럼 삭제는 다단계로 진행해야 함
    • 해당 컬럼을 사용하지 않는 애플리케이션 코드를 먼저 배포함
    • 오래된 애플리케이션 인스턴스가 모두 교체될 때까지 기다림
    • 별도 마이그레이션에서 컬럼을 삭제함
  • 컬럼 타입 변경은 테이블 재작성이나 호환성 문제를 만들 수 있음
    • 다운타임, 데이터 손실, 애플리케이션 오류로 이어질 수 있음
  • 문제가 되는 예시는 다음과 같음
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • integer에서 bigint로 바꿀 때는 새 컬럼을 이용한 다단계 절차가 필요함
  • varchar 길이를 줄일 때는 데이터를 먼저 확인하고, 변경이 정말 필요한지 검토해야 함

대량 변경, 복제, 의존 객체

  • 너무 많은 데이터를 하나의 트랜잭션에서 수정하는 마이그레이션은 피해야 함
    • 잠금 경합과 메모리 사용량이 늘어남
    • 문제가 생겼을 때 복구 시간이 길어짐
    • 복제 지연이 커질 수 있음
  • 대규모 데이터 마이그레이션은 작은 배치로 나누는 편이 더 안전함
  • 마이그레이션이 읽기 복제본과 복제 지연에 미치는 영향도 함께 봐야 함
    • 큰 마이그레이션은 상당한 복제 지연을 만들 수 있음
    • 읽기 복제본 성능에 영향을 줄 수 있음
  • 수정 대상 컬럼이나 테이블에 의존하는 객체도 확인해야 함
    • 뷰, 함수, 트리거 등 의존 객체를 놓치면 연쇄 실패나 추가 수동 개입이 필요할 수 있음

테스트와 롤백 계획

  • 작은 개발 데이터셋에서만 마이그레이션을 테스트하면 큰 데이터셋의 성능 특성을 확인하기 어려움
  • 프로덕션 규모 데이터 클론에서 테스트해야 하며, Database Lab Engine 같은 도구를 사용할 수 있음
  • 문제가 발생했을 때 마이그레이션을 되돌릴 방법이 없으면 프로덕션 이슈가 장기 다운타임으로 이어질 수 있음
  • 특히 파괴적 작업에는 검증된 롤백 계획이 필요함
  • 안전한 스키마 변경의 기본은 다음과 같음
    • 프로덕션 규모 데이터에서 테스트함
    • 위험한 작업에는 다단계 접근을 사용함
    • CONCURRENTLY와 NOT VALID 같은 PostgreSQL 기능을 활용함
    • 성능과 복제 영향을 모니터링함
    • 항상 롤백 계획을 준비함

함께 보면 좋은 글 β

GN⁺   [-]
Hacker News 의견들

  • Postgres를 정말 좋아하지만, 이 글의 대부분은 피할 수 있고 주의할 만한 내용임. 다만 Postgres의 최악은 역할 관리라고 봄
    기능은 강력해서 잘 쓰면 훌륭하겠지만, 실제로 동작하게 만드는 과정은 흑마술처럼 느껴짐. 인터페이스 곳곳이 기대대로 동작할지 모르는 난해한 주문 같고, 이렇게 중요한 것을 관리하기엔 끔찍한 방식임
    이 부분의 매뉴얼도 얇아서 좁은 사용 사례에서 대략 어떻게 동작해야 하는지만 알려주는 수준임. 예상대로 안 되면 시행착오로 무엇을 잘못했는지 찾아야 하고, 올바른 방법은 여전히 감이 안 잡힘. 복잡한 사용자 권한을 가진 DB를 마이그레이션하려면 정말 고생함
    한 달쯤 잡고 cookbook을 써야겠다고 느낌. 한 명이라도 그걸 보고 울면서 잠들지 않게 된다면 가치가 있을 것임

    • PostgreSQL의 IAM이 복잡하다는 데 동의함. 복잡한 이유는 객체 계층이 Database, Schema, Tables의 3단계이고, DB 객체 소유자에게 암묵적으로 부여되는 권한도 있기 때문임
      테이블에서 SELECT하려면 Database의 CONNECT, Schema의 USAGE가 필요하고 Schema 소유자에게는 암묵적으로 주어짐. Table의 SELECT도 필요하며 테이블 소유자에게 암묵적으로 부여됨
      권한을 보려면 grantee=privilege-abbreviation[]/grantor: 형식의 ACL 항목을 이해해야 함. Database 권한은 \l+, Schema 권한은 \dn+, Table 권한은 \dp+로 볼 수 있음
      권한 목록은 here에 있음. 예를 들어 user=arwdDxt/postgres는 postgres 역할이 사용자에게 모든 권한을 준 상태임
      어떤 객체의 grantee 열이 비어 있으면 기본 소유자 권한, 즉 모든 권한을 뜻할 수도 있고, 존재하는 모든 역할인 PUBLIC 역할에 대한 권한을 뜻할 수도 있음. 예시는 =r/postgres임
      public Schema를 쓰면 더 헷갈림. Schema에 CREATE 권한이 있어서, 데이터를 조회하는 같은 사용자로 테이블을 만들면 소유자 권한이 기본으로 붙어 바로 조회가 됨
    • 인증에 역할을 의존하는 postgREST 문서도 그리 자세하지 않아 보임: https://postgrest.org/en/v12/explanations/db_authz.html
      Postgres 역할에 대한 cookbook을 진지하게 쓰고 Kickstarter 같은 걸 연다면 가장 먼저 후원할 사람 중 하나가 될 듯함
    • “동작하게 만드는 게 흑마술 같다”는 말에 동의함. 작년에 행 수준 보안을 붙인 간단한 postgREST 서버를 구현했는데, 거기까지 가는 길은 꽤 어려웠음
      그래도 일단 동작하니 정말 마법 같았고, 관련 메커니즘 자체는 의외로 꽤 단순했음
    • 그런 글이 있으면 읽을 것 같음. 역할 관리는 추측이 많이 들어가고, 그 결과 역할에 과도한 권한이 붙는 일이 너무 잦음
    • 꼭 써줬으면 함. 그 정도 내용이면 20달러쯤 기꺼이 낼 수 있음

  • 운영 환경에서 Schema 마이그레이션을 돌린다면 lock_timeout 을 써야 함
    외래 키가 있는 테이블 삭제나 외래 키 삭제처럼 겉보기엔 무해하고 테스트에서는 거의 즉시 끝나는 변경도, 트래픽이 많은 운영 DB에서는 기존 트랜잭션이나 autovacuum 때문에 잠금 충돌을 만날 수 있음
    그 ALTER는 첫 트랜잭션의 잠금을 기다리면서 ACCESS EXCLUSIVE 잠금을 잡게 되고, 그러면 잠긴 테이블에 대한 쿼리가 모두 막힘
    규모 있는 Postgres를 운영하면 이런 충돌은 시간문제임. lock_timeout을 설정하면 다른 모든 쿼리를 막은 채 기다리는 대신, 제한 시간이 지나면 마이그레이션이 실패함

    • statement_timeout 은 잠금 대기 시간까지 포함하므로, 바쁜 테이블에 미치는 영향을 더 잘 추정할 수 있음
      제한 시간을 5초로 잡으면 총 중단 시간이 최대 5초라는 걸 알 수 있고, 이후 트랜잭션은 계속됨. lock_timeout만 쓰면 잠금을 얻은 뒤 작업이 얼마나 걸릴지 제어할 수 없고, 동시 트래픽 때문에 빠를 수도 느릴 수도 있음
    • Postgres 버전에 따라 특정 DML 쿼리가 배타 잠금을 잡는지 여부가 꽤 크게 달라짐
      쿼리를 분석해서 어떤 종류의 잠금을 잡을지 알려주는 좋은 방법이 있는지 궁금함. 확신이 없을 때는 늘 문서를 다시 읽는 식으로 해왔음
    • 좋은 조언임. 다만 기술적으로는 이미 ACCESS EXCLUSIVE 잠금을 획득해서 기다리는 게 아니라, 잠금 큐 때문에 기다리는 것으로 알고 있었음
      ALTER는 ACCESS EXCLUSIVE보다 낮은 잠금이 해제되기를 기다리는 상태임
    • 그렇게 하면 ALTER가 영원히 실행되지 않을 수도 있음. 해당 테이블에 트래픽이 충분하면 그럴 수 있음
      이런 경우 앱이 복구 가능하다면 ALTER를 막고 있는 다른 진행 중 쿼리를 죽이는 게 최선이라고 봄

  • Fly.io의 Safe Migrations in Ecto 가이드를 일주일에도 여러 번 참고함. Ecto는 Elixir의 DB 어댑터임
    기본 마이그레이션으로 충분한지, 아니면 더 복잡한 절차가 필요한지 빠르게 확인하기에 매우 유용한 참고자료임
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • 초보 때 Postgres 인덱스에서 가장 놀라웠던 점은 UNIQUE 인덱스가 추가 잠금 때문에 동시 쿼리 결과에 영향을 줄 수 있다는 것이었음
    INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo); 같은 쿼리는 기본 모드에서 동시에 실행하면 중복 bar 값을 넣을 수 있음. 한 트랜잭션이 다른 트랜잭션이 만든 새 최댓값을 보지 못할 수 있기 때문임
    UNIQUE 인덱스를 추가하면 “진” 트랜잭션이 제약 조건 오류를 받을 것 같지만, 실제로는 두 트랜잭션이 모두 성공하고 경쟁 상태도 사라짐

    • 그건 사실이 아님. 인덱스 경쟁에서 진 하위 트랜잭션은 중단됨
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • UNIQUE 인덱스가 있는 상태에서도 두 삽입이 모두 성공하고 결국 중복 값이 들어간다는 말이라면, 맞다면 그건 버그임
    • 착각이 아니라면, 일반 인덱스를 CONCURRENTLY로 만들고 검사하지 않은 UNIQUE 제약 조건을 생성하면 무중단으로 가능함
      그 제약은 새 INSERT/UPDATE에만 적용됨. 이후 제약 조건에 VALIDATE를 실행하면 완전한 UNIQUE 제약 조건이 됨
    • 놀랍게 느껴진다면 명령형 언어에 너무 많이 노출됐기 때문이라고 봄
      흔한 일이라는 데는 동의하지만, 문제는 Postgres라기보다 소프트웨어 개발 전반에 있음
    • 어떤 격리 수준에서 그런가?

  • 이런 함정들 때문에 무중단 Schema 마이그레이션 자동화를 목표로 Reshape [0]를 만들었음
    모든 문제를 피한다고 말할 수는 없지만, 그걸 목표로 하는 새 제품을 만들고 있음. 이 영역, 특히 Postgres에 관심이 있다면 연락을 받고 싶음: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • crdb에서도 동작할 가능성이 있나?

  • 자주 보는 또 다른 실수는 테이블을 복제하면서 인덱스는 빼먹는 것임
    CREATE TABLE SELECT * FROM WHERE <>는 그런 식으로 동작하지 않음. 백업 테이블을 만들거나 대량 삭제를 하려고 할 때 사람들이 자주 이렇게 함

    • 백업 테이블을 만드는 경우라면, 즉 즉시 예측 못 할 방식으로 망가질 수 있는 복잡하고 애매한 작업을 하려는 거라면 인덱스나 제약 조건은 전혀 신경 쓰지 않음
      DB 백업과 WAL에서 복원하지 않아도 되도록, 쓸 일은 없겠지만 바로 존재하는 데이터 사본을 원하는 것임. 인덱스를 만드는 건 서버 시간과 디스크 공간 낭비임
      일이 꼬이거나 정말 필요해지면 나중에 그 인덱스를 만들 수 있음
    • 그럼 적절한 방식이 무엇인지도 같이 말해줄 수 있나?

  • “Case 2. IF [NOT] EXISTS 오용” 부분은 좋은 오용 예시를 제시하지 않았음
    그리고 실제로 그렇게 쓰는 게 맞음. 깔끔하고 단순하며 숨은 함정도 없음. 테이블이 몇 개뿐이면 Schema 마이그레이션 도구는 과한 부담임

    • 함정은 간단함. “로직으로 문제를 가려서 이상 상태의 위험을 더한다”는 것임
      나쁜 데이터 위에 반창고를 붙인다고 문제가 해결되는 게 아니라 가려질 뿐임. 문제의 종류에 따라 나중에 예상 못 한 방식으로, 최악의 시점에 터질 수 있음
      이 경우 “나쁜 데이터”는 있어야 하거나 없어야 하는데 반대로 존재하는 테이블, 컬럼, 뷰임. 왜 아직 존재하면 안 되는 테이블이 존재하는가? 삭제에 실패했나? 기존 테이블의 Schema가 맞나? 같은 마이그레이션이 실수로 두 번 실행됐나?
      각 마이그레이션 뒤 Schema는 정확한 상태여야 함. 마이그레이션에 IF [NOT] EXISTS가 들어간다면, 이전 마이그레이션 이후 Schema가 정확한 상태로 남지 않았다는 뜻임. Schema 상태를 확신하지 못하는 건 좋지 않음
    • 글이 오용을 꽤 잘 설명했다고 봄. 핵심은 별도 경로의 Schema 변경은 프로세스와 워크플로 문제이므로 직접 해결해야 한다는 것임
      이미 존재하는 테이블의 컬럼이 마이그레이션에서 만들려는 것과 다르면 어떡할 것인가? IF EXISTS는 마이그레이션을 성공시켜 버리지만 Schema는 나쁜 상태로 남김. 이런 경우에는 마이그레이션이 빠르게 실패하는 편이 낫다

  • int4를 대리 기본 키로 쓰는 부분에 대한 사소한 지적임
    중요한 건 테이블 크기가 아니라 인덱스 크기 아닌가? 테이블 크기에는 이미 23바이트 헤더와 정렬 패딩이 있어서 4바이트 차이는 별 영향이 없음. 하지만 인덱스를 메모리에 더 많이 올릴 수 있다면 이점이 있을 수 있음. 인덱스 엔트리에는 8바이트 헤더가 있음
    또 예시에 나온 10억 행은 int4의 최댓값에 너무 가까워서 불안함
    그래도 글은 훌륭함

    • 맞음. 인덱스 크기도 있고 디스크 크기도 있음. Postgres는 디스크에서는 테이블 행을 촘촘히 패킹하지만, RAM에서는 그렇지 않음
      그러면 디스크의 8KB 페이지가 RAM에서는 8KB보다 커질 수도 있다는 뜻인가?
      테이블 행 데이터의 작업 메모리에만 영향을 주는 것 같음. 여전히 중요하긴 함. 특히 Postgres는 행이 무작위 순서라 범위 쿼리의 지역성에 끔찍함. 다만 결정적 통찰까지는 아니라고 봄

  • DB 관련 문제에서 대체로 보호받아 온 개발자임. Django 안에서는 마이그레이션 만들기, 모델 테이블 만들기, ORM으로 쿼리하기는 알지만 내부에서 벌어지는 많은 일은 흑마술처럼 느껴짐
    이제 회사를 시작하면서 이런 문제를 마주치고 혼자 해결해야 할까 봐 불안함. 개발 환경에서 무엇을 해야 하는지 배우려면 어떻게 접근해야 할까?

    • 실패하고 실수에서 배우면 됨. 아니면 개발자를 고용해서 함께 실패하고 함께 배우면 됨

  • Postgres를 좋아하지만 내장된 배치 업데이트/삭제 방법이 없다는 점은 정말 싫음
    가장 짜증나는 부분이고, 벽에 부딪힐 때마다 거의 매달 배처를 다시 작성해야 함

답변달기