스타트업이 Postgres로 버티려면 알아야 할 생존 팁

Postgres는 “인덱스 붙이면 끝”에서 시작하지만, 거기서 끝나면 터짐

• 이 글은 Hatchet이 2년 동안 프로덕션 Postgres를 굴리며 쌓은 내부 문서를 공개한 것임

  • 작성자의 출발점도 솔직함: 처음엔 “쿼리가 느리면 인덱스가 필요하다” 정도만 알고 있었다고 함
  • 그래서 글도 SQL 기본, row, table, index를 아는 개발자가 실제 운영에서 부딪히는 문제를 따라가는 식으로 구성돼 있음

• ORM을 쓰더라도 이 내용은 피하기 어렵다는 게 초반 메시지임

  • 스케일이 올라가면 ORM 추상화 안에서는 못 하는 최적화가 꽤 많아짐
  • Hatchet은 Go 스택에서 sqlc를 쓰고 있고, Prisma TypedSQL 같은 접근도 흥미롭다고 언급함

스키마와 읽기 쿼리는 초반에 대충 넘기면 나중에 이자 붙음

• 스키마는 배포 후 바꾸기 제일 어려운 축이라, 처음부터 읽기·쓰기 패턴을 같이 봐야 함

  • 이 테이블이 읽기 중심인지 쓰기 중심인지, 가장 자주 쓰는 필터가 뭔지, 자주 업데이트되는 컬럼이 뭔지 먼저 물어보라는 조언
  • 정규화도 중요하지만, 빠르게 움직이는 팀에서는 쿼리 효율과 사용성을 위해 jsonb 컬럼에 데이터를 넣는 선택이 더 현실적일 때도 있다고 함

• 기본 스키마 룰은 꽤 실용적임

  • 기본 키는 identity column이나 내장 UUID를 쓰고, bigserial보다 identity column이 약간 더 낫다고 봄
  • 시간 컬럼은 항상 timestamptz를 쓰라고 함
  • 기본 키는 반드시 두고, 저볼륨 테이블에서는 정합성이 중요할 때 cascading delete가 있는 foreign key도 괜찮지만 고볼륨에서는 조심하라고 함

• SELECT 성능의 단순한 정신 모델은 “인덱스로 바로 찾거나, 테이블을 다 읽거나”임

  • Postgres가 명시적 인덱스, unique constraint, primary key를 탈 수 있으면 빠르게 찾음
  • 기본 인덱스는 B-tree이고, 대략 log(n) 시간에 row를 찾을 수 있다는 감각으로 이해하면 됨
  • 인덱스를 못 쓰면 sequential scan, 즉 seq scan으로 전체 row를 훑는데, 2만 row 미만 테이블에서는 거의 티가 안 날 수 있어 초반엔 문제를 못 느끼기 쉬움

• JOIN도 WHERE만큼 진지하게 봐야 함

  • inner join에서 안쪽 조인은 보통 primary key를 쓰는 게 자연스럽고, 그렇지 않다면 스키마 설계나 정규화가 이상할 가능성이 있음
  • ON 절도 필터 조건이므로 인덱스를 고려해야 함

쓰기, 마이그레이션, 커넥션은 장애로 바로 이어지는 영역임

• 좋은 write query의 전제는 트랜잭션을 짧게 유지하는 것임

  • 트랜잭션 중간에 외부 서비스를 호출하는 건 정말 이유가 있을 때만 하라는 조언
  • row를 업데이트하면 커밋 전까지 그 row에 락이 걸리므로, 필요한 row만 잠그는 습관이 중요함

• 큰 테이블에서 인덱스를 만들 때 CREATE INDEX를 그냥 치면 쓰기가 막힐 수 있음

  • 일반 CREATE INDEX는 테이블을 잠그고 insert/update를 막을 수 있음
  • 이미 커진 테이블에는 CREATE INDEX CONCURRENTLY를 쓰라는 게 핵심

