유용한 PostgreSQL 기능 8가지

PostgreSQL을 "그냥 데이터를 넣고 빼는 곳"으로만 쓰면 애플리케이션 코드에서 루프를 돌리고, 락을 직접 구현하고, 정합성을 application 레벨에서 지키느라 고생한다. 시니어 DBA·백엔드는 그 일의 상당 부분을 DB 한 줄로 끝낸다. 잘 안 알려졌지만 실전에서 강력한 PostgreSQL 기능 8가지를 정리한다.

1. DISTINCT ON — 그룹별 첫 행

"사용자별 최신 주문 1건"처럼 그룹마다 대표 행 하나를 뽑는 일은 흔하다. 보통 윈도우 함수 + 서브쿼리로 풀지만, PostgreSQL에는 전용 문법 DISTINCT ON이 있다.

-- user_id 마다, created_at 이 가장 최근인 행 1건
SELECT DISTINCT ON (user_id) user_id, id, created_at, total
FROM orders
ORDER BY user_id, created_at DESC;

규칙은 하나 — DISTINCT ON (...) 의 컬럼이 ORDER BY 의 맨 앞에 와야 한다. 그 다음 정렬 기준(created_at DESC)이 "그룹 안에서 어떤 행을 첫 행으로 볼지"를 정한다. 간결하고 빠르다.

2. LATERAL 조인 — 그룹별 Top-N

DISTINCT ON 이 그룹당 1건이라면, 그룹당 N건(사용자별 최근 주문 3건)은 LATERAL 조인이 정석이다. LATERAL 은 오른쪽 서브쿼리가 왼쪽 행의 값을 참조할 수 있게 해 준다 — 사실상 "행마다 도는 상관 서브쿼리를 조인처럼" 쓰는 것.

SELECT u.id AS user_id, o.id AS order_id, o.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT id, created_at
  FROM orders
  WHERE orders.user_id = u.id      -- 바깥 행 u 를 참조
  ORDER BY created_at DESC
  LIMIT 3
) o ON true;

LEFT JOIN ... ON true 로 두면 주문이 없는 사용자도 남는다. 윈도우 함수(row_number() ... <= 3)보다 의도가 분명하고, LIMIT 덕에 불필요한 행을 덜 읽는다.

3. FILTER — 조건부 집계

CASE WHEN 을 집계 안에 욱여넣는 대신, PostgreSQL은 집계 함수에 FILTER 절을 붙일 수 있다. 한 번의 스캔으로 여러 조건 집계를 깔끔하게 뽑는다.

SELECT
  count(*)                                AS total,
  count(*) FILTER (WHERE status = 'paid') AS paid,
  count(*) FILTER (WHERE status = 'refunded') AS refunded,
  avg(total) FILTER (WHERE status = 'paid')   AS avg_paid_amount
FROM orders;

sum(CASE WHEN status='paid' THEN 1 ELSE 0 END) 보다 읽기 쉽고, 대시보드 쿼리를 한 방에 정리한다.

4. 범위 타입 + EXCLUDE 제약 — 겹침을 DB가 막는다

"같은 회의실은 시간이 겹치게 예약될 수 없다" 같은 규칙을 애플리케이션에서 막으면, 동시 요청에서 레이스가 난다. PostgreSQL은 범위 타입(tstzrange) 과 EXCLUDE 제약으로 겹침 자체를 DB 레벨에서 금지한다.

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- room_id 의 '=' 비교를 GiST에 넣기 위해
 
CREATE TABLE bookings (
  room_id int NOT NULL,
  during  tstzrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)  -- 같은 방(=)에서 시간 겹침(&&) 금지
);
 
INSERT INTO bookings VALUES (1, '[2026-06-14 10:00, 2026-06-14 11:00)');
INSERT INTO bookings VALUES (1, '[2026-06-14 10:30, 2026-06-14 12:00)');
-- ERROR: conflicting key value violates exclusion constraint  ← DB가 거부

UNIQUE 가 "정확히 같은 값"을 막는다면, EXCLUDE 는 "연산자로 정의한 충돌"(여기선 겹침 &&)을 막는다. 예약·스케줄·요금구간 시스템에서 정합성을 코드가 아니라 스키마로 보장한다.

5. FOR UPDATE SKIP LOCKED — PostgreSQL로 작업 큐 만들기

