CHHB stroy

PostgreSQL, 왜 다들 Postgres 쓰라고 하는지 이제 알겠다 본문

DB

PostgreSQL, 왜 다들 Postgres 쓰라고 하는지 이제 알겠다

CHHB 2026. 5. 12. 20:50

MySQL만 쓰다가 PostgreSQL로 넘어온 지 꽤 됐다. 처음에는 "뭐가 다르다고 이걸 굳이?" 싶었는데, 쓰면 쓸수록 돌아갈 수가 없다. 오늘은 내가 PostgreSQL 쓰면서 느낀 점들이랑, 입문할 때 알았으면 좋았을 내용들을 한번 정리해보려고 한다.

PostgreSQL이 뭔가요?

한 줄로 말하면 오픈소스 관계형 데이터베이스다. MySQL이랑 같은 카테고리인데, 좀 더 기능이 많고 표준 SQL을 충실하게 따른다. 흔히 줄여서 Postgres(포스트그레스)라고 부른다. "포스트그레스큐엘"이라고 매번 말하기 귀찮으니까.

원래 UC 버클리에서 1986년에 시작된 프로젝트인데, 거기서부터 지금까지 꾸준히 개발되고 있다. 오픈소스 DB 중에서 역사가 가장 길고 커뮤니티도 탄탄하다.

MySQL에서 넘어온 사람이 느끼는 차이점

MySQL 쓰다가 Postgres로 오면 처음에 좀 당황하는 부분들이 있다. 나도 그랬고.

1. 대소문자 처리가 다르다

MySQL은 기본적으로 테이블명, 컬럼명 대소문자를 안 가린다. 근데 Postgres는 따옴표 안 쓰면 전부 소문자로 바꿔버린다.

-- 이렇게 만들면
CREATE TABLE UserProfile (
    UserName VARCHAR(50)
);

-- Postgres는 내부적으로 이렇게 저장함
-- userprofile 테이블의 username 컬럼

-- 대소문자 유지하고 싶으면 쌍따옴표 필수
CREATE TABLE "UserProfile" (
    "UserName" VARCHAR(50)
);

근데 솔직히 쌍따옴표 쓰지 마라. 한번 쓰기 시작하면 모든 쿼리에서 다 써야 해서 미친다. 그냥 snake_case 쓰자. user_profile, user_name 이렇게.

2. AUTO_INCREMENT가 없다

MySQL에서 AUTO_INCREMENT 달던 습관이 있으면, Postgres에서는 SERIAL 또는 GENERATED를 써야 한다.

-- Postgres 전통 방식
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

-- Postgres 10+ 표준 방식 (이걸 권장)
CREATE TABLE posts (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

SERIAL도 여전히 잘 동작하지만, GENERATED AS IDENTITY가 SQL 표준이라 요즘은 이쪽을 쓰는 추세다.

3. LIMIT 문법은 같은데 다른 것도 있다

LIMIT은 똑같이 쓴다. 근데 MySQL에 있는 LIMIT 10, 20 같은 축약 문법은 안 된다.

-- MySQL 스타일 (Postgres에서 안 됨)
SELECT * FROM posts LIMIT 10, 20;

-- Postgres 스타일 (MySQL에서도 됨)
SELECT * FROM posts LIMIT 20 OFFSET 10;

사실 LIMIT ... OFFSET 방식이 가독성도 좋고 표준이니까, MySQL 쓸 때도 이렇게 쓰는 게 낫다.

Postgres가 진짜 빛나는 기능들

여기서부터가 본론이다. MySQL에서는 못 하거나 불편한데 Postgres에서는 자연스럽게 되는 것들.

1. JSONB — NoSQL 부럽지 않은 JSON 처리

이게 내가 Postgres로 넘어온 가장 큰 이유다. MongoDB 같은 도큐먼트 DB 안 써도, Postgres 하나로 관계형 + JSON 데이터를 다 처리할 수 있다.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    specs JSONB
);

INSERT INTO products (name, specs) VALUES
('맥북 프로', '{"cpu": "M3 Pro", "ram": 18, "storage": "512GB", "ports": ["USB-C", "HDMI", "MagSafe"]}'),
('갤럭시 S24', '{"cpu": "Snapdragon 8 Gen 3", "ram": 8, "storage": "256GB", "colors": ["black", "gray", "violet"]}');

JSONB의 진가는 쿼리할 때 나온다.

-- RAM이 12GB 이상인 제품 찾기
SELECT name, specs->>'cpu' AS cpu
FROM products
WHERE (specs->>'ram')::int >= 12;

-- JSON 배열 안의 값으로 검색
SELECT name
FROM products
WHERE specs->'ports' ? 'HDMI';

-- JSONB 인덱스도 가능!
CREATE INDEX idx_specs ON products USING GIN (specs);

-> 연산자는 JSON 객체를 반환하고, ->> 연산자는 텍스트를 반환한다. 이 차이를 기억해두면 된다.

💡 팁: JSON과 JSONB 중에 뭘 쓸지 고민된다면 무조건 JSONB 써라. 바이너리로 저장돼서 읽기가 빠르고, 인덱스도 걸 수 있다. JSON 타입은 입력 그대로 텍스트로 저장하는 거라 쿼리 성능이 안 나온다.

2. CTE (Common Table Expressions) — 복잡한 쿼리를 읽기 좋게

MySQL 8.0에서도 CTE가 되긴 하는데, Postgres에서는 재귀 CTE까지 아주 자연스럽게 쓸 수 있다.

-- 카테고리 계층 구조 조회 (재귀 CTE)
WITH RECURSIVE category_tree AS (
    -- 루트 카테고리
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 하위 카테고리 재귀 탐색
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS category_hierarchy
FROM category_tree
ORDER BY depth, name;

이런 계층 쿼리를 서브쿼리로 짜면 눈이 빠지는데, CTE로 쓰면 위에서 아래로 읽기만 하면 된다. 코드 리뷰할 때도 동료가 안 죽인다.

3. Window Functions — 집계의 끝판왕

사실 Window Function은 MySQL 8.0에서도 지원하지만, Postgres가 먼저 지원했고 기능도 더 풍부하다.

-- 부서별 급여 순위 매기기
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

결과가 이런 식으로 나온다:

김철수 개발 6000 1 1000
이영희 개발 5000 2 0
박민수 개발 4000 3 -1000
정다은 기획 5500 1 500
최서연 기획 4500 2 -500

name department salary dept_rank diff_from_avg

GROUP BY 없이 각 행에 집계 결과를 붙일 수 있다는 게 핵심이다. 리포트성 쿼리 짤 때 이거 없으면 진짜 답 없다.

4. 배열 타입 — 컬럼 하나에 여러 값 저장

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[]
);

INSERT INTO articles (title, tags) VALUES
('Postgres 입문', ARRAY['database', 'postgresql', 'backend']),
('Docker 가이드', ARRAY['docker', 'devops', 'container']);

-- 특정 태그가 포함된 글 검색
SELECT title FROM articles
WHERE 'postgresql' = ANY(tags);

-- 배열 겹치는 항목이 있는지 확인
SELECT title FROM articles
WHERE tags && ARRAY['devops', 'backend'];

태그, 권한 목록 같은 간단한 다대다 관계는 별도 테이블 안 만들고 배열로 처리하면 편하다. 물론 복잡한 관계는 정규화하는 게 맞다.

5. UPSERT — INSERT 할 때 중복이면 UPDATE

INSERT INTO user_settings (user_id, theme, language)
VALUES (1, 'dark', 'ko')
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language;

EXCLUDED는 INSERT 하려던 값을 가리킨다. 이 패턴 실무에서 정말 많이 쓴다. "있으면 수정, 없으면 생성" 로직을 쿼리 하나로 끝낼 수 있다.

인덱스 — 성능의 핵심

Postgres가 지원하는 인덱스 종류가 정말 다양한데, 상황에 맞는 걸 쓰는 게 중요하다.

-- B-tree (기본, 대부분의 경우)
CREATE INDEX idx_users_email ON users (email);

-- Hash (동등 비교만 할 때)
CREATE INDEX idx_users_id ON users USING HASH (id);

-- GIN (JSONB, 배열, 전문검색)
CREATE INDEX idx_products_specs ON products USING GIN (specs);

