Skip to content

PostgreSQL 치트시트

고급 기능, 확장성, SQL 준수를 갖춘 강력한 오픈소스 관계형 데이터베이스 시스템입니다.

01

시작하기

psql로 연결

psql이 PostgreSQL의 명령줄 클라이언트입니다. 메타 명령은 백슬래시(\)로 시작합니다. 데이터베이스 나열은 \l, 테이블 설명은 \d, 도움말은 \?입니다. 연결 문자열(postgresql://)은 편리하며 대부분의 드라이버가 지원합니다. SQL 파일 실행은 \i filename.sql입니다. 비밀번호 타이핑을 피하려 PGPASSWORD 환경 변수나 ~/.pgpass 파일을 설정하세요.

postgresql
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database

# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"

# common psql meta-commands
\l              # list databases
\c mydb         # connect to mydb
\dt             # list tables
\d users        # describe table
\dn             # list schemas
\df             # list functions
\du             # list roles/users
\q              # quit
\?              # help on meta-commands
\timing         # toggle query timing

데이터베이스 생성과 삭제

CREATE DATABASE가 새 데이터베이스를 만들며 소유자, 인코딩, 로캘, 템플릿을 지정할 수 있습니다. template0은 로캘별 데이터가 없는 깨끗한 템플릿이고, template1(기본)은 커스터마이징할 수 있습니다. DROP DATABASE에는 활성 연결이 없어야 하며 WITH (FORCE)(13+)가 먼저 연결을 끊습니다. 연결 중인 데이터베이스는 드롭할 수 없습니다. ALTER DATABASE RENAME은 이름만 갱신하며 디스크 디렉터리는 그대로입니다.

postgresql
# create a database
CREATE DATABASE mydb
  WITH OWNER alice
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8'
  TEMPLATE template0
  CONNECTION LIMIT 100;

# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;

# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;

# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);

# rename a database
ALTER DATABASE mydb RENAME TO newdb;

스키마와 search_path

스키마는 (테이블의 폴더처럼) 데이터베이스 객체의 네임스페이스입니다. 기본 스키마는 'public'입니다. search_path가 자격 없는 이름 참조 시 검색할 스키마를 결정합니다. 멀티테넌트 앱 조직화, 관심사 분리, 버전 관리에 스키마를 쓰세요. CASCADE가 의존 객체를 드롭하고, RESTRICT(기본)는 객체가 있으면 거부합니다. 단일 연결 안의 멀티테넌시에는 데이터베이스보다 스키마가 더 유연합니다.

postgresql
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;

# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;

# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);

# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;

# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';

# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;

설정

PostgreSQL 설정은 postgresql.conf에 있습니다. SHOW가 현재 값을 읽고, SET이 세션용으로, SET LOCAL은 현재 트랜잭션만, ALTER DATABASE/ROLE이 지속되는 기본값을 정합니다. 일부 설정(shared_buffers, max_connections)은 서버 재시작이 필요하고, 다른 것(work_mem, statement_timeout)은 즉시 적용됩니다. 재시작 없이 설정 파일 변경 적용은 pg_reload_conf()입니다. 느린 쿼리를 위해 log_min_duration_statement를 모니터링하세요.

postgresql
# view a setting
SHOW shared_buffers;
SHOW max_connections;

# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';

# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT;  -- reverts to previous value

# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;

# reload config without restart
SELECT pg_reload_conf();

# view config file location
SHOW config_file;

psql 스크립팅과 변수

psql은 변수(\set), 파일 포함(\i), 출력 리다이렉션(\o), 셸 실행(\!)으로 스크립팅을 지원합니다. 변수 치환은 :varname입니다. 스크립팅 루프와 조건문에는 \set을 쓰세요. -f 플래그가 파일을 실행하고 종료하며 배치 작업과 마이그레이션에 유용합니다. 복잡한 스크립팅은 PL/pgSQL 함수나 마이그레이션 관리용 sqitch, Flyway 같은 외부 도구를 고려하세요.

postgresql
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql

# inside psql
\i /path/to/script.sql

# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;

# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;

# output to file
\o results.txt
SELECT * FROM users;
\o

# execute shell command
\! date
02

테이블과 제약

테이블 생성

serial이 자동 증가 정수용 시퀀스를 자동 생성하며, 새 테이블에는 대신 GENERATED ALWAYS AS IDENTITY(SQL 표준, PG10부터 권장)를 쓰세요. 제약: PRIMARY KEY(고유 + not null), UNIQUE, NOT NULL, CHECK, REFERENCES(외래 키). ON DELETE CASCADE가 부모 삭제 시 의존 행을 삭제하고, SET NULL은 FK를 NULL로 만듭니다. TEMP 테이블은 세션 후 사라지며 중간 결과에 좋습니다.

postgresql
CREATE TABLE users (
  id          serial PRIMARY KEY,
  username    varchar(50) UNIQUE NOT NULL,
  email       varchar(255) UNIQUE NOT NULL,
  password    text NOT NULL,
  age         integer CHECK (age >= 0 AND age <= 150),
  role        varchar(20) NOT NULL DEFAULT 'user',
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

# create with a foreign key
CREATE TABLE posts (
  id          serial PRIMARY KEY,
  user_id     integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  body        text,
  published   boolean DEFAULT false
);

# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
  SELECT user_id, count(*) FROM posts GROUP BY user_id;

Identity 컬럼 (PG10+)

GENERATED ALWAYS AS IDENTITY가 serial의 SQL 표준 대체물입니다. 시퀀스를 컬럼에 묶어 컬럼을 드롭하면 시퀀스도 드롭됩니다(serial은 고아 시퀀스를 남김). ALWAYS는 ID의 수동 삽입을 막습니다(강제는 OVERRIDING SYSTEM VALUE). BY DEFAULT는 serial처럼 동작합니다(수동 삽입 허용). 새 테이블에는 identity 컬럼을 쓰고 기존에는 serial도 괜찮습니다. 둘 다 내부적으로 시퀀스를 씁니다.

postgresql
# preferred over serial (SQL standard)
CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# allow manual override (like serial behavior)
CREATE TABLE products (
  id    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name  text NOT NULL
);

# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;

# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';

테이블 변경

ALTER TABLE이 테이블 재생성 없이 스키마를 진화시키는 방법입니다. (비휘발성) 기본값을 준 컬럼 추가는 PG11부터 빠릅니다(전체 테이블 재작성 없음). 컬럼 타입 변경은 전체 재작성과 변환용 USING 절이 필요할 수 있습니다. CASCADE가 의존 객체(뷰, 외래 키)를 드롭하니 신중히 쓰세요. 이후 관리 편의를 위해 제약에 이름을 붙이세요. 스크립트 멱등성을 위해 IF EXISTS/IF NOT EXISTS를 쓰세요.

postgresql
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;

# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE;  -- drop dependent objects

# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;

# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;

# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
  CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');

제약

제약이 데이터베이스 수준에서 무결성을 강제하며 앱 검사보다 항상 선호됩니다. 복합 키가 중간 테이블에 동작합니다. ON UPDATE CASCADE가 PK 변경을 FK에 전파하고, ON DELETE SET NULL은 (FK 컬럼이 nullable이어야 하는) FK를 null로 만듭니다. DEFERRABLE 제약은 (문장별이 아니라) 커밋 시점에 검사되어 순환 참조에 유용합니다. 관리 편의를 위해 제약에 이름을 붙이세요. 자동 생성 이름은 지저분하고 데이터베이스마다 다릅니다.

postgresql
# named constraint for easy management
ALTER TABLE users
  ADD CONSTRAINT positive_age CHECK (age >= 0);

# composite primary key
CREATE TABLE enrollments (
  student_id integer REFERENCES students(id),
  course_id  integer REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
  UNIQUE (email, domain);

# foreign key with actions
ALTER TABLE posts
  ADD FOREIGN KEY (user_id) REFERENCES users(id)
  ON UPDATE CASCADE ON DELETE SET NULL;

# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;

# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;

테이블 상속

PostgreSQL의 테이블 상속은 독특합니다. 자식 테이블이 부모의 컬럼을 상속합니다. ONLY를 쓰지 않으면 부모 조회가 모든 자식의 행을 반환합니다. (선언적 파티셔닝 이전의) 테이블 파티셔닝과 분류 모델링에 유용합니다. 하지만 상속에는 기묘함이 있습니다. 외래 키가 전파되지 않고 UNIQUE 제약은 테이블별로만 적용됩니다. 새 파티셔닝 요구에는 더 견고하고 잘 지원되는 선언적 파티셔닝(PARTITION BY)을 쓰세요.

postgresql
# PostgreSQL supports table inheritance
CREATE TABLE people (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE employees (
  salary numeric,
  dept   text
) INHERITS (people);

CREATE TABLE customers (
  credit_limit numeric
) INHERITS (people);

# query parent sees all children
SELECT * FROM people;          -- includes employees and customers
SELECT * FROM ONLY people;     -- only people, not children

# insert into a child
INSERT INTO employees (name, salary, dept)
  VALUES ('Alice', 50000, 'Engineering');
03

데이터 타입

숫자 타입

데이터에 맞는 크기의 정수 타입을 쓰세요. 작은 범위는 smallint, 큰 것은 bigint. numeric/decimal은 정확합니다(돈에 필수) 하지만 real/double보다 느립니다. 돈에 float은 절대 쓰지 마세요. 반올림 오류가 쌓입니다. serial은 레거시이며 GENERATED AS IDENTITY를 선호하세요. money 타입은 로캘을 인식하고 경직되어 numeric(10,2)을 쓰세요. 갭이 있어도 되는 자동 증가는 롤백/크래시 시 (성능상 설계에 의해) 시퀀스가 번호를 건너뛸 수 있습니다.

postgresql
# integer types (use the smallest that fits)
smallint    -- 2 bytes, -32768 to 32767
integer     -- 4 bytes, -2B to 2B
bigint      -- 8 bytes, very large

# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY

# decimal/numeric (exact)
numeric(precision, scale)   -- e.g. numeric(10,2) for currency
decimal                     -- alias for numeric

# floating point (inexact, faster)
real        -- 4 bytes, 6 decimal digits
double precision  -- 8 bytes, 15 decimal digits

# special
serial / bigserial   -- legacy auto-increment (prefer identity)
money                -- currency (prefer numeric)

# examples
CREATE TABLE products (
  price numeric(10,2),        -- $99999999.99
  weight real,                -- 1.5 (kg)
  quantity integer DEFAULT 0
);

텍스트와 문자 타입

PostgreSQL에서 varchar, char, text의 성능은 동일합니다. 무제한에는 text가 선호되고, 길이 제약이 필요하면 varchar(n)입니다. char(n)은 공백 패딩을 하며 거의 쓸모가 없습니다. varchar 길이 변경은 메타데이터 전용 연산입니다(빠름). LIKE는 %와 _ 와일드카드를 쓰고, ILIKE는 대소문자 무시, ~는 POSIX 정규식입니다. 전문 검색에는 tsvector/tsquery를(뒤에서 다룸) 쓰세요. 텍스트는 항상 bytea가 아니라 text/varchar로 저장하세요.

postgresql
# three text types (all variable-length)
varchar(n)   -- varchar with max length
char(n)      -- fixed-length, padded with spaces
text         -- unlimited length (preferred)

# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)

CREATE TABLE articles (
  title    varchar(200) NOT NULL,
  slug     varchar(100) UNIQUE,
  body     text,
  summary  text DEFAULT ''
);

# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;

# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);

# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$';   -- regex

날짜와 시간 타입

타임스탬프에는 항상 timestamp가 아니라 timestamptz를 쓰세요. UTC로 저장하고 표시 시 세션 시간대로 변환합니다. (tz 없는) timestamp는 naive라 다중 시간대 앱에서 버그를 낳습니다. interval이 ('1 day', '2 hours', '3 months') 산술에 강력합니다. date_trunc가 단위로 내림합니다. to_char/to_timestamp 형식 문자열이 유연합니다. 세션이나 역할별 timezone을 설정하세요. 데이터는 어차피 UTC로 저장됩니다.

postgresql
# date/time types
date              -- date only (4 bytes)
time              -- time only (8 bytes)
timetz            -- time with time zone (12 bytes)
timestamp         -- timestamp without tz (8 bytes)
timestamptz       -- timestamp WITH time zone (8 bytes, preferred)
interval          -- time span (16 bytes)

# always use timestamptz for timestamps
CREATE TABLE events (
  id         serial PRIMARY KEY,
  created_at timestamptz DEFAULT now(),
  scheduled  timestamptz,
  duration   interval
);

# common operations
SELECT now();                               -- current timestamp
SELECT current_date;                         -- today
SELECT created_at + interval '1 hour';       -- add time
SELECT age('2025-01-01', '2024-01-01');       -- interval between
SELECT date_trunc('month', created_at);       -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);

# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';

JSON과 JSONB

jsonb는 이진 JSON으로 조회와 인덱싱이 빠르지만 삽입이 약간 느리고 키 순서를 보존하지 않습니다. 모든 새 작업에는 jsonb를 쓰세요. ->는 jsonb를, ->>는 text를 반환합니다. @>(containment)가 가장 강력한 JSON 연산자이며 GIN 인덱스와 잘 맞습니다. jsonb_set은 경로를 수정하고, ||는 객체를 병합하며, -는 키를 제거합니다. JSON 필드의 빈번한 쿼리에는 추출 값에 GIN 인덱스나 표현식 인덱스를 추가하세요. JSONB는 관계형 DB 안의 유연한 스키마에 이상적입니다.

postgresql
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
  id    serial PRIMARY KEY,
  data  jsonb
);

# insert JSON
INSERT INTO products (data) VALUES
  ('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');

# query JSON fields
SELECT data->'name'        FROM products;  -- returns jsonb
SELECT data->>'name'       FROM products;  -- returns text
SELECT data->'tags'->0     FROM products;  -- first tag (jsonb)

# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';  -- containment

# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}';        -- merge
UPDATE products SET data = data - 'sale';                    -- remove key

# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));

배열과 커스텀 타입

PostgreSQL 배열은 (대부분의 언어와 달리) 1부터 인덱싱합니다. 주요 쿼리 연산자는 ANY와 @>이며, unnest()가 배열을 행으로 확장합니다(조인에 좋음). 배열은 강력하지만 현명하게 쓰세요. 배열 요소를 자주 쿼리/필터한다면 정규화된 테이블이 더 나을 수 있습니다. Enum은 순서가 있고 삽입 시 검증됩니다. 컴포지트 타입이 구조화된 컬럼을 허용합니다. 복잡한 중첩 데이터에는 컴포지트 배열 대신 JSONB를 고려하세요.

postgresql
# array columns
CREATE TABLE teams (
  id      serial PRIMARY KEY,
  name    text,
  members text[]           -- array of text
);

INSERT INTO teams (name, members) VALUES
  ('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
  ('Sales', '{"Dave", "Eve"}');

# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice'];  -- contains
SELECT members[1] FROM teams;            -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1;  -- expand to rows

# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');

# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);
04

CRUD 연산

INSERT

RETURNING은 PostgreSQL 기능으로 삽입/갱신/삭제된 행을 반환해 삽입 후 별도 SELECT가 필요 없게 합니다. ON CONFLICT(UPSERT)가 강력합니다. DO UPDATE는 제안된 행 참조에 EXCLUDED를 쓰고, DO NOTHING은 조용히 건너뜁니다. 다중 행 삽입이 개별 삽입보다 훨씬 빠릅니다. INSERT ... SELECT가 테이블 간 데이터를 복사합니다. 대량 로드에는 (더 빠른) COPY를 쓰세요. SQL 파싱 오버헤드를 우회합니다.

postgresql
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

# multi-row insert
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;

# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;

# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;

UPDATE

모든 행을 갱신하려는 것이 아니라면 WHERE를 항상 포함하세요. UPDATE ... FROM이 갱신에 조인을 허용합니다(비표준이지만 강력). RETURNING이 무엇이 바뀌었는지 보여 주며 감사에 필수입니다. CASE 표현식이 단일 문장에서 조건부 갱신을 가능하게 합니다. 값을 바꾸지 않는 갱신은 no-op입니다(WAL 없음, 문장 수준 트리거 미발동). 큰 갱신은 테이블을 부풀리므로 배치로 하고 나중에 VACUUM하세요.

postgresql
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;

# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;

# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';

# conditional update with CASE
UPDATE products
SET price = CASE
  WHEN price < 10 THEN price * 1.2
  WHEN price < 50 THEN price * 1.1
  ELSE price
END;

# update all rows (be careful!)
UPDATE users SET status = 'active';

DELETE와 TRUNCATE

DELETE가 행을 하나씩 제거합니다(트리거 발동, 트랜잭션에 가시). TRUNCATE가 모든 행을 즉시 제거합니다(DDL이며 같은 방식의 트랜잭셔널이 아니고, RESTART IDENTITY로 시퀀스를 리셋). 테이블 전체 비우기에는 TRUNCATE를 쓰세요. 몇 자릿수 더 빠릅니다. CASCADE가 이 테이블을 참조하는 외래 키 테이블을 truncate합니다. RETURNING을 준 DELETE가 감사 로그에 유용합니다. DELETE에는 항상 WHERE를 쓰세요. 없으면 전부 삭제합니다(트랜잭션 안에서는 안전, 커밋하면 재앙).

postgresql
# basic delete
DELETE FROM users WHERE id = 1;

# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;

# delete based on a join
DELETE FROM posts
WHERE user_id IN (
  SELECT id FROM users WHERE role = 'deleted'
);

# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';

# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments;          -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY;   -- reset sequences
TRUNCATE TABLE posts CASCADE;            -- also truncates FK refs

# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;

SELECT 기본

DISTINCT ON은 PostgreSQL 확장으로 그룹별 첫 행을 반환합니다. '카테고리별 최신' 쿼리에 강력합니다(DISTINCT ON 컬럼을 먼저 ORDER BY해야 함). LIMIT/OFFSET이 단순 페이지네이션이지만 큰 오프셋에서는 느리므로 더 나은 성능을 위해 keyset 페이지네이션(WHERE id > last_id)을 쓰세요. 스키마 변경 시의 놀라움을 피하려 프로덕션에서는 SELECT *이 아니라 컬럼을 명시하세요. SQL 표준 문법은 FETCH FIRST n ROWS ONLY입니다.

postgresql
# basic select
SELECT id, username, email FROM users;

# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';

# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10;  -- pagination (or use FETCH)

# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC;  -- latest post per user

# column aliases and expressions
SELECT
  username AS name,
  EXTRACT(YEAR FROM created_at) AS join_year,
  age * 365 AS age_in_days
FROM users;

MERGE (PG15+)

MERGE(SQL 표준, PG15+)가 ON CONFLICT보다 강력합니다. 매칭 조건에 따라 한 문장에서 삽입, 갱신, '그리고 삭제'가 가능합니다. 복잡한 동기화 연산(ETL, 데이터 웨어하우스 병합)에 쓰세요. 단순 upsert에는 ON CONFLICT가 더 간결하고 널리 지원됩니다. MERGE에는 소스(테이블 또는 쿼리)와 매칭 조건이 필요합니다. WHEN MATCHED/NOT MATCHED 절이 동작을 정의하고, 각 분기에 세밀한 제어를 위한 선택적 AND 조건이 가능합니다.

postgresql
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
  UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);

# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;

# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;
05

조회와 필터링

WHERE와 조건

ILIKE는 대소문자 무시 LIKE입니다(PostgreSQL 확장). ~는 POSIX 정규식(대소문자 구분), ~*는 대소문자 무시, !~는 부정입니다. IS DISTINCT FROM이 NULL을 비교 가능한 값으로 취급합니다(NULL = NULL은 참이 아니라 NULL). ANY/ALL이 배열과 서브쿼리에 동작합니다. 복잡한 텍스트 매칭에는 성능을 위해 전문 검색(tsvector)이나 트라이그램 인덱스(pg_trgm)를 고려하세요. 큰 테이블의 WHERE 절에 쓰이는 컬럼은 항상 인덱스하세요.

postgresql
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0;        -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';

# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
   OR (price < 10 AND category = 'books');

# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%';     -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';

# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;

# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);

# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]';       -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget';       -- case-insensitive
SELECT * FROM products WHERE name !~ 'test';         -- does not match

ORDER BY와 페이지네이션

Keyset 페이지네이션(WHERE id > last_id)이 큰 데이터셋에서 OFFSET보다 극적으로 빠릅니다. OFFSET은 행을 스캔하고 버립니다. ORDER BY random()은 큰 테이블에서 느리며 통계적 샘플링에는 TABLESAMPLE을 쓰세요. NULLS FIRST/LAST가 NULL 정렬 위치를 제어합니다(기본은 ASC/DESC에 따라 다름). 페이지네이션 시 항상 ORDER BY를 포함하세요. 없으면 행 순서가 정의되지 않습니다. 안정적인 페이지네이션은 유니크 컬럼 순 정렬입니다.

postgresql
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;

# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;

# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- page 2

# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;

# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;

# random rows
SELECT * FROM products ORDER BY random() LIMIT 5;  -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1);    -- 1% sample

GROUP BY와 집계

GROUP BY가 그룹핑 컬럼별로 행을 축소하고, 집계(count, sum, avg, min, max)가 그룹별 계산합니다. HAVING이 그룹을 필터하고(WHERE는 그룹화 전 행을 필터) ROLLUP이 각 수준의 소계를, CUBE가 모든 조합을, GROUPING SETS가 원하는 그룹핑을 정확히 지정합니다. 보고서에 모두 강력합니다. count(*)는 행을 세고 count(column)는 non-null 값을 셉니다. 고유 개수에는 count(DISTINCT col)를 쓰세요.

postgresql
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;

# multiple aggregates
SELECT
  category,
  count(*)           AS total,
  avg(price)         AS avg_price,
  min(price)         AS min_price,
  max(price)         AS max_price,
  sum(price)         AS total_value
FROM products
GROUP BY category;

# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;

# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;

# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status);  -- adds subtotals and grand total

# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

DISTINCT와 집합 연산

UNION이 결과를 결합하며 중복을 제거합니다(느림). UNION ALL은 중복을 유지합니다(빠름. 중복이 없음을 알거나 상관없을 때 선호). INTERSECT가 공통 행을, EXCEPT가 차이를 반환합니다. 집합 연산에는 호환되는 컬럼 타입과 개수가 필요합니다. DISTINCT ON은 PostgreSQL의 보석입니다. ORDER BY 기준으로 그룹별 첫 행을 반환하며 '카테고리별 최신/가장 비싼' 쿼리에 완벽합니다. 모든 집합 연산은 체이닝 가능하며 우선순위 제어는 괄호로 하세요.

postgresql
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;

# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC;  -- most expensive per category

# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;

# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;

# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;

CASE와 조건 로직

CASE 표현식이 SQL에 if/then/else 로직을 더합니다. 단순 CASE는 값을 매칭하고 탐색형 CASE는 조건을 평가합니다. (PostgreSQL의) FILTER 절이 조건부 카운팅에 집계 안의 CASE보다 깔끔합니다. CASE는 SELECT, WHERE, ORDER BY, 심지어 GROUP BY에 나타날 수 있습니다. 피벗, 조건 서식, 데이터베이스가 가져야 할 비즈니스 로직에 쓰세요. 복잡한 다분기 로직은 함수나 뷰를 고려하세요.

postgresql
# simple CASE
SELECT
  name,
  CASE category
    WHEN 'book' THEN 'Media'
    WHEN 'laptop' THEN 'Electronics'
    ELSE 'Other'
  END AS category_group
FROM products;

# searched CASE (more flexible)
SELECT
  name,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    WHEN price < 100 THEN 'Expensive'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

# CASE in aggregate (pivot-like)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active_count,
  count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

# CASE for conditional sorting
SELECT * FROM products
ORDER BY
  CASE WHEN featured THEN 0 ELSE 1 END,
  name;
06

조인

INNER와 OUTER 조인

INNER JOIN은 매칭 행만 반환합니다. LEFT JOIN은 왼쪽 행을 모두 유지하며(매칭이 없으면 오른쪽 컬럼 NULL) '관련 데이터가 있으면 포함'의 가장 흔한 조인입니다. RIGHT JOIN은 거의 안 쓰입니다(LEFT로 재작성). FULL OUTER JOIN은 전부 유지합니다. LEFT JOIN 뒤의 WHERE p.id IS NULL이 매칭 없는 행을 찾는 '안티 조인' 패턴입니다. 모호함을 피하려 컬럼 이름에 항상 테이블 별칭(u.id, p.user_id)을 붙이세요.

postgresql
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;

# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- users with no posts

다중 테이블 조인

PostgreSQL은 많은 테이블을 조인할 수 있지만 각 조인이 비용을 더하므로 조인이 인덱스를 쓰도록 하세요. self-join이 계층 데이터에 유용합니다(깊은 계층에는 재귀 CTE 고려). 집계와 조인 시, 게시물이 없는 사용자에게 0을 주려면 count(*)가 아니라 LEFT JOIN + count(p.id)를 쓰세요. 옵티마이저가 조인 순서를 고르지만 명확한 쿼리 작성이 도움이 됩니다. 복잡한 다중 테이블 쿼리는 뷰 생성을 고려하세요.

postgresql
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

# join with different conditions
SELECT
  u.username,
  p.title,
  l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;

# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

CROSS JOIN과 LATERAL

CROSS JOIN이 카테시안 곱(A의 모든 행과 B의 모든 행)을 만듭니다. 원하는 것은 드물지만 데이터 생성용 generate_series와 함께 쓰면 유용합니다. LATERAL이 강력합니다. 서브쿼리가 외부 쿼리의 컬럼을 참조하게 하며 행별 함수 같습니다. '그룹별 top N' 쿼리에 이상적입니다(단순한 경우 윈도우 함수보다 훨씬 깔끔). 매칭 없는 행도 포함은 LEFT JOIN LATERAL ... ON true입니다.

postgresql
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;

# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);

# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent;

# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
  SELECT title FROM posts WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 3
) recent ON true;