별도 메시지 큐 없이 PostgreSQL 테이블만으로 경합 없는 작업 큐를 만들 수 있다. 핵심은 SKIP LOCKED — 다른 워커가 이미 잠근 행은 건너뛰고 다음 가용 행을 집어간다.

-- 워커가 작업 하나를 원자적으로 점유 (여러 워커가 동시에 돌아도 서로 다른 행을 가져감)
WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs SET status = 'running', started_at = now()
FROM next WHERE jobs.id = next.id
RETURNING jobs.*;

SKIP LOCKED 가 없으면 워커들이 같은 행을 기다리며 줄을 선다. 있으면 각자 다른 작업을 동시에 집어가 처리량이 워커 수만큼 늘어난다. 소규모~중규모에선 Redis/SQS 없이 이걸로 충분하다.

6. WITH RECURSIVE — 트리·그래프 질의

댓글 스레드, 조직도, 카테고리 트리처럼 자기참조 계층은 재귀 CTE로 한 번에 펼친다.

-- id=42 댓글의 모든 자손을 깊이와 함께
WITH RECURSIVE thread AS (
  SELECT id, parent_id, body, 1 AS depth
  FROM comments WHERE id = 42            -- 시작점(anchor)
  UNION ALL
  SELECT c.id, c.parent_id, c.body, t.depth + 1
  FROM comments c
  JOIN thread t ON c.parent_id = t.id    -- 직전 결과를 다시 조인
)
SELECT * FROM thread ORDER BY depth;

참고로 PostgreSQL 12부터 CTE가 기본 인라인(최적화)된다. 의도적으로 결과를 한 번만 계산해 고정하고 싶으면 WITH x AS MATERIALIZED (...), 반대로 펼치고 싶으면 NOT MATERIALIZED 로 옵티마이저에 힌트를 준다.

7. 생성 컬럼 (Generated Columns)

값을 애플리케이션에서 매번 계산해 넣는 대신, 다른 컬럼에서 파생되는 값을 컬럼 정의로 둔다. 항상 일관되고, 인덱스도 걸 수 있다.

CREATE TABLE products (
  price_cents int NOT NULL,
  tax_cents   int NOT NULL,
  total_cents int GENERATED ALWAYS AS (price_cents + tax_cents) STORED
);

STORED 라 디스크에 실제 저장되며 INSERT/UPDATE 시 자동 갱신된다. 정규화하긴 애매하지만 자주 조회·정렬하는 파생값(합계, 정규화된 검색용 텍스트 등)에 좋다.

8. 데이터 변경 CTE — 한 문장으로 원자적 이동

WITH 안에서 INSERT/UPDATE/DELETE 를 RETURNING 과 함께 쓰면, 여러 테이블 변경을 한 문장(=원자적) 으로 묶는다. 대표 예가 "오래된 행을 아카이브 테이블로 옮기기".

WITH moved AS (
  DELETE FROM events
  WHERE created_at < now() - interval '90 days'
  RETURNING *
)
INSERT INTO events_archive
SELECT * FROM moved;

DELETE 와 INSERT 가 한 구문이라 중간에 실패해도 반쪽 상태가 남지 않는다. 애플리케이션에서 두 쿼리로 나눠 트랜잭션을 직접 관리할 필요가 없다.

보너스 — 느린 쿼리부터 찾기

기능을 잘 쓰는 것만큼 무엇이 느린지 아는 것이 중요하다. pg_stat_statements 확장은 쿼리별 누적 실행 시간을 집계해 준다(서버 shared_preload_libraries 설정 필요).

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

총 소요시간 상위 쿼리가 곧 최적화 1순위다. 추측 대신 데이터로 병목을 짚는다.

정리

이 기능들의 공통점은 애플리케이션이 떠안던 일을 DB로 내리는 것이다 — 그룹별 대표 행(DISTINCT ON·LATERAL), 조건부 집계(FILTER), 정합성(EXCLUDE), 동시성(SKIP LOCKED), 계층(WITH RECURSIVE), 파생값(생성 컬럼), 원자적 이동(데이터 변경 CTE). 코드가 줄고, 레이스가 사라지고, 정합성이 스키마로 보장된다. PostgreSQL은 생각보다 훨씬 많은 일을 대신 해 줄 수 있다.