DATABASE

PostgreSQL 완전 가이드 - SQL 최적화 실전

junetapa 2026. 2. 18 12 min read

PostgreSQL은 가장 진보한 오픈소스 관계형 데이터베이스다. SQL 표준을 충실히 지원하면서 JSONB, 익스텐션으로 확장성까지 갖췄다. 설치부터 쿼리 최적화, Node.js/Python 연동까지 실전 중심으로 정리했다.

PostgreSQL이란? SQL vs NoSQL 선택 기준

PostgreSQL(포스트그레스큐엘)은 30년 이상의 역사를 가진 오픈소스 RDBMS다. ACID 트랜잭션, 강력한 JSON 지원, 풍부한 확장 기능으로 스타트업부터 대기업까지 폭넓게 사용된다. 2026년 현재 Stack Overflow 개발자 설문에서 가장 선호하는 데이터베이스 1위를 유지하고 있다.

PostgreSQL

관계형. ACID 완전 지원, JSON 컬럼, 복잡한 쿼리에 강함.

MySQL / MariaDB

관계형. 빠른 읽기, 웹 서비스에 전통적으로 많이 사용됨.

MongoDB

문서형(NoSQL). 스키마 유연, JSON 문서 저장, 빠른 개발에 적합.

Redis

인메모리(NoSQL). 캐시·세션·큐에 특화. 초고속 읽기/쓰기.

언제 PostgreSQL을 선택하나요?

상황추천 DB이유
복잡한 관계·JOIN이 많은 데이터PostgreSQL외래 키, 복합 JOIN 최적화
트랜잭션 정확성이 중요 (금융·커머스)PostgreSQL완전한 ACID 보장
스키마가 자주 바뀌는 초기 MVPMongoDB스키마 없이 빠른 변경 가능
세션 저장, 캐싱, 실시간 순위Redis메모리 기반 초저지연
읽기가 압도적으로 많은 단순 구조MySQL읽기 성능 최적화
실무에서의 조합 대부분의 서비스는 PostgreSQL(메인 DB) + Redis(캐시/세션)를 함께 사용한다. MongoDB는 콘텐츠·로그처럼 구조가 유동적인 데이터에 추가로 사용하는 경우가 많다.

설치 및 초기 설정

Docker로 빠르게 시작 (개발 환경 추천)

docker-compose.ymlyaml
services:
  postgres:
    image: postgres:17-alpine
    container_name: my-postgres
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:
Docker 실행 및 접속bash
# 컨테이너 시작
docker-compose up -d

# psql 접속 (컨테이너 내부)
docker exec -it my-postgres psql -U myuser -d mydb

# 로컬 psql 클라이언트로 접속
psql -h localhost -p 5432 -U myuser -d mydb

직접 설치 (Windows)

Windows 설치bash
# winget으로 설치 (PowerShell)
winget install PostgreSQL.PostgreSQL

# 또는 공식 인스톨러: https://www.postgresql.org/download/windows/
# 설치 후 pgAdmin 4 자동 포함

psql 기본 명령어

psql 메타 명령어sql
-- 데이터베이스 목록
\l

-- 데이터베이스 접속
\c mydb

-- 테이블 목록
\dt

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

-- 쿼리 실행 시간 표시
\timing on

-- 종료
\q

테이블 설계 — DDL 완전 정복

DDL(Data Definition Language)은 테이블 구조를 정의한다. 실무에서 자주 쓰는 데이터 타입과 제약 조건을 함께 정리한다.

실전 테이블 설계 — 유저·포스트·댓글sql
-- UUID 확장 활성화
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- users 테이블
CREATE TABLE users (
    id          UUID        PRIMARY KEY DEFAULT uuid_generate_v4(),
    email       VARCHAR(255) UNIQUE NOT NULL,
    username    VARCHAR(50)  UNIQUE NOT NULL,
    password    VARCHAR(255) NOT NULL,
    role        VARCHAR(20)  DEFAULT 'user' CHECK (role IN ('user', 'admin')),
    created_at  TIMESTAMPTZ  DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  DEFAULT NOW()
);

-- posts 테이블 (users 참조)
CREATE TABLE posts (
    id          BIGSERIAL    PRIMARY KEY,
    user_id     UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       VARCHAR(500) NOT NULL,
    content     TEXT         NOT NULL,
    tags        TEXT[]       DEFAULT '{}',        -- 배열 타입
    meta        JSONB        DEFAULT '{}',        -- JSON 타입
    views       INTEGER      DEFAULT 0,
    published   BOOLEAN      DEFAULT false,
    created_at  TIMESTAMPTZ  DEFAULT NOW()
);