-- GiST (지리 데이터, 범위 타입)
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- 부분 인덱스 — 이거 진짜 좋다
CREATE INDEX idx_active_users ON users (email)
WHERE is_active = true;

부분 인덱스(Partial Index)는 MySQL에 없는 기능인데, 조건에 맞는 행만 인덱싱하니까 인덱스 크기도 작고 빠르다. 활성 유저만 자주 검색하는데 전체 유저에 인덱스 걸 필요 없잖아?

EXPLAIN ANALYZE — 쿼리 성능 분석

쿼리가 느리다 싶으면 무조건 이거 돌려보자.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
AND created_at > '2024-01-01';

Bitmap Heap Scan on orders  (cost=12.45..156.78 rows=42 width=120) (actual time=0.089..0.234 rows=38 loops=1)
  Recheck Cond: (customer_id = 42)
  Filter: (created_at > '2024-01-01'::date)
  ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..12.44 rows=50 width=0) (actual time=0.065..0.066 rows=50 loops=1)
Planning Time: 0.125 ms
Execution Time: 0.289 ms

Seq Scan이 보이면 인덱스를 안 타고 있다는 뜻이다. 데이터가 많은 테이블에서 Seq Scan이 뜨면 인덱스를 확인하자.

트랜잭션과 동시성 제어

Postgres의 MVCC(Multi-Version Concurrency Control)는 정말 잘 만들어져 있다. 읽기가 쓰기를 블록하지 않고, 쓰기가 읽기를 블록하지 않는다.

-- 트랜잭션 기본
BEGIN;

UPDATE accounts SET balance = balance - 50000
WHERE id = 1;

UPDATE accounts SET balance = balance + 50000
WHERE id = 2;

-- 문제 없으면 커밋
COMMIT;

-- 문제 있으면 롤백
-- ROLLBACK;

SAVEPOINT — 부분 롤백

이건 꽤 유용한데 모르는 사람이 많다.

BEGIN;

INSERT INTO orders (product_id, qty) VALUES (1, 10);

SAVEPOINT before_payment;

-- 결제 처리 시도
UPDATE wallets SET balance = balance - 100000 WHERE user_id = 1;

-- 잔액 부족 같은 문제 발생 시
ROLLBACK TO SAVEPOINT before_payment;
-- 주문은 유지하되 결제만 롤백

-- 다른 결제 수단으로 재시도
UPDATE cards SET ... ;

COMMIT;

유용한 psql 명령어

CLI로 작업할 때 알아두면 편한 것들.

# 접속
psql -U postgres -d mydb

# 또는 접속 문자열로
psql "postgresql://user:password@localhost:5432/mydb"

-- 데이터베이스 목록
\l

-- 테이블 목록
\dt

-- 테이블 구조 확인
\d users

-- 상세 구조 (인덱스, 제약조건 포함)
\d+ users

-- 현재 연결 정보
\conninfo

-- 쿼리 결과를 세로로 보기 (컬럼 많을 때 유용)
\x
SELECT * FROM users LIMIT 1;

-- SQL 파일 실행
\i /path/to/script.sql

-- 결과를 파일로 저장
\o output.txt
SELECT * FROM users;
\o

💡 팁: \dt+처럼 명령어 뒤에 +를 붙이면 더 상세한 정보가 나온다. 테이블 크기 같은 것도 볼 수 있어서 유용하다.

백업과 복원

운영 DB 다루면 백업은 생명이다. 한 번 날려보면 안다 (나는 개발 DB였지만 그래도 식은땀 났다).

# 전체 데이터베이스 백업
pg_dump -U postgres mydb > backup.sql

# 커스텀 포맷 (압축됨, 부분 복원 가능)
pg_dump -U postgres -Fc mydb > backup.dump

# 특정 테이블만 백업
pg_dump -U postgres -t users -t orders mydb > partial_backup.sql

# 복원
psql -U postgres mydb < backup.sql

# 커스텀 포맷 복원
pg_restore -U postgres -d mydb backup.dump

# 전체 클러스터 백업 (모든 DB + 역할)
pg_dumpall -U postgres > cluster_backup.sql

프로덕션에서는 pg_dump보다 WAL 아카이빙 + PITR(Point-in-Time Recovery) 설정하는 게 좋다. 근데 그건 DBA 영역이니까 여기서는 넘어가겠다.

설정 튜닝 — 기본값은 너무 보수적이다

Postgres 설치 직후 기본 설정은 아주 작은 서버에 맞춰져 있다. 운영 환경이면 최소한 이것들은 조정하자.

# postgresql.conf 주요 설정

# 메모리 (서버 RAM의 25% 정도)
shared_buffers = '4GB'

# 쿼리당 사용 가능한 메모리
work_mem = '256MB'

# VACUUM, CREATE INDEX 등에 사용할 메모리
maintenance_work_mem = '1GB'

# 쿼리 플래너가 사용 가능한 총 메모리 (서버 RAM의 70~80%)
effective_cache_size = '12GB'

# WAL 설정
wal_buffers = '64MB'
checkpoint_completion_target = 0.9

# 동시 연결 수
max_connections = 200

⚠️ 이 값들은 서버 사양에 따라 다르니까 무작정 복붙하지 말고, PGTune 같은 도구로 서버 사양에 맞는 값을 계산하는 걸 추천한다.

흔히 저지르는 실수들

1. 인덱스 안 걸고 느리다고 투덜대기

-- 이 쿼리가 느리다고?
SELECT * FROM orders WHERE customer_email = 'someone@test.com';

-- 인덱스부터 확인하자
\d orders

-- 인덱스 없으면 만들고
CREATE INDEX idx_orders_email ON orders (customer_email);

당연한 얘기 같지만, 개발 중에는 데이터가 적어서 괜찮다가 운영에서 데이터 쌓이면 터지는 케이스가 정말 많다.

2. SELECT * 남발

-- 나쁜 습관
SELECT * FROM users WHERE id = 1;

-- 필요한 컬럼만
SELECT name, email FROM users WHERE id = 1;

특히 JSONB 컬럼이나 TEXT 컬럼이 있는 테이블에서 SELECT * 하면 불필요한 데이터를 엄청 읽게 된다.

3. N+1 쿼리

-- 글 목록 가져오고... (1번 쿼리)
SELECT * FROM posts;

-- 각 글마다 작성자 조회... (N번 쿼리)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ...

-- 이러지 말고 JOIN 쓰자
SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON p.user_id = u.id;

ORM 쓸 때 특히 주의해야 한다. Laravel이든 Django든 eager loading 안 하면 N+1이 기본이다.

4. VACUUM 안 하기

Postgres는 삭제된 행을 바로 지우지 않는다. VACUUM이 정리해주는데, autovacuum이 기본으로 켜져 있긴 하지만 대량 삭제/업데이트 후에는 수동으로 해주는 게 좋다.

-- 일반 VACUUM
VACUUM ANALYZE orders;

-- 공간까지 회수 (테이블 락 걸림 주의!)
VACUUM FULL orders;

VACUUM FULL은 운영 중에 함부로 돌리면 안 된다. 테이블에 락이 걸려서 서비스가 멈출 수 있다.

마무리

Postgres는 처음 넘어올 때 약간의 학습 비용이 있지만, 그 투자 대비 얻는 것이 정말 크다. 특히 JSONB, Window Function, 부분 인덱스, CTE 같은 기능들은 한번 맛보면 빠져나올 수가 없다.

핵심만 다시 정리하면:

  • MySQL에서 넘어올 때 대소문자, SERIAL, 문법 차이에 주의하자
  • JSONB는 진짜 강력하다. 간단한 비정형 데이터는 별도 NoSQL 안 써도 된다
  • CTE와 Window Function으로 복잡한 쿼리를 읽기 좋게 쓸 수 있다
  • 인덱스 종류를 상황에 맞게 고르자 (특히 부분 인덱스는 꼭 알아두자)
  • EXPLAIN ANALYZE는 습관처럼 쓰자
  • 기본 설정은 반드시 튜닝하자

다음에는 Postgres + Docker 로컬 개발 환경 세팅이나, pgAdmin 대신 CLI로 작업하는 팁 같은 것도 다뤄볼 생각이다.

궁금한 점이나 틀린 부분 있으면 댓글로 알려주세요! 🙌