조인 전략 (EXPLAIN)

PostgreSQL은 테이블 크기, 인덱스, 통계를 기반으로 조인 전략을 고릅니다. Nested Loop는 O(N*M)으로 작거나 인덱스된 조회에 적합합니다. Hash Join이 작은 입력에 해시 테이블을 만든 뒤 탐색하며 큰 미정렬 조인에 탁월합니다. Merge Join은 양쪽 입력이 정렬되어야 하며 인덱스가 순서를 제공할 때 좋습니다. 프로덕션에서 전략을 강제하지 말고 옵티마이저가 결정하게 하세요. 실제 타이밍 확인과 인덱스/통계 조정에는 EXPLAIN (ANALYZE)을 쓰세요.

postgresql
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join strategies you'll see:
#   Nested Loop:   for each left row, scan right (good for small/indexed)
#   Hash Join:     hash one table, probe with other (good for large unsorted)
#   Merge Join:    both sorted, merge (good for sorted/indexed large tables)

# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;

# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;

조인 성능 팁

가장 영향력 큰 단일 조인 최적화는 외래 키 컬럼 인덱싱입니다. PostgreSQL은 FK에 인덱스를 '자동 생성하지 않습니다'. ANALYZE로 통계를 신선하게 유지하세요(autovacuum이 하지만 대량 로드 후 수동 ANALYZE가 도움). 조인 컬럼을 함수로 감싸지 마세요(인덱스 사용 방지). 필요한 컬럼만 선택하세요. 거대 테이블은 파티셔닝을 고려해 옵티마이저가 파티션을 가지치기하게 하세요. 느린 조인은 pg_stat_statements로 모니터링하세요.

postgresql
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);

# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id);  -- PK already indexed

# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;

# avoid joining on expressions (prevents index use)
-- BAD:  JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)

# limit columns selected (reduces I/O)
SELECT u.username, p.title  -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;

# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01';  -- prunes partitions
07

집계와 윈도우 함수

집계 함수

PostgreSQL에는 count/sum/avg 넘어 풍부한 집계가 있습니다. string_agg와 array_agg가 값을 문자열/배열로 연결해 N+1 쿼리를 피하게 합니다. bool_or/bool_and가 불리언을 집계합니다. percentile_cont가 정확한 백분위를 계산합니다(중앙값 = 0.5). 분석에 매우 유용합니다. mode()가 최빈 값을 반환합니다. (count(*) FILTER (WHERE...) 같은) FILTER 절이 조건부 집계에 CASE보다 깔끔합니다. 집계 안의 ORDER BY가 출력 순서를 제어합니다.

postgresql
# standard aggregates
SELECT
  count(*)              AS total_rows,
  count(distinct category) AS unique_categories,
  sum(price)            AS total_value,
  avg(price)            AS avg_price,
  min(price)            AS min_price,
  max(price)            AS max_price,
  stddev(price)         AS std_dev,
  variance(price)       AS variance
FROM products;

# string aggregation
SELECT
  user_id,
  string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;

# array aggregation
SELECT
  user_id,
  array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;

# boolean aggregation
SELECT
  bool_or(published)    AS any_published,
  bool_and(published)   AS all_published
FROM posts;

# statistical aggregates
SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
  mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;

윈도우 함수

윈도우 함수는 현재 행과 관련된 행 집합에 걸쳐 계산하며 GROUP BY처럼 행을 축소하지 않습니다. PARTITION BY가 윈도우 그룹을 정의하고 ORDER BY가 그 안의 순서를 정의합니다. ROW_NUMBER는 항상 고유하고, RANK는 동률 뒤 번호를 건너뛰며, DENSE_RANK는 건너뛰지 않습니다. LAG/LEAD가 다른 행에 접근해 시계열 분석에 완벽합니다. (ROWS BETWEEN ...) 프레임 절이 윈도우에 포함될 행을 제어합니다. 윈도우 함수는 WHERE/GROUP BY/HAVING 뒤에 평가됩니다.

postgresql
# ROW_NUMBER: unique sequential number
SELECT
  title,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;

# RANK and DENSE_RANK (handle ties)
SELECT
  title,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

# LAG and LEAD (compare to previous/next row)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;

# running total
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

윈도우 프레임과 NTILE

윈도우 프레임이 윈도우 함수가 볼 행을 제어합니다. 집계의 기본 프레임은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(누적 합)입니다. LAST_VALUE는 프레임을 UNBOUNDED FOLLOWING으로 늘여야 하며 아니면 현재 행을 반환합니다(기본 프레임은 현재에서 끝남). NTILE이 행을 N개 버킷으로 나누며 사분위/십분위에 유용합니다. FIRST_VALUE/LAST_VALUE가 프레임 경계 행의 값을 반환합니다. 프레임 이해가 윈도우 함수 마스터의 열쇠입니다.

postgresql
# frame types
#   ROWS:   exact row count
#   RANGE:  logical range (e.g., same value)
#   GROUPS: groups of peer rows

SELECT
  date,
  revenue,
  # running sum from start to current
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,

  # 7-day moving average
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,

  # sum of all rows (no frame = entire partition)
  SUM(revenue) OVER () AS grand_total
FROM daily_sales;

# NTILE: divide rows into N equal buckets
SELECT
  name,
  price,
  NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;

# FIRST_VALUE / LAST_VALUE
SELECT
  name,
  category,
  price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
  LAST_VALUE(name) OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS least_expensive
FROM products;

FILTER 절

FILTER 절은 PostgreSQL 확장으로 집계 안의 CASE보다 깔끔하고 종종 빠릅니다. 여러 조건부 집계가 필요한 피벗형 보고서에 특히 유용합니다. 쿼리가 테이블을 한 번 스캔하며 모든 집계를 단일 패스로 계산하므로 여러 서브쿼리보다 훨씬 효율적입니다. FILTER는 (많은 PG 확장과 달리) SQL 표준의 일부이며 다른 데이터베이스도 지원합니다.