-- comments 테이블
CREATE TABLE comments (
    id          BIGSERIAL    PRIMARY KEY,
    post_id     BIGINT       NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id     UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content     TEXT         NOT NULL CHECK (length(content) >= 1),
    created_at  TIMESTAMPTZ  DEFAULT NOW()
);

PostgreSQL 주요 데이터 타입

타입설명예시
BIGSERIAL자동 증가 정수 (PK에 사용)1, 2, 3…
UUID범용 고유 식별자550e8400-e29b-…
VARCHAR(n)최대 n자 가변 문자열'홍길동'
TEXT길이 제한 없는 문자열긴 본문 내용
INTEGER / BIGINT4바이트 / 8바이트 정수42, 1000000
NUMERIC(p,s)정밀도 고정 소수점 (금융)99.99
BOOLEAN참/거짓true, false
TIMESTAMPTZ타임존 포함 날짜·시간 (권장)2026-02-19 09:00:00+09
JSONB바이너리 저장 JSON (인덱스 지원){"key": "value"}
TEXT[]문자열 배열ARRAY['태그1','태그2']

기본 CRUD — SELECT, INSERT, UPDATE, DELETE

INSERT — 데이터 삽입sql
-- 단일 삽입
INSERT INTO users (email, username, password)
VALUES ('hong@example.com', 'honggildong', 'hashed_pw')
RETURNING id, email, created_at; -- 삽입된 행 바로 반환

-- 다중 삽입
INSERT INTO posts (user_id, title, content, tags, published)
VALUES
    ('uuid-here', 'PostgreSQL 가이드', '내용..', ARRAY['DB','SQL'], true),
    ('uuid-here', '인덱스 최적화', '내용..', ARRAY['DB'], true);

-- 중복 시 업데이트 (UPSERT)
INSERT INTO users (email, username, password)
VALUES ('hong@example.com', 'honggildong', 'new_pw')
ON CONFLICT (email)
DO UPDATE SET password = EXCLUDED.password, updated_at = NOW();
SELECT — 조회 실전 패턴sql
-- 기본 조회 + 정렬 + 페이징
SELECT id, title, views, created_at
FROM posts
WHERE published = true
ORDER BY created_at DESC
LIMIT 20 OFFSET 0; -- 1페이지 (20개)

-- LIKE 검색 (대소문자 무관: ILIKE)
SELECT * FROM posts
WHERE title ILIKE '%postgresql%';

-- 배열 포함 검색
SELECT * FROM posts
WHERE 'DB' = ANY(tags);

-- JSONB 필드 조회
SELECT * FROM posts
WHERE meta->>'category' = 'tutorial';

-- 집계 함수
SELECT
    user_id,
    COUNT(*) AS post_count,
    SUM(views) AS total_views,
    AVG(views)::INT AS avg_views
FROM posts
WHERE published = true
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY total_views DESC;
UPDATE / DELETEsql
-- 업데이트 + RETURNING
UPDATE posts
SET views = views + 1, updated_at = NOW()
WHERE id = 42
RETURNING id, views;

-- 소프트 삭제 (실무 권장)
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ;

UPDATE posts SET deleted_at = NOW() WHERE id = 42;

-- 실제 삭제 (CASCADE 주의)
DELETE FROM posts WHERE id = 42 RETURNING id;

JOIN 완전 정복

JOIN은 여러 테이블의 데이터를 연결하는 핵심 기능다. 실무에서 가장 많이 쓰는 패턴을 정리한다.

JOIN 유형별 실전 예제sql
-- INNER JOIN: 양쪽 모두 일치하는 행만
SELECT
    p.id, p.title, p.created_at,
    u.username, u.email
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE p.published = true
ORDER BY p.created_at DESC;

-- LEFT JOIN: 왼쪽 테이블 전부 + 오른쪽 일치 항목 (없으면 NULL)
SELECT
    u.username,
    COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id AND p.published = true
GROUP BY u.id, u.username
ORDER BY post_count DESC;

-- 다중 JOIN: posts + users + 댓글 수
SELECT
    p.id,
    p.title,
    u.username AS author,
    COUNT(c.id) AS comment_count
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = true
GROUP BY p.id, u.username
ORDER BY comment_count DESC
LIMIT 10;

CTE (Common Table Expression) — 복잡한 쿼리 정리

WITH 절 — 가독성 높은 서브쿼리sql
WITH active_users AS (
    SELECT id, username
    FROM users
    WHERE created_at >= NOW() - INTERVAL '30 days'
),
user_stats AS (
    SELECT
        user_id,
        COUNT(*) AS post_count,
        SUM(views) AS total_views
    FROM posts
    GROUP BY user_id
)
SELECT
    au.username,
    COALESCE(us.post_count, 0) AS posts,
    COALESCE(us.total_views, 0) AS views