• 마이그레이션의 기본 질문은 “이게 모든 write를 막는가?”임

  • 가능하면 additive migration, 즉 컬럼 삭제나 제거보다 추가 중심으로 가는 게 롤백과 부분 실패 대응에 유리함
  • ALTER TABLE 계열 작업은 한 번 더 의심해야 하고, 큰 테이블에 check constraint를 추가할 때도 NOT VALID 없이 하면 write를 막을 수 있음
  • 더 고급으로 가면 expand and contract migration 패턴을 보라고 함

• 커넥션은 공짜가 아니라 CPU와 메모리를 먹는 비싼 자원임

  • connection churn이 크면 리소스 낭비가 커지고, connection storm은 Postgres 내부 락과 엮여 디버깅하기 빡센 문제를 만들 수 있음
  • 외부 풀러로 pgbouncer를 추천하고, 어렵다면 인메모리 풀러가 차선책이라고 함
  • Hatchet은 오픈소스라 사용자 DB에 풀러가 있다고 가정할 수 없어 Go의 pgxpool을 사용한다고 밝힘

쿼리 플래너는 믿되, 가끔 직접 멱살 잡고 봐야 함

• 복잡한 쿼리에서는 단순히 인덱스를 하나 더 붙이는 걸로 해결이 안 될 수 있음

  • JOIN이 많거나 여러 조인 타입이 섞이면 Postgres가 어떤 실행 경로를 고를지 애매해짐
  • 작성자는 쿼리 플래너를 “가장 새는 추상화”라고 부르면서, 통제는 거의 못 하지만 성격은 알아야 한다고 말함

• 쿼리 플래너가 기대와 다르게 움직이는 흔한 이유는 table statistics임

  • 통계는 ANALYZE 때 수집되고, autovacuum이 돌 때도 갱신됨
  • autovacuum이 자주 돌수록 쿼리 통계도 최신에 가까워지므로, 분석이 덜 된 테이블은 잘못된 실행 계획으로 이어질 수 있음

• 느린 쿼리 디버깅에는 EXPLAIN ANALYZE가 핵심 도구임

  • 실행 계획을 보여주고 실제로 쿼리를 실행한 뒤, 통계 기반 추정치와 실제 스캔 row 수를 비교해줌
  • 운영에서 ANALYZE는 실제 실행이므로 조심해야 하고, 계획만 보려면 EXPLAIN만 쓸 수 있음
  • 작성자는 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) 형태로 뽑고 explain.dalibo.com으로 시각화한다고 함

• 인덱스가 있어도 seq scan이 더 싸다고 판단되면 Postgres는 전체 스캔을 고를 수 있음

  • 인덱스는 별도 구조에 있고 실제 row는 heap에 있으므로, 많은 row를 찾아야 하면 index scan 자체가 비쌀 수 있음
  • 이 경우 쿼리를 크게 바꾸거나 partitioning 같은 구조적 선택을 고민해야 함

쓰기량이 늘면 batch와 autovacuum이 생존선이 됨

• 대량 쓰기에서는 쿼리마다 붙는 오버헤드를 줄여야 함

  • DB 왕복 시간, 커넥션 풀에서 커넥션 얻는 시간, Postgres 내부 처리와 락 비용이 모두 누적됨
  • 여러 row를 한 번에 묶어 보내면 이 비용을 줄일 수 있고, Hatchet은 batching으로 처리량이 약 10배 늘 수 있었다고 함

• 기본 autovacuum 설정은 고쓰기 시스템에서 DB를 죽일 수 있음

  • Postgres는 update/delete 후 예전 row 버전이 dead tuple로 남고, 모든 관련 트랜잭션이 끝난 뒤에야 정리 가능함
  • autovacuum이 dead tuple 정리와 transaction id 관리를 맡는데, 쓰기 속도를 못 따라가면 상태가 급격히 나빠짐
  • autovacuum 쿼리가 1시간 넘게 돌고 있다면 설정 조정을 고민하라고 함

