PostgreSQL 고급 기능: JSONB, CTE, Window Function

Prologue: SQL 한 줄이 애플리케이션 코드 100줄을 대체하던 순간

처음엔 PostgreSQL을 그냥 MySQL의 대체품 정도로 생각했다. SELECT, INSERT, UPDATE, DELETE만 쓰고, 복잡한 로직은 전부 애플리케이션 코드에서 처리했다. 사용자 활동 로그를 분석하려면 데이터 전부를 가져와서 JavaScript로 필터링하고 집계했다. 계층형 댓글을 보여주려면 재귀 함수를 짜고, 순위를 매기려면 배열을 정렬한 뒤 인덱스를 붙였다.

그러던 어느 날, 레거시 코드를 뜯어보다가 한 줄짜리 SQL 쿼리가 내가 50줄로 짠 로직을 대체하고 있는 걸 발견했다. WITH RECURSIVE로 댓글 트리를 한 번에 가져오고, ROW_NUMBER() OVER(PARTITION BY ...)로 그룹별 순위를 매기고, JSONB 연산자로 중첩된 JSON 데이터를 마치 NoSQL처럼 다루고 있었다.

그 순간 깨달았다. 내가 PostgreSQL의 5%만 쓰고 있었다는 걸. JSONB, CTE, Window Function... 이 세 가지만 제대로 이해해도 데이터베이스가 완전히 다른 도구처럼 느껴진다.

Aha Moment: 데이터베이스가 단순 저장소가 아니라 엔진이었다

JSONB - 관계형 데이터베이스에서 NoSQL 유연성을 얻다

첫 번째 충격은 JSONB였다. 사용자 프로필에 커스텀 필드를 추가하고 싶었는데, 컬럼을 계속 추가하는 건 스키마가 지저분해지고, 별도 테이블로 분리하면 JOIN이 복잡해진다. JSONB는 이 딜레마를 해결했다.

-- 사용자 테이블에 JSONB 컬럼 추가
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb
);

-- 다양한 구조의 메타데이터 저장
INSERT INTO users (email, metadata) VALUES
  ('founder@startup.com', '{"role": "founder", "preferences": {"theme": "dark", "language": "ko"}}'),
  ('developer@company.com', '{"role": "developer", "skills": ["PostgreSQL", "TypeScript"], "experience_years": 5}');

JSONB는 단순히 JSON을 저장만 하는 게 아니다. 쿼리가 가능하다는 게 핵심이다.

-- -> 연산자: JSON 필드 접근 (결과도 JSONB)
SELECT metadata -> 'role' AS role FROM users;

-- ->> 연산자: JSON 필드를 텍스트로 추출
SELECT metadata ->> 'role' AS role FROM users WHERE metadata ->> 'role' = 'developer';

-- @> 연산자: JSON 포함 여부 확인 (인덱스 활용 가능!)
SELECT * FROM users WHERE metadata @> '{"role": "founder"}';

-- ? 연산자: 키 존재 여부 확인
SELECT * FROM users WHERE metadata ? 'skills';

-- jsonb_agg: 여러 행의 데이터를 JSON 배열로 집계
SELECT
  metadata ->> 'role' AS role,
  jsonb_agg(email) AS members
FROM users
GROUP BY metadata ->> 'role';

여기서 게임 체인저는 GIN 인덱스다. JSONB 쿼리도 인덱스를 타게 만들 수 있다.

-- JSONB 전체에 GIN 인덱스 생성
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- 이제 @>, ?, ?&, ?| 연산자가 인덱스를 활용한다
EXPLAIN ANALYZE
SELECT * FROM users WHERE metadata @> '{"preferences": {"theme": "dark"}}';

마치 MongoDB의 유연성을 얻으면서도 PostgreSQL의 트랜잭션과 일관성을 유지하는 느낌이었다. 스키마 마이그레이션 없이도 새 필드를 추가할 수 있고, 타입 안정성이 필요한 부분은 일반 컬럼으로, 유동적인 부분은 JSONB로 처리하는 하이브리드 설계가 가능해졌다.

CTE - 복잡한 쿼리를 읽기 쉽게 분해하다

CTE(Common Table Expression)는 WITH 절로 시작하는 임시 결과 집합이다. 처음엔 "이거 서브쿼리랑 뭐가 다르지?"라고 생각했는데, 실제로 써보니 코드 가독성이 극적으로 개선됐다.