FROM active_users au
LEFT JOIN user_stats us ON au.id = us.user_id
ORDER BY views DESC;

인덱스 설계 — B-Tree, 복합 인덱스, 부분 인덱스

인덱스는 쿼리 속도를 극적으로 향상시키지만, 쓰기 성능과 디스크 공간을 소비한다. 어디에 어떤 인덱스를 걸어야 하는지가 핵심다.

인덱스 생성 패턴sql
-- 기본 B-Tree 인덱스 (가장 범용적)
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- 복합 인덱스 (자주 같이 쓰는 컬럼)
-- WHERE published = true ORDER BY created_at DESC 쿼리 최적화
CREATE INDEX idx_posts_published_created
    ON posts(published, created_at DESC);

-- 부분 인덱스 (조건부 인덱스 — 크기 최소화)
-- published = true인 행만 인덱싱
CREATE INDEX idx_posts_published_only
    ON posts(created_at DESC)
    WHERE published = true;

-- 고유 인덱스 (UNIQUE 제약과 동일)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- JSONB 인덱스 (GIN — 완전 탐색)
CREATE INDEX idx_posts_meta ON posts USING GIN(meta);

-- 배열 인덱스 (GIN)
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- 인덱스 목록 확인
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'posts';
인덱스 남용 주의 인덱스는 SELECT를 빠르게 하지만 INSERT/UPDATE/DELETE 시 인덱스도 갱신되어 쓰기 성능이 저하된다. WHERE 절에 자주 등장하는 컬럼, 높은 카디널리티(고유값이 많은) 컬럼에만 인덱스를 추가하자.

트랜잭션 & ACID 보장

트랜잭션은 여러 SQL이 모두 성공하거나 모두 실패하도록 묶어주는 원자적 작업 단위다. 계좌 이체처럼 데이터 정합성이 중요한 작업에 필수다.

트랜잭션 실전 — 계좌 이체 예제sql
BEGIN; -- 트랜잭션 시작

DO $$
DECLARE
    v_balance INTEGER;
BEGIN
    -- 1. 출금 계좌 잔액 확인 (행 잠금)
    SELECT balance INTO v_balance
    FROM accounts
    WHERE id = 1
    FOR UPDATE; -- 다른 트랜잭션이 수정 못하게 잠금

    -- 2. 잔액 부족 시 롤백
    IF v_balance < 10000 THEN
        RAISE EXCEPTION '잔액 부족: %', v_balance;
    END IF;

    -- 3. 출금 처리
    UPDATE accounts SET balance = balance - 10000 WHERE id = 1;

    -- 4. 입금 처리
    UPDATE accounts SET balance = balance + 10000 WHERE id = 2;

    -- 5. 거래 로그 기록
    INSERT INTO transfer_logs (from_id, to_id, amount)
    VALUES (1, 2, 10000);

END;
$$ LANGUAGE plpgsql;

COMMIT; -- 모두 성공 시 커밋 (ROLLBACK 하면 모두 취소)

격리 수준 (Isolation Level)

격리 수준특징용도
READ COMMITTED (기본값)커밋된 데이터만 읽음일반적인 CRUD
REPEATABLE READ트랜잭션 내 동일 조회 보장리포트, 집계
SERIALIZABLE완전한 직렬화, 최고 정합성금융, 결제

EXPLAIN ANALYZE — 쿼리 성능 분석

느린 쿼리를 발견했다면 EXPLAIN ANALYZE로 실행 계획을 분석해 병목을 찾다. 인덱스가 제대로 쓰이고 있는지 확인하는 핵심 도구다.

EXPLAIN ANALYZE 사용법sql
EXPLAIN ANALYZE
SELECT p.title, u.username, COUNT(c.id) AS comments
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.published = true
GROUP BY p.id, u.username
ORDER BY comments DESC
LIMIT 10;

/*
출력 예시:
Limit  (cost=1234.56.1234.81 rows=10 width=48)
  ->  Sort  (cost=1234.56.1245.12 rows=4224 width=48)
        Sort Key: (count(c.id)) DESC
        ->  Hash Left Join  (cost=..)
              Hash Cond: (p.id = c.post_id)
              ->  Hash Join  (cost=..)
                    ->  Index Scan using idx_posts_published_only on posts
                          (actual time=0.050.2.340 rows=1500 loops=1)
                                           ↑ 인덱스 사용 확인!
Planning Time: 1.2 ms
Execution Time: 4.8 ms   ← 전체 실행 시간
*/

느린 쿼리 자동 감지 — pg_stat_statements

Top 10 느린 쿼리 찾기sql
-- 확장 활성화 (postgresql.conf에 shared_preload_libraries = 'pg_stat_statements' 추가 후)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 총 실행 시간 상위 10개 쿼리
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Node.js 연동 — pg + Connection Pool