• bloat도 dead tuple만의 문제가 아님
  • Postgres page는 8KB 단위인데, dead tuple이 회수된 뒤에도 page가 부분적으로 비어 있으면 table bloat가 생겨 디스크 사용량이 크게 늘 수 있음
  • VACUUM FULL은 거의 좋은 생각이 아니라고 보고, bloated table에는 pg_repack 같은 확장을 언급함
  • index bloat는 REINDEX INDEX CONCURRENTLY로 대응할 수 있음
  • Postgres 19에는 REPACK...CONCURRENTLY가 들어올 예정이라고 언급하지만, 작성자는 아직 테스트하진 않았다고 함

고급 기능은 job queue, partitioning, 대형 이관에서 빛남

• FOR UPDATE SKIP LOCKED는 여러 워커가 row를 나눠 잡을 때 강력함

  • 선택한 row를 현재 트랜잭션용으로 예약하면서, 이미 잠긴 row는 건너뛸 수 있음
  • Hatchet은 주로 job queue 구현에 쓰고, 여러 애플리케이션 인스턴스 사이에서 tenant lease를 분산하는 데도 사용함

• partitioning은 시계열 데이터에서 특히 유용함

  • Hatchet의 경우 historical task data가 예시임
  • 각 파티션을 독립적으로 autovacuum할 수 있어 vacuum 스케일을 키우기 좋음
  • 오래된 데이터 삭제도 row를 하나씩 지우는 대신 파티션 테이블을 drop하면 거의 즉시 끝남
  • 단, planner가 partition pruning을 못 하면 read query에 오버헤드가 생길 수 있고, 최근 Postgres에서는 이 부분이 많이 좋아졌다고 함

• 정말 큰 테이블을 다른 테이블로 옮기는 migration은 단일 트랜잭션으로 밀어붙이면 위험함

  • 수 시간짜리 복사는 long-running transaction이 되고, autovacuum이 일을 제대로 못 해서 시스템 전체에 dead tuple과 bloat를 쌓을 수 있음
  • 기존 테이블에 계속 write가 들어오면 새 테이블은 새 데이터를 놓치는 문제도 생김
  • Hatchet은 Postgres trigger와 대형 batched backfill을 조합하고, primary key unique constraint로 중복 write를 막는 방식을 쓴다고 함

---

기술 맥락

• 이 글의 핵심 선택은 Postgres를 단순 저장소가 아니라 운영 대상 시스템으로 보는 거예요. 쿼리 하나가 느린 문제처럼 보여도 실제 원인은 인덱스, 통계, 락, autovacuum, 커넥션 풀이 같이 얽혀 있거든요.

• CREATE INDEX CONCURRENTLY 같은 선택이 중요한 이유는 서비스가 살아 있는 상태에서 스키마를 바꿔야 하기 때문이에요. 작은 테이블에서는 일반 인덱스 생성이 티가 안 나지만, 큰 테이블에서는 write 전체를 막아서 바로 장애처럼 보일 수 있어요.

• batching으로 처리량이 약 10배 늘었다는 대목은 “DB가 느리다”를 하드웨어 문제로만 보면 안 된다는 얘기예요. 많은 요청을 한 줄씩 보내면 네트워크 왕복, 커넥션 획득, 내부 락 비용을 매번 내야 하니까, 같은 Postgres라도 요청 모양을 바꾸면 결과가 크게 달라져요.

• autovacuum 이야기가 긴 건 Postgres의 MVCC 구조 때문이에요. update/delete가 실제 파일에서 즉시 깨끗하게 사라지는 게 아니라 예전 row 버전이 남고, 이걸 제때 치우지 못하면 성능 문제가 아니라 다운타임 리스크로 커져요.

• partitioning은 무작정 큰 테이블을 쪼개는 기술이 아니라, 데이터 수명과 운영 작업을 분리하기 위한 선택이에요. 시간 기준 데이터라면 오래된 파티션을 통째로 버릴 수 있고, vacuum도 파티션 단위로 움직여서 운영자가 다룰 수 있는 크기로 문제가 줄어들어요.