postgresql
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'active') AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status = 'banned') AS banned,
  sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;

# equivalent with CASE (more verbose)
SELECT
  count(*) AS total,
  count(CASE WHEN status = 'active' THEN 1 END) AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;

# FILTER with multiple aggregates in one pass (efficient)
SELECT
  category,
  count(*) FILTER (WHERE price > 100) AS expensive_count,
  avg(price) FILTER (WHERE price > 100) AS expensive_avg,
  count(*) FILTER (WHERE price <= 100) AS cheap_count,
  avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;

공통 테이블 표현식 (CTE)

CTE(WITH 절)가 쿼리 가독성을 높이고 서브쿼리 재사용을 허용합니다. PostgreSQL 12+는 기본적으로 비재귀 CTE를 인라인합니다(서브쿼리와 성능 일치). 12 이전에는 CTE가 최적화 울타리였습니다. RECURSIVE CTE가 계층/트리 데이터에 강력합니다. 앵커 쿼리가 시드하고 재귀 부분이 자랍니다. RETURNING을 준 CTE가 (한 테이블에서 지우고 다른 테이블에 아카이브하는) 복잡한 데이터 이동을 한 문장으로 가능하게 합니다. 복잡한 쿼리를 읽기 좋은 단계로 쪼개는 데 CTE를 쓰세요.

postgresql
# basic CTE (WITH clause)
WITH active_users AS (
  SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;

# multiple CTEs (can reference each other)
WITH user_stats AS (
  SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
  SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;

# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;

# CTE for DML (data modification)
WITH deleted AS (
  DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;
08

인덱스

B-Tree 인덱스

B-tree가 기본이자 가장 흔한 인덱스 타입입니다. 등호, 범위, 정렬을 지원합니다. 복합 인덱스는 왼쪽부터 검색되므로 등호 컬럼을 먼저, 그다음 범위/정렬 컬럼을 두세요. 부분 인덱스(WHERE 절)가 공간을 아끼고 더 빠르며 흔한 필터 쿼리(예: WHERE active = true)에 쓰세요. 표현식 인덱스가 함수 결과의 인덱싱을 가능하게 합니다. CONCURRENTLY는 쓰기를 막지 않고 빌드합니다(느리지만 프로덕션 안전). 외래 키는 항상 인덱스하세요!

postgresql
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

# composite index (order matters!)
CREATE INDEX idx_posts_user_created
  ON posts(user_id, created_at DESC);

# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
  WHERE active = true;

# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
#   SELECT * FROM users WHERE lower(email) = '[email protected]'

# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- no lock, slower

GIN과 GiST 인덱스

GIN 인덱스는 각 행이 여러 인덱스 값(JSONB 키, 배열 요소, 텍스트 토큰)을 가질 때 이상적입니다. @>(contains), ?(키 존재), 전문 @@ 연산자를 지원합니다. GIN 인덱스는 더 크지만 이런 쿼리를 효율적으로 처리합니다. GiST는 기하 데이터(점, 다각형)와 범위 타입용이며 overlap(&&), contains(@>), contained(<@)를 지원합니다. GIN과 GiST는 B-tree보다 빌드/갱신이 느리지만 B-tree가 못 하는 쿼리를 가능하게 합니다. JSONB와 전문에는 GIN, 공간과 범위 쿼리에는 GiST를 쓰세요.

postgresql
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search

CREATE INDEX idx_products_data ON products USING GIN (data);        -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);              -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text

# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');

# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location);  -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during);       -- tsrange

# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';

# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');

BRIN과 기타 인덱스 타입

BRIN(Block Range INdex)은 극히 컴팩트합니다(GB가 아니라 KB). 블록 범위별 min/max를 저장합니다. 물리 행 순서가 쿼리 순서와 일치할 때(예: 새 데이터가 append되는 시계열 로그) 효과적입니다. BRIN이 '충분히 좋은' 필터로 대부분의 블록을 제거하고 PostgreSQL이 남은 행을 검사합니다. 거대한 append-only 테이블에는 BRIN이 판도를 바꿉니다. 해시 인덱스는 등호만 지원하지만(범위 불가) 포인트 조회에 빠릅니다. SP-GiST가 IP 라우팅 테이블 같은 불균형 데이터에 적합합니다.

postgresql
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!

# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)

# compare sizes:
#   B-tree on 1B rows: ~20GB
#   BRIN on 1B rows:   ~2MB (10000x smaller)

# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix);  -- e.g., IP routing

# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting

# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';

인덱스 유지보수와 분석

pg_stat_user_indexes로 인덱스 사용을 모니터링하세요. 쓰지 않는 인덱스는 공간을 낭비하고 쓰기를 느리게 합니다. 중복(같은 컬럼, 같은 테이블)을 제거하세요. REINDEX가 부풀어 오른 인덱스를 재구축합니다(무거운 갱신/삭제로 발생). CONCURRENTLY(12+)가 잠금을 피합니다. 인덱스 비대는 정상이며 autovacuum이 유지하지만 때때로 수동 REINDEX가 필요합니다. 매우 큰 인덱스에는 REINDEX CONCURRENTLY나 (잠금 없이 재구축하는 서드파티 도구) pg_repack을 고려하세요. 인덱스 변경은 항상 스테이징에서 테스트하세요.

postgresql
# find unused indexes (candidates for removal)
SELECT
  schemaname, relname, indexrelname,
  idx_scan AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
       (array_agg(idx::text))[1] AS indexes
FROM (
  SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
  FROM pg_index
  GROUP BY indrelid, indkey
  HAVING count(*) > 1
) sub;

# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users;  -- PG12+, no lock

# check index bloat
SELECT
  relname, pg_size_pretty(pg_relation_size(relid)) AS size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;

인덱싱 전략

PK(자동 인덱스)와 FK(수동)로 시작하세요. 핫 쿼리 경로에 인덱스를 추가합니다. 복합 인덱스는 leftmost-prefix 규칙을 따르며 컬럼을 선택성과 쿼리 패턴 순으로 두세요. INCLUDE(커버링 인덱스)가 행을 가져오지 않고 인덱스만으로 쿼리가 충족되게 합니다(인덱스 전용 스캔). 읽기 무거운 워크로드에 큰 이득입니다. 모든 것을 인덱스하지 마세요. 각 인덱스가 쓰기 오버헤드를 더합니다. pg_stat_user_indexes로 정기 검토·가지치기하고, 인덱스 사용 검증은 항상 EXPLAIN으로 하세요.

postgresql
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);

# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);

# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
  WHERE status = 'active';

# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
  INCLUDE (name);  -- name available without heap fetch

# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)

# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
09

트랜잭션과 동시성

트랜잭션 기본

트랜잭션이 문장을 원자적 단위로 묶습니다. 전부 성공하거나 전부 실패. BEGIN이 시작하고, COMMIT이 저장하고, ROLLBACK이 취소합니다. 세이브포인트가 트랜잭션 내 부분 롤백을 허용하며 복잡한 연산의 오류 복구에 유용합니다. 기본적으로 PostgreSQL은 각 문장을 자동 커밋합니다. 다중 문장 연산(이체, 다중 행 삽입)에는 항상 명시적 트랜잭션을 쓰세요. 락 경쟁과 교착을 줄이려 트랜잭션을 짧게 유지하세요.

postgresql
# explicit transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK to undo

# savepoints (partial rollback)
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  SAVEPOINT order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
  -- oops, wrong product
  ROLLBACK TO order_inserted;
  INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;

# transaction control
BEGIN;
  -- statements
SAVEPOINT my_savepoint;
  -- more statements
ROLLBACK TO my_savepoint;  -- undo back to savepoint
RELEASE my_savepoint;      -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything

# auto-commit (default — each statement is its own transaction)
SET autocommit = on;  -- default

격리 수준

PostgreSQL의 기본(READ COMMITTED)이 대부분의 앱에 좋습니다. 더티 읽기는 없지만 같은 트랜잭션의 재읽기가 새 데이터를 볼 수 있습니다. PostgreSQL의 REPEATABLE READ는 SQL 표준보다 강합니다. (스냅샷 격리로) 팬텀도 막습니다. SERIALIZABLE이 가장 강력하지만 충돌하는 트랜잭션을 중단할 수 있으므로 앱은 SQLSTATE 40001에서 재시도해야 합니다. 대부분의 웹 앱에는 READ COMMITTED로 충분합니다. (금융 원장 같은) 중요 정확성에는 재시도 로직과 함께 SERIALIZABLE을 쓰세요.

postgresql
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED;   -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

# or set it for the session
SET default_transaction_isolation = 'serializable';

# READ COMMITTED (default):
#   - each query sees committed data at query start
#   - no dirty reads, but non-repeatable reads and phantoms possible

# REPEATABLE READ:
#   - each query sees snapshot from transaction start
#   - no non-repeatable reads, but serialization anomalies possible
#   - PostgreSQL's RR is stronger than SQL standard (no phantoms!)

# SERIALIZABLE:
#   - strongest isolation, behaves as if transactions ran one at a time
#   - may abort with serialization failure (retry needed)
#   - uses SSI (Serializable Snapshot Isolation)

# check current level
SHOW transaction_isolation;

# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- work
COMMIT;  -- may fail with SQLSTATE 40001 -> retry the transaction

락과 교착

SELECT FOR UPDATE가 갱신용 행 잠금으로 동시 수정을 막으며 '읽고, 확인하고, 갱신' 패턴에 유용합니다. 어드바이저리 락이 정수 키를 쓰는 앱 수준 락으로 분산 프로세스 조율에 좋습니다. 교착은 두 트랜잭션이 서로 기다릴 때 발생하며 PostgreSQL이 감지해 하나를 중단합니다(잡아서 재시도). 교착 회피는 트랜잭션 간 일관된 순서의 락 획득과 짧은 트랜잭션입니다. 막힌 세션은 pg_locks로 모니터링하세요.

postgresql
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;        -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE;         -- allow reads, block writes

# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345);          -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345);      -- non-blocking
SELECT pg_advisory_xact_lock(12345);     -- transaction-level (auto-released)

# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

# terminate a blocked session
SELECT pg_cancel_backend(pid);    -- cancel query
SELECT pg_terminate_backend(pid); -- kill session

# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detected

MVCC와 스냅샷 격리

PostgreSQL의 MVCC는 읽기가 절대 쓰기를 막지 않고 그 반대도 아님을 의미합니다. 각 트랜잭션이 스냅샷을 봅니다. 갱신이 새 행 버전을 만들고, 오래된 버전은 VACUUM이 제거할 때까지 남습니다. 장기 트랜잭션이 VACUUM의 정리를 막아 비대를 일으킵니다. 트랜잭션은 짧게! 'idle in transaction' 세션을 모니터링하세요. 스냅샷을 쥐고 정리를 막습니다. 트랜잭션 ID wraparound는 심각한 문제입니다(autovacuum이 막지만 곤란하면 모니터링).

postgresql
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data

# visible transactions
SELECT txid_current();           -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());

# see long-running transactions (can prevent vacuum)
SELECT
  pid,
  age(clock_timestamp(), xact_start) AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;

# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
  pid,
  age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;

잠금 패턴

FOR UPDATE는 비관적으로 행을 잠그며 (이체 같은) 중요 일관성에 쓰세요. 낙관적 잠금(버전 컬럼)이 낮은 경쟁에 더 낫습니다. 락 없이 충돌 시 재시도만. SKIP LOCKED가 (여러 워커가 막히지 않고 다른 작업을 잡는) 작업 큐에 완벽합니다. NOWAIT는 잠겨 있으면 빠르게 실패합니다('갱신 시도, 대기 없음'). 어드바이저리 락이 앱 수준 연산(마이그레이션, cron 작업)을 조율합니다. 높은 경쟁에는 비관적, 낮은 경쟁에는 낙관적을 고르세요.

postgresql
# pessimistic locking (lock before work)
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  -- now safe to modify
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 0 rows = conflict, retry

# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)

# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked

# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));
10

뷰와 머터리얼라이즈드 뷰

뷰 생성

뷰는 테이블처럼 동작하는 저장된 쿼리입니다. 단순 뷰(단일 테이블, 집계 없음)는 자동으로 갱신 가능하며 삽입/갱신이 기저 테이블로 전달됩니다. WITH CHECK OPTION이 뷰를 통해 보이지 않게 되는 삽입/갱신을 막습니다. 뷰는 복잡한 쿼리 단순화, 보안 강제(컬럼 수준 접근), 안정 API 제공에 쓰세요. CASCADE가 의존 객체를 드롭합니다. 뷰는 데이터를 저장하지 않으며 매번 쿼리를 실행합니다.

postgresql
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;

# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# view with computed columns
CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  count(p.id) AS post_count,
  max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;

# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]');  -- works!

# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails

# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;

머터리얼라이즈드 뷰

머터리얼라이즈드 뷰가 쿼리 결과를 저장합니다. 조회는 훨씬 빠르지만 갱신 전까지는 오래됩니다. (대시보드, 보고서 같은) 비싼 집계에 쓰세요. CONCURRENTLY는 뷰를 잠그지 않고 갱신합니다(읽기 계속) 하지만 유니크 인덱스가 필요합니다. 갱신 스케줄은 pg_cron이나 외부 도구로 합니다. 실시간에 가까운 데이터는 몇 분마다, 역사적 분석은 매일. 절충은 신선함 vs 조회 속도이며 머터리얼라이즈드 뷰가 큰 테이블의 빠른 분석의 열쇠입니다.

postgresql
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
  date_trunc('day', created_at) AS day,
  product_id,
  count(*) AS order_count,
  sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA;  -- or WITH NO DATA to populate later

# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;  -- needs unique index

# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);

# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');

# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';

보안 뷰

뷰는 강력한 보안 도구입니다. 테이블 접근을 부여하지 않고 사용자가 봐야 하는 컬럼/행만 노출합니다. 기저 테이블이 아니라 뷰에 SELECT를 부여하세요. 행 수준 보안에는 뷰 대신 PostgreSQL 내장 RLS(Row Level Security) 정책을 고려하세요. 더 견고하고 직접 테이블 접근과도 동작합니다. (current_setting 같은) 세션 변수 뷰가 사용자별 필터링을 가능하게 합니다. 뷰로 스키마 복잡성을 앱에게 숨기는 안정 API 계층을 만드세요.

postgresql
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden

# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;

-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts;  -- only sees user 42's posts

# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
  o.id AS order_id,
  o.created_at,
  u.username,
  p.name AS product_name,
  oi.quantity,
  oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;

뷰 관리

CREATE OR REPLACE VIEW에는 한계가 있습니다. 컬럼을 끝에 추가할 수 있지만 제거나 재정렬은 못 합니다. 큰 변화는 드롭 후 재생성이되(먼저 의존성 확인) 뷰는 다른 뷰에 의존할 수 있어 기저 뷰 드롭에는 CASCADE가 필요합니다. 정의 검사는 pg_views입니다. 모든 머터리얼라이즈드 뷰를 갱신하는 DO 블록이 유지보수 스크립트에 편리하지만 잠금을 피하려 가능한 한 CONCURRENTLY를 쓰세요.

postgresql
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';

# get view definition
\d+ view_name  -- in psql

# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder

# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;

# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';

# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
  END LOOP;
END $$;

재귀 뷰

재귀 뷰(CREATE RECURSIVE VIEW)는 재귀 CTE를 감쌉니다. 트리/그래프 순회에 좋습니다. (데이터의 사이클로 인한) 무한 루프를 막으려 재귀 부분에 항상 깊이/거리 한도를 포함하세요. (텍스트 연결인) path 컬럼이 순회 경로 추적·표시의 간단한 방법입니다. 매우 깊은 계층에는 더 나은 성능을 위해 클로저 테이블이나 ltree 확장 저장을 고려하세요. 재귀 뷰는 편리하지만 큰 데이터셋에서 느릴 수 있으니 조인 컬럼에 인덱스를 추가하세요.