-- CTE 없이 작성한 복잡한 쿼리
SELECT
  u.email,
  (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_count,
  (SELECT AVG(views) FROM posts p WHERE p.user_id = u.id) AS avg_views
FROM users u
WHERE (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) > 5;

-- CTE로 리팩토링 - 훨씬 읽기 쉽다
WITH user_stats AS (
  SELECT
    user_id,
    COUNT(*) AS post_count,
    AVG(views) AS avg_views
  FROM posts
  GROUP BY user_id
)
SELECT
  u.email,
  us.post_count,
  us.avg_views
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE us.post_count > 5;

하지만 진짜 파워는 Recursive CTE에 있다. 계층형 데이터를 다룰 때 이게 없으면 지옥이다.

-- 댓글과 대댓글이 있는 테이블
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT NOT NULL,
  parent_id INT REFERENCES comments(id),
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Recursive CTE로 전체 댓글 트리 가져오기
WITH RECURSIVE comment_tree AS (
  -- Base case: 최상위 댓글 (parent_id가 NULL)
  SELECT
    id,
    parent_id,
    content,
    ARRAY[id] AS path,
    1 AS depth
  FROM comments
  WHERE parent_id IS NULL AND post_id = 123

  UNION ALL

  -- Recursive case: 자식 댓글 찾기
  SELECT
    c.id,
    c.parent_id,
    c.content,
    ct.path || c.id,
    ct.depth + 1
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree ORDER BY path;

이전엔 최상위 댓글을 가져온 다음 각 댓글마다 자식을 찾는 쿼리를 재귀적으로 실행했다. N+1 쿼리 문제의 전형. Recursive CTE는 한 번의 쿼리로 전체 트리를 가져온다. 조직도, 카테고리 계층, 파일 시스템... 계층 구조가 있는 곳이라면 어디든 적용 가능했다.

Window Function - 그룹 단위 분석의 새로운 차원

Window Function은 내 SQL 관점을 완전히 바꿔놓았다. GROUP BY는 그룹당 하나의 결과만 반환하지만, Window Function은 각 행마다 그룹 기반 계산 결과를 붙인다.

-- 카테고리별 게시글 조회수 순위
SELECT
  title,
  category,
  views,
  ROW_NUMBER() OVER(PARTITION BY category ORDER BY views DESC) AS rank_in_category,
  RANK() OVER(ORDER BY views DESC) AS global_rank
FROM posts;

PARTITION BY는 "이 컬럼별로 그룹을 나누되, 결과는 각 행마다 보여줘"라는 의미다. 카테고리별 TOP 3를 뽑는 쿼리가 이렇게 간단해진다.

WITH ranked_posts AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY category ORDER BY views DESC) AS rn
  FROM posts
)
SELECT * FROM ranked_posts WHERE rn <= 3;

LAG와 LEAD는 이전/다음 행의 값을 참조할 수 있게 해준다. 사용자 활동 간격을 계산하는 쿼리가 이렇게 바뀐다.

-- 이전 활동과의 시간 차이 계산
SELECT
  user_id,
  action,
  created_at,
  LAG(created_at) OVER(PARTITION BY user_id ORDER BY created_at) AS previous_action_time,
  created_at - LAG(created_at) OVER(PARTITION BY user_id ORDER BY created_at) AS time_since_last_action
FROM user_activities
ORDER BY user_id, created_at;

예전엔 이런 계산을 하려면 데이터를 전부 가져와서 애플리케이션 코드로 처리했다. Window Function은 데이터베이스 내에서 바로 해결한다.

실제로 마주친 구체적 문제들 뜯어보기

Generated Columns - 계산된 필드를 컬럼처럼 쓰기

JSONB 데이터에서 특정 필드를 자주 조회하는데 매번 ->> 연산자를 쓰는 게 번거로웠다. Generated Column을 쓰니 JSONB의 유연성과 일반 컬럼의 성능을 동시에 얻을 수 있었다.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  data JSONB NOT NULL,
  -- JSONB에서 price를 추출해서 가상 컬럼으로
  price NUMERIC GENERATED ALWAYS AS ((data->>'price')::numeric) STORED,
  -- STORED: 디스크에 저장 (인덱스 가능), VIRTUAL: 조회 시 계산
  category VARCHAR(100) GENERATED ALWAYS AS (data->>'category') STORED
);

-- Generated Column에 인덱스도 가능
CREATE INDEX idx_products_price ON products(price);

-- 이제 일반 컬럼처럼 쿼리 가능
SELECT * FROM products WHERE price > 10000 AND category = 'electronics';

UPSERT - INSERT와 UPDATE를 하나로