이 글에 포함된 다이어그램 이미지는 AI 도구를 활용하여 생성되었습니다.

PostgreSQL, 왜 다들 Postgres 쓰라고 하는지 이제 알겠다

MySQL만 쓰다가 PostgreSQL로 넘어온 지 꽤 됐다. 처음에는 "뭐가 다르다고 이걸 굳이?" 싶었는데, 쓰면 쓸수록 돌아갈 수가 없다. 오늘은 내가 PostgreSQL 쓰면서 느낀 점들이랑, 입문할 때 알았으면 좋았을 내용들을 한번 정리해보려고 한다.

PostgreSQL이 뭔가요?

한 줄로 말하면 오픈소스 관계형 데이터베이스다. MySQL이랑 같은 카테고리인데, 좀 더 기능이 많고 표준 SQL을 충실하게 따른다. 흔히 줄여서 Postgres(포스트그레스)라고 부른다. "포스트그레스큐엘"이라고 매번 말하기 귀찮으니까.

원래 UC 버클리에서 1986년에 시작된 프로젝트인데, 거기서부터 지금까지 꾸준히 개발되고 있다. 오픈소스 DB 중에서 역사가 가장 길고 커뮤니티도 탄탄하다.

MySQL에서 넘어온 사람이 느끼는 차이점

MySQL 쓰다가 Postgres로 오면 처음에 좀 당황하는 부분들이 있다. 나도 그랬고.

1. 대소문자 처리가 다르다

MySQL은 기본적으로 테이블명, 컬럼명 대소문자를 안 가린다. 근데 Postgres는 따옴표 안 쓰면 전부 소문자로 바꿔버린다.

-- 이렇게 만들면
CREATE TABLE UserProfile (
    UserName VARCHAR(50)
);

-- Postgres는 내부적으로 이렇게 저장함
-- userprofile 테이블의 username 컬럼

-- 대소문자 유지하고 싶으면 쌍따옴표 필수
CREATE TABLE "UserProfile" (
    "UserName" VARCHAR(50)
);

근데 솔직히 쌍따옴표 쓰지 마라. 한번 쓰기 시작하면 모든 쿼리에서 다 써야 해서 미친다. 그냥 snake_case 쓰자. user_profile, user_name 이렇게.

2. AUTO_INCREMENT가 없다

MySQL에서 AUTO_INCREMENT 달던 습관이 있으면, Postgres에서는 SERIAL 또는 GENERATED를 써야 한다.

-- Postgres 전통 방식
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

-- Postgres 10+ 표준 방식 (이걸 권장)
CREATE TABLE posts (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

SERIAL도 여전히 잘 동작하지만, GENERATED AS IDENTITY가 SQL 표준이라 요즘은 이쪽을 쓰는 추세다.

3. LIMIT 문법은 같은데 다른 것도 있다

LIMIT은 똑같이 쓴다. 근데 MySQL에 있는 LIMIT 10, 20 같은 축약 문법은 안 된다.

-- MySQL 스타일 (Postgres에서 안 됨)
SELECT * FROM posts LIMIT 10, 20;

-- Postgres 스타일 (MySQL에서도 됨)
SELECT * FROM posts LIMIT 20 OFFSET 10;

사실 LIMIT ... OFFSET 방식이 가독성도 좋고 표준이니까, MySQL 쓸 때도 이렇게 쓰는 게 낫다.

Postgres가 진짜 빛나는 기능들

여기서부터가 본론이다. MySQL에서는 못 하거나 불편한데 Postgres에서는 자연스럽게 되는 것들.

1. JSONB — NoSQL 부럽지 않은 JSON 처리

이게 내가 Postgres로 넘어온 가장 큰 이유다. MongoDB 같은 도큐먼트 DB 안 써도, Postgres 하나로 관계형 + JSON 데이터를 다 처리할 수 있다.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    specs JSONB
);

INSERT INTO products (name, specs) VALUES
('맥북 프로', '{"cpu": "M3 Pro", "ram": 18, "storage": "512GB", "ports": ["USB-C", "HDMI", "MagSafe"]}'),
('갤럭시 S24', '{"cpu": "Snapdragon 8 Gen 3", "ram": 8, "storage": "256GB", "colors": ["black", "gray", "violet"]}');

JSONB의 진가는 쿼리할 때 나온다.

-- RAM이 12GB 이상인 제품 찾기
SELECT name, specs->>'cpu' AS cpu
FROM products
WHERE (specs->>'ram')::int >= 12;

-- JSON 배열 안의 값으로 검색
SELECT name
FROM products
WHERE specs->'ports' ? 'HDMI';

-- JSONB 인덱스도 가능!
CREATE INDEX idx_specs ON products USING GIN (specs);

-> 연산자는 JSON 객체를 반환하고, ->> 연산자는 텍스트를 반환한다. 이 차이를 기억해두면 된다.

💡 팁: JSON과 JSONB 중에 뭘 쓸지 고민된다면 무조건 JSONB 써라. 바이너리로 저장돼서 읽기가 빠르고, 인덱스도 걸 수 있다. JSON 타입은 입력 그대로 텍스트로 저장하는 거라 쿼리 성능이 안 나온다.

2. CTE (Common Table Expressions) — 복잡한 쿼리를 읽기 좋게

MySQL 8.0에서도 CTE가 되긴 하는데, Postgres에서는 재귀 CTE까지 아주 자연스럽게 쓸 수 있다.

-- 카테고리 계층 구조 조회 (재귀 CTE)
WITH RECURSIVE category_tree AS (
    -- 루트 카테고리
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 하위 카테고리 재귀 탐색
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS category_hierarchy
FROM category_tree
ORDER BY depth, name;

이런 계층 쿼리를 서브쿼리로 짜면 눈이 빠지는데, CTE로 쓰면 위에서 아래로 읽기만 하면 된다. 코드 리뷰할 때도 동료가 안 죽인다.

3. Window Functions — 집계의 끝판왕

사실 Window Function은 MySQL 8.0에서도 지원하지만, Postgres가 먼저 지원했고 기능도 더 풍부하다.

-- 부서별 급여 순위 매기기
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

결과가 이런 식으로 나온다:

김철수 개발 6000 1 1000
이영희 개발 5000 2 0
박민수 개발 4000 3 -1000
정다은 기획 5500 1 500
최서연 기획 4500 2 -500

name department salary dept_rank diff_from_avg

GROUP BY 없이 각 행에 집계 결과를 붙일 수 있다는 게 핵심이다. 리포트성 쿼리 짤 때 이거 없으면 진짜 답 없다.

4. 배열 타입 — 컬럼 하나에 여러 값 저장

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[]
);

INSERT INTO articles (title, tags) VALUES
('Postgres 입문', ARRAY['database', 'postgresql', 'backend']),
('Docker 가이드', ARRAY['docker', 'devops', 'container']);

-- 특정 태그가 포함된 글 검색
SELECT title FROM articles
WHERE 'postgresql' = ANY(tags);

-- 배열 겹치는 항목이 있는지 확인
SELECT title FROM articles
WHERE tags && ARRAY['devops', 'backend'];

태그, 권한 목록 같은 간단한 다대다 관계는 별도 테이블 안 만들고 배열로 처리하면 편하다. 물론 복잡한 관계는 정규화하는 게 맞다.

5. UPSERT — INSERT 할 때 중복이면 UPDATE

INSERT INTO user_settings (user_id, theme, language)
VALUES (1, 'dark', 'ko')
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language;

EXCLUDED는 INSERT 하려던 값을 가리킨다. 이 패턴 실무에서 정말 많이 쓴다. "있으면 수정, 없으면 생성" 로직을 쿼리 하나로 끝낼 수 있다.

인덱스 — 성능의 핵심

Postgres가 지원하는 인덱스 종류가 정말 다양한데, 상황에 맞는 걸 쓰는 게 중요하다.

-- B-tree (기본, 대부분의 경우)
CREATE INDEX idx_users_email ON users (email);

-- Hash (동등 비교만 할 때)
CREATE INDEX idx_users_id ON users USING HASH (id);

-- GIN (JSONB, 배열, 전문검색)
CREATE INDEX idx_products_specs ON products USING GIN (specs);

-- GiST (지리 데이터, 범위 타입)
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- 부분 인덱스 — 이거 진짜 좋다
CREATE INDEX idx_active_users ON users (email)
WHERE is_active = true;

