PostgreSQL 치트시트: 개발자를 위한 빠른 참고

PostgreSQL 빠른 참고

Page content

일상적인 PostgreSQL 작업(연결, SQL 문법, psql 메타커맨드, 성능, JSON, 윈도우 함수 등)을 위한 빠른 참고 자료입니다.

Postgres의 세부 사항에 들어가기 전에 데이터베이스 간 간결한 복습을 원하신다면, 가장 유용한 SQL 명령어가 포함된 이 SQL 치트시트가 유용한 동반자가 될 것입니다.

postgresql 로고

연결 및 기본 사항

# 연결
psql -h HOST -p 5432 -U USER -d DB
psql $DATABASE_URL

# psql 내부
\conninfo             -- 연결 정보 표시
\l[+]                 -- 데이터베이스 목록
\c DB                 -- DB 연결
\dt[+] [schema.]pat   -- 테이블 목록
\dv[+]                -- 뷰 목록
\ds[+]                -- 시퀀스 목록
\df[+] [pat]          -- 함수 목록
\d[S+] name           -- 테이블/뷰/시퀀스 설명
\dn[+]                -- 스키마 목록
\du[+]                -- 롤(역할) 목록
\timing               -- 쿼리 타이밍 전환
\x                    -- 확장된 표시
\e                    -- $EDITOR에서 버퍼 편집
\i file.sql           -- 파일 실행
\copy ...             -- 클라이언트 사이드 COPY
\! shell_cmd          -- 셸 명령 실행

데이터 유형 (일반)

  • 숫자형: smallint, integer, bigint, decimal(p,s), numeric, real, double precision, serial, bigserial
  • 텍스트형: text, varchar(n), char(n)
  • 불리언형: boolean
  • 시간 관련: timestamp [with/without time zone], date, time, interval
  • UUID: uuid
  • JSON: json, jsonb (권장)
  • 배열: type[] 예: text[]
  • 네트워크: inet, cidr, macaddr
  • 기하학적: point, line, polygon 등

PostgreSQL 버전 확인

SELECT version();

PostgreSQL 서버 버전:

pg_config --version

PostgreSQL 클라이언트 버전:

psql --version

DDL (생성 / 수정)

-- 스키마 및 테이블 생성
CREATE SCHEMA IF NOT EXISTS app;
CREATE TABLE app.users (
  id           bigserial PRIMARY KEY,
  email        text NOT NULL UNIQUE,
  name         text,
  active       boolean NOT NULL DEFAULT true,
  created_at   timestamptz NOT NULL DEFAULT now(),
  profile      jsonb,
  tags         text[]
);

-- 수정
ALTER TABLE app.users ADD COLUMN last_login timestamptz;
ALTER TABLE app.users ALTER COLUMN name SET NOT NULL;
ALTER TABLE app.users DROP COLUMN tags;

-- 제약 조건
ALTER TABLE app.users ADD CONSTRAINT email_lower_uk UNIQUE (lower(email));

-- 인덱스
CREATE INDEX ON app.users (email);
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON app.users (lower(email));
CREATE INDEX users_profile_gin ON app.users USING gin (profile);
CREATE INDEX users_created_at_idx ON app.users (created_at DESC);

DML (삽입 / 수정 / 업서트 / 삭제)

INSERT INTO app.users (email, name) VALUES
  ('a@x.com','A'), ('b@x.com','B')
RETURNING id;