postgresql
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
  id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
  e.id, e.name, e.manager_id, et.level + 1,
  et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;

# query the tree
SELECT * FROM employee_tree ORDER BY path;

# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;

# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
  fc.person1, f.person2, fc.distance + 1,
  fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6;  -- limit depth to prevent infinite loops
11

함수와 연산자

문자열 함수

PostgreSQL에는 포괄적인 문자열 함수가 있습니다. substring 검색은 position()과 strpos()이며, FROM/FOR를 준 substring()이 SQL 표준이고 substr()이 더 간결합니다. 구분자 데이터에는 split_part()가 편리합니다. initcap()이 각 단어를 대문자로 만들고, lpad/rpad가 문자열을 고정 폭으로 패딩합니다(서식에 유용). string_to_array/array_to_string이 문자열과 배열을 변환합니다. 패턴 매칭은 LIKE/ILIKE(와일드카드), ~(정규식), SIMILAR TO(SQL 패턴)가 대부분의 요구를 커버합니다.

postgresql
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello');          -- 4
SELECT strpos('hello', 'lo');               -- 4

# substring and slicing
SELECT substring('hello' from 2 for 3);     -- 'ell'
SELECT substring('hello' from 2);           -- 'ello'
SELECT substr('hello', 2, 3);               -- 'ell'
SELECT left('hello', 3);                    -- 'hel'
SELECT right('hello', 3);                   -- 'llo'

# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim('  hello  '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x');             -- trim both sides

# split and join
SELECT split_part('a,b,c', ',', 2);         -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ',');       -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', ');  -- 'a, b, c'

# replace and pad
SELECT replace('hello', 'l', 'L');          -- 'heLLo'
SELECT lpad('5', 3, '0');                   -- '005'
SELECT rpad('5', 3, '0');                   -- '500'
SELECT repeat('ab', 3);                     -- 'ababab'
SELECT reverse('hello');                    -- 'olleh'

날짜/시간 함수

now()와 current_timestamp는 등가입니다(트랜잭션 시작 시각). 구성 요소 추출은 EXTRACT/date_part이며, date_trunc가 단위로 내림해 시간 버킷팅에 필수입니다. age()가 (사람이 읽는) interval을 반환합니다. 서식/파싱은 to_char/to_date/to_timestamp입니다. 날짜와 함께 쓰는 generate_series가 시계열 데이터의 갭 채우기에 놀랍도록 유용합니다(0 개수일을 보여 주려 LEFT JOIN). 항상 timestamptz를 쓰세요. 날짜 산술은 세션 시간대를 존중합니다.

postgresql
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;

# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now());     -- day of week (0=Sunday)
SELECT date_part('epoch', now());   -- Unix timestamp

# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01');                    -- interval since date
SELECT age('2025-01-01', '2024-01-01');      -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)

# truncation and rounding
SELECT date_trunc('month', now());           -- first day of month, 00:00
SELECT date_trunc('hour', now());            -- current hour, 00 minutes

# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000);             -- Unix epoch

# generate series (date ranges)
SELECT generate_series(
  '2025-01-01'::date,
  '2025-01-31'::date,
  '1 day'::interval
);

숫자와 수학 함수

인자 둘인 round()는 numeric에서 동작합니다(double이 아님. 먼저 캐스트). 삼각 함수는 라디안이며 변환은 radians()/degrees()입니다. random()이 0-1을 반환하며 범위는 수학과 결합하세요. generate_series가 PostgreSQL의 주력으로 시퀀스, 날짜 범위, 갭 채우기를 위한 행을 즉석에서 생성합니다. gcd/lcm(13+)이 편리합니다. 금융 계산에는 항상 (정확한) numeric을 쓰세요. double에 대한 round 같은 부동소수점 함수는 정밀도 문제가 있을 수 있습니다.

postgresql
# rounding
SELECT round(3.14159, 2);    -- 3.14
SELECT ceil(3.1), ceiling(3.1);  -- 4
SELECT floor(3.9);           -- 3
SELECT trunc(3.14159, 2);    -- 3.14 (truncate, no rounding)

# power and roots
SELECT power(2, 10);         -- 1024
SELECT sqrt(16);             -- 4
SELECT cbrt(27);             -- 3
SELECT exp(1);               -- 2.718... (e^x)
SELECT ln(10), log(100);     -- natural log, base-10 log

# trigonometry (radians!)
SELECT sin(radians(90));     -- 1 (convert degrees to radians)
SELECT degrees(1.5708);      -- ~90
SELECT pi();                 -- 3.14159...

# random
SELECT random();             -- 0 to 1
SELECT floor(random() * 100)::int;  -- 0 to 99
SELECT setseed(0.5);         -- seed for reproducibility

# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2);   -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1);  -- 0.0, 0.1, ..., 1.0

# absolute, sign, gcd
SELECT abs(-5), sign(-5);    -- 5, -1
SELECT gcd(12, 8);           -- 4 (PG 13+)
SELECT lcm(12, 8);           -- 24 (PG 13+)

조건과 NULL 함수

COALESCE가 첫 non-NULL 값을 반환하며 기본값 부여에 필수입니다. NULLIF는 값이 같으면 NULL을 반환하며 0 나눗셈 회피에 CASE보다 깔끔합니다. GREATEST/LEAST가 값을 비교합니다(NULL은 GREATEST에서 전파되지만 LEAST에서는 항상 그렇지 않음). CASE가 일반 조건문입니다. ISNULL/NOTNULL은 PG 축약입니다. 행 비교(ROW(a,b) < ROW(c,d))가 사전식 비교로 복합 키 페이지네이션에 유용합니다. NULL 동작 이해(NULL = NULL은 참이 아니라 NULL)가 PostgreSQL에서 결정적입니다.

postgresql
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;

# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users;  -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats;  -- NULL instead of error

# GREATEST / LEAST
SELECT GREATEST(1, 2, 3);        -- 3
SELECT LEAST(NULL, 1, 2);        -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;

# CASE expressions
SELECT
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age >= 65 THEN 'senior'
    ELSE 'adult'
  END AS age_group
FROM users;

# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL;    -- IS NULL
SELECT * FROM users WHERE email NOTNULL;   -- IS NOT NULL

# row comparison
SELECT ROW(1, 2) < ROW(1, 3);  -- true (compares column by column)

배열과 JSON 함수

배열 함수가 데이터베이스 내 강력한 데이터 조작을 가능하게 합니다. unnest가 배열을 행으로 확장하고(조인에 좋음) array_agg가 그 역입니다. JSONB 연산자: ->와 #>가 jsonb를, ->>와 #>>가 text를 반환합니다. @>(contains)가 가장 중요한 JSONB 연산자이며 GIN 인덱스를 씁니다. ?가 키 존재를 검사합니다. jsonb_build_object가 컬럼에서 JSON을 만들고, jsonb_set/||/-가 JSON을 수정합니다. 이 함수들이 JSONB를 일급 데이터 타입으로 만듭니다. 유연하면서 조회 가능합니다.

postgresql
# array functions
SELECT array_length(ARRAY[1,2,3], 1);    -- 3
SELECT array_append(ARRAY[1,2], 3);       -- {1,2,3}
SELECT array_prepend(1, ARRAY[2,3]);      -- {1,2,3}
SELECT array_concat(ARRAY[1,2], ARRAY[3,4]); -- {1,2,3,4}
SELECT array_remove(ARRAY[1,2,3], 2);     -- {1,3}
SELECT array_replace(ARRAY[1,2,1], 1, 9); -- {9,2,9}
SELECT unnest(ARRAY[1,2,3]);              -- 3 rows
SELECT array_agg(name) FROM users;        -- aggregate to array

# JSONB functions
SELECT data->'name'           -- jsonb (key access)
SELECT data->>'name'          -- text (key access)
SELECT data#>'{address,city}' -- jsonb (path access)
SELECT data#>>'{address,city}'-- text (path access)
SELECT jsonb_build_object('name', username, 'age', age) FROM users;
SELECT jsonb_object_keys(data) FROM products;  -- keys as rows
SELECT jsonb_array_elements(data->'tags') FROM products;  -- expand array

# JSONB predicates
SELECT data @> '{"active": true}'     -- contains
SELECT data ? 'name'                   -- key exists
SELECT data ?| ARRAY['name','email']   -- any key exists
SELECT data ?& ARRAY['name','email']   -- all keys exist

# modify JSONB
SELECT jsonb_set(data, '{price}', '29.99')
SELECT data || '{"new": true}'         -- merge
SELECT data - 'old_key'                -- remove key
SELECT data #- '{nested,old_key}'      -- remove nested key
12

트리거

트리거 함수

트리거 함수는 trigger를 반환해야 하며 PL/pgSQL(또는 다른 언어)로 씁니다. 트리거 안에서 사용 가능한 특수 변수는 TG_OP, TG_TABLE_NAME, OLD, NEW입니다. BEFORE 트리거는 NEW를 수정할 수 있고(NULL 반환으로 연산 생략 가능), AFTER 트리거는 (감사 로그, 알림 같은) 부수 효과용입니다. FOR EACH ROW는 행마다, FOR EACH STATEMENT는 한 번 발동합니다. 트리거는 강력하지만 숨은 부수 효과를 낳을 수 있으니 (감사, 타임스탬프 같은) 횡단 관심사에 쓰지 비즈니스 로직에는 쓰지 마세요.

postgresql
# create a trigger function (must return trigger)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# attach the trigger
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

# now any UPDATE on users sets updated_at automatically
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- updated_at is set automatically

# audit log trigger
CREATE OR REPLACE FUNCTION audit_log()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table (table_name, operation, user_name, changed_at, row_data)
  VALUES (
    TG_TABLE_NAME, TG_OP, current_user, now(),
    to_jsonb(OLD)  -- or NEW for insert
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_log();

문장 vs 행 트리거

FOR EACH ROW 트리거가 행마다 발동합니다(대량 연산에서는 느릴 수 있음). FOR EACH STATEMENT는 한 번 발동하며 개별 OLD/NEW에는 접근할 수 없지만 대량용 전이 테이블을 쓸 수 있습니다. BEFORE 트리거는 행을 수정·생략할 수 있고 AFTER는 부수 효과용입니다. WHEN 절이 트리거 발동 여부를 필터합니다(행별 평가). 전이 테이블(REFERENCING NEW TABLE/OLD TABLE)이 문장 트리거에 모든 변경 행 접근을 주어 행별 오버헤드 없이 대량 연산을 감사하기 좋습니다.

postgresql
# ROW trigger (fires once per affected row)
CREATE TRIGGER log_each_change
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

# STATEMENT trigger (fires once per statement, regardless of rows)
CREATE TRIGGER refresh_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION refresh_sales_summary();

# BEFORE vs AFTER
# BEFORE: can modify NEW, skip the operation (return NULL), or change values
# AFTER: cannot modify the row, used for side effects (logs, notifications)

# conditional trigger (WHEN clause — evaluated per row)
CREATE TRIGGER check_high_value
  BEFORE INSERT ON orders
  FOR EACH ROW
  WHEN (NEW.amount > 10000)
  EXECUTE FUNCTION require_approval();

# transition tables (NEW/OLD as sets, for statement triggers)
CREATE TRIGGER audit_orders
  AFTER INSERT ON orders
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION log_bulk_insert();

이벤트 트리거 (DDL)

이벤트 트리거는 DDL 명령(CREATE/ALTER/DROP)에서 발동합니다. 스키마 변경 감사나 프로덕션의 위험 연산 방지에 유용합니다. 명령 식별은 tg_event와 tg_tag입니다. 흔한 사용 사례: DROP TABLE 방지, 규정 준수를 위한 전체 DDL 로그, 마이그레이션 스크립트 자동 생성. 이벤트 트리거는 명령 '후에' 발동하거나(ddl_command_end) 가로챌 수 있습니다(ddl_command_start). 테이블 수준이 아니라 데이터베이스 수준입니다. 모든 DDL에 오버헤드를 더하니 아껴 쓰세요.

postgresql
# event triggers fire on DDL events (CREATE, ALTER, DROP)
CREATE OR REPLACE FUNCTION no_drop_table()
RETURNS event_trigger AS $$
BEGIN
  IF tg_event = 'ddl_command_end' AND tg_tag = 'DROP TABLE' THEN
    RAISE EXCEPTION 'Dropping tables is not allowed in production';
  END IF;
END;
$$ LANGUAGE plpgsql;

# register the event trigger
CREATE EVENT TRIGGER protect_tables
  ON ddl_command_end
  WHEN tag IN ('DROP TABLE')
  EXECUTE FUNCTION no_drop_table();

# log all DDL changes
CREATE OR REPLACE FUNCTION log_ddl()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  INSERT INTO ddl_log (event, tag, user_name, object_identity)
  VALUES (tg_event, tg_tag, current_user, NULL);
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER ddl_logger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl();

# drop an event trigger
DROP EVENT TRIGGER IF EXISTS protect_tables;

트리거 관리

대량 로드에서는 트리거를 임시 비활성화하세요(훨씬 빠름). 하지만 다시 켜는 것을 잊지 마세요! session_replication_role = 'replica'가 모든 트리거와 규칙을 비활성화하며 대량 임포트나 복제용입니다. 더 목표 지향적인 것은 ALTER TABLE ... DISABLE TRIGGER입니다. 대량 로드 후에는 항상 트리거를 다시 켜세요. 트리거 사용을 모니터링하세요. 각 트리거가 행당 오버헤드를 더해 숨은 성능 문제를 낳을 수 있습니다. 전체 트리거 목록은 information_schema.triggers이고 검사에는 psql의 \d+가 더 빠릅니다.

postgresql
# list triggers
SELECT
  event_object_table AS table_name,
  trigger_name,
  action_timing,       -- BEFORE/AFTER/INSTEAD OF
  event_manipulation,  -- INSERT/UPDATE/DELETE
  action_statement     -- function call
FROM information_schema.triggers;

# or use psql
\d+ users  -- shows triggers on the table

# enable/disable triggers
ALTER TABLE users DISABLE TRIGGER set_updated_at;
ALTER TABLE users ENABLE TRIGGER set_updated_at;
ALTER TABLE users DISABLE TRIGGER ALL;    -- all triggers
ALTER TABLE users ENABLE TRIGGER ALL;

# session_replication_role (disable for replication)
SET session_replication_role = 'replica';
-- triggers don't fire (useful for bulk data loads)
SET session_replication_role = 'origin';  -- re-enable

# drop a trigger
DROP TRIGGER IF EXISTS set_updated_at ON users;

# rename a trigger
ALTER TRIGGER set_updated_at ON users RENAME TO update_timestamp;

흔한 트리거 패턴

흔한 트리거 패턴: 자동 타임스탬프(updated_at), 감사 로그(규정 준수를 위한 모든 변경 추적), 비즈니스 규칙 강제(교차 테이블 검증), 비정규화 데이터 동기화(카운터 유지). 각 패턴이 진짜 문제를 풀지만 복잡성과 오버헤드를 더합니다. 대안 고려: 타임스탬프에는 기본값, 앱 수준 감사 로깅, 단순 규칙에는 CHECK 제약, 비정규화에는 머터리얼라이즈드 뷰. 트리거는 로직이 데이터베이스에 있어야 할 때(여러 앱이 데이터에 접근하거나 규칙이 협상 불가) 사용하세요.

postgresql
# 1. Auto-update updated_at
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# 2. Audit log (track all changes)
CREATE FUNCTION audit() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, old_data, new_data, changed_by, changed_at)
  VALUES (TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END,
    current_user, now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

# 3. Enforce business rules
CREATE FUNCTION check_budget() RETURNS trigger AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = NEW.dept_id AND d.budget < NEW.amount
  ) THEN
    RAISE EXCEPTION 'Amount exceeds department budget';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# 4. Sync denormalized data
CREATE FUNCTION update_post_count() RETURNS trigger AS $$
BEGIN
  UPDATE users SET post_count = (
    SELECT count(*) FROM posts WHERE user_id = NEW.user_id
  ) WHERE id = NEW.user_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