패키지 설치bash
npm install pg
npm install -D @types/pg  # TypeScript 사용 시
db.ts — Connection Pool 설정ts
import { Pool } from 'pg';

// 환경 변수로 접속 정보 관리
export const pool = new Pool({
  host:     process.env.DB_HOST     || 'localhost',
  port:     Number(process.env.DB_PORT) || 5432,
  database: process.env.DB_NAME     || 'mydb',
  user:     process.env.DB_USER     || 'myuser',
  password: process.env.DB_PASSWORD || 'mypassword',
  max:      20,    // 최대 연결 수
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: false }
    : false,
});

// 헬퍼 함수 (파라미터 바인딩으로 SQL 인젝션 방지)
export async function query<T = any>(
  sql: string,
  params?: any[]
): Promise<T[]> {
  const { rows } = await pool.query(sql, params);
  return rows;
}
routes/posts.ts — 실전 CRUD 라우터ts
import { Router } from 'express';
import { query, pool } from './db';

const router = Router();

// 포스트 목록 (페이징)
router.get('/', async (req, res) => {
  const page = Number(req.query.page) || 1;
  const limit = 20;
  const offset = (page - 1) * limit;

  const posts = await query(`
    SELECT p.id, p.title, p.views, p.created_at,
           u.username AS author
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.published = true
    ORDER BY p.created_at DESC
    LIMIT $1 OFFSET $2
  `, [limit, offset]);

  res.json({ posts, page });
});

// 포스트 생성 (트랜잭션)
router.post('/', async (req, res) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { title, content, tags } = req.body;
    const [post] = await client.query(`
      INSERT INTO posts (user_id, title, content, tags)
      VALUES ($1, $2, $3, $4)
      RETURNING id, title, created_at
    `, [req.user.id, title, content, tags]);
    await client.query('COMMIT');
    res.status(201).json(post.rows[0]);
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release(); // 반드시 반환!
  }
});

export default router;

Python 연동 — psycopg2 + SQLAlchemy

패키지 설치bash
pip install psycopg2-binary sqlalchemy python-dotenv
psycopg2 직접 사용python
import psycopg2
from psycopg2.extras import RealDictCursor
import os

# 연결
conn = psycopg2.connect(
    host=os.getenv("DB_HOST", "localhost"),
    dbname=os.getenv("DB_NAME", "mydb"),
    user=os.getenv("DB_USER", "myuser"),
    password=os.getenv("DB_PASSWORD"),
    port=5432
)

# RealDictCursor: 결과를 dict 형태로 반환
with conn.cursor(cursor_factory=RealDictCursor) as cur:
    # 파라미터는 반드시 %s 플레이스홀더 사용 (SQL 인젝션 방지)
    cur.execute(
        "SELECT id, title, views FROM posts WHERE published = %s ORDER BY created_at DESC LIMIT %s",
        (True, 20)
    )
    posts = cur.fetchall()
    for p in posts:
        print(p['title'], p['views'])

conn.close()
SQLAlchemy 2.x ORM — 모델 정의 및 쿼리python
from sqlalchemy import create_engine, String, Integer, Boolean, DateTime
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
from datetime import datetime
import os

DATABASE_URL = (
    f"postgresql+psycopg2://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}"
    f"@{os.getenv('DB_HOST')}:5432/{os.getenv('DB_NAME')}"
)
engine = create_engine(DATABASE_URL, pool_size=10, max_overflow=20)

class Base(DeclarativeBase):
    pass

class Post(Base):
    __tablename__ = "posts"

    id:         Mapped[int]      = mapped_column(Integer, primary_key=True)
    title:      Mapped[str]      = mapped_column(String(500))
    views:      Mapped[int]      = mapped_column(Integer, default=0)
    published:  Mapped[bool]     = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)

# 쿼리 예시
with Session(engine) as session:
    # 발행된 포스트 조회
    posts = (
        session.query(Post)
        .filter(Post.published == True)
        .order_by(Post.created_at.desc())
        .limit(20)
        .all()
    )
    for p in posts:
        print(f"{p.id}: {p.title} ({p.views} views)")

    # 새 포스트 추가
    new_post = Post(title="SQLAlchemy 가이드", published=True)
    session.add(new_post)
    session.commit()
PostgreSQL SQL 인덱스 쿼리 최적화 EXPLAIN 데이터베이스
junetapa
junetapa
AI 도구를 직접 써보고 솔직한 경험을 공유하는 개발자.
Twitter Facebook URL 복사
Related

관련 포스트

Database
MySQL vs PostgreSQL 2026 선택 가이드
Database
Prisma ORM TypeScript DB 관리 가이드