부분 인덱스(Partial Index)는 MySQL에 없는 기능인데, 조건에 맞는 행만 인덱싱하니까 인덱스 크기도 작고 빠르다. 활성 유저만 자주 검색하는데 전체 유저에 인덱스 걸 필요 없잖아?

EXPLAIN ANALYZE — 쿼리 성능 분석

쿼리가 느리다 싶으면 무조건 이거 돌려보자.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
AND created_at > '2024-01-01';

Bitmap Heap Scan on orders  (cost=12.45..156.78 rows=42 width=120) (actual time=0.089..0.234 rows=38 loops=1)
  Recheck Cond: (customer_id = 42)
  Filter: (created_at > '2024-01-01'::date)
  ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..12.44 rows=50 width=0) (actual time=0.065..0.066 rows=50 loops=1)
Planning Time: 0.125 ms
Execution Time: 0.289 ms

Seq Scan이 보이면 인덱스를 안 타고 있다는 뜻이다. 데이터가 많은 테이블에서 Seq Scan이 뜨면 인덱스를 확인하자.

트랜잭션과 동시성 제어

Postgres의 MVCC(Multi-Version Concurrency Control)는 정말 잘 만들어져 있다. 읽기가 쓰기를 블록하지 않고, 쓰기가 읽기를 블록하지 않는다.

-- 트랜잭션 기본
BEGIN;

UPDATE accounts SET balance = balance - 50000
WHERE id = 1;

UPDATE accounts SET balance = balance + 50000
WHERE id = 2;

-- 문제 없으면 커밋
COMMIT;

-- 문제 있으면 롤백
-- ROLLBACK;

SAVEPOINT — 부분 롤백

이건 꽤 유용한데 모르는 사람이 많다.

BEGIN;

INSERT INTO orders (product_id, qty) VALUES (1, 10);

SAVEPOINT before_payment;

-- 결제 처리 시도
UPDATE wallets SET balance = balance - 100000 WHERE user_id = 1;

-- 잔액 부족 같은 문제 발생 시
ROLLBACK TO SAVEPOINT before_payment;
-- 주문은 유지하되 결제만 롤백

-- 다른 결제 수단으로 재시도
UPDATE cards SET ... ;

COMMIT;

유용한 psql 명령어

CLI로 작업할 때 알아두면 편한 것들.

# 접속
psql -U postgres -d mydb

# 또는 접속 문자열로
psql "postgresql://user:password@localhost:5432/mydb"

-- 데이터베이스 목록
\l

-- 테이블 목록
\dt

-- 테이블 구조 확인
\d users

-- 상세 구조 (인덱스, 제약조건 포함)
\d+ users

-- 현재 연결 정보
\conninfo

-- 쿼리 결과를 세로로 보기 (컬럼 많을 때 유용)
\x
SELECT * FROM users LIMIT 1;

-- SQL 파일 실행
\i /path/to/script.sql

-- 결과를 파일로 저장
\o output.txt
SELECT * FROM users;
\o

💡 팁: \dt+처럼 명령어 뒤에 +를 붙이면 더 상세한 정보가 나온다. 테이블 크기 같은 것도 볼 수 있어서 유용하다.

백업과 복원

운영 DB 다루면 백업은 생명이다. 한 번 날려보면 안다 (나는 개발 DB였지만 그래도 식은땀 났다).

# 전체 데이터베이스 백업
pg_dump -U postgres mydb > backup.sql

# 커스텀 포맷 (압축됨, 부분 복원 가능)
pg_dump -U postgres -Fc mydb > backup.dump

# 특정 테이블만 백업
pg_dump -U postgres -t users -t orders mydb > partial_backup.sql

# 복원
psql -U postgres mydb < backup.sql

# 커스텀 포맷 복원
pg_restore -U postgres -d mydb backup.dump

# 전체 클러스터 백업 (모든 DB + 역할)
pg_dumpall -U postgres > cluster_backup.sql

프로덕션에서는 pg_dump보다 WAL 아카이빙 + PITR(Point-in-Time Recovery) 설정하는 게 좋다. 근데 그건 DBA 영역이니까 여기서는 넘어가겠다.

설정 튜닝 — 기본값은 너무 보수적이다

Postgres 설치 직후 기본 설정은 아주 작은 서버에 맞춰져 있다. 운영 환경이면 최소한 이것들은 조정하자.

# postgresql.conf 주요 설정

# 메모리 (서버 RAM의 25% 정도)
shared_buffers = '4GB'

# 쿼리당 사용 가능한 메모리
work_mem = '256MB'

# VACUUM, CREATE INDEX 등에 사용할 메모리
maintenance_work_mem = '1GB'

# 쿼리 플래너가 사용 가능한 총 메모리 (서버 RAM의 70~80%)
effective_cache_size = '12GB'

# WAL 설정
wal_buffers = '64MB'
checkpoint_completion_target = 0.9

# 동시 연결 수
max_connections = 200

⚠️ 이 값들은 서버 사양에 따라 다르니까 무작정 복붙하지 말고, PGTune 같은 도구로 서버 사양에 맞는 값을 계산하는 걸 추천한다.

흔히 저지르는 실수들

1. 인덱스 안 걸고 느리다고 투덜대기

-- 이 쿼리가 느리다고?
SELECT * FROM orders WHERE customer_email = 'someone@test.com';

-- 인덱스부터 확인하자
\d orders

-- 인덱스 없으면 만들고
CREATE INDEX idx_orders_email ON orders (customer_email);

당연한 얘기 같지만, 개발 중에는 데이터가 적어서 괜찮다가 운영에서 데이터 쌓이면 터지는 케이스가 정말 많다.

2. SELECT * 남발

-- 나쁜 습관
SELECT * FROM users WHERE id = 1;

-- 필요한 컬럼만
SELECT name, email FROM users WHERE id = 1;

특히 JSONB 컬럼이나 TEXT 컬럼이 있는 테이블에서 SELECT * 하면 불필요한 데이터를 엄청 읽게 된다.

3. N+1 쿼리

-- 글 목록 가져오고... (1번 쿼리)
SELECT * FROM posts;

-- 각 글마다 작성자 조회... (N번 쿼리)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ...

-- 이러지 말고 JOIN 쓰자
SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON p.user_id = u.id;

ORM 쓸 때 특히 주의해야 한다. Laravel이든 Django든 eager loading 안 하면 N+1이 기본이다.

4. VACUUM 안 하기

Postgres는 삭제된 행을 바로 지우지 않는다. VACUUM이 정리해주는데, autovacuum이 기본으로 켜져 있긴 하지만 대량 삭제/업데이트 후에는 수동으로 해주는 게 좋다.

-- 일반 VACUUM
VACUUM ANALYZE orders;

-- 공간까지 회수 (테이블 락 걸림 주의!)
VACUUM FULL orders;

VACUUM FULL은 운영 중에 함부로 돌리면 안 된다. 테이블에 락이 걸려서 서비스가 멈출 수 있다.

마무리

Postgres는 처음 넘어올 때 약간의 학습 비용이 있지만, 그 투자 대비 얻는 것이 정말 크다. 특히 JSONB, Window Function, 부분 인덱스, CTE 같은 기능들은 한번 맛보면 빠져나올 수가 없다.

핵심만 다시 정리하면:

  • MySQL에서 넘어올 때 대소문자, SERIAL, 문법 차이에 주의하자
  • JSONB는 진짜 강력하다. 간단한 비정형 데이터는 별도 NoSQL 안 써도 된다
  • CTE와 Window Function으로 복잡한 쿼리를 읽기 좋게 쓸 수 있다
  • 인덱스 종류를 상황에 맞게 고르자 (특히 부분 인덱스는 꼭 알아두자)
  • EXPLAIN ANALYZE는 습관처럼 쓰자
  • 기본 설정은 반드시 튜닝하자

다음에는 Postgres + Docker 로컬 개발 환경 세팅이나, pgAdmin 대신 CLI로 작업하는 팁 같은 것도 다뤄볼 생각이다.

궁금한 점이나 틀린 부분 있으면 댓글로 알려주세요! 🙌

이 글에 포함된 다이어그램 이미지는 AI 도구를 활용하여 생성되었습니다.