13

저장 프로시저와 PL/pgSQL

PL/pgSQL 함수

PL/pgSQL이 PostgreSQL의 절차 언어로 SQL에 변수, 조건문, 루프, 오류 처리를 더합니다. 함수는 값이나 테이블을 반환합니다. SELECT INTO가 쿼리 결과를 변수에 할당하고, RETURN QUERY가 쿼리의 행을 반환합니다. 파라미터는 기본값을 가질 수 있습니다. 재사용 로직, 복잡한 쿼리, 비즈니스 규칙 캡슐화에 함수를 쓰세요. 함수는 단순하고 이름이 좋게 유지합니다. 성능을 위해 단순 쿼리에는 (옵티마이저가 인라인할 수 있는) SQL 함수(LANGUAGE sql)를 고려하세요.

postgresql
# basic function
CREATE OR REPLACE FUNCTION add(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

# function with variables and logic
CREATE OR REPLACE FUNCTION get_user_post_count(user_id integer)
RETURNS integer AS $$
DECLARE
  post_count integer;
BEGIN
  SELECT count(*) INTO post_count
  FROM posts WHERE posts.user_id = get_user_post_count.user_id;

  IF post_count IS NULL THEN
    RETURN 0;
  END IF;

  RETURN post_count;
END;
$$ LANGUAGE plpgsql;

# function returning a table (set-returning)
CREATE OR REPLACE FUNCTION get_recent_posts(limit_count integer DEFAULT 10)
RETURNS TABLE(id integer, title text, created_at timestamptz) AS $$
BEGIN
  RETURN QUERY
  SELECT posts.id, posts.title, posts.created_at
  FROM posts
  ORDER BY posts.created_at DESC
  LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

# call functions
SELECT get_user_post_count(42);
SELECT * FROM get_recent_posts(5);

제어 구조

PL/pgSQL에는 완전한 제어 구조가 있습니다. IF/ELSIF/ELSE, CASE, LOOP/EXIT/CONTINUE, WHILE, FOR(정수 범위), FOREACH(배열 순회). 루프 탈출은 EXIT WHEN이고 FOR i IN 1..n이 (포함) 범위를 순회합니다. FOREACH가 배열을 순회합니다. (안전한 식별자 주입은 format()과 함께) 동적 SQL 실행은 EXECUTE입니다. 데이터베이스 안의 루프는 작게 유지하세요. 큰 루프는 가능한 한 집합 기반 SQL이어야 합니다. 대량 연산에는 행별 루프 대신 SELECT가 붙은 INSERT/UPDATE를 쓰세요.

postgresql
# IF/ELSIF/ELSE
CREATE OR REPLACE FUNCTION categorize_price(price numeric)
RETURNS text AS $$
BEGIN
  IF price IS NULL THEN RETURN 'unknown';
  ELSIF price < 10 THEN RETURN 'cheap';
  ELSIF price < 50 THEN RETURN 'moderate';
  ELSE RETURN 'expensive';
  END IF;
END;
$$ LANGUAGE plpgsql;

# CASE expression (inside BEGIN...END)
CREATE OR REPLACE FUNCTION get_discount(tier text)
RETURNS numeric AS $$
BEGIN
  RETURN CASE tier
    WHEN 'gold'   THEN 0.20
    WHEN 'silver' THEN 0.10
    WHEN 'bronze' THEN 0.05
    ELSE 0.00
  END;
END;
$$ LANGUAGE plpgsql;

# LOOP (with EXIT)
CREATE OR REPLACE FUNCTION find_first_null(table_name text)
RETURNS integer AS $$
DECLARE i integer := 1; val text;
BEGIN
  LOOP
    EXECUTE format('SELECT col FROM %I WHERE id = %s', table_name, i) INTO val;
    EXIT WHEN val IS NULL;
    i := i + 1;
    EXIT WHEN i > 1000;  -- safety limit
  END LOOP;
  RETURN i;
END;
$$ LANGUAGE plpgsql;

# WHILE and FOR loops
CREATE OR REPLACE FUNCTION sum_n(n integer)
RETURNS integer AS $$
DECLARE total integer := 0;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$ LANGUAGE plpgsql;

저장 프로시저 (PG11+)

프로시저(PG11+)는 함수와 다릅니다. 트랜잭션을 관리할 수 있고(안에서 COMMIT/ROLLBACK), 함수처럼 값을 반환하지 않으며(대신 OUT 파라미터) CALL로 호출됩니다. 트랜잭션 제어가 필요한 다중 문장 연산(ETL, 데이터 마이그레이션)에는 프로시저를, 값을 반환하는 계산과 쿼리에는 함수를 쓰세요. 프로시저가 트랜잭션 로직 캡슐화의 SQL 표준 방법입니다. PG11 전에는 함수가 커밋/롤백을 못 했고 프로시저가 그 공백을 채웁니다.

postgresql
# PROCEDURE (unlike FUNCTION, can manage transactions)
CREATE OR REPLACE PROCEDURE transfer_money(
  from_account integer,
  to_account integer,
  amount numeric
) AS $$
BEGIN
  -- inside a procedure, we can use COMMIT/ROLLBACK
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;

  -- check for negative balance
  IF EXISTS (SELECT 1 FROM accounts WHERE id = from_account AND balance < 0) THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  COMMIT;
END;
$$ LANGUAGE plpgsql;

# call a procedure (CALL, not SELECT)
CALL transfer_money(1, 2, 100.00);

# procedure with output parameters
CREATE OR REPLACE PROCEDURE get_stats(
  OUT user_count integer,
  OUT post_count integer
) AS $$
BEGIN
  SELECT count(*) INTO user_count FROM users;
  SELECT count(*) INTO post_count FROM posts;
END;
$$ LANGUAGE plpgsql;

CALL get_stats(?, ?);  -- output params

오류 처리

RAISE EXCEPTION이 커스텀 오류로 트랜잭션을 중단합니다. EXCEPTION 블록이 (try/catch처럼) 오류를 잡아 우아한 저하에 흔합니다. SQLSTATE가 5자 오류 코드이고 SQLERRM이 메시지입니다. USING HINT/DETAIL이 도움 되는 맥락을 더합니다. 디버깅에는 (psql에서 보이는) RAISE NOTICE가 좋습니다. 진짜 버그를 가리지 않으려 OTHERS보다 특정 오류(division_by_zero, unique_violation)를 잡으세요. EXCEPTION 안에서 트랜잭션은 중단 상태이며 커밋/롤백만 가능합니다(추가 쿼리는 PG11+ 프로시저까지 불가).

postgresql
# RAISE for messages and exceptions
CREATE OR REPLACE FUNCTION validate_age(age integer)
RETURNS void AS $$
BEGIN
  IF age < 0 THEN
    RAISE EXCEPTION 'Age cannot be negative: %', age
      USING HINT = 'Please provide a valid age (0-150)';
  ELSIF age > 150 THEN
    RAISE EXCEPTION 'Age seems unrealistic: %', age;
  ELSE
    RAISE NOTICE 'Age validated: %', age;
  END IF;
END;
$$ LANGUAGE plpgsql;

# TRY/CATCH with EXCEPTION block
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric AS $$
DECLARE result numeric;
BEGIN
  result := a / b;
  RETURN result;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

# RAISE levels: DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION
# EXCEPTION aborts the transaction; others just log

# get error details
SELECT sqlstate, message, detail, hint, context
FROM pg_stat_activity;  -- or in exception handler: SQLSTATE, SQLERRM

동적 SQL

동적 SQL(EXECUTE)이 문자열을 SQL로 실행합니다. 테이블/컬럼 이름이 가변일 때 필수입니다. SQL 주입을 막으려 항상 %I(식별자)와 %L(리터럴)의 format()을 쓰세요. 사용자 입력을 직접 연결하는 것은 절대 금물! USING이 (준비 문장처럼) 파라미터를 안전히 전달합니다. 동적 SQL은 강력하지만 쿼리 플래너와 정적 분석 도구에 불투명합니다. 필요할 때(동적 테이블 이름, DDL 생성) 쓰되 가능하면 정적 SQL을 선호하세요. 철저히 테스트하세요. 동적 SQL 오류는 컴파일 타임이 아니라 런타임입니다.

postgresql
# EXECUTE for dynamic queries
CREATE OR REPLACE FUNCTION count_rows(table_name text)
RETURNS integer AS $$
DECLARE result integer;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', table_name) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

# format() for safe SQL construction
#   %I = identifier (table/column name, quoted)
#   %L = literal (value, quoted)
#   %s = simple string substitution (UNSAFE — avoid for user input)
SELECT format('SELECT * FROM %I WHERE id = %L', 'users', 42);
-- 'SELECT * FROM users WHERE id = 42'

# dynamic SQL with USING for parameters
CREATE OR REPLACE FUNCTION get_user_by(field text, value text)
RETURNS setof users AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM users WHERE %I = $1', field
  ) USING value;
END;
$$ LANGUAGE plpgsql;

# bulk operations with dynamic SQL
CREATE OR REPLACE FUNCTION archive_old_data(days_old integer)
RETURNS integer AS $$
DECLARE deleted_count integer;
BEGIN
  EXECUTE format(
    'WITH deleted AS (
       DELETE FROM logs WHERE created_at < now() - interval '%s days'
       RETURNING *
     ) SELECT count(*) FROM deleted'
  ) INTO deleted_count USING days_old;

  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;
14

보안과 권한

사용자와 역할

PostgreSQL에서 사용자와 역할은 같은 엔티티이며 '사용자'는 LOGIN 권한이 있는 역할일 뿐입니다. 권한을 중앙 관리하려 (LOGIN 없는) 그룹 역할을 만들고 이를 로그인 역할에 GRANT하세요. VALID UNTIL이 비밀번호 만기를 정하고, CONNECTION LIMIT이 역할별 동시 연결을 제한합니다. 멀티테넌트 격리에는 역할별 SET search_path가 좋습니다. 개별 사용자에게 권한을 부여하기보다 권한 그룹핑에는 항상 역할을 쓰세요. DROP ROLE에는 먼저 모든 권한과 멤버십 제거가 필요합니다.

postgresql
# create a role (users are roles that can log in)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE ROLE analyst WITH LOGIN PASSWORD 'secret' VALID UNTIL '2025-12-31';

# create a role that cannot log in (for grouping)
CREATE ROLE readonly;

# alter a role
ALTER ROLE app_user WITH PASSWORD 'new_secret';
ALTER ROLE app_user WITH VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE app_user SET search_path TO app_schema, public;

# grant role to another (inherit permissions)
GRANT readonly TO app_user;
GRANT readonly TO analyst;

# view roles
\du  -- in psql
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

# drop a role (must revoke privileges first)
REVOKE readonly FROM app_user;
DROP ROLE app_user;

권한 부여

권한: 테이블에는 SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER, 스키마에는 USAGE/CREATE, 시퀀스에는 USAGE/SELECT, 함수에는 EXECUTE, 데이터베이스에는 CONNECT/CREATE/TEMPORARY. 컬럼 수준 부여가 보안에 강력합니다. ALTER DEFAULT PRIVILEGES가 미래 객체에 권한을 부여하며 새 테이블이 자주 만들어질 때 필수입니다. serial/identity 컬럼이 있는 테이블에 삽입하는 역할에는 항상 시퀀스 USAGE를 부여하세요. 최소 권한 접근을 위해 과잉 권한은 회수하세요.

postgresql
# grant table privileges
GRANT SELECT, INSERT, UPDATE ON products TO app_user;
GRANT ALL ON products TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

# grant column-level privileges
GRANT SELECT (id, name, email) ON users TO app_user;
-- app_user can only see those columns

# grant schema privileges
GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT CREATE ON SCHEMA app_schema TO developer;

# grant sequence privileges (for serial/identity)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

# grant database privileges
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT TEMPORARY ON DATABASE mydb TO app_user;

# grant function privileges
GRANT EXECUTE ON FUNCTION get_user_post_count(integer) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

# revoke privileges
REVOKE INSERT, UPDATE ON products FROM app_user;

# alter default privileges (for future objects)
ALTER DEFAULT PRIVILEGES FOR ROLE postgres
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

행 수준 보안 (RLS)

RLS(Row Level Security)가 행별 접근 제어를 강제하며 사용자는 정책과 일치하는 행만 봅니다. USING이 읽기를 필터하고 WITH CHECK가 쓰기를 검증합니다. 현재 사용자 식별에는 앱별 세션 변수(SET app.user_id)를 설정하세요. FORCE RLS는 테이블 소유자조차 정책의 대상으로 만들고, BYPASSRLS 역할 속성이 모든 RLS를 건너뜁니다(관리자용). RLS가 멀티테넌트 앱에 강력합니다. 사용자별 뷰가 필요 없습니다. 주의: RLS 정책은 데이터를 조용히 필터하므로 올바라야 합니다. 다른 사용자로 철저히 테스트하세요.

postgresql
# enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

# create a policy (users see only their own posts)
CREATE POLICY user_posts ON posts
  FOR SELECT
  USING (user_id = current_setting('app.user_id')::int);

# policy for all operations
CREATE POLICY user_posts_all ON posts
  USING (user_id = current_setting('app.user_id')::int)
  WITH CHECK (user_id = current_setting('app.user_id')::int);

# USING:    filter existing rows (SELECT, UPDATE, DELETE)
# WITH CHECK: validate new rows (INSERT, UPDATE)

# set the user context per session
SET app.user_id = '42';
SELECT * FROM posts;  -- only sees user 42's posts

# bypass RLS (for admins)
ALTER TABLE posts FORCE ROW LEVEL SECURITY;  -- even owner respects RLS
GRANT BYPASSRLS TO admin_role;  -- role-level bypass

# policy for admins (see all)
CREATE POLICY admin_all ON posts
  FOR ALL
  TO admin_role
  USING (true)
  WITH CHECK (true);

# disable RLS
ALTER TABLE posts DISABLE ROW LEVEL SECURITY;

인증과 SSL

pg_hba.conf가 첫 방어선입니다. 어느 호스트/사용자가 어떻게 연결할 수 있는지 제어합니다. scram-sha-256을 항상 쓰세요(md5나 trust 아님). hostssl이 연결에 SSL을 요구합니다. sslmode=require가 암호화를 보장하고 verify-full이 인증서까지 검증합니다(가장 안전). 프로덕션: 모든 원격 연결에 SSL, pg_hba.conf를 알려진 IP로 제한, scram-sha-256 사용. 편집 후 pg_reload_conf()로 리로드하세요. 무단 연결은 pg_stat_activity로 모니터링하세요.

postgresql
# pg_hba.conf controls who can connect and how
# host  database  user  address  method
#   host all all 127.0.0.1/32 scram-sha-256
#   host all all 0.0.0.0/0   scram-sha-256
#   hostssl all all 0.0.0.0/0 scram-sha-256  # require SSL

# reload pg_hba.conf
SELECT pg_reload_conf();

# authentication methods:
#   trust:       no password (NEVER use in production)
#   md5:         legacy password hash (deprecated)
#   scram-sha-256:  modern password auth (default, recommended)
#   cert:        client certificate
#   peer:        OS username matches DB username (local only)

# set password encryption
SET password_encryption = 'scram-sha-256';
ALTER ROLE app_user PASSWORD 'secret';  -- re-hash with scram

# SSL connections
SHOW ssl;                  -- is SSL enabled?
SHOW ssl_cert_file;
SHOW ssl_key_file;

# connect with SSL
psql "postgresql://user@host/db?sslmode=require"
# sslmode: disable, allow, prefer, require, verify-ca, verify-full

# view active connections
SELECT pid, usename, client_addr, ssl, ssl_cipher
FROM pg_stat_activity;

암호화와 감사

