PostgreSQL

정의

PostgreSQL 은 1986 년 UC Berkeley 의 POSTGRES 프로젝트에서 출발해 1996 년 SQL 지원 버전으로 오픈소스화된 객체-관계형 DBMS (ORDBMS). PostgreSQL Global Development Group 이 관리, PostgreSQL License (BSD/MIT 계열).

표준 SQL 적합성, 확장성 (extension), 강력한 JSONB, 풍부한 인덱스 종류, 표준 호환 MVCC 가 특징. “오픈소스의 Oracle”.

표준 SQL 의 골격은 SQL 참고. 본 wiki 는 PostgreSQL dialect-specific 한 부분만 정리.

핵심 특성

• 표준 적합성 높음: SQL:2023 의 상당 부분 구현.
• MVCC: 읽기-쓰기 비차단. 자세한 격리 수준은 SQL DCL TCL 참고.
• 확장성: CREATE EXTENSION 으로 PostGIS, pg_trgm, hstore, vector (pgvector) 등.
• JSONB: 바이너리 JSON, 인덱싱 + containment 강력.
• 배열 / 범위 / 사용자 정의 타입: 표준을 넘어선 풍부함.
• 다양한 인덱스: B-tree, Hash, GIN, GiST, BRIN, SP-GiST.
• 트랜잭션 DDL: CREATE TABLE 도 롤백 가능. 마이그레이션 안정성에 결정적.

PostgreSQL 만의 / 두드러진 문법

SERIAL 과 IDENTITY

SQL DDL 의 자동 증가 PK 비교 참고. 짧게:

-- 구식 (여전히 흔함)
CREATE TABLE t (id SERIAL PRIMARY KEY);