PostgreSQL, 왜 다들 Postgres 쓰라고 하는지 이제 알겠다

MySQL만 쓰다가 PostgreSQL로 넘어온 지 꽤 됐다. 처음에는 "뭐가 다르다고 이걸 굳이?" 싶었는데, 쓰면 쓸수록 돌아갈 수가 없다. 오늘은 내가 PostgreSQL 쓰면서 느낀 점들이랑, 입문할 때 알았으면 좋았을 내용들을 한번 정리해보려고 한다.

PostgreSQL이 뭔가요?

한 줄로 말하면 오픈소스 관계형 데이터베이스다. MySQL이랑 같은 카테고리인데, 좀 더 기능이 많고 표준 SQL을 충실하게 따른다. 흔히 줄여서 Postgres(포스트그레스)라고 부른다. "포스트그레스큐엘"이라고 매번 말하기 귀찮으니까.

원래 UC 버클리에서 1986년에 시작된 프로젝트인데, 거기서부터 지금까지 꾸준히 개발되고 있다. 오픈소스 DB 중에서 역사가 가장 길고 커뮤니티도 탄탄하다.

MySQL에서 넘어온 사람이 느끼는 차이점

MySQL 쓰다가 Postgres로 오면 처음에 좀 당황하는 부분들이 있다. 나도 그랬고.

1. 대소문자 처리가 다르다

MySQL은 기본적으로 테이블명, 컬럼명 대소문자를 안 가린다. 근데 Postgres는 따옴표 안 쓰면 전부 소문자로 바꿔버린다.

-- 이렇게 만들면
CREATE TABLE UserProfile (
    UserName VARCHAR(50)
);

-- Postgres는 내부적으로 이렇게 저장함
-- userprofile 테이블의 username 컬럼

-- 대소문자 유지하고 싶으면 쌍따옴표 필수
CREATE TABLE "UserProfile" (
    "UserName" VARCHAR(50)
);

근데 솔직히 쌍따옴표 쓰지 마라. 한번 쓰기 시작하면 모든 쿼리에서 다 써야 해서 미친다. 그냥 snake_case 쓰자. user_profile, user_name 이렇게.

2. AUTO_INCREMENT가 없다

MySQL에서 AUTO_INCREMENT 달던 습관이 있으면, Postgres에서는 SERIAL 또는 GENERATED를 써야 한다.

-- Postgres 전통 방식
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

-- Postgres 10+ 표준 방식 (이걸 권장)
CREATE TABLE posts (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

SERIAL도 여전히 잘 동작하지만, GENERATED AS IDENTITY가 SQL 표준이라 요즘은 이쪽을 쓰는 추세다.

3. LIMIT 문법은 같은데 다른 것도 있다

LIMIT은 똑같이 쓴다. 근데 MySQL에 있는 LIMIT 10, 20 같은 축약 문법은 안 된다.

-- MySQL 스타일 (Postgres에서 안 됨)
SELECT * FROM posts LIMIT 10, 20;

-- Postgres 스타일 (MySQL에서도 됨)
SELECT * FROM posts LIMIT 20 OFFSET 10;

사실 LIMIT ... OFFSET 방식이 가독성도 좋고 표준이니까, MySQL 쓸 때도 이렇게 쓰는 게 낫다.

Postgres가 진짜 빛나는 기능들

여기서부터가 본론이다. MySQL에서는 못 하거나 불편한데 Postgres에서는 자연스럽게 되는 것들.

1. JSONB — NoSQL 부럽지 않은 JSON 처리

이게 내가 Postgres로 넘어온 가장 큰 이유다. MongoDB 같은 도큐먼트 DB 안 써도, Postgres 하나로 관계형 + JSON 데이터를 다 처리할 수 있다.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    specs JSONB
);

INSERT INTO products (name, specs) VALUES
('맥북 프로', '{"cpu": "M3 Pro", "ram": 18, "storage": "512GB", "ports": ["USB-C", "HDMI", "MagSafe"]}'),
('갤럭시 S24', '{"cpu": "Snapdragon 8 Gen 3", "ram": 8, "storage": "256GB", "colors": ["black", "gray", "violet"]}');

JSONB의 진가는 쿼리할 때 나온다.

-- RAM이 12GB 이상인 제품 찾기
SELECT name, specs->>'cpu' AS cpu
FROM products
WHERE (specs->>'ram')::int >= 12;

-- JSON 배열 안의 값으로 검색
SELECT name
FROM products
WHERE specs->'ports' ? 'HDMI';

-- JSONB 인덱스도 가능!
CREATE INDEX idx_specs ON products USING GIN (specs);

-> 연산자는 JSON 객체를 반환하고, ->> 연산자는 텍스트를 반환한다. 이 차이를 기억해두면 된다.

💡 팁: JSON과 JSONB 중에 뭘 쓸지 고민된다면 무조건 JSONB 써라. 바이너리로 저장돼서 읽기가 빠르고, 인덱스도 걸 수 있다. JSON 타입은 입력 그대로 텍스트로 저장하는 거라 쿼리 성능이 안 나온다.

2. CTE (Common Table Expressions) — 복잡한 쿼리를 읽기 좋게

MySQL 8.0에서도 CTE가 되긴 하는데, Postgres에서는 재귀 CTE까지 아주 자연스럽게 쓸 수 있다.

-- 카테고리 계층 구조 조회 (재귀 CTE)
WITH RECURSIVE category_tree AS (
    -- 루트 카테고리
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 하위 카테고리 재귀 탐색
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS category_hierarchy
FROM category_tree
ORDER BY depth, name;

이런 계층 쿼리를 서브쿼리로 짜면 눈이 빠지는데, CTE로 쓰면 위에서 아래로 읽기만 하면 된다. 코드 리뷰할 때도 동료가 안 죽인다.

3. Window Functions — 집계의 끝판왕

사실 Window Function은 MySQL 8.0에서도 지원하지만, Postgres가 먼저 지원했고 기능도 더 풍부하다.

-- 부서별 급여 순위 매기기
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

결과가 이런 식으로 나온다:

김철수 개발 6000 1 1000
이영희 개발 5000 2 0
박민수 개발 4000 3 -1000
정다은 기획 5500 1 500
최서연 기획 4500 2 -500

name department salary dept_rank diff_from_avg

GROUP BY 없이 각 행에 집계 결과를 붙일 수 있다는 게 핵심이다. 리포트성 쿼리 짤 때 이거 없으면 진짜 답 없다.

4. 배열 타입 — 컬럼 하나에 여러 값 저장

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[]
);

INSERT INTO articles (title, tags) VALUES
('Postgres 입문', ARRAY['database', 'postgresql', 'backend']),
('Docker 가이드', ARRAY['docker', 'devops', 'container']);

-- 특정 태그가 포함된 글 검색
SELECT title FROM articles
WHERE 'postgresql' = ANY(tags);

-- 배열 겹치는 항목이 있는지 확인
SELECT title FROM articles
WHERE tags && ARRAY['devops', 'backend'];

태그, 권한 목록 같은 간단한 다대다 관계는 별도 테이블 안 만들고 배열로 처리하면 편하다. 물론 복잡한 관계는 정규화하는 게 맞다.

5. UPSERT — INSERT 할 때 중복이면 UPDATE

INSERT INTO user_settings (user_id, theme, language)
VALUES (1, 'dark', 'ko')
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language;

EXCLUDED는 INSERT 하려던 값을 가리킨다. 이 패턴 실무에서 정말 많이 쓴다. "있으면 수정, 없으면 생성" 로직을 쿼리 하나로 끝낼 수 있다.

인덱스 — 성능의 핵심

Postgres가 지원하는 인덱스 종류가 정말 다양한데, 상황에 맞는 걸 쓰는 게 중요하다.

-- B-tree (기본, 대부분의 경우)
CREATE INDEX idx_users_email ON users (email);

-- Hash (동등 비교만 할 때)
CREATE INDEX idx_users_id ON users USING HASH (id);

-- GIN (JSONB, 배열, 전문검색)
CREATE INDEX idx_products_specs ON products USING GIN (specs);

-- GiST (지리 데이터, 범위 타입)
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- 부분 인덱스 — 이거 진짜 좋다
CREATE INDEX idx_active_users ON users (email)
WHERE is_active = true;