pgcrypto가 컬럼 수준 암호화를 제공합니다. 가역 암호화는 pgp_sym_encrypt/decrypt, 비밀번호 해싱은 crypt/gen_salt. 비밀번호에는 bcrypt(bf)를 쓰세요. 설계상 느려 무차별 대입에 저항합니다. 규정 준수를 위해 pgAudit가 모든 DDL과 DML 연산을 서버 로그에 기록합니다. 민감 데이터에는 앱 계층 암호화를 선호하세요(키는 데이터베이스에 있으면 안 됨). 비밀번호는 항상 해싱하세요(가역 암호화는 절대 아님). 활성 쿼리 감사는 pg_stat_activity 모니터링입니다. 심층 방어를 위해 이 도구들을 결합하세요.

postgresql
# encrypt columns with pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id serial PRIMARY KEY,
  email text,
  ssn bytea  -- encrypted
);

INSERT INTO users (email, ssn) VALUES
  ('[email protected]', pgp_sym_encrypt('123-45-6789', 'secret_key'));

SELECT pgp_sym_decrypt(ssn, 'secret_key') AS ssn FROM users;

# one-way hashing (passwords)
SELECT crypt('password', gen_salt('bf'));      -- bcrypt
SELECT crypt('password', gen_salt('md5'));     -- MD5 (weak)
-- store the hash, verify with:
SELECT crypt('password', stored_hash) = stored_hash;

# audit with pgAudit extension
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
SELECT pg_reload_conf();
-- logs all writes and DDL to the PostgreSQL log

# session auditing
SELECT
  pid, usename, application_name, client_addr,
  query_start, state, query
FROM pg_stat_activity
WHERE state = 'active';
15

백업과 복구

pg_dump 기본

pg_dump가 (데이터베이스를 재생성하는 SQL 문장의) 논리 백업을 만듭니다. 커스텀 형식(-F c)은 압축되며 병렬 복원과 선택적 복원을 지원하므로 항상 이를 선호하세요. 디렉터리 형식(-F d)이 병렬 덤프와 복원을 가능하게 합니다. pg_dumpall이 역할과 테이블스페이스를 포함한 모든 데이터베이스를 백업하며(완전 클러스터 백업용) --clean --if-exists가 덤프를 멱등하게 합니다(여러 번 복원 가능). 큰 데이터베이스에는 (디렉터리 형식 전용) --jobs로 병렬 덤프를 고려하세요.

postgresql
# backup a single database
pg_dump -h localhost -U postgres -d mydb -F c -f mydb.dump
# -F c: custom format (compressed, supports parallel restore)
# -F p: plain SQL (human-readable, default)
# -F d: directory format (parallel, multiple files)

# backup with options
pg_dump -d mydb -F c -f mydb.dump \
  --no-owner           -- don't include ownership commands
  --no-privileges      -- don't include GRANT/REVOKE
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data, no schema
  --schema-only        -- only schema, no data
  --clean              -- include DROP statements
  --if-exists          -- use IF EXISTS in DROPs

# backup all databases
pg_dumpall -h localhost -U postgres -f all_databases.sql
# includes roles, tablespaces, and all databases

# compress a plain SQL dump
pg_dump -d mydb | gzip > mydb.sql.gz

# backup a remote database
pg_dump "postgresql://user:pass@remote-host:5432/mydb" -F c -f mydb.dump

pg_restore

pg_restore가 커스텀/디렉터리 형식에서 복원합니다(평문 SQL은 아니므로 그건 psql). --jobs=N이 병렬 복원을 켜며 멀티코어에서 훨씬 빠릅니다. --clean --if-exists가 먼저 기존 객체를 드롭합니다. 다른 사용자로 복원할 때 --no-owner가 필수입니다. --list가 덤프 내용을 보여 주고 선택적 복원에는 --table/--schema입니다. 큰 복원에서는 트리거와 제약을 임시 비활성화했다가 다시 켜세요. 항상 복원을 테스트하세요. 복원할 수 없는 백업은 무가치합니다.

postgresql
# restore from custom format
pg_restore -h localhost -U postgres -d mydb -F c mydb.dump

# restore with options
pg_restore -d mydb mydb.dump \
  --clean              -- drop objects before recreating
  --if-exists          -- use IF EXISTS in DROPs
  --no-owner           -- don't restore ownership
  --schema=app_schema  -- only this schema
  --table=users        -- only this table
  --data-only          -- only data
  --jobs=4             -- parallel restore (4 threads)
  --verbose

# restore to a new database
createdb newdb
pg_restore -d newdb mydb.dump

# restore from plain SQL
psql -d mydb -f mydb.sql
gunzip -c mydb.sql.gz | psql -d mydb

# list contents of a dump
pg_restore -l mydb.dump

# restore specific items
pg_restore -d mydb mydb.dump --table=users --table=posts

# generate SQL to inspect (without restoring)
pg_restore mydb.dump > mydb.sql
pg_restore --list mydb.dump  -- list items with indices

물리 백업과 PITR

pg_basebackup이 전체 데이터 디렉터리의 바이너리 복사본을 만듭니다. 큰 데이터베이스에서 pg_dump보다 빠르고 PITR을 지원합니다. PITR에는 WAL(Write-Ahead Log) 아카이빙이 필수입니다. 모든 WAL 파일을 아카이브한 뒤 베이스 백업 복원 + 원하는 시점까지 WAL 재생. recovery_target_time이 특정 타임스탬프로 복구하게 합니다(실수로 인한 DROP TABLE 취소). pg_create_restore_point가 이름 붙은 복구 타깃을 만듭니다. PITR이 백업의 금융 표준이며 논리 백업은 pg_dump와 결합하세요.

postgresql
# physical backup with pg_basebackup (online)
pg_basebackup -h localhost -U repluser -D /backup/base \
  -Ft -z -P          -- tar format, compressed, progress
  --wal-method=stream  -- stream WAL files separately

# set up WAL archiving (postgresql.conf)
#   archive_mode = on
#   archive_command = 'cp %p /archive/%f'
SELECT pg_reload_conf();

# Point-in-Time Recovery (PITR):
# 1. Restore base backup
# 2. Create recovery.signal file
# 3. Configure recovery (postgresql.conf or recovery.conf):
#      restore_command = 'cp /archive/%f %p'
#      recovery_target_time = '2025-01-15 14:30:00'
#      recovery_target_action = 'promote'
# 4. Start PostgreSQL — it replays WAL to the target time

# create a recovery point
SELECT pg_create_restore_point('before_migration');

# check WAL archiving status
SELECT * FROM pg_stat_archiver;

복제 설정

스트리밍 복제: 프라이머리가 실시간으로 WAL을 레플리카에 보냅니다. pg_basebackup -R이 한 명령으로 레플리카를 설정합니다. 레플리카는 읽기 전용(핫 스탠바이)이며 읽기 쿼리를 제공할 수 있습니다. 복제 지연 모니터링은 프라이머리의 pg_stat_replication, 레플리카의 pg_last_xact_replay_timestamp입니다. 승격(pg_ctl promote)이 레플리카를 프라이머리로 만들며 페일오버에 씁니다. 자동 페일오버에는 Patroni, repmgr, pg_auto_failover 같은 도구를 쓰세요. 캐스케이딩 복제가 레플리카를 사슬로 묶어 프라이머리 부하를 줄입니다.

postgresql
# on primary (postgresql.conf):
#   wal_level = replica
#   max_wal_senders = 10
#   hot_standby = on  (for replica)

# create replication role
CREATE ROLE repluser WITH REPLICATION LOGIN PASSWORD 'secret';

# in pg_hba.conf, allow replication connections:
#   host replication repluser 0.0.0.0/0 scram-sha-256

# on replica, create standby using pg_basebackup:
pg_basebackup -h primary-host -U repluser -D /var/lib/postgresql/data \
  -Fp -Xs -P -R
# -R creates standby.signal and configures primary_conninfo

# monitor replication
# on primary:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_current_wal_lsn();

# on replica:
SELECT * FROM pg_stat_wal_receiver;
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

# promote a replica to primary (failover)
pg_ctl promote -D /var/lib/postgresql/data

# cascading replication
#   replica can also be a primary to other replicas
#   set cascade_level or use primary_conninfo pointing to another replica

논리 복제

논리 복제(PG10+)가 테이블 수준에서 변경을 복제하며 물리 복제보다 유연합니다. 다른 PostgreSQL 버전, 선택적 테이블, 양쪽의 다른 스키마. 퍼블리셔가 PUBLICATION을 만들고 서브스크라이버가 SUBSCRIPTION을 만듭니다. 초기 데이터가 복사된 뒤 변경이 실시간 스트리밍됩니다. 충돌(예: 기존 PK로 INSERT)은 복제를 멈추므로 SKIP이나 수동 수정으로 처리하세요. 무중단 업그레이드(구버전에서 신버전으로 복제 후 전환)에 훌륭합니다. 데이터베이스 간 데이터 공유나 부분 복제에도 쓰세요.

postgresql
# logical replication (PG10+) — replicate specific tables
# on publisher:
CREATE PUBLICATION my_pub FOR TABLE users, posts;

# add more tables to the publication
ALTER PUBLICATION my_pub ADD TABLE comments;

# on subscriber:
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher-host user=repluser dbname=mydb'
  PUBLICATION my_pub;

# initial data copy happens automatically
# subsequent changes are replicated in real-time

# monitor logical replication
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

# replicate only specific operations
CREATE PUBLICATION insert_only FOR TABLE logs WITH (publish = 'insert');

# control conflict resolution
# (subscriber applies changes; conflicts cause replication to stop)
# handle with: ALTER SUBSCRIPTION ... SKIP or manual resolution

# cross-version replication (e.g., PG14 -> PG16)
# logical replication works across major versions!

# remove
DROP SUBSCRIPTION my_sub;
DROP PUBLICATION my_pub;
16

성능과 튜닝

EXPLAIN

EXPLAIN이 쿼리 플랜을 보여 주고 ANALYZE가 쿼리를 실행해 실제 타이밍을 보여 줍니다. 큰 테이블의 Seq Scan(누락 인덱스), 높은 Rows Removed by Filter(비효율 쿼리), 외부 정렬(work_mem 증가)을 찾으세요. Index Only Scan이 가장 빠르며(커버링 인덱스 필요) Bitmap Scan은 Seq Scan과 Index Scan 사이입니다. 'cost' 수치는 플래너 추정이고 'actual time'이 실제입니다. 튜닝에는 항상 EXPLAIN (ANALYZE, BUFFERS)를 쓰세요. 진짜 병목을 드러냅니다. 오래된 통계 발견을 위해 추정 행과 실제 행을 비교하세요.

postgresql
# basic EXPLAIN (query plan without running)
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# EXPLAIN ANALYZE (actually runs the query, shows real stats)
EXPLAIN ANALYZE SELECT * FROM users JOIN posts ON users.id = posts.user_id;

# with buffer stats
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE id = 42;

# with format (JSON, TEXT, XML, YAML)
EXPLAIN (FORMAT JSON) SELECT * FROM users;

# common plan nodes:
#   Seq Scan:        full table scan (slow for large tables)
#   Index Scan:      index lookup + heap fetch
#   Index Only Scan: index-only (fastest, needs covering index)
#   Bitmap Scan:     bitmap of matching rows, then fetch
#   Hash Join:       hash one table, probe with other
#   Nested Loop:     for each left row, scan right (small datasets)
#   Sort:            explicit sort (look for "Sort Method: external merge" = bad)

# warning signs:
#   "Seq Scan on large_table" -> missing index
#   "Sort Method: external disk" -> increase work_mem
#   "Rows Removed by Filter: 999999" -> bad filter, missing index

VACUUM과 ANALYZE

PostgreSQL의 MVCC는 UPDATE/DELETE에서 죽은 행을 만들며 VACUUM이 이를 회수합니다. autovacuum이 자동 실행되지만(꺼두지 마세요!) 쓰기 많은 테이블에는 튜닝이 필요할 수 있습니다. VACUUM FULL은 테이블을 재작성하며(공간을 OS에 회수) 배타 잠금이 필요하므로 온라인 재조직에는 대신 pg_repack을 쓰세요. ANALYZE가 플래너가 쓰는 통계를 갱신하며 대량 로드 후 결정적입니다. 오래된 통계가 나쁜 쿼리 플랜을 낳습니다. 테이블별 autovacuum_scale_factor를 튜닝하세요(크고 쓰기 많은 테이블은 낮게). vacuum 지연 감지는 n_dead_tup 모니터링입니다.

postgresql
# VACUUM (reclaim space from dead rows, don't lock)
VACUUM users;            -- regular vacuum
VACUUM FULL users;       -- rewrites table (locks, reclaims to OS)
VACUUM ANALYZE users;    -- vacuum + update statistics
VACUUM (VERBOSE, ANALYZE) users;  -- with output

# autovacuum (should be ON in production)
SHOW autovacuum;  -- should be on
SHOW autovacuum_vacuum_threshold;     -- default 50
SHOW autovacuum_vacuum_scale_factor;  -- default 0.2 (20% dead rows)

# tune autovacuum per table
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,  -- vacuum at 10% dead rows
  autovacuum_analyze_scale_factor = 0.05
);

# ANALYZE (update planner statistics — critical for query plans)
ANALYZE users;         -- sample the table
ANALYZE;               -- all tables

# check dead tuples (need vacuuming)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

# check autovacuum activity
SELECT pid, datname, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

메모리와 설정

shared_buffers가 PostgreSQL의 공유 캐시입니다(RAM의 25%가 고전적 권장). effective_cache_size가 OS 캐시를 플래너에게 알려 주며(RAM의 50~75%로 설정. 단지 힌트입니다) work_mem이 정렬/해시 연산당입니다. 많은 연결에서 너무 높게 잡으면 메모리가 고갈됩니다. maintenance_work_mem이 VACUUM과 CREATE INDEX를 가속합니다. SSD에는 random_page_cost=1.1을 설정하세요(기본 4.0은 느린 HDD 가정). 이 설정들이 성능을 극적으로 바꾸므로 항상 변경을 벤치마크하세요.

postgresql
# key memory settings (in postgresql.conf)
SHOW shared_buffers;        -- default 128MB (set to 25% of RAM)
SHOW effective_cache_size;  -- default 4GB (set to 50-75% of RAM)
SHOW work_mem;              -- default 4MB (per-sort/hash memory)
SHOW maintenance_work_mem;  -- default 64MB (for VACUUM, CREATE INDEX)

# recommended production settings (example for 16GB RAM):
#   shared_buffers = 4GB
#   effective_cache_size = 12GB
#   work_mem = 64MB          (careful: per-operation, not global)
#   maintenance_work_mem = 512MB
#   max_connections = 100
#   random_page_cost = 1.1   (for SSDs; default 4.0 is for HDDs)

# check current connections vs max
SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

# WAL settings for write performance
SHOW wal_buffers;        -- default -1 (auto-tuned to 1/32 shared_buffers)
SHOW checkpoint_timeout; -- default 5min
SHOW max_wal_size;       -- default 1GB

# parallel query
SHOW max_parallel_workers;        -- default 8
SHOW max_parallel_workers_per_gather; -- default 2
SET max_parallel_workers_per_gather = 4;  -- more parallelism

# reload config
SELECT pg_reload_conf();

쿼리 최적화

가장 큰 이득: FK 컬럼 인덱싱, 대량 로드에 COPY, 페이지네이션의 OFFSET 회피(keyset 사용), 인덱스 컬럼을 함수로 감싸지 않기. 서브쿼리에서 IN보다 EXISTS가 종종 빠릅니다. SELECT *은 인덱스 전용 스캔을 막고 I/O를 낭비합니다. 배치 삽입(다중 행 또는 COPY)이 개별 삽입보다 10~100배 빠릅니다. 대량 로드 후 플래너가 정확한 통계를 갖도록 ANALYZE를 실행하세요. 개선 검증을 위해 변경 전후로 항상 EXPLAIN (ANALYZE)하세요.

postgresql
# 1. use indexes (check with EXPLAIN)
# bad:  SELECT * FROM users WHERE lower(email) = '[email protected]'
# good: SELECT * FROM users WHERE email = '[email protected]'
#   (or use an expression index on lower(email))