-- 표준 (10+ 권장)
CREATE TABLE t (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
CREATE TABLE t (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);

SERIAL 은 실제로 시퀀스 + DEFAULT 의 sugar. 권한 관리, 백업/복원 시 약점이 있어 IDENTITY 가 더 깔끔.

RETURNING

INSERT/UPDATE/DELETE 의 결과를 즉시 반환. MySQL 에는 없는 강력한 기능.

INSERT INTO users (name, city)
VALUES ('Alice', 'Seoul')
RETURNING id, created_at;
​
UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100
RETURNING balance;
​
DELETE FROM events
WHERE created_at < NOW() - INTERVAL '30 day'
RETURNING id;

 id |       created_at
----+------------------------
4 | 2026-06-20 14:23:01+09
​
balance
---------
   900
​
-- 삭제된 행들의 id 가 반환됨

CTE 와 결합하면 한 쿼리로 복잡한 워크플로 처리.

WITH moved AS (
  DELETE FROM staging WHERE processed_at IS NOT NULL
  RETURNING *
)
INSERT INTO archive SELECT * FROM moved;

ON CONFLICT (UPSERT)

INSERT INTO users (email, name, login_count)
VALUES ('alice@x.com', 'Alice', 1)
ON CONFLICT (email) DO UPDATE
SET name        = EXCLUDED.name,
    login_count = users.login_count + 1
RETURNING id, login_count;

-- 무시
INSERT INTO users (email, name) VALUES ('alice@x.com', 'Alice')
ON CONFLICT DO NOTHING;

-- 조건부 update
ON CONFLICT (email) DO UPDATE SET ... WHERE users.login_count < 100;

race-condition 측면에서 가장 안전한 upsert. 동일 키에 충돌 시 atomic.

JSONB

CREATE TABLE events (id SERIAL PK, payload JSONB);

-- 인덱스
CREATE INDEX idx_events_payload ON events USING GIN (payload);
CREATE INDEX idx_events_user ON events ((payload ->> 'user'));

-- 연산자
payload -> 'key'         -- jsonb
payload ->> 'key'        -- text
payload #> '{a,b,c}'     -- 깊은 경로 (jsonb)
payload #>> '{a,b,c}'    -- 깊은 경로 (text)
payload @> '{"x":1}'     -- containment, GIN 인덱스 가능
payload ? 'key'          -- key 존재
payload ?| array['a','b']  -- 어느 key 라도 존재
payload || other_jsonb   -- 병합

-- 갱신 (15+)
UPDATE events SET payload = jsonb_set(payload, '{user}', '99'::jsonb);

자세한 비교는 SQL Data Types 의 JSON 섹션 참고.

배열

CREATE TABLE posts (id SERIAL PK, tags TEXT[]);

SELECT * FROM posts WHERE tags @> ARRAY['db'];      -- 포함
SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT id, t FROM posts, unnest(tags) AS t;          -- 펼침
SELECT array_agg(name ORDER BY name) FROM users;     -- 집계

정규식

SELECT * FROM users WHERE name ~ '^A';          -- 대소문자 구분
SELECT * FROM users WHERE name ~* '^a';         -- 대소문자 무시
SELECT REGEXP_REPLACE(name, '[^a-zA-Z]', '', 'g');
SELECT REGEXP_MATCHES('foo123bar456', '[0-9]+', 'g');
-- → {'123'}, {'456'} 두 행

PostgreSQL 의 정규식은 POSIX 확장 + 일부 Perl 호환.

윈도 함수와 PARTITION BY

표준이지만 PG 가 가장 풍부한 옵션을 가진다.

SELECT
  city,
  salary,
  RANK()         OVER w AS rnk,
  PERCENT_RANK() OVER w AS pct,
  AVG(salary)    OVER (PARTITION BY city
                       ORDER BY hired_at
                       ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS ma
FROM users
WINDOW w AS (PARTITION BY city ORDER BY salary DESC);

WINDOW 절로 윈도 정의를 이름 붙여 재사용. SQL GROUP BY 의 윈도 섹션 참고.

CTE 와 재귀

WITH RECURSIVE org AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY depth, name;

PG 12+ 부터 CTE 가 기본 inline. 이전엔 항상 materialize 라 옵티마이저 fence 로 활용했다 (현재는 MATERIALIZED / NOT MATERIALIZED 명시).

LATERAL JOIN

오른쪽 서브쿼리가 왼쪽의 컬럼을 참조 가능. “각 행마다 별도 쿼리”.

SELECT u.name, recent.amount, recent.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT amount, created_at
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent ON TRUE;

각 사용자의 최근 3 주문. MySQL 도 8.0.14+ 지원하지만 PG 가 먼저.

DISTINCT ON

표준이 아니지만 매우 유용. “그룹별 첫 행” 을 짧게.

SELECT DISTINCT ON (user_id) user_id, created_at, amount
FROM orders
ORDER BY user_id, created_at DESC;
-- 각 user 의 가장 최근 주문 한 건

같은 결과를 윈도 함수로:

SELECT user_id, created_at, amount
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
  FROM orders
) t WHERE rn = 1;

MVCC 와 VACUUM

PG 의 MVCC 는 UPDATE/DELETE 시 이전 버전을 삭제하지 않고 dead tuple 로 남긴다. 트랜잭션이 끝나도 다른 트랜잭션이 볼 수 있어서 즉시 제거 불가.

dead tuple 누적 → 테이블 bloat → 쿼리 느려짐. autovacuum 튜닝이 운영 핵심 과제.

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

인덱스 종류

표준에 없는 다양한 인덱스. SQL DDL 의 인덱스 섹션 참고.

-- JSONB 부분 인덱스
CREATE INDEX idx_active_users
  ON users USING GIN ((payload -> 'tags'))
  WHERE active = TRUE;

-- BRIN, 수억 행 시계열에 효과적
CREATE INDEX idx_events_ts_brin ON events USING BRIN (ts);

EXPLAIN

쿼리 plan 확인.

EXPLAIN SELECT * FROM users WHERE city = 'Seoul';
EXPLAIN ANALYZE SELECT ...;        -- 실제 실행 (주의: UPDATE/DELETE 도 실행됨)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

BEGIN;
EXPLAIN ANALYZE UPDATE ...;     -- 변경 후
ROLLBACK;                        -- 되돌림 (PG 의 트랜잭션 DDL/DML 덕분)

확장 (Extensions)

CREATE EXTENSION 으로 기능 추가.

파티셔닝

PG 10+ 부터 선언적 파티셔닝.

CREATE TABLE events (
  id BIGSERIAL,
  ts TIMESTAMPTZ NOT NULL,
  payload JSONB
) PARTITION BY RANGE (ts);

CREATE TABLE events_2026q1 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE events_2026q2 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

조건: 파티션 키가 쿼리에 자주 등장해야 partition pruning 이 작동.

복제 / HA

PostgreSQL vs MySQL 요약

자세한 비교는 MySQL 참고.

pandas 와 PostgreSQL

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://user:pwd@host:5432/db')

df = pd.read_sql("""
  SELECT id, payload ->> 'user' AS user_id, ts
  FROM events
  WHERE ts >= NOW() - INTERVAL '7 day'
""", engine)

대량 적재는 COPY 가 가장 빠르다. Pandas read_sql 의 psql_insert_copy 예제 참고.

JSONB 컬럼을 pandas 로 읽으면 dict (object dtype). 자주 쓰는 키는 SQL 단계에서 평탄화 후 가져오는 게 더 효율적.

함정

1. SERIAL 의 시퀀스 권한

GRANT INSERT ON t TO alice;
-- 그래도 INSERT 실패: 시퀀스 USAGE 권한 필요
GRANT USAGE ON SEQUENCE t_id_seq TO alice;

IDENTITY 는 이 문제가 없다.

2. 대소문자 식별자

CREATE TABLE "MyTable" (...);     -- 따옴표 → 대소문자 보존
SELECT * FROM MyTable;            -- 실패 (mytable 로 변환)
SELECT * FROM "MyTable";          -- 성공

따옴표는 피하고 snake_case 권장.

3. NULL 의 GROUP BY

-- PG 는 NULL 그룹을 모음 (MySQL 도 동일)
SELECT city, COUNT(*) FROM users GROUP BY city;

4. 시간대 (TIMESTAMPTZ)

SQL Data Types 의 날짜·시간 섹션 참고. 운영은 항상 TIMESTAMPTZ.

5. autovacuum 끄기

성능 이슈가 있어도 절대 끄지 말 것. 튜닝으로 해결 (autovacuum_vacuum_scale_factor, _threshold).

6. CTE 의 최적화 변화

PG 11 이하: CTE 는 항상 머티리얼라이즈 (옵티마이저 fence). PG 12+: 기본 inline. 강제하려면 WITH ... AS MATERIALIZED ....

관련 위키

• SQL
• SQL DDL
• SQL DML
• SQL JOIN
• SQL GROUP BY
• SQL DCL TCL
• SQL Data Types
• MySQL
• Pandas read_sql