부분 인덱스(Partial Index)는 MySQL에 없는 기능인데, 조건에 맞는 행만 인덱싱하니까 인덱스 크기도 작고 빠르다. 활성 유저만 자주 검색하는데 전체 유저에 인덱스 걸 필요 없잖아?

EXPLAIN ANALYZE — 쿼리 성능 분석

쿼리가 느리다 싶으면 무조건 이거 돌려보자.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
AND created_at > '2024-01-01';

Bitmap Heap Scan on orders  (cost=12.45..156.78 rows=42 width=120) (actual time=0.089..0.234 rows=38 loops=1)
  Recheck Cond: (customer_id = 42)
  Filter: (created_at > '2024-01-01'::date)
  ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..12.44 rows=50 width=0) (actual time=0.065..0.066 rows=50 loops=1)
Planning Time: 0.125 ms
Execution Time: 0.289 ms

Seq Scan이 보이면 인덱스를 안 타고 있다는 뜻이다. 데이터가 많은 테이블에서 Seq Scan이 뜨면 인덱스를 확인하자.

트랜잭션과 동시성 제어

Postgres의 MVCC(Multi-Version Concurrency Control)는 정말 잘 만들어져 있다. 읽기가 쓰기를 블록하지 않고, 쓰기가 읽기를 블록하지 않는다.

-- 트랜잭션 기본
BEGIN;

UPDATE accounts SET balance = balance - 50000
WHERE id = 1;

UPDATE accounts SET balance = balance + 50000
WHERE id = 2;

-- 문제 없으면 커밋
COMMIT;

-- 문제 있으면 롤백
-- ROLLBACK;

SAVEPOINT — 부분 롤백

이건 꽤 유용한데 모르는 사람이 많다.

BEGIN;

INSERT INTO orders (product_id, qty) VALUES (1, 10);

SAVEPOINT before_payment;

-- 결제 처리 시도
UPDATE wallets SET balance = balance - 100000 WHERE user_id = 1;

-- 잔액 부족 같은 문제 발생 시
ROLLBACK TO SAVEPOINT before_payment;
-- 주문은 유지하되 결제만 롤백

-- 다른 결제 수단으로 재시도
UPDATE cards SET ... ;

COMMIT;

유용한 psql 명령어

CLI로 작업할 때 알아두면 편한 것들.

# 접속
psql -U postgres -d mydb

# 또는 접속 문자열로
psql "postgresql://user:password@localhost:5432/mydb"

-- 데이터베이스 목록
\l

-- 테이블 목록
\dt

-- 테이블 구조 확인
\d users

-- 상세 구조 (인덱스, 제약조건 포함)
\d+ users

-- 현재 연결 정보
\conninfo

-- 쿼리 결과를 세로로 보기 (컬럼 많을 때 유용)
\x
SELECT * FROM users LIMIT 1;

-- SQL 파일 실행
\i /path/to/script.sql

-- 결과를 파일로 저장
\o output.txt
SELECT * FROM users;
\o

💡 팁: \dt+처럼 명령어 뒤에 +를 붙이면 더 상세한 정보가 나온다. 테이블 크기 같은 것도 볼 수 있어서 유용하다.

백업과 복원

운영 DB 다루면 백업은 생명이다. 한 번 날려보면 안다 (나는 개발 DB였지만 그래도 식은땀 났다).

# 전체 데이터베이스 백업
pg_dump -U postgres mydb > backup.sql

# 커스텀 포맷 (압축됨, 부분 복원 가능)
pg_dump -U postgres -Fc mydb > backup.dump

# 특정 테이블만 백업
pg_dump -U postgres -t users -t orders mydb > partial_backup.sql

# 복원
psql -U postgres mydb < backup.sql

# 커스텀 포맷 복원
pg_restore -U postgres -d mydb backup.dump

# 전체 클러스터 백업 (모든 DB + 역할)
pg_dumpall -U postgres > cluster_backup.sql

프로덕션에서는 pg_dump보다 WAL 아카이빙 + PITR(Point-in-Time Recovery) 설정하는 게 좋다. 근데 그건 DBA 영역이니까 여기서는 넘어가겠다.

설정 튜닝 — 기본값은 너무 보수적이다

Postgres 설치 직후 기본 설정은 아주 작은 서버에 맞춰져 있다. 운영 환경이면 최소한 이것들은 조정하자.

# postgresql.conf 주요 설정

# 메모리 (서버 RAM의 25% 정도)
shared_buffers = '4GB'

# 쿼리당 사용 가능한 메모리
work_mem = '256MB'

# VACUUM, CREATE INDEX 등에 사용할 메모리
maintenance_work_mem = '1GB'

# 쿼리 플래너가 사용 가능한 총 메모리 (서버 RAM의 70~80%)
effective_cache_size = '12GB'

# WAL 설정
wal_buffers = '64MB'
checkpoint_completion_target = 0.9

# 동시 연결 수
max_connections = 200

⚠️ 이 값들은 서버 사양에 따라 다르니까 무작정 복붙하지 말고, PGTune 같은 도구로 서버 사양에 맞는 값을 계산하는 걸 추천한다.

흔히 저지르는 실수들

1. 인덱스 안 걸고 느리다고 투덜대기

-- 이 쿼리가 느리다고?
SELECT * FROM orders WHERE customer_email = 'someone@test.com';

-- 인덱스부터 확인하자
\d orders

-- 인덱스 없으면 만들고
CREATE INDEX idx_orders_email ON orders (customer_email);

당연한 얘기 같지만, 개발 중에는 데이터가 적어서 괜찮다가 운영에서 데이터 쌓이면 터지는 케이스가 정말 많다.

2. SELECT * 남발

-- 나쁜 습관
SELECT * FROM users WHERE id = 1;

-- 필요한 컬럼만
SELECT name, email FROM users WHERE id = 1;

특히 JSONB 컬럼이나 TEXT 컬럼이 있는 테이블에서 SELECT * 하면 불필요한 데이터를 엄청 읽게 된다.

3. N+1 쿼리

-- 글 목록 가져오고... (1번 쿼리)
SELECT * FROM posts;

-- 각 글마다 작성자 조회... (N번 쿼리)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ...

-- 이러지 말고 JOIN 쓰자
SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON p.user_id = u.id;

ORM 쓸 때 특히 주의해야 한다. Laravel이든 Django든 eager loading 안 하면 N+1이 기본이다.

4. VACUUM 안 하기

Postgres는 삭제된 행을 바로 지우지 않는다. VACUUM이 정리해주는데, autovacuum이 기본으로 켜져 있긴 하지만 대량 삭제/업데이트 후에는 수동으로 해주는 게 좋다.

-- 일반 VACUUM
VACUUM ANALYZE orders;

-- 공간까지 회수 (테이블 락 걸림 주의!)
VACUUM FULL orders;

VACUUM FULL은 운영 중에 함부로 돌리면 안 된다. 테이블에 락이 걸려서 서비스가 멈출 수 있다.

마무리

Postgres는 처음 넘어올 때 약간의 학습 비용이 있지만, 그 투자 대비 얻는 것이 정말 크다. 특히 JSONB, Window Function, 부분 인덱스, CTE 같은 기능들은 한번 맛보면 빠져나올 수가 없다.

핵심만 다시 정리하면:

  • MySQL에서 넘어올 때 대소문자, SERIAL, 문법 차이에 주의하자
  • JSONB는 진짜 강력하다. 간단한 비정형 데이터는 별도 NoSQL 안 써도 된다
  • CTE와 Window Function으로 복잡한 쿼리를 읽기 좋게 쓸 수 있다
  • 인덱스 종류를 상황에 맞게 고르자 (특히 부분 인덱스는 꼭 알아두자)
  • EXPLAIN ANALYZE는 습관처럼 쓰자
  • 기본 설정은 반드시 튜닝하자

다음에는 Postgres + Docker 로컬 개발 환경 세팅이나, pgAdmin 대신 CLI로 작업하는 팁 같은 것도 다뤄볼 생각이다.

궁금한 점이나 틀린 부분 있으면 댓글로 알려주세요! 🙌

이 글에 포함된 다이어그램 이미지는 AI 도구를 활용하여 생성되었습니다.

PostgreSQL, 왜 다들 Postgres 쓰라고 하는지 이제 알겠다

