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

PostgreSQL 빠른 참고 자료

Page content

일상적인 작업을 위한 빠른 참고 자료: PostgreSQL 연결, SQL 문법, psql 메타 명령어, 성능, JSON, 윈도우 함수 등.

Postgres 구체적 내용을 다루기 전에 간결한 데이터베이스 전반에 대한 복습이 필요하다면, 가장 유용한 SQL 명령어를 포함한 SQL 치트시트 가 유용한 동반자가 될 것입니다.

postgresql logo

연결 및 기초

# 연결
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)
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 Expressions)
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);

배열

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

-- 추가 (append)
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;

-- 인터벌
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 등을 포함한 필수 개발 도구에 대한 더 넓은 개요는 Developer Tools: The Complete Guide to Modern Development Workflows 를 참조하세요.

필요에 따라 확장 모듈을 활성화하세요:

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 전체 텍스트 검색 대 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 및 Install DBeaver on linux - howto 를 참조하세요.

복제 (고수준)

  • 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+)

-- 생성된 ID
CREATE TABLE t (id bigINT GENERATED ALWAYS AS IDENTITY, ...);

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

유용한 링크

구독하기

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