# 2. avoid SELECT * (reduces I/O, enables index-only scans)
SELECT id, username FROM users WHERE active = true;

# 3. use EXISTS instead of IN for subqueries
-- BAD:  SELECT * FROM users WHERE id IN (SELECT user_id FROM posts)
-- GOOD: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = u.id)

# 4. batch inserts (much faster than row-by-row)
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');  -- one statement
-- or use COPY for bulk loads:
COPY logs FROM '/path/to/file.csv' WITH (FORMAT csv);

# 5. use CTEs or subqueries for readability, but check performance
# (PG12+ inlines CTEs; before that, CTEs were optimization fences)

# 6. avoid OFFSET for pagination (slow for large offsets)
-- BAD:  SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 10
-- GOOD: SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 10

# 7. use partial indexes for common filtered queries
CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

# 8. analyze after bulk loads
ANALYZE products;  -- update statistics for the planner

쿼리 모니터링

pg_stat_statements가 가장 가치 있는 모니터링 도구입니다. 쿼리 개수, 총/평균 시간, 행 수를 추적합니다. shared_preload_libraries에서 활성화하세요. pg_stat_activity가 실시간 쿼리를 보여 줍니다(긴 소요와 'idle in transaction' 확인). 비대를 위해 죽은 튜플 비율을 모니터링하세요. (idx_scan=0인) 쓰지 않는 인덱스를 찾아 드롭하면 공간을 아끼고 쓰기가 빨라집니다. 느린 쿼리 자동 로그는 log_min_duration_statement 설정입니다. 병목을 체계적으로 찾아 고치려 이 도구들을 EXPLAIN과 결합하세요.

postgresql
# find slow queries (needs pg_stat_statements extension)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
  query,
  calls,
  mean_exec_time AS avg_ms,
  total_exec_time AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

# currently running queries
SELECT
  pid,
  now() - query_start AS duration,
  state,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

# long-running transactions (block vacuum)
SELECT
  pid,
  now() - xact_start AS transaction_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# table sizes (find bloat candidates)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

# index usage (find unused indexes)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
17

파티셔닝

Range 파티셔닝

Range 파티셔닝이 시계열 데이터에 이상적입니다. PostgreSQL이 삽입을 자동으로 올바른 파티션에 라우팅하고 쿼리에서 파티션을 가지치기합니다(관련 것만 스캔). 범위 밖 데이터를 잡으려 DEFAULT 파티션을 만드세요(또는 미래 파티션 생성 작업을 설정). 각 파티션은 별도 테이블이므로 독립 관리가 가능합니다(느린 DELETE 대신 오래된 데이터는 DROP TABLE). 서브 파티셔닝(range 후 hash)이 매우 큰 데이터셋에 동작합니다. 파티션 생성 자동화는 pg_partman 확장입니다.

postgresql
# partition by date range (most common for time-series)
CREATE TABLE orders (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     integer NOT NULL,
  amount      numeric
) PARTITION BY RANGE (created_at);

# create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

# create a default partition (catches anything outside range)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

# insert (automatically routed to correct partition)
INSERT INTO orders (created_at, user_id, amount)
VALUES ('2025-01-15', 1, 100.00);  -- goes to orders_2025_01

# query (partition pruning — only scans relevant partitions)
SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

# sub-partition (e.g., range by date, then hash by user)
CREATE TABLE orders_2025_01 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01')
  PARTITION BY HASH (user_id);

List 파티셔닝

List 파티셔닝은 이산 값(지역, 카테고리, 테넌트 ID)용입니다. 각 파티션이 특정 값의 행을 담습니다. Range 파티셔닝처럼 PostgreSQL이 쿼리에서 파티션을 가지치기합니다. 멀티테넌트 앱에 훌륭합니다. 테넌트별 데이터가 격리되고 독립 백업/복원이 가능하며 다른 스토리지로 옮길 수 있습니다. 예상 못한 값에는 DEFAULT를 쓰세요. 새 값의 새 파티션은 CREATE TABLE ... FOR VALUES IN (...). (아카이빙에 유용한) 파티션 분리는 ALTER TABLE ... DETACH PARTITION입니다.

postgresql
# partition by discrete values (e.g., region, category)
CREATE TABLE users (
  id       serial,
  name     text,
  region   text NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE users_us PARTITION OF users
  FOR VALUES IN ('US', 'CA');

CREATE TABLE users_eu PARTITION OF users
  FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_apac PARTITION OF users
  FOR VALUES IN ('CN', 'JP', 'AU', 'SG');

CREATE TABLE users_other PARTITION OF users DEFAULT;

# insert routes automatically
INSERT INTO users (name, region) VALUES ('Alice', 'US');  -- users_us

# query prunes to relevant partition
SELECT * FROM users WHERE region = 'JP';  -- only scans users_apac

# multi-region apps benefit: partition by tenant
CREATE TABLE tenant_data (
  id          serial,
  tenant_id   integer NOT NULL,
  data        jsonb
) PARTITION BY LIST (tenant_id);

CREATE TABLE tenant_1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_2 PARTITION OF tenant_data FOR VALUES IN (2);

Hash 파티셔닝

Hash 파티셔닝이 파티션 키를 해싱해 행을 파티션에 균등 분배합니다. 부하 분산에 좋습니다. 파티션 키로 필터하는 쿼리(WHERE user_id = 42)는 한 파티션으로 가지치기되고, 범위 쿼리(WHERE user_id > 100)는 모든 파티션을 스캔합니다. 파티션 수는 생성 시 고정되며(modulus는 2의 거듭제곱) 나중에 늘리려면 테이블 재생성이 필요합니다. 파티션 수는 예상 데이터 크기 기준으로 고르세요. 균등 분포와 파티션 키 기준 항상 조회가 필요하면 hash 파티셔닝을 쓰세요.

postgresql
# partition by hash (distribute evenly)
CREATE TABLE events (
  id          bigserial,
  user_id     integer NOT NULL,
  event_type  text,
  data        jsonb
) PARTITION BY HASH (user_id);

# create 4 partitions (modulus 4, remainder 0-3)
CREATE TABLE events_0 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

# insert distributes by hash of user_id
INSERT INTO events (user_id, event_type) VALUES (42, 'login');  -- routes to a partition

# query by user_id prunes to one partition
SELECT * FROM events WHERE user_id = 42;

# note: range queries on user_id scan ALL partitions
SELECT * FROM events WHERE user_id > 100;  -- scans all 4

# adding partitions later requires recreating (modulus must be power of 2)
# plan partition count upfront!

파티션 유지보수

DETACH PARTITION이 파티션을 독립 테이블로 바꿔 부모를 잠그지 않고 오래된 데이터를 아카이브하기 완벽합니다. ATTACH PARTITION이 기존 테이블을 새 파티션으로 추가합니다(스키마와 제약 일치 필요). 계층화 스토리지에는 (오래된 파티션을 더 싼 디스크로 옮기는) 테이블스페이스를 쓰세요. pg_partman이 파티션 생성과 유지보수를 자동화하며 시계열에 필수입니다. 파티션 가지치기 메커니즘은 constraint exclusion입니다(기본 'partition'). 미래 파티션 부족이 INSERT 실패를 낳으므로 항상 파티션 생성을 미리 계획하세요.

postgresql
# detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_01;
-- now orders_2024_01 is a standalone table

# detach and archive
ALTER TABLE orders DETACH PARTITION orders_2024_01;
ALTER TABLE orders_2024_01 RENAME TO orders_archive_2024_01;
-- move to cheaper storage, export, etc.

# attach an existing table as a partition
CREATE TABLE orders_2025_03 (LIKE orders);
ALTER TABLE orders ATTACH PARTITION orders_2025_03
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

# move partition to different tablespace (tiered storage)
CREATE TABLESPACE cold_storage LOCATION '/mnt/cold';
ALTER TABLE orders_2024_01 SET TABLESPACE cold_storage;

# create future partitions automatically (pg_partman extension)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');
-- pg_partman auto-creates future partitions

# constraint exclusion (older partitioning, avoid for new work)
SET constraint_exclusion = on;  -- default 'partition' is usually fine

# check partition info
SELECT
  inhrelid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class c ON inhrelid = c.oid
WHERE inhparent = 'orders'::regclass;

파티셔닝 전략

테이블이 커지거나(>100GB) 빠른 데이터 수명 주기(DELETE 대신 오래된 파티션 드롭)가 필요할 때 파티셔닝하세요. 가장 흔한 쿼리 패턴 기준으로 파티션 키를 고르세요. 파티션 가지치기는 파티션 키로 필터할 때만 동작합니다. 파티션된 테이블의 유니크 제약은 '파티션 키를 포함해야' 합니다(PG가 강제). 파티션된 테이블의 인덱스는 모든 파티션에 자동 적용됩니다. 너무 많은 파티션은 피하세요(계획 오버헤드). 시계열에는 월간 파티션이 흔한 스위트 스팟입니다. 자동화는 pg_partman을 쓰세요.

postgresql
# when to partition:
#   - table > 100GB (hard to vacuum, slow queries)
#   - time-series data (drop old data fast)
#   - multi-tenant (isolate tenants)
#   - parallel maintenance (vacuum per partition)

# choosing partition key:
#   - most common filter in queries (enables pruning)
#   - time for time-series (created_at, ordered_at)
#   - tenant_id for multi-tenant

# choosing partition type:
#   - RANGE: time-series, ordered data
#   - LIST: discrete categories (region, tenant)
#   - HASH: even distribution, always query by key

# partition count:
#   - too few:  doesn't help (large partitions)
#   - too many: planning overhead (1000+ partitions slow planning)
#   - sweet spot: 50-500 partitions

# indexes on partitioned tables
CREATE INDEX idx_orders_user ON orders(user_id);  -- creates index on all partitions
CREATE INDEX idx_orders_created ON orders(created_at);

# unique constraint must include partition key
CREATE TABLE orders (...) PARTITION BY RANGE (created_at);
ALTER TABLE orders ADD CONSTRAINT unique_order
  UNIQUE (id, created_at);  -- must include created_at!

# foreign keys TO partitioned tables (PG12+)
ALTER TABLE order_items ADD FOREIGN KEY (order_id, order_date)
  REFERENCES orders(id, created_at);
18

고급 기능

전문 검색

PostgreSQL에는 내장 전문 검색이 있습니다. tsvector(전처리 텍스트), tsquery(검색어), @@ 연산자. GIN 인덱스가 검색을 빠르게 합니다. to_tsvector가 단어를 토큰화·어간 처리하며(영어, 중국어 등. 언어 설정) ts_rank가 결과에 점수를 매기고 ts_headline이 매칭을 하이라이트합니다. tsvector_update_trigger가 검색 컬럼을 자동 유지합니다. 단순 검색에는 Elasticsearch에 필적합니다. 외부 서비스 불필요. 규모 있는 복잡 검색에는 Elasticsearch/OpenSearch를 고려하되 PostgreSQL의 FTS부터 시작하세요.

postgresql
# create a search column (tsvector)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
  to_tsvector('english', title || ' ' || body);

# index the search vector (GIN)
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

# search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgres & index');

# match any word (OR)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres | mysql');

# phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('full text search');

# rank results
SELECT
  title,
  ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query
ORDER BY rank DESC;

# highlight matches
SELECT
  ts_headline('english', body, query) AS highlighted
FROM articles, to_tsquery('postgres') query
WHERE search_vector @@ query;

# auto-update search_vector with a trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);

JSONB 고급 쿼리

JSONB 경로 표현식(PG12+)이 (SQL/JSON 경로 언어인) $로 강력한 조회를 합니다. 필터, 술어, 추출이 하나의 표현식에. 관계형 데이터에서 JSON 구성은 jsonb_agg와 jsonb_build_object이며 API 응답에 좋습니다. jsonb_path_ops GIN 인덱스가 기본 GIN보다 작고 빠르지만 @>와 경로 연산자(@?, @@)만 지원합니다. 매칭 배열 요소 추출은 jsonb_path_query, 행에서 JSON 배열 구축은 jsonb_agg입니다. ORM 없이 SQL에서 바로 API 응답을 생성하기 완벽합니다.

postgresql
# JSONB path queries
SELECT data#>>'{address,city}' FROM users;  -- text at path

# JSON Path (PG12+) — powerful JSON querying
SELECT * FROM products
WHERE data @? '$.tags[*] ? (@ == "sale")';

# find products with price > 100 in JSON
SELECT * FROM products
WHERE data @? '$.price ? (@ > 100)';

# extract values with JSON Path
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;

# JSONB aggregation (build JSON from rows)
SELECT
  jsonb_agg(jsonb_build_object('id', id, 'name', name)) AS products
FROM products
WHERE category = 'electronics';

# group by JSON field
SELECT
  data->>'category' AS category,
  count(*) AS count
FROM products
GROUP BY data->>'category'
ORDER BY count DESC;

# JSONB containment and existence
SELECT * FROM products WHERE data @> '{"color": "red"}';
SELECT * FROM products WHERE data ? 'discount';
SELECT * FROM products WHERE data ?| ARRAY['discount', 'sale'];
SELECT * FROM products WHERE data ?& ARRAY['discount', 'sale'];

# JSONB with GIN index for fast queries
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);
# jsonb_path_ops: smaller index, supports @> and path operators

PostGIS 지리 공간

PostGIS가 PostgreSQL을 완전한 공간 데이터베이스로 만들며 CREATE EXTENSION으로 추가합니다. GPS 좌표(lat/lng)에는 geography, 투영 좌표에는 geometry를 쓰세요. 거리 내 점 찾기는 ST_DWithin, 정밀 측정은 ST_Distance입니다. <-> 연산자가 GiST 인덱스를 효율적으로 쓰는 KNN(최근접 이웃) 쿼리를 가능하게 합니다. geometry/geography 컬럼에는 항상 GiST 인덱스를 만드세요. ST_MakePoint는 (lng, lat)을 받습니다. 경도가 먼저! 진지한 지리 워크로드에는 PostGIS가 전용 GIS 시스템에 필적합니다.

postgresql
-- enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- create a table with geography (lat/lng)
CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location geography(POINT, 4326)
);

-- insert a point (lng, lat — note the order!)
INSERT INTO places (name, location)
VALUES ('Eiffel Tower', ST_MakePoint(2.2945, 48.8584)::geography);

-- find places within 5 km
SELECT name, ST_Distance(location, ST_MakePoint(2.3522, 48.8566)::geography) AS meters
FROM places
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

-- bounding box query (uses GiST index)
SELECT name FROM places
WHERE location && ST_MakeEnvelope(2.2, 48.8, 2.4, 48.9, 4326);

-- find the nearest 10 places (KNN query)
SELECT name, location <-> ST_MakePoint(2.35, 48.85)::geography AS dist
FROM places
ORDER BY dist
LIMIT 10;

-- compute the area of a polygon (in square meters)
SELECT ST_Area(ST_GeomFromText(
  'POLYGON((2.3 48.8, 2.4 48.8, 2.4 48.9, 2.3 48.9, 2.3 48.8))', 4326)::geography
) AS sq_meters;

외부 데이터 래퍼 (FDW)

FDW(Foreign Data Wrappers)는 외부 데이터 소스를 로컬 테이블처럼 조회하게 합니다. 다른 PostgreSQL 서버 연결은 postgres_fdw, CSV 파일 읽기는 file_fdw입니다. MySQL, Oracle, SQLite, REST API 등의 FDW도 있습니다. 가능하면 쿼리가 (WHERE, JOIN을) 원격 서버로 푸시되지만 크로스 서버 조인은 데이터를 로컬로 가져옵니다. FDW는 ETL 없이 데이터 통합, 마이그레이션, 연합 쿼리에 훌륭합니다. 네트워크 오버헤드로 로컬 테이블보다 느립니다.