MySQL만 쓰다가 PostgreSQL로 넘어온 지 꽤 됐다. 처음에는 "뭐가 다르다고 이걸 굳이?" 싶었는데, 쓰면 쓸수록 돌아갈 수가 없다. 오늘은 내가 PostgreSQL 쓰면서 느낀 점들이랑, 입문할 때 알았으면 좋았을 내용들을 한번 정리해보려고 한다.

PostgreSQL이 뭔가요?

한 줄로 말하면 오픈소스 관계형 데이터베이스다. MySQL이랑 같은 카테고리인데, 좀 더 기능이 많고 표준 SQL을 충실하게 따른다. 흔히 줄여서 Postgres(포스트그레스)라고 부른다. "포스트그레스큐엘"이라고 매번 말하기 귀찮으니까.

원래 UC 버클리에서 1986년에 시작된 프로젝트인데, 거기서부터 지금까지 꾸준히 개발되고 있다. 오픈소스 DB 중에서 역사가 가장 길고 커뮤니티도 탄탄하다.

MySQL에서 넘어온 사람이 느끼는 차이점

MySQL 쓰다가 Postgres로 오면 처음에 좀 당황하는 부분들이 있다. 나도 그랬고.

1. 대소문자 처리가 다르다

MySQL은 기본적으로 테이블명, 컬럼명 대소문자를 안 가린다. 근데 Postgres는 따옴표 안 쓰면 전부 소문자로 바꿔버린다.

-- 이렇게 만들면
CREATE TABLE UserProfile (
    UserName VARCHAR(50)
);

-- Postgres는 내부적으로 이렇게 저장함
-- userprofile 테이블의 username 컬럼

-- 대소문자 유지하고 싶으면 쌍따옴표 필수
CREATE TABLE "UserProfile" (
    "UserName" VARCHAR(50)
);

근데 솔직히 쌍따옴표 쓰지 마라. 한번 쓰기 시작하면 모든 쿼리에서 다 써야 해서 미친다. 그냥 snake_case 쓰자. user_profile, user_name 이렇게.

2. AUTO_INCREMENT가 없다

MySQL에서 AUTO_INCREMENT 달던 습관이 있으면, Postgres에서는 SERIAL 또는 GENERATED를 써야 한다.

-- Postgres 전통 방식
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

-- Postgres 10+ 표준 방식 (이걸 권장)
CREATE TABLE posts (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

SERIAL도 여전히 잘 동작하지만, GENERATED AS IDENTITY가 SQL 표준이라 요즘은 이쪽을 쓰는 추세다.

3. LIMIT 문법은 같은데 다른 것도 있다

LIMIT은 똑같이 쓴다. 근데 MySQL에 있는 LIMIT 10, 20 같은 축약 문법은 안 된다.

-- MySQL 스타일 (Postgres에서 안 됨)
SELECT * FROM posts LIMIT 10, 20;

-- Postgres 스타일 (MySQL에서도 됨)
SELECT * FROM posts LIMIT 20 OFFSET 10;

사실 LIMIT ... OFFSET 방식이 가독성도 좋고 표준이니까, MySQL 쓸 때도 이렇게 쓰는 게 낫다.

Postgres가 진짜 빛나는 기능들

여기서부터가 본론이다. MySQL에서는 못 하거나 불편한데 Postgres에서는 자연스럽게 되는 것들.

1. JSONB — NoSQL 부럽지 않은 JSON 처리

이게 내가 Postgres로 넘어온 가장 큰 이유다. MongoDB 같은 도큐먼트 DB 안 써도, Postgres 하나로 관계형 + JSON 데이터를 다 처리할 수 있다.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    specs JSONB
);

INSERT INTO products (name, specs) VALUES
('맥북 프로', '{"cpu": "M3 Pro", "ram": 18, "storage": "512GB", "ports": ["USB-C", "HDMI", "MagSafe"]}'),
('갤럭시 S24', '{"cpu": "Snapdragon 8 Gen 3", "ram": 8, "storage": "256GB", "colors": ["black", "gray", "violet"]}');

JSONB의 진가는 쿼리할 때 나온다.

-- RAM이 12GB 이상인 제품 찾기
SELECT name, specs->>'cpu' AS cpu
FROM products
WHERE (specs->>'ram')::int >= 12;

-- JSON 배열 안의 값으로 검색
SELECT name
FROM products
WHERE specs->'ports' ? 'HDMI';

-- JSONB 인덱스도 가능!
CREATE INDEX idx_specs ON products USING GIN (specs);

-> 연산자는 JSON 객체를 반환하고, ->> 연산자는 텍스트를 반환한다. 이 차이를 기억해두면 된다.

💡 팁: JSON과 JSONB 중에 뭘 쓸지 고민된다면 무조건 JSONB 써라. 바이너리로 저장돼서 읽기가 빠르고, 인덱스도 걸 수 있다. JSON 타입은 입력 그대로 텍스트로 저장하는 거라 쿼리 성능이 안 나온다.

2. CTE (Common Table Expressions) — 복잡한 쿼리를 읽기 좋게

MySQL 8.0에서도 CTE가 되긴 하는데, Postgres에서는 재귀 CTE까지 아주 자연스럽게 쓸 수 있다.

-- 카테고리 계층 구조 조회 (재귀 CTE)
WITH RECURSIVE category_tree AS (
    -- 루트 카테고리
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 하위 카테고리 재귀 탐색
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT
    REPEAT('  ', depth) || name AS category_hierarchy
FROM category_tree
ORDER BY depth, name;

이런 계층 쿼리를 서브쿼리로 짜면 눈이 빠지는데, CTE로 쓰면 위에서 아래로 읽기만 하면 된다. 코드 리뷰할 때도 동료가 안 죽인다.

3. Window Functions — 집계의 끝판왕

사실 Window Function은 MySQL 8.0에서도 지원하지만, Postgres가 먼저 지원했고 기능도 더 풍부하다.

-- 부서별 급여 순위 매기기
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

결과가 이런 식으로 나온다:

김철수 개발 6000 1 1000
이영희 개발 5000 2 0
박민수 개발 4000 3 -1000
정다은 기획 5500 1 500
최서연 기획 4500 2 -500

name department salary dept_rank diff_from_avg

GROUP BY 없이 각 행에 집계 결과를 붙일 수 있다는 게 핵심이다. 리포트성 쿼리 짤 때 이거 없으면 진짜 답 없다.

4. 배열 타입 — 컬럼 하나에 여러 값 저장

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[]
);

INSERT INTO articles (title, tags) VALUES
('Postgres 입문', ARRAY['database', 'postgresql', 'backend']),
('Docker 가이드', ARRAY['docker', 'devops', 'container']);

-- 특정 태그가 포함된 글 검색
SELECT title FROM articles
WHERE 'postgresql' = ANY(tags);

-- 배열 겹치는 항목이 있는지 확인
SELECT title FROM articles
WHERE tags && ARRAY['devops', 'backend'];

태그, 권한 목록 같은 간단한 다대다 관계는 별도 테이블 안 만들고 배열로 처리하면 편하다. 물론 복잡한 관계는 정규화하는 게 맞다.

5. UPSERT — INSERT 할 때 중복이면 UPDATE

INSERT INTO user_settings (user_id, theme, language)
VALUES (1, 'dark', 'ko')
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language;

EXCLUDED는 INSERT 하려던 값을 가리킨다. 이 패턴 실무에서 정말 많이 쓴다. "있으면 수정, 없으면 생성" 로직을 쿼리 하나로 끝낼 수 있다.

인덱스 — 성능의 핵심

Postgres가 지원하는 인덱스 종류가 정말 다양한데, 상황에 맞는 걸 쓰는 게 중요하다.

-- B-tree (기본, 대부분의 경우)
CREATE INDEX idx_users_email ON users (email);

-- Hash (동등 비교만 할 때)
CREATE INDEX idx_users_id ON users USING HASH (id);

-- GIN (JSONB, 배열, 전문검색)
CREATE INDEX idx_products_specs ON products USING GIN (specs);

-- GiST (지리 데이터, 범위 타입)
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- 부분 인덱스 — 이거 진짜 좋다
CREATE INDEX idx_active_users ON users (email)
WHERE is_active = true;