사용자 활동 통계를 업데이트하는 로직에서 "존재하면 UPDATE, 없으면 INSERT"를 구현하려고 했다. ON CONFLICT를 알기 전엔 SELECT로 확인 후 분기 처리했는데, race condition도 신경 써야 하고 코드가 지저분했다.

-- 일일 사용자 활동 통계
CREATE TABLE daily_user_stats (
  user_id INT NOT NULL,
  date DATE NOT NULL,
  action_count INT DEFAULT 0,
  last_action_at TIMESTAMP,
  PRIMARY KEY (user_id, date)
);

-- UPSERT: 있으면 UPDATE, 없으면 INSERT
INSERT INTO daily_user_stats (user_id, date, action_count, last_action_at)
VALUES (123, '2026-02-03', 1, NOW())
ON CONFLICT (user_id, date)
DO UPDATE SET
  action_count = daily_user_stats.action_count + EXCLUDED.action_count,
  last_action_at = EXCLUDED.last_action_at;

EXCLUDED는 INSERT하려던 값을 참조한다. 이 한 줄이 트랜잭션 안정성까지 보장하면서 INSERT/UPDATE 로직을 대체했다.

구체적인 예제 - 복합 분석 쿼리

실제로 마주친 요구사항: "카테고리별로 최근 30일간 가장 많이 조회된 게시글 TOP 5를 보여주고, 각 게시글의 일별 조회수 추이를 JSON으로 반환하라."

이걸 애플리케이션 코드로 짜려면 여러 쿼리와 복잡한 로직이 필요했다. PostgreSQL의 고급 기능을 조합하니 하나의 쿼리로 해결됐다.

WITH recent_views AS (
  -- 최근 30일 조회수 데이터
  SELECT
    post_id,
    DATE(created_at) AS view_date,
    COUNT(*) AS daily_views
  FROM post_views
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY post_id, DATE(created_at)
),
post_totals AS (
  -- 게시글별 총 조회수
  SELECT
    post_id,
    SUM(daily_views) AS total_views
  FROM recent_views
  GROUP BY post_id
),
ranked_posts AS (
  -- 카테고리별 순위
  SELECT
    p.id,
    p.title,
    p.category,
    pt.total_views,
    ROW_NUMBER() OVER(PARTITION BY p.category ORDER BY pt.total_views DESC) AS rank
  FROM posts p
  JOIN post_totals pt ON p.id = pt.post_id
)
SELECT
  rp.category,
  rp.title,
  rp.total_views,
  rp.rank,
  -- 일별 조회수를 JSON 배열로 집계
  jsonb_agg(
    jsonb_build_object(
      'date', rv.view_date,
      'views', rv.daily_views
    ) ORDER BY rv.view_date
  ) AS daily_trend
FROM ranked_posts rp
LEFT JOIN recent_views rv ON rp.id = rv.post_id
WHERE rp.rank <= 5
GROUP BY rp.category, rp.title, rp.total_views, rp.rank
ORDER BY rp.category, rp.rank;

CTE로 복잡한 로직을 단계별로 분해하고, Window Function으로 순위를 매기고, jsonb_agg로 시계열 데이터를 JSON으로 묶었다. 이 쿼리 하나가 수십 줄의 JavaScript 코드와 여러 번의 데이터베이스 왕복을 대체했다.

Summary: 데이터베이스를 제대로 활용하기 시작했다

결국 이해한 건 이거였다. PostgreSQL은 단순한 데이터 저장소가 아니라 강력한 데이터 처리 엔진이다. 애플리케이션 코드에서 복잡한 집계와 변환을 하는 대신, 데이터베이스가 최적화된 방식으로 처리하게 하는 게 훨씬 효율적이다.

• JSONB: 스키마 유연성이 필요한 부분에 NoSQL의 장점을 가져오되, GIN 인덱스로 성능을 유지한다
• CTE: 복잡한 쿼리를 읽기 쉽게 분해하고, Recursive CTE로 계층형 데이터를 한 번에 처리한다
• Window Function: GROUP BY의 한계를 넘어 행별로 그룹 기반 계산을 수행한다

이 세 가지를 제대로 익히고 나니, "이거 SQL로 할 수 있을까?"라는 질문에 대한 답이 대부분 "할 수 있다"로 바뀌었다. 데이터베이스가 할 수 있는 일을 데이터베이스에 맡기니, 애플리케이션 코드는 더 단순해지고 성능은 더 좋아졌다.

PostgreSQL의 고급 기능은 선택이 아니라 필수다. 단순 CRUD만 쓰고 있다면, 엔진의 5%만 쓰고 있는 것이다.

---