postgresql
-- enable the postgres_fdw extension (foreign PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- create a foreign server
CREATE SERVER foreign_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.host', dbname 'remotedb', port '5432');

-- map a local user to the foreign server
CREATE USER MAPPING FOR local_user
  SERVER foreign_db
  OPTIONS (user 'remote_user', password 'secret');

-- import a schema from the foreign server
IMPORT FOREIGN SCHEMA public
  FROM SERVER foreign_db
  INTO remote_schema;

-- query a foreign table like a local one
SELECT * FROM remote_schema.users WHERE active = true;

-- file_fdw: read CSV files as tables
CREATE EXTENSION file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE logs (
  id int, level text, message text, ts timestamp
) SERVER csv_server
  OPTIONS (filename '/var/log/app.csv', format 'csv', header 'true');

-- join local and remote data
SELECT l.id, u.name FROM local_orders l
  JOIN remote_schema.users u ON l.user_id = u.id;

어드바이저리 락과 동시성

어드바이저리 락은 테이블 행에 묶이지 않은 앱 정의 락으로 조율에 완벽합니다. 세션 락은 명시적 해제나 세션 종료까지 유지되고, 트랜잭션 락은 COMMIT/ROLLBACK에서 자동 해제됩니다. (블로킹하지 않는) pg_try_advisory_lock이 '워커 하나만' 패턴에 좋습니다. 안전한 작업 큐 처리를 위해 FOR UPDATE SKIP LOCKED와 결합하세요. 락 식별은 bigint 또는 정수 둘입니다. 오류 경로에서는 항상 락과 언락을 짝지으세요. 어드바이저리 락이 PostgreSQL 안전 동시 백그라운드 작업 시스템의 등뼈입니다.

postgresql
-- acquire a session-level advisory lock (held until session ends)
SELECT pg_advisory_lock(12345);

-- release it
SELECT pg_advisory_unlock(12345);

-- transaction-level lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- critical section here
COMMIT;  -- lock auto-released

-- try-lock (non-blocking, returns true/false)
SELECT pg_try_advisory_lock(12345);  -- true if acquired, false if held

-- two-key variant (namespace + id)
SELECT pg_advisory_lock(100, 42);

-- use case: ensure only one worker runs a job
SELECT CASE
  WHEN pg_try_advisory_lock(999) THEN run_job()
  ELSE 'skipped: another worker is running'
END;

-- use case: per-row processing lock
SELECT id, pg_advisory_lock(hashtext(id::text))
FROM tasks WHERE status = 'pending'
LIMIT 10 FOR UPDATE SKIP LOCKED;
19

확장

확장 관리

확장이 코어 PostgreSQL 넘어의 기능을 더하며 CREATE EXTENSION으로 설치합니다. 디스크에 설치된 것은 pg_available_extensions, 로드된 것은 pg_extension입니다. 확장은 데이터베이스별로 설치해야 합니다. 일부(pg_stat_statements 같은)는 shared_preload_libraries에 설정 항목도 필요합니다. CASCADE가 의존 객체를 드롭합니다. 재현성을 위해 프로덕션에서는 항상 확장 버전을 고정하세요. 확장이 PostgreSQL의 핵심 강점이며 이를 플랫폼(데이터베이스 그 이상)으로 만듭니다.

postgresql
-- list all available extensions
SELECT * FROM pg_available_extensions ORDER BY name;

-- list installed extensions
SELECT * FROM pg_extension;

-- install an extension (requires superuser or privileges)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- install in a specific schema
CREATE EXTENSION postgis SCHEMA geo;

-- update an extension to a new version
ALTER EXTENSION pg_stat_statements UPDATE TO '1.10';

-- remove an extension
DROP EXTENSION IF EXISTS postgis CASCADE;

-- check extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

pg_stat_statements (쿼리 프로파일링)

pg_stat_statements가 가장 가치 있는 PostgreSQL 확장입니다. 모든 쿼리의 실행 통계를 기록합니다. 느린 쿼리 찾기는 total_exec_time(영향)이나 mean_exec_time(쿼리당 비용)입니다. 높은 rows/call은 누락 인덱스를 암시합니다. 확장이 쿼리를 정규화하므로(리터럴을 $1로 교체) 유사 쿼리가 집계됩니다. 새 성능 추적을 위해 배포 후 통계를 리셋하세요. 최적화할 때 가장 먼저 찾는 도구로, 시간이 정확히 어디로 가는지 보여 줍니다.

postgresql
-- enable in postgresql.conf first:
-- shared_preload_libraries = 'pg_stat_statements'
-- then restart PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- find the slowest queries by total time
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- find queries with highest average time
SELECT query, calls, round(mean_exec_time, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- find queries that scan the most rows
SELECT query, rows, calls, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 10;

-- reset stats (e.g., after a deploy)
SELECT pg_stat_statements_reset();

pgcrypto (암호화와 해싱)

pgcrypto가 해싱(digest, HMAC), UUID 생성(gen_random_uuid), 암호화(PGP 대칭/비대칭)를 제공합니다. 랜덤 UUID v4 표준 방법은 gen_random_uuid()이며(PG13부터 내장, 이전 버전에는 pgcrypto가 제공) 비밀번호 저장에는 앱 코드의 bcrypt나 argon2를 선호하세요. pgcrypto의 해싱은 데이터 무결성용이지 비밀번호 저장용이 아닙니다. 저장 중 컬럼 암호화에는 PGP 암호화가 적합합니다. 프로덕션에서는 항상 암호화 키를 데이터베이스 밖에서 관리하세요.

postgresql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- hash a password with SHA-256
SELECT encode(digest('mypassword', 'sha256'), 'hex');

-- generate a UUID v4
SELECT gen_random_uuid();

-- HMAC (keyed hashing)
SELECT encode(hmac('message', 'secretkey', 'sha256'), 'hex');

-- encrypt data with PGP symmetric encryption
SELECT armor(pgp_sym_encrypt('sensitive data', 'my_passphrase'))
  AS encrypted_blob;

-- decrypt data
SELECT pgp_sym_decrypt(
  dearmor('-----BEGIN PGP MESSAGE-----...-----END PGP MESSAGE-----'),
  'my_passphrase'
) AS decrypted;

-- store encrypted columns
CREATE TABLE secrets (
  id serial PRIMARY KEY,
  data bytea  -- store pgp_sym_encrypt() output here
);

INSERT INTO secrets (data)
VALUES (pgp_sym_encrypt('credit card number', 'passphrase'));

UUID와 ID 생성

랜덤 UUID에는 gen_random_uuid()(PG13+)를 쓰세요. 내장이며 확장이 필요 없습니다. 필요하면 uuid-ossp가 v1(시간 기반)과 v5(네임스페이스) UUID를 제공합니다. 자동 증가 정수에는 serial보다 GENERATED ALWAYS AS IDENTITY(PG10+)를 선호하세요. SQL 표준이고 실수로 인한 수동 삽입을 막으며 권한을 깔끔히 처리합니다. 한 번의 왕복으로 삽입된 ID를 얻는 것은 RETURNING id입니다. UUID가 분산 시스템에 좋고, 단일 노드 앱에는 identity 컬럼이 더 단순합니다.

postgresql
-- built-in UUID v4 (PostgreSQL 13+)
SELECT gen_random_uuid();

-- using uuid-ossp extension (for other UUID versions)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();  -- time-based
SELECT uuid_generate_v4();  -- random
SELECT uuid_generate_v5('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'name'); -- namespace+name

-- UUID primary key
CREATE TABLE events (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  data jsonb,
  created_at timestamptz DEFAULT now()
);

-- identity columns (PostgreSQL 10+, preferred over serial)
CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

-- compare: serial vs identity
-- serial: old, separate sequence, can be overridden
-- identity: SQL standard, tied to table, cleaner

-- get the last inserted ID
INSERT INTO users (name) VALUES ('Alice') RETURNING id;

유용한 확장 카탈로그

PostgreSQL의 확장 생태계는 방대합니다. 퍼지 텍스트 매칭(% 연산자)과 빠른 LIKE 쿼리는 pg_trgm, 지리 공간 능력은 PostGIS, 시계열 파티션 생성 자동화는 pg_partman입니다. TimescaleDB와 Citus는 각각 시계열과 수평 확장의 서드파티 확장입니다. hstore는 레거시이므로 jsonb를 쓰세요. 국제화 검색을 위해 분음 부호 제거는 unaccent입니다. 사용 가능 확장은 pg_available_extensions로 탐색하고 커뮤니티 확장은 PGXN(PostgreSQL Extension Network)을 확인하세요.

postgresql
-- PostGIS: geospatial data
CREATE EXTENSION postgis;

-- pg_trgm: trigram fuzzy text search
CREATE EXTENSION pg_trgm;
SELECT * FROM users WHERE name % 'Jon';  -- fuzzy match
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

-- btree_gin / btree_gist: B-tree support in GIN/GiST
CREATE EXTENSION btree_gin;

-- hstore: key-value store (legacy, use jsonb instead)
CREATE EXTENSION hstore;

-- pg_partman: partition management
CREATE EXTENSION pg_partman;

-- timescaledb: time-series optimization (third-party)
-- citus: distributed PostgreSQL (third-party)

-- intarray: integer array operations
CREATE EXTENSION intarray;
SELECT * FROM posts WHERE tags && ARRAY[1,2,3];

-- unaccent: remove accents for search
CREATE EXTENSION unaccent;
SELECT unaccent('café');  -- 'cafe'
20

모니터링과 문제 해결

활성 쿼리 (pg_stat_activity)

pg_stat_activity가 PostgreSQL이 하고 있는 일의 실시간 뷰입니다. state 확인(active, idle, idle in transaction). 장기 쿼리와 idle-in-transaction 세션이 흔한 문제이며, 후자가 락을 쥐고 vacuum을 막습니다. pg_cancel_backend는 SIGINT를 보내고(쿼리 취소, 세션 생존) pg_terminate_backend가 세션을 완전히 죽입니다. 죽이기 전 항상 조사하세요. 막힌 트랜잭션 자동 종료는 idle_in_transaction_session_timeout 설정입니다. max_connections 도달을 피하려 연결 수를 모니터링하세요.

postgresql
-- see all active queries
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  query_start,
  now() - query_start AS duration,
  query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- find long-running queries (> 5 minutes)
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes';

-- count connections by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- terminate a runaway query (use with caution!)
SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- kill the session

-- find idle-in-transaction sessions (hold locks)
SELECT pid, usename, xact_start,
  now() - xact_start AS xact_duration,
  query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

락과 블로킹

락 경쟁이 흔한 프로덕션 문제입니다. pg_locks가 모든 활성 락을 보여 주며 미부여 락은 세션이 기다리는 중임을 뜻합니다. 블로킹 체인 쿼리가 정확히 누가 누구를 막는지 식별하며 교착과 느린 쿼리 디버깅에 필수입니다. (ALTER TABLE, DROP, VACUUM FULL의) AccessExclusive는 모든 것을 막으므로 유지보수 창에서 실행하세요. 세션이 영원히 기다리지 않게 lock_timeout을 설정하세요. 보통 범인은 긴 트랜잭션입니다. 트랜잭션을 짧게 하고 즉시 커밋하세요.

postgresql
-- see all locks
SELECT
  pid,
  relation::regclass AS table_name,
  mode,
  granted,
  query
FROM pg_locks
WHERE granted = false;

-- find blocking chains (who blocks whom)
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
  AND ul.relation IS NOT DISTINCT FROM bl.relation
  AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- lock modes: AccessShare, RowShare, RowExclusive,
--   ShareUpdateExclusive, Share, ShareRowExclusive,
--   Exclusive, AccessExclusive

-- check for table-level locks
SELECT relation::regclass, mode, pid, granted
FROM pg_locks
WHERE locktype = 'relation' AND granted = false;

-- set a lock timeout for a session
SET lock_timeout = '5s';
SET statement_timeout = '30s';

Vacuum과 비대

PostgreSQL은 MVCC를 쓰며 UPDATE/DELETE가 VACUUM이 회수하는 죽은 튜플을 만듭니다. Autovacuum이 자동 실행되지만 크거나 쓰기 많은 테이블에는 튜닝이 필요할 수 있습니다. 대기 정리는 n_dead_tup이 보여 주며 높은 dead_pct는 autovacuum이 뒤처졌다는 뜻입니다. VACUUM FULL이 디스크 공간을 회수하지만 AccessExclusive 락이 필요하므로 온라인 비대 제거에는 pg_repack을 쓰세요. 장기 트랜잭션이 (오래된 행을 볼 수 있으므로) vacuum을 막아 비대와 트랜잭션 ID wraparound 위험을 낳습니다. wraparound 방지는 (약 20억에서 강제 실패) xid_age 모니터링입니다.

postgresql
-- check table bloat (dead tuples)
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_manual_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- manual vacuum (analyze updates planner stats)
VACUUM ANALYZE users;
VACUUM FULL users;  -- reclaims space, locks table!

-- check autovacuum settings
SHOW autovacuum;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;

-- tune autovacuum per table
ALTER TABLE big_table SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

-- check if any transactions prevent vacuum
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY xact_age DESC;

-- check wraparound risk (transaction ID age)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r', 't', 'm')
ORDER BY xid_age DESC LIMIT 10;

로그 분석

로깅은 postgresql.conf에서 설정합니다. 느린 쿼리 로그는 log_min_duration_statement이며(모두는 0, >100ms는 100) 락 경쟁 포착은 log_lock_waits입니다. CSV 로깅은 (file_fdw로) 로그를 외부 테이블로 조회하게 합니다. 흔한 접두사: %t(타임스탬프), %p(PID), %u(사용자), %d(데이터베이스). 프로덕션에서는 (로그를 파싱·시각화하는) pgBadger 같은 로그 수집기를 쓰세요. 프로덕션에서 모든 쿼리를 로그하지 마세요(I/O 오버헤드). 집계 분석에는 대신 pg_stat_statements를 쓰세요.

postgresql
-- in postgresql.conf, enable slow query logging:
-- log_min_duration_statement = 100  -- log queries > 100ms
-- log_line_prefix = '%t [%p] %u@%d '
-- log_lock_waits = on
-- log_temp_files = 0
-- log_autovacuum_min_duration = 0

-- check current log settings
SHOW log_min_duration_statement;
SHOW log_destination;

-- view server logs (if using CSV logging)
CREATE FOREIGN TABLE pg_log (
  log_time timestamp,
  user_name text,
  database_name text,
  process_id int,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos int,
  context text,
  query text,
  query_pos int,
  location text,
  file_name text,
  file_line_num int,
  application_name text
) SERVER pglog
  OPTIONS (filename '/var/log/postgresql/postgres.csv', format 'csv');

-- query logs with SQL
SELECT log_time, error_severity, message
FROM pg_log
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC LIMIT 20;

헬스 체크와 지표

핵심 헬스 지표: 캐시 히트율(>99% 겨냥. 낮으면 shared_buffers 증가), 인덱스 사용 비율(낮으면 누락 인덱스나 나쁜 쿼리), 테이블 크기(비대 조기 감지), 연결 수. pg_stat_user_tables과 pg_statio_user_tables가 운영 데이터의 금광입니다. 대시보드는 Prometheus + postgres_exporter로 구축하세요. 주시 항목: 늘어나는 죽은 튜플, 떨어지는 캐시 히트율, 늘어나는 연결, 예상보다 빠르게 자라는 테이블. 정기 헬스 체크가 새벽 비상을 예방합니다.

postgresql
-- database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- table sizes (with indexes)
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) AS hits,
  sum(heap_blks_read) AS reads,
  round(sum(heap_blks_hit)::numeric /
    NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) AS hit_ratio_pct
FROM pg_statio_user_tables;

-- index usage ratio
SELECT
  sum(idx_scan) AS index_scans,
  sum(seq_scan) AS seq_scans,
  round(sum(idx_scan)::numeric / NULLIF(sum(idx_scan) + sum(seq_scan), 0) * 100, 2) AS index_usage_pct
FROM pg_stat_user_tables;

-- connection stats
SELECT count(*) AS total,
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

-- uptime and version
SELECT version();
SELECT pg_postmaster_start_time();

Was this helpful?