부분 인덱스(Partial Index)는 MySQL에 없는 기능인데, 조건에 맞는 행만 인덱싱하니까 인덱스 크기도 작고 빠르다. 활성 유저만 자주 검색하는데 전체 유저에 인덱스 걸 필요 없잖아?

EXPLAIN ANALYZE — 쿼리 성능 분석

쿼리가 느리다 싶으면 무조건 이거 돌려보자.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
AND created_at > '2024-01-01';

Bitmap Heap Scan on orders  (cost=12.45..156.78 rows=42 width=120) (actual time=0.089..0.234 rows=38 loops=1)
  Recheck Cond: (customer_id = 42)
  Filter: (created_at > '2024-01-01'::date)
  ->  Bitmap Index Scan on idx_orders_customer  (cost=0.00..12.44 rows=50 width=0) (actual time=0.065..0.066 rows=50 loops=1)
Planning Time: 0.125 ms
Execution Time: 0.289 ms

Seq Scan이 보이면 인덱스를 안 타고 있다는 뜻이다. 데이터가 많은 테이블에서 Seq Scan이 뜨면 인덱스를 확인하자.

트랜잭션과 동시성 제어

Postgres의 MVCC(Multi-Version Concurrency Control)는 정말 잘 만들어져 있다. 읽기가 쓰기를 블록하지 않고, 쓰기가 읽기를 블록하지 않는다.

-- 트랜잭션 기본
BEGIN;

UPDATE accounts SET balance = balance - 50000
WHERE id = 1;

UPDATE accounts SET balance = balance + 50000
WHERE id = 2;

-- 문제 없으면 커밋
COMMIT;

-- 문제 있으면 롤백
-- ROLLBACK;

SAVEPOINT — 부분 롤백

이건 꽤 유용한데 모르는 사람이 많다.

BEGIN;

INSERT INTO orders (product_id, qty) VALUES (1, 10);

SAVEPOINT before_payment;

-- 결제 처리 시도
UPDATE wallets SET balance = balance - 100000 WHERE user_id = 1;

-- 잔액 부족 같은 문제 발생 시
ROLLBACK TO SAVEPOINT before_payment;
-- 주문은 유지하되 결제만 롤백

-- 다른 결제 수단으로 재시도
UPDATE cards SET ... ;

COMMIT;

유용한 psql 명령어

CLI로 작업할 때 알아두면 편한 것들.

# 접속
psql -U postgres -d mydb

# 또는 접속 문자열로
psql "postgresql://user:password@localhost:5432/mydb"

-- 데이터베이스 목록
\l

-- 테이블 목록
\dt

-- 테이블 구조 확인
\d users

-- 상세 구조 (인덱스, 제약조건 포함)
\d+ users

-- 현재 연결 정보
\conninfo

-- 쿼리 결과를 세로로 보기 (컬럼 많을 때 유용)
\x
SELECT * FROM users LIMIT 1;

-- SQL 파일 실행
\i /path/to/script.sql

-- 결과를 파일로 저장
\o output.txt
SELECT * FROM users;
\o

💡 팁: \dt+처럼 명령어 뒤에 +를 붙이면 더 상세한 정보가 나온다. 테이블 크기 같은 것도 볼 수 있어서 유용하다.

백업과 복원

운영 DB 다루면 백업은 생명이다. 한 번 날려보면 안다 (나는 개발 DB였지만 그래도 식은땀 났다).

# 전체 데이터베이스 백업
pg_dump -U postgres mydb > backup.sql

# 커스텀 포맷 (압축됨, 부분 복원 가능)
pg_dump -U postgres -Fc mydb > backup.dump

# 특정 테이블만 백업
pg_dump -U postgres -t users -t orders mydb > partial_backup.sql

# 복원
psql -U postgres mydb < backup.sql

# 커스텀 포맷 복원
pg_restore -U postgres -d mydb backup.dump

# 전체 클러스터 백업 (모든 DB + 역할)
pg_dumpall -U postgres > cluster_backup.sql

프로덕션에서는 pg_dump보다 WAL 아카이빙 + PITR(Point-in-Time Recovery) 설정하는 게 좋다. 근데 그건 DBA 영역이니까 여기서는 넘어가겠다.

설정 튜닝 — 기본값은 너무 보수적이다

Postgres 설치 직후 기본 설정은 아주 작은 서버에 맞춰져 있다. 운영 환경이면 최소한 이것들은 조정하자.

# postgresql.conf 주요 설정

# 메모리 (서버 RAM의 25% 정도)
shared_buffers = '4GB'

# 쿼리당 사용 가능한 메모리
work_mem = '256MB'

# VACUUM, CREATE INDEX 등에 사용할 메모리
maintenance_work_mem = '1GB'

# 쿼리 플래너가 사용 가능한 총 메모리 (서버 RAM의 70~80%)
effective_cache_size = '12GB'

# WAL 설정
wal_buffers = '64MB'
checkpoint_completion_target = 0.9

# 동시 연결 수
max_connections = 200

⚠️ 이 값들은 서버 사양에 따라 다르니까 무작정 복붙하지 말고, PGTune 같은 도구로 서버 사양에 맞는 값을 계산하는 걸 추천한다.

흔히 저지르는 실수들

1. 인덱스 안 걸고 느리다고 투덜대기

-- 이 쿼리가 느리다고?
SELECT * FROM orders WHERE customer_email = 'someone@test.com';

-- 인덱스부터 확인하자
\d orders

-- 인덱스 없으면 만들고
CREATE INDEX idx_orders_email ON orders (customer_email);

당연한 얘기 같지만, 개발 중에는 데이터가 적어서 괜찮다가 운영에서 데이터 쌓이면 터지는 케이스가 정말 많다.

2. SELECT * 남발

-- 나쁜 습관
SELECT * FROM users WHERE id = 1;

-- 필요한 컬럼만
SELECT name, email FROM users WHERE id = 1;

특히 JSONB 컬럼이나 TEXT 컬럼이 있는 테이블에서 SELECT * 하면 불필요한 데이터를 엄청 읽게 된다.

3. N+1 쿼리

-- 글 목록 가져오고... (1번 쿼리)
SELECT * FROM posts;

-- 각 글마다 작성자 조회... (N번 쿼리)
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;
SELECT * FROM users WHERE id = 3;
-- ...

-- 이러지 말고 JOIN 쓰자
SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON p.user_id = u.id;

ORM 쓸 때 특히 주의해야 한다. Laravel이든 Django든 eager loading 안 하면 N+1이 기본이다.

4. VACUUM 안 하기

Postgres는 삭제된 행을 바로 지우지 않는다. VACUUM이 정리해주는데, autovacuum이 기본으로 켜져 있긴 하지만 대량 삭제/업데이트 후에는 수동으로 해주는 게 좋다.

-- 일반 VACUUM
VACUUM ANALYZE orders;

-- 공간까지 회수 (테이블 락 걸림 주의!)
VACUUM FULL orders;

VACUUM FULL은 운영 중에 함부로 돌리면 안 된다. 테이블에 락이 걸려서 서비스가 멈출 수 있다.

마무리

Postgres는 처음 넘어올 때 약간의 학습 비용이 있지만, 그 투자 대비 얻는 것이 정말 크다. 특히 JSONB, Window Function, 부분 인덱스, CTE 같은 기능들은 한번 맛보면 빠져나올 수가 없다.

핵심만 다시 정리하면:

  • MySQL에서 넘어올 때 대소문자, SERIAL, 문법 차이에 주의하자
  • JSONB는 진짜 강력하다. 간단한 비정형 데이터는 별도 NoSQL 안 써도 된다
  • CTE와 Window Function으로 복잡한 쿼리를 읽기 좋게 쓸 수 있다
  • 인덱스 종류를 상황에 맞게 고르자 (특히 부분 인덱스는 꼭 알아두자)
  • EXPLAIN ANALYZE는 습관처럼 쓰자
  • 기본 설정은 반드시 튜닝하

'DB' 카테고리의 다른 글

MSSQL 인덱스의 INCLUDE, 이거 모르면 인덱스를 반만 쓰는 거다  (0) 2026.05.19
SQL Server PIVOT과 UNPIVOT 완벽 정리  (0) 2026.05.11
[DB] 트랜잭션 완전 정리  (0) 2025.09.23
[DB] 데이터베이스 키(KEY) 종류 정리  (0) 2025.09.23
'DB' Related Articles more