-- 업서트 (ON CONFLICT)
INSERT INTO app.users (email, name)
VALUES ('a@x.com','Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now();

UPDATE app.users SET active = false WHERE last_login < now() - interval '1 year';
DELETE FROM app.users WHERE active = false AND last_login IS NULL;

쿼리 필수 요소

SELECT * FROM app.users ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- 페이지네이션

-- 필터링
SELECT * FROM app.users WHERE email ILIKE '%@example.%' AND active;

-- 집계 및 GROUP BY
SELECT active, count(*) AS n
FROM app.users
GROUP BY active
HAVING count(*) > 10;

-- 조인 (Joins)
SELECT o.id, u.email, o.total
FROM app.orders o
JOIN app.users u ON u.id = o.user_id
LEFT JOIN app.discounts d ON d.id = o.discount_id;

-- DISTINCT ON (Postgres 고유)
SELECT DISTINCT ON (user_id) user_id, status, created_at
FROM app.events
ORDER BY user_id, created_at DESC;

-- CTE (Common Table Expression)
WITH recent AS (
  SELECT * FROM app.orders WHERE created_at > now() - interval '30 days'
)
SELECT count(*) FROM recent;

-- 재귀적 CTE
WITH RECURSIVE t(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM t WHERE n < 10
)
SELECT sum(n) FROM t;

윈도우 함수

SELECT
  user_id,
  created_at,
  sum(total)      OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
  row_number()    OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
  lag(total, 1)   OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total
FROM app.orders;

JSON / JSONB

-- 추출
SELECT profile->>'company' AS company FROM app.users;
SELECT profile->'address'->>'city' FROM app.users;

-- jsonb 쿼리를 위한 인덱스
CREATE INDEX users_profile_company_gin ON app.users USING gin ((profile->>'company'));

-- 존재 확인 / 포함 여부
SELECT * FROM app.users WHERE profile ? 'company';              -- 키 존재
SELECT * FROM app.users WHERE profile @> '{"role":"admin"}'; -- 포함

-- jsonb 업데이트
UPDATE app.users
SET profile = jsonb_set(COALESCE(profile,'{}'::jsonb), '{prefs,theme}', '"dark"', true);

배열 (Arrays)

-- 멤버십 및 포함
SELECT * FROM app.users WHERE 'vip' = ANY(tags);
SELECT * FROM app.users WHERE tags @> ARRAY['beta'];

-- 추가
UPDATE app.users SET tags = array_distinct(tags || ARRAY['vip']);

시간 및 날짜

SELECT now() AT TIME ZONE 'Australia/Melbourne';
SELECT date_trunc('day', created_at) AS d, count(*)
FROM app.users GROUP BY d ORDER BY d;

-- 인터벌 (Intervals)
SELECT now() - interval '7 days';

트랜잭션 및 락

BEGIN;
UPDATE app.accounts SET balance = balance - 100 WHERE id = 1;
UPDATE app.accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 또는 ROLLBACK

-- 격리 수준
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 락 확인
SELECT * FROM pg_locks l JOIN pg_stat_activity a USING (pid);

롤 및 권한

-- 롤/사용자 생성
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

가져오기 / 내보내기

-- 서버 사이드 (슈퍼유저 또는 적절한 권한 필요)
COPY app.users TO '/tmp/users.csv' CSV HEADER;
COPY app.users(email,name) FROM '/tmp/users.csv' CSV HEADER;

-- 클라이언트 사이드 (psql)
\copy app.users TO 'users.csv' CSV HEADER
\copy app.users(email,name) FROM 'users.csv' CSV HEADER

성능 및 관찰 가능성

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;  -- 실제 런타임

-- 통계 뷰
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20; -- 확장 프로그램 필요

-- 유지보수
VACUUM [FULL] [VERBOSE] table_name;
ANALYZE table_name;
REINDEX TABLE table_name;

Docker, Git, PostgreSQL을 포함한 필수 개발 도구의 포괄적인 개요를 위해, 개발 도구: 모던 개발 워크플로우 완벽 가이드를 참고하세요.

필요한 경우 확장 프로그램을 활성화하세요:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 삼중음절 검색

전문 검색 (간단)

ALTER TABLE app.docs ADD COLUMN tsv tsvector;
UPDATE app.docs SET tsv = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
CREATE INDEX docs_tsv_idx ON app.docs USING gin(tsv);
SELECT id FROM app.docs WHERE tsv @@ plainto_tsquery('english', 'quick brown fox');

네이티브 Postgres 검색이 충분한지, 아니면 별도의 검색 스택을 실행해야 하는지 결정하고 있다면, PostgreSQL 전문 검색 vs Elasticsearch 비교에서 트레이드오프를 심층적으로 다루고 있습니다.

유용한 psql 설정

\pset pager off       -- 페이저 비활성화
\pset null '∅'
\pset format aligned  -- 기타: unaligned, csv
\set ON_ERROR_STOP on
\timing on

유용한 카탈로그 쿼리

-- 테이블 크기
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

-- 인덱스 부종 (대략적)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan ASC NULLS FIRST LIMIT 20;

백업 / 복원

# 논리적 덤프
pg_dump -h HOST -U USER -d DB -F c -f db.dump        # 커스텀 포맷
pg_restore -h HOST -U USER -d NEW_DB -j 4 db.dump

# 일반 SQL
pg_dump -h HOST -U USER -d DB > dump.sql
psql -h HOST -U USER -d DB -f dump.sql

GUI 데이터베이스 관리 도구에 대해서는 DBeaver vs Beekeeper - SQL Database Management Tools 및 linux에 DBeaver 설치 - howto를 참고하세요.

레포지토리 및 파일 백업과 함께 pg_dump/psql이 사용되는 실제 사례에 대해서는 Gitea 서버 백업 및 복원을 참고하세요.

복제 (고급 수준)

  • WAL 파일이 주석(primary) → **스탠바이(standby)**로 스트리밍됩니다
  • 주요 설정: wal_level, max_wal_senders, hot_standby, primary_conninfo
  • 도구: pg_basebackup, standby.signal (PG ≥12)

주의할 점 및 팁

  • 인덱싱 및 연산자를 위해 항상 json 대신 jsonb를 사용하세요.
  • timestamptz (타임존 인식) 사용을 권장합니다.
  • DISTINCT ON은 “그룹별 상위 N개"에 대한 Postgres의 보물입니다.
  • serial 대신 GENERATED ALWAYS AS IDENTITY를 사용하세요.
  • 프로덕션 쿼리에서 SELECT *를 피하세요.
  • 선택적 필터 및 조인 키를 위해 인덱스를 생성하세요.
  • 최적화하기 전에 EXPLAIN (ANALYZE)로 측정하세요.

버전별 유용한 기능 (≥v12+)

-- 생성된 아이덴티티
CREATE TABLE t (id bigINT GENERATED ALWAYS AS IDENTITY, ...);

-- 부분 인덱스를 사용한 UPSERT
CREATE UNIQUE INDEX ON t (key) WHERE is_active;

유용한 링크

구독하기

시스템, 인프라, AI 엔지니어링에 관한 새 글을 받아보세요.