PostgreSQL 17 환경 구축(3) : 핵심 오브젝트 정리 - 테이블, 인덱스, 파티션, 뷰, 시퀀스, 함수, 트리거

안녕하세요! 갓대희 입니다.

오늘은 PostgreSQL의 핵심 오브젝트인 테이블, 인덱스, 파티션, 뷰, 시퀀스, 함수, 트리거를 정리하려 한다. 데이터베이스 설계와 운영의 기본이 되는 내용이다.

 

• 대상 버전: PostgreSQL 17.x (2025년 릴리즈)
• 사전 조건: PostgreSQL 설치 완료, 기본 SQL 문법 이해
• 공식문서 링크: Data Definition | Indexes | Partitioning | Triggers

목차

1. 테이블(Table)
2. 인덱스(Index)
3. 파티션(Partition)
4. 뷰(View)
5. 시퀀스(Sequence)
6. 함수(Function)
7. 트리거(Trigger)

 

1. 테이블(Table)

테이블은 PostgreSQL에서 데이터를 저장하는 가장 기본적인 단위이다. 행(Row)과 열(Column)로 구성되며, 각 열은 특정 데이터 타입을 가진다.

 

1-1. 테이블 생성 (CREATE TABLE)

테이블 생성의 기본 문법과 주요 옵션을 살펴본다.

-- 기본 테이블 생성
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- IDENTITY 컬럼 사용 (PostgreSQL 10+, SQL 표준)
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INT REFERENCES users(id),
  total_amount NUMERIC(12,2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now()
);

SERIAL vs IDENTITY

구분	SERIAL	IDENTITY
표준	PostgreSQL 전용	SQL 표준 (PostgreSQL 10+)
수동 값 삽입	가능	GENERATED ALWAYS: 기본 불가 GENERATED BY DEFAULT: 가능
권장	레거시, 호환성	신규 프로젝트 권장

(출처: PostgreSQL 17 - CREATE TABLE)

 

1-2. 제약조건 (Constraints)

데이터 무결성을 보장하는 제약조건을 정의할 수 있다.

표 1-1. 주요 제약조건

제약조건	설명	예시
PRIMARY KEY	기본키 (UNIQUE + NOT NULL)	id INT PRIMARY KEY
UNIQUE	중복 값 불허	email VARCHAR(255) UNIQUE
NOT NULL	NULL 값 불허	name VARCHAR(100) NOT NULL
CHECK	조건 검증	age INT CHECK (age >= 0)
FOREIGN KEY	외래키 참조	user_id INT REFERENCES users(id)
DEFAULT	기본값 설정	status VARCHAR(20) DEFAULT 'active'
EXCLUDE	배타적 제약 (범위 중복 방지)	EXCLUDE USING gist (room WITH =, period WITH &&)

 

EXCLUSION 제약조건 (범위 중복 방지)

EXCLUSION 제약조건은 지정한 연산자로 비교했을 때 두 행이 동시에 참이 되는 것을 방지한다. 예약 시스템의 시간 범위 중복 방지에 특히 유용하다.

-- btree_gist 확장 설치 (범위 연산에 필요)
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 회의실 예약: 같은 방에서 시간대 중복 방지
CREATE TABLE room_reservations (
  id SERIAL PRIMARY KEY,
  room_id INT NOT NULL,
  reserved_at TSTZRANGE NOT NULL,
  reserved_by TEXT NOT NULL,
  EXCLUDE USING gist (room_id WITH =, reserved_at WITH &&)
);

-- 예약 삽입 (성공)
INSERT INTO room_reservations (room_id, reserved_at, reserved_by)
VALUES (101, '[2025-01-26 09:00, 2025-01-26 10:00)', 'Alice');

-- 중복 예약 시도 (실패: 시간대 겹침)
INSERT INTO room_reservations (room_id, reserved_at, reserved_by)
VALUES (101, '[2025-01-26 09:30, 2025-01-26 11:00)', 'Bob');
-- ERROR: conflicting key value violates exclusion constraint

EXCLUSION 제약조건 핵심

• WITH =: 값이 같을 때 (room_id가 같으면)
• WITH &&: 범위가 겹칠 때 (시간대가 겹치면)
• 두 조건이 모두 참이면 삽입/수정 거부
• GiST 인덱스가 자동 생성됨 (범위 검색에 최적화)

(출처: PostgreSQL 17 - Exclusion Constraints)

-- 복합 PRIMARY KEY
CREATE TABLE order_items (
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  price NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- 테이블 레벨 제약조건 (이름 지정)
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price NUMERIC(10,2),
  discount_price NUMERIC(10,2),
  CONSTRAINT price_positive CHECK (price > 0),
  CONSTRAINT discount_valid CHECK (discount_price IS NULL OR discount_price < price)
);

 

1-3. 테이블 종류

PostgreSQL은 용도에 따라 여러 종류의 테이블을 제공한다.

표 1-2. 테이블 종류

종류	특징	사용 사례
일반 테이블 (Heap)	기본 테이블, WAL 기록, 복구 가능	대부분의 경우
UNLOGGED	WAL 미기록, 빠른 쓰기, 크래시 시 데이터 손실	임시 데이터, 캐시, 세션
TEMPORARY	세션/트랜잭션 종료 시 자동 삭제	중간 계산 결과
FOREIGN	외부 데이터 소스 연결 (FDW)	다른 DB, 파일 시스템

-- UNLOGGED 테이블 (빠른 쓰기, 복구 불가)
CREATE UNLOGGED TABLE session_cache (
  session_id VARCHAR(64) PRIMARY KEY,
  user_id INT,
  data JSONB,
  expires_at TIMESTAMPTZ
);

-- TEMPORARY 테이블 (세션 종료 시 삭제)
CREATE TEMPORARY TABLE temp_results (
  id INT,
  value NUMERIC
) ON COMMIT DROP; -- 트랜잭션 종료 시 삭제

UNLOGGED 테이블 주의사항
UNLOGGED 테이블은 WAL을 기록하지 않아 쓰기 성능이 빠르지만, 서버 크래시 시 데이터가 손실된다. 복제 환경에서 스탠바이 서버로 복제되지 않는다. 중요 데이터에는 사용하지 않는다.
(출처: PostgreSQL 17 - UNLOGGED)

FOREIGN TABLE (외부 테이블)

FOREIGN TABLE은 Foreign Data Wrapper(FDW)를 통해 외부 데이터 소스(다른 PostgreSQL, MySQL, 파일 등)에 접근하는 테이블이다. 로컬 테이블처럼 SELECT, JOIN이 가능하다.

-- 1. postgres_fdw 확장 설치
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 2. 외부 서버 정의
CREATE SERVER remote_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '192.168.1.100', port '5432', dbname 'remote_db');

-- 3. 사용자 매핑 (인증 정보)
CREATE USER MAPPING FOR current_user
  SERVER remote_server
  OPTIONS (user 'remote_user', password 'secret');

-- 4. 외부 테이블 생성
CREATE FOREIGN TABLE remote_customers (
  id INT,
  name VARCHAR(100),
  email VARCHAR(255)
)
SERVER remote_server
OPTIONS (schema_name 'public', table_name 'customers');

-- 5. 외부 테이블 조회 (로컬 테이블처럼 사용)
SELECT * FROM remote_customers WHERE name LIKE 'A%';

-- 스키마 전체 가져오기 (편리)
IMPORT FOREIGN SCHEMA public
  FROM SERVER remote_server
  INTO local_schema;

주요 FDW 종류

• postgres_fdw: 다른 PostgreSQL 서버 연결
• mysql_fdw: MySQL/MariaDB 연결
• file_fdw: CSV, 텍스트 파일 읽기
• oracle_fdw: Oracle DB 연결

(출처: PostgreSQL 17 - postgres_fdw)

 

1-4. Generated Columns (생성 컬럼)

Generated Column은 다른 컬럼 값을 기반으로 자동 계산되는 컬럼이다. 뷰가 테이블에 대해 하는 역할을 컬럼 수준에서 수행한다.

-- Generated Column 생성
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  tax_rate NUMERIC(4,2) DEFAULT 0.10,
  price_with_tax NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

-- 삽입 시 자동 계산됨
INSERT INTO products (name, price, tax_rate) VALUES ('Laptop', 1000.00, 0.10);
SELECT * FROM products;
-- price_with_tax = 1100.00 (자동 계산)

-- 업데이트 시에도 자동 재계산
UPDATE products SET price = 1200.00 WHERE id = 1;
-- price_with_tax = 1320.00 (자동 재계산)

표 1-3. Generated Column 특징

특징	설명
STORED만 지원	PostgreSQL은 현재 STORED 타입만 지원 (물리적 저장)
IMMUTABLE 함수만	표현식에 IMMUTABLE 함수만 사용 가능 (now() 불가)
직접 수정 불가	INSERT/UPDATE에서 값 지정 불가 (DEFAULT만 허용)
다른 Generated 참조 불가	다른 Generated Column을 참조할 수 없음
파티션 키 불가	파티션 키로 사용할 수 없음

DEFAULT vs Generated Column

• DEFAULT: 삽입 시 1회만 평가, 이후 수정 가능, 다른 컬럼 참조 불가
• Generated: 행이 변경될 때마다 재계산, 수정 불가, 다른 컬럼 참조 가능

(출처: PostgreSQL 17 - Generated Columns)

 

1-5. System Columns (시스템 컬럼)

모든 PostgreSQL 테이블에는 6개의 암시적 시스템 컬럼이 존재한다. 사용자가 같은 이름의 컬럼을 만들 수 없다.

표 1-4. 시스템 컬럼

컬럼	설명	용도
tableoid	테이블의 OID	상속/파티션에서 원본 테이블 식별
ctid	행의 물리적 위치 (page, offset)	빠른 행 접근 (UPDATE/VACUUM 시 변경됨)
xmin	삽입 트랜잭션 ID	MVCC 가시성 판단
xmax	삭제 트랜잭션 ID (0=미삭제)	MVCC 가시성 판단
cmin	삽입 명령 ID	트랜잭션 내 순서 판단
cmax	삭제 명령 ID	트랜잭션 내 순서 판단

-- 시스템 컬럼 조회
SELECT ctid, xmin, xmax, tableoid::regclass, * FROM users LIMIT 5;

-- 파티션/상속에서 원본 테이블 확인
SELECT tableoid::regclass AS source_table, * FROM logs
WHERE created_at > '2025-01-01';

-- 중복 행 제거 (ctid 활용)
DELETE FROM users a
USING users b
WHERE a.ctid < b.ctid AND a.email = b.email;

ctid를 PK 대신 사용하지 말 것
ctid는 UPDATE나 VACUUM FULL 시 변경될 수 있다. 장기적인 행 식별자로는 반드시 PRIMARY KEY를 사용해야 한다.
(출처: PostgreSQL 17 - System Columns)

 

1-6. Table Inheritance (테이블 상속)

PostgreSQL은 테이블 간 상속을 지원한다. 자식 테이블은 부모의 컬럼과 제약조건을 상속받는다. 파티셔닝 이전의 레거시 기능이지만, 특정 시나리오에서 여전히 유용하다.

-- 부모 테이블
CREATE TABLE cities (
  name TEXT NOT NULL,
  population FLOAT,
  elevation INT -- 해발 고도 (feet)
);

-- 자식 테이블 (부모 컬럼 상속 + 추가 컬럼)
CREATE TABLE capitals (
  state CHAR(2) NOT NULL
) INHERITS (cities);

-- capitals는 name, population, elevation, state 컬럼을 가짐
INSERT INTO capitals (name, population, elevation, state)
VALUES ('Seoul', 9700000, 282, 'KR');

-- 부모 테이블 조회 시 자식 데이터도 포함 (기본 동작)
SELECT * FROM cities; -- cities + capitals 모두 조회

-- 부모 테이블만 조회 (ONLY 키워드)
SELECT * FROM ONLY cities; -- cities만 조회

-- 원본 테이블 식별 (tableoid 활용)
SELECT tableoid::regclass AS table_name, name, population
FROM cities;

표 1-5. 상속 vs 파티셔닝

구분	상속 (INHERITS)	파티셔닝 (PARTITION BY)
자동 라우팅	없음 (수동 INSERT)	있음 (자동 분배)
UNIQUE/PK	부모/자식 별도	전체 테이블 적용
권장 용도	계층적 데이터 모델링	대용량 테이블 분할

상속 시 주의사항

• UNIQUE, PRIMARY KEY, FOREIGN KEY는 상속되지 않음
• CHECK, NOT NULL 제약조건만 상속됨
• 부모 INSERT 시 자식으로 자동 라우팅되지 않음
• 새 프로젝트에서는 파티셔닝을 권장

(출처: PostgreSQL 17 - Inheritance)

 

1-7. 테이블 수정 (ALTER TABLE)

-- 컬럼 추가
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 컬럼 타입 변경
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

-- 컬럼 기본값 설정/제거
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- NOT NULL 추가/제거
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

-- 컬럼 이름 변경
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- 컬럼 삭제
ALTER TABLE users DROP COLUMN phone_number;

-- 제약조건 추가
ALTER TABLE users ADD CONSTRAINT email_format CHECK (email LIKE '%@%');

-- 제약조건 삭제
ALTER TABLE users DROP CONSTRAINT email_format;

-- 테이블 이름 변경
ALTER TABLE users RENAME TO members;

 

1-8. 테이블 정보 조회

-- psql: 테이블 목록
\dt
\dt+ -- 크기 포함

-- psql: 테이블 상세 (컬럼, 인덱스, 제약조건)
\d users
\d+ users -- 저장 옵션 포함

-- SQL: 테이블 크기 조회
SELECT
  relname AS table_name,
  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;

-- SQL: 행 수 추정치 (빠름)
SELECT relname, reltuples::bigint AS row_estimate
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace;

information_schema (SQL 표준)

information_schema는 SQL 표준에 정의된 메타데이터 조회 방법이다. pg_catalog보다 이식성이 좋지만, PostgreSQL 고유 기능 정보는 제공하지 않는다.

-- 테이블 목록 조회
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- 컬럼 정보 조회
SELECT
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users'
ORDER BY ordinal_position;

-- 제약조건 조회
SELECT
  constraint_name,
  constraint_type,
  table_name
FROM information_schema.table_constraints
WHERE table_schema = 'public';

-- 인덱스 정보 (pg_catalog 필요, information_schema에는 없음)
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'users';

pg_catalog vs information_schema

• pg_catalog: PostgreSQL 전용, 더 상세한 정보, OID 기반
• information_schema: SQL 표준, 이식성 좋음, 뷰 기반
• PostgreSQL 고유 기능(상속, 파티션 등)은 pg_catalog에서만 조회 가능

(출처: PostgreSQL 17 - Information Schema)

 

2. 인덱스(Index)

인덱스는 테이블 검색 속도를 높이는 자료구조이다. 적절한 인덱스 설계는 쿼리 성능에 큰 영향을 미친다.

 

2-1. 인덱스 유형

표 2-1. PostgreSQL 인덱스 유형

유형	특징	사용 사례
B-tree	기본 인덱스. =, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE 'abc%' 지원	대부분의 경우 (기본값)
Hash	등호(=) 검색만 지원. PostgreSQL 10+에서 WAL 지원	정확한 값 매칭만 필요할 때
GiST	일반화된 검색 트리. 범위, 기하, 전문검색 등	지리 데이터, 범위 타입, 전문검색
GIN	역 인덱스(Inverted Index). 배열, JSONB, 전문검색	배열 포함 검색, JSONB, tsvector
BRIN	블록 범위 인덱스. 작은 크기, 정렬된 데이터에 효과적	시계열 데이터, 로그 테이블
SP-GiST	공간 분할 GiST. 비균등 분포 데이터	전화번호, IP 주소, 기하 데이터

(출처: PostgreSQL 17 - Index Types)

 

2-2. 인덱스 생성

-- 기본 B-tree 인덱스
CREATE INDEX idx_users_email ON users(email);

-- UNIQUE 인덱스
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 복합 인덱스 (다중 컬럼)
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- 부분 인덱스 (조건부)
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

-- 표현식 인덱스
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- GIN 인덱스 (JSONB)
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

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

-- BRIN 인덱스 (시계열)
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

인덱스와 ORDER BY 최적화

B-tree 인덱스는 정렬된 순서로 저장되므로 ORDER BY 절의 정렬 비용을 제거할 수 있다. 인덱스 정의 시 정렬 방향을 지정하면 효과적이다.

-- DESC 정렬 최적화 인덱스
CREATE INDEX idx_orders_created_desc ON orders(created_at DESC);

-- 복합 컬럼 정렬 (ASC/DESC 혼합)
CREATE INDEX idx_orders_multi ON orders(user_id ASC, created_at DESC);

-- NULLS FIRST/LAST 지정
CREATE INDEX idx_products_price ON products(price DESC NULLS LAST);

-- 이 쿼리는 정렬 없이 인덱스 순서로 반환
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;

-- EXPLAIN으로 "Sort" 노드 없음 확인
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

ORDER BY 최적화 조건

• 인덱스 컬럼 순서와 ORDER BY 순서가 일치해야 함
• 정렬 방향(ASC/DESC)이 일치하거나, 모든 컬럼이 반대여야 함
• NULLS FIRST/LAST 설정도 일치해야 함
• LIMIT과 함께 사용하면 특히 효과적

(출처: PostgreSQL 17 - Indexes and ORDER BY)

Bitmap Index Scan (다중 인덱스 조합)

PostgreSQL은 여러 인덱스를 조합하여 복잡한 WHERE 조건을 처리할 수 있다. 각 인덱스 스캔 결과를 비트맵으로 변환한 후 AND/OR 연산으로 병합한다.

-- 두 개의 단일 컬럼 인덱스
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_region ON orders(region);

-- AND 조건: 두 인덱스 결과를 BitmapAnd로 병합
SELECT * FROM orders
WHERE status = 'pending' AND region = 'asia';

-- OR 조건: 두 인덱스 결과를 BitmapOr로 병합
SELECT * FROM orders
WHERE status = 'pending' OR region = 'asia';

-- EXPLAIN에서 BitmapAnd/BitmapOr 노드 확인
EXPLAIN (ANALYZE) SELECT * FROM orders
WHERE status = 'pending' AND region = 'asia';

Bitmap Scan vs 복합 인덱스

• 복합 인덱스: 특정 쿼리 패턴에 최적화, 더 빠름
• Bitmap Scan: 유연하지만 오버헤드 있음
• 자주 사용되는 조건 조합은 복합 인덱스 권장
• 다양한 조합이 필요하면 단일 인덱스 + Bitmap 활용

(출처: PostgreSQL 17 - Combining Multiple Indexes)

 

2-3. 커버링 인덱스 (Covering Index)

커버링 인덱스는 INCLUDE 절을 사용하여 검색 키가 아닌 추가 컬럼을 인덱스에 포함시킨다. 이를 통해 테이블 힙(Heap)에 접근하지 않고 인덱스만으로 쿼리를 처리하는 Index-Only Scan이 가능해진다.

-- 기본 커버링 인덱스
-- user_id로 검색하면서 email도 함께 반환해야 할 때
CREATE INDEX idx_users_covering ON users(user_id) INCLUDE (email, name);

-- 유니크 커버링 인덱스
-- user_id에만 유니크 제약, email과 name은 추가 데이터
CREATE UNIQUE INDEX idx_users_unique_covering ON users(user_id) INCLUDE (email, name);

-- 실제 활용 예시: 주문 조회
CREATE INDEX idx_orders_covering ON orders(user_id, created_at DESC)
  INCLUDE (order_status, total_amount);

-- 위 인덱스로 Index-Only Scan 가능한 쿼리
SELECT order_status, total_amount
FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 10;

표 2-2. INCLUDE 컬럼 vs 일반 인덱스 컬럼

특성	인덱스 키 컬럼	INCLUDE 컬럼
검색/정렬에 사용	O	X (검색 불가)
B-tree 내부 노드에 저장	O	X (리프 노드만)
UNIQUE 제약에 포함	O	X
인덱스 크기 영향	크게 증가	리프만 증가 (상대적으로 작음)
지원 인덱스 타입	B-tree, GiST, SP-GiST, GIN, BRIN	B-tree, GiST, SP-GiST만

Index-Only Scan과 Visibility Map

Index-Only Scan이 실제로 작동하려면 Visibility Map이 중요하다. PostgreSQL의 MVCC 특성상 인덱스 엔트리에는 트랜잭션 가시성 정보가 없다. Visibility Map은 각 힙 페이지의 모든 튜플이 모든 트랜잭션에서 보이는지를 추적한다.

-- Index-Only Scan 사용 여부 확인
EXPLAIN (ANALYZE, BUFFERS) SELECT email, name
FROM users WHERE user_id = 100;

-- 결과에서 "Index Only Scan" 확인
-- "Heap Fetches: 0"이면 완전한 Index-Only Scan

-- Visibility Map 상태 확인 (pg_visibility 확장 필요)
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility('users');

Index-Only Scan 효과를 높이려면

• VACUUM을 정기적으로 실행하여 Visibility Map을 업데이트
• 테이블이 자주 갱신되면 Index-Only Scan 효과가 떨어짐
• Heap Fetches 수가 높으면 VACUUM 필요
• 읽기 중심 테이블에서 가장 효과적

(출처: PostgreSQL 17 - Index-Only Scans)

 

2-4. CREATE INDEX CONCURRENTLY (CIC)

기본 CREATE INDEX는 테이블에 쓰기 락을 건다. 운영 중인 테이블에서는 CONCURRENTLY 옵션을 사용하여 락 없이 인덱스를 생성할 수 있다.

-- 락 없이 인덱스 생성 (운영 환경 권장)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- 실패 시 INVALID 상태로 남음 - 확인 방법
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- INVALID 인덱스 삭제 후 재생성
DROP INDEX CONCURRENTLY idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

CONCURRENTLY 주의사항

• 일반 인덱스 생성보다 시간이 더 오래 걸림 (테이블 2회 스캔)
• 트랜잭션 블록 내에서 사용 불가
• 실패 시 INVALID 인덱스가 남을 수 있음 (수동 삭제 필요)
• 유니크 인덱스 생성 시 중복 발생하면 실패

(출처: PostgreSQL 17 - CREATE INDEX CONCURRENTLY)

 

2-5. 인덱스 리빌드 (REINDEX)

인덱스가 손상되었거나 블로트(Bloat)가 심할 때 리빌드한다.

-- 특정 인덱스 리빌드
REINDEX INDEX idx_users_email;

-- 테이블의 모든 인덱스 리빌드
REINDEX TABLE users;

-- 스키마의 모든 인덱스 리빌드
REINDEX SCHEMA public;

-- 데이터베이스의 모든 인덱스 리빌드
REINDEX DATABASE myapp;

-- 락 없이 리빌드 (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;
REINDEX TABLE CONCURRENTLY users;

2-6. 인덱스 정보 조회

-- psql: 테이블의 인덱스 목록
\di users*

-- SQL: 인덱스 상세 정보
SELECT
  indexrelid::regclass AS index_name,
  indrelid::regclass AS table_name,
  indisunique AS is_unique,
  indisvalid AS is_valid,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
WHERE indrelid = 'users'::regclass;

-- SQL: 인덱스 사용 통계
SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan AS scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

사용되지 않는 인덱스 찾기
idx_scan = 0인 인덱스는 사용되지 않는 것이다. 단, 통계가 리셋된 후 충분한 시간이 지났는지 확인해야 한다. 불필요한 인덱스는 쓰기 성능을 저하시키고 디스크 공간을 낭비한다.

 

3. 파티션(Partition)

파티셔닝은 대용량 테이블을 물리적으로 여러 조각으로 나누는 기법이다. 쿼리 성능 향상, 유지보수 용이성, 데이터 생명주기 관리에 효과적이다.

 

3-1. 파티션 유형

표 3-1. 파티션 유형

유형	분할 기준	사용 사례
RANGE	연속적인 값 범위로 분할	날짜별 (월별, 연도별), 숫자 범위
LIST	특정 값 목록으로 분할	지역별, 카테고리별, 상태별
HASH	해시 값으로 균등 분할	테넌트 ID, 균등 분산 필요 시

(출처: PostgreSQL 17 - Table Partitioning)

 

3-2. RANGE 파티션

시계열 데이터에 가장 많이 사용되는 파티션 방식이다.

-- 1) 파티션 부모 테이블 생성
CREATE TABLE logs (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  message TEXT,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at) -- 파티션 키 포함 필수
) PARTITION BY RANGE (created_at);

-- 2) 월별 파티션 생성
CREATE TABLE logs_2025_01 PARTITION OF logs
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

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

CREATE TABLE logs_2025_03 PARTITION OF logs
  FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- 3) 기본 파티션 (범위 밖 데이터 수용)
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

파티션 키와 PRIMARY KEY
파티션 테이블에서 PRIMARY KEY나 UNIQUE 제약조건은 반드시 파티션 키를 포함해야 한다. 이는 PostgreSQL이 단일 파티션 내에서만 유일성을 보장할 수 있기 때문이다.

 

3-3. LIST 파티션

-- 지역별 LIST 파티션
CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  name VARCHAR(100) NOT NULL,
  region VARCHAR(20) NOT NULL,
  PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

-- 지역별 파티션 생성
CREATE TABLE customers_asia PARTITION OF customers
  FOR VALUES IN ('KR', 'JP', 'CN', 'TW');

CREATE TABLE customers_europe PARTITION OF customers
  FOR VALUES IN ('DE', 'FR', 'UK', 'IT');

CREATE TABLE customers_americas PARTITION OF customers
  FOR VALUES IN ('US', 'CA', 'MX', 'BR');

CREATE TABLE customers_others PARTITION OF customers DEFAULT;

3-4. HASH 파티션

-- 테넌트 ID로 HASH 파티션 (4개로 균등 분할)
CREATE TABLE tenant_data (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id INT NOT NULL,
  data JSONB,
  PRIMARY KEY (id, tenant_id)
) PARTITION BY HASH (tenant_id);

-- HASH 파티션 생성 (MODULUS: 총 개수, REMAINDER: 0부터 시작)
CREATE TABLE tenant_data_0 PARTITION OF tenant_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE tenant_data_1 PARTITION OF tenant_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE tenant_data_2 PARTITION OF tenant_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE tenant_data_3 PARTITION OF tenant_data
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

 

3-5. 파티션 관리

-- 파티션 추가
CREATE TABLE logs_2025_04 PARTITION OF logs
  FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');

-- 파티션 분리 (데이터 보존, 락 발생)
ALTER TABLE logs DETACH PARTITION logs_2024_01;

-- 파티션 분리 (PostgreSQL 14+, 락 최소화)
-- CONCURRENTLY는 2단계로 분리: 짧은 락 + 백그라운드 작업
ALTER TABLE logs DETACH PARTITION logs_2024_01 CONCURRENTLY;

-- FINALIZE: CONCURRENTLY 실패 시 완료 처리
ALTER TABLE logs DETACH PARTITION logs_2024_01 FINALIZE;

-- 분리된 테이블 삭제
DROP TABLE logs_2024_01;

-- 기존 테이블을 파티션으로 연결
ALTER TABLE logs ATTACH PARTITION logs_2025_05
  FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');

-- 파티션 목록 확인
SELECT
  parent.relname AS parent_table,
  child.relname AS partition_name,
  pg_get_expr(child.relpartbound, child.oid) AS partition_expression
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'logs';

DETACH PARTITION CONCURRENTLY 주의사항

• PostgreSQL 14 이상에서 사용 가능
• 트랜잭션 블록 내에서 사용 불가
• 실패 시 FINALIZE로 완료해야 함
• 분리 중인 파티션은 여전히 쿼리에 포함됨 (완료 전까지)

(출처: PostgreSQL 17 - ALTER TABLE)

 

3-6. 서브파티셔닝 (Sub-partitioning)

파티션 테이블 자체를 다시 파티셔닝하여 다단계 파티션 구조를 만들 수 있다. 복합 파티션 키나 매우 큰 데이터셋에서 유용하다.

-- 2단계 파티셔닝: 연도(RANGE) → 지역(LIST)

-- 1. 부모 테이블 (연도별 RANGE)
CREATE TABLE sales (
  id BIGINT,
  sale_date DATE,
  region TEXT,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

-- 2. 1차 파티션 (2025년) - LIST로 서브파티셔닝
CREATE TABLE sales_2025 PARTITION OF sales
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
  PARTITION BY LIST (region);

-- 3. 2차 파티션 (2025년 + 지역별)
CREATE TABLE sales_2025_asia PARTITION OF sales_2025
  FOR VALUES IN ('asia', 'asia-pacific');
CREATE TABLE sales_2025_europe PARTITION OF sales_2025
  FOR VALUES IN ('europe', 'eu');
CREATE TABLE sales_2025_americas PARTITION OF sales_2025
  FOR VALUES IN ('north-america', 'south-america');

-- 데이터 삽입 시 자동으로 적절한 서브파티션에 저장
INSERT INTO sales VALUES (1, '2025-03-15', 'asia', 1500.00);
-- → sales_2025_asia에 저장됨

서브파티셔닝 고려사항

• 쿼리에 모든 파티션 키가 포함되어야 최적의 프루닝 효과
• 파티션 개수가 기하급수적으로 증가할 수 있음 (관리 복잡도 ↑)
• DEFAULT 파티션은 각 레벨마다 필요할 수 있음
• 3단계 이상은 복잡도 대비 효과가 떨어지는 경우가 많음

(출처: PostgreSQL 17 - Table Partitioning)

 

3-7. 파티션 인덱스

PostgreSQL 11부터 부모 테이블에 인덱스를 생성하면 모든 파티션에 자동으로 인덱스가 생성된다.

-- 부모 테이블에 인덱스 생성 → 모든 파티션에 적용
CREATE INDEX idx_logs_message ON logs(message);

-- 개별 파티션에만 인덱스 생성
CREATE INDEX idx_logs_2025_01_message ON logs_2025_01(message);

파티션 프루닝 (Partition Pruning)
쿼리에 파티션 키 조건이 포함되면 PostgreSQL이 자동으로 필요한 파티션만 스캔한다. EXPLAIN으로 어떤 파티션이 스캔되는지 확인할 수 있다.
SET enable_partition_pruning = on; (기본값)

3-8. 파티션 설계 모범 사례

PostgreSQL 공식 문서에서 권장하는 파티션 설계 원칙이다.

파티션 수 가이드라인
- 수백 개 정도의 파티션은 대부분 잘 작동
- 수천 개 이상이면 쿼리 플래닝 시간이 길어질 수 있음
- 파티션이 너무 많으면 플래너가 모든 파티션 메타데이터를 확인해야 함
- enable_partition_pruning이 켜져 있어도 플래닝 오버헤드 발생

항목	권장 사항
파티션 키 선택	대부분의 쿼리에서 WHERE 조건으로 사용되는 컬럼
키 변경 빈도	값이 자주 변경되지 않는 컬럼 (UPDATE 시 파티션 이동 비용 발생)
파티션 크기	각 파티션이 메모리에 들어갈 수 있는 크기가 이상적
데이터 삭제	대량 삭제가 필요하면 DROP TABLE로 파티션 통째로 삭제
테이블 상속 vs 선언적 파티션	새 프로젝트는 선언적 파티션(PARTITION BY) 사용 권장

파티션 유지보수 자동화
시간 기반 파티션의 경우, pg_partman 확장을 사용하면 파티션 생성/삭제를 자동화할 수 있다.
CREATE EXTENSION pg_partman;

 

4. 뷰(View)

뷰는 저장된 쿼리로, 복잡한 쿼리를 단순화하고 보안 레이어를 제공한다.

4-1. 일반 뷰

-- 뷰 생성
CREATE VIEW active_users AS
SELECT id, email, name, created_at
FROM users
WHERE status = 'active';

-- 뷰 사용
SELECT * FROM active_users WHERE created_at > '2025-01-01';

-- 조인을 포함한 복잡한 뷰
CREATE VIEW order_summary AS
SELECT
  o.id AS order_id,
  u.email AS customer_email,
  o.total_amount,
  o.status,
  o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id;

-- 뷰 수정 (OR REPLACE)
CREATE OR REPLACE VIEW active_users AS
SELECT id, email, name, created_at, phone
FROM users
WHERE status = 'active' AND deleted_at IS NULL;

-- 뷰 삭제
DROP VIEW active_users;

 

4-2. Materialized View (구체화된 뷰)

Materialized View는 쿼리 결과를 물리적으로 저장하여 읽기 성능을 높인다. 데이터가 변경되면 수동으로 갱신해야 한다.

-- Materialized View 생성
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
  date_trunc('month', created_at) AS month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_sales
FROM orders
WHERE status = 'completed'
GROUP BY date_trunc('month', created_at)
ORDER BY month;

-- 인덱스 추가 (Materialized View에만 가능)
CREATE INDEX idx_monthly_sales_month ON monthly_sales(month);

-- 데이터 갱신 (전체 재계산)
REFRESH MATERIALIZED VIEW monthly_sales;

-- 동시 갱신 (UNIQUE 인덱스 필요, 읽기 락 없음)
CREATE UNIQUE INDEX idx_monthly_sales_unique ON monthly_sales(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- WITH NO DATA: 스키마만 생성, 데이터는 나중에 채움
CREATE MATERIALIZED VIEW heavy_report AS
SELECT /* 복잡한 쿼리 */ ...
WITH NO DATA;

-- 나중에 데이터 채우기
REFRESH MATERIALIZED VIEW heavy_report;

표 4-1. View vs Materialized View

구분	View	Materialized View
데이터 저장	저장 안 함 (매번 쿼리 실행)	물리적으로 저장
읽기 성능	원본 쿼리와 동일	매우 빠름 (테이블 스캔)
데이터 신선도	항상 최신	REFRESH 필요
인덱스	생성 불가	생성 가능
사용 사례	쿼리 단순화, 보안	복잡한 집계, 대시보드, 리포트

WITH NO DATA 활용
WITH NO DATA로 생성하면 스키마만 정의되고 데이터는 비어 있다. DDL 마이그레이션이나 초기 배포 시 유용하다.

• 쿼리가 오래 걸릴 때: 스키마 먼저 배포, 데이터는 비활성 시간에 채움
• 인덱스 선생성: WITH NO DATA → 인덱스 생성 → REFRESH (인덱스가 이미 있으므로 빠름)
• WITH NO DATA로 생성된 뷰는 REFRESH 전까지 조회 불가

(출처: PostgreSQL 17 - CREATE MATERIALIZED VIEW)

 

5. 시퀀스(Sequence)

시퀀스는 자동 증가 숫자를 생성하는 객체이다. SERIAL이나 IDENTITY 컬럼의 내부 구현으로 사용되지만, 독립적으로도 활용할 수 있다.

 

5-1. 시퀀스 생성 및 사용

-- 기본 시퀀스 생성
CREATE SEQUENCE order_number_seq;

-- 옵션 지정
CREATE SEQUENCE invoice_number_seq
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1000
  MAXVALUE 9999999999
  NO CYCLE
  CACHE 20; -- 성능을 위해 20개씩 캐시

-- 다음 값 가져오기
SELECT nextval('order_number_seq'); -- 1
SELECT nextval('order_number_seq'); -- 2

-- 현재 값 확인 (세션에서 nextval 호출 후에만 가능)
SELECT currval('order_number_seq'); -- 2

-- 다음 값 미리보기 (값 증가 없음)
SELECT last_value FROM order_number_seq;

-- 시퀀스 값 재설정
ALTER SEQUENCE order_number_seq RESTART WITH 100;

-- 테이블에서 시퀀스 사용
INSERT INTO orders (order_number, user_id, total_amount)
VALUES (nextval('order_number_seq'), 1, 50000);

 

5-2. SERIAL/IDENTITY와 시퀀스 관계

-- SERIAL은 내부적으로 시퀀스를 생성함
CREATE TABLE test (id SERIAL PRIMARY KEY);

-- 자동 생성된 시퀀스 확인
\d test
-- id | integer | not null | nextval('test_id_seq'::regclass)

-- 시퀀스 현재 값 확인
SELECT last_value FROM test_id_seq;

-- 대량 삽입 후 시퀀스 동기화
SELECT setval('test_id_seq', (SELECT MAX(id) FROM test));

시퀀스 갭(Gap) 발생
시퀀스는 트랜잭션이 롤백되어도 값이 되돌아가지 않는다. 따라서 ID에 갭이 발생할 수 있다. 이는 정상적인 동작이며, 연속성이 필요한 경우 별도 로직을 구현해야 한다.

 

6. 함수(Function)

PostgreSQL 함수는 SQL, PL/pgSQL, Python, C 등 다양한 언어로 작성할 수 있다. 비즈니스 로직을 DB 레벨에서 처리하거나 복잡한 계산을 캡슐화할 때 유용하다.

 

6-1. SQL 함수

-- 단순 SQL 함수
CREATE FUNCTION get_user_count() RETURNS INT AS $$
  SELECT COUNT(*)::INT FROM users;
$$ LANGUAGE SQL;

-- 파라미터가 있는 함수
CREATE FUNCTION get_user_by_email(p_email VARCHAR)
RETURNS TABLE(id INT, name VARCHAR, created_at TIMESTAMPTZ) AS $$
  SELECT id, name, created_at
  FROM users
  WHERE email = p_email;
$$ LANGUAGE SQL;

-- 함수 호출
SELECT get_user_count();
SELECT * FROM get_user_by_email('test@example.com');

 

6-2. PL/pgSQL 함수

-- PL/pgSQL 함수 (제어문 사용 가능)
CREATE OR REPLACE FUNCTION calculate_discount(p_amount NUMERIC, p_membership VARCHAR)
RETURNS NUMERIC AS $$
DECLARE
  v_discount_rate NUMERIC;
BEGIN
  -- 멤버십 등급에 따른 할인율
  CASE p_membership
    WHEN 'gold' THEN v_discount_rate := 0.20;
    WHEN 'silver' THEN v_discount_rate := 0.10;
    WHEN 'bronze' THEN v_discount_rate := 0.05;
    ELSE v_discount_rate := 0;
  END CASE;

  RETURN p_amount * (1 - v_discount_rate);
END;
$$ LANGUAGE plpgsql;

-- 함수 호출
SELECT calculate_discount(10000, 'gold'); -- 8000

 

6-3. 함수 보안 속성

표 6-1. 함수 보안 옵션

옵션	설명
SECURITY INVOKER	호출자의 권한으로 실행 (기본값)
SECURITY DEFINER	함수 소유자의 권한으로 실행 (권한 상승 주의)
VOLATILE	같은 입력에 다른 결과 가능 (기본값, now() 등)
STABLE	같은 트랜잭션 내 동일 결과 (DB 조회 등)
IMMUTABLE	항상 동일 결과 (순수 계산, 인덱스 사용 가능)

-- SECURITY DEFINER: 소유자 권한으로 실행
CREATE FUNCTION get_sensitive_data(p_user_id INT)
RETURNS TEXT AS $$
  SELECT secret_column FROM sensitive_table WHERE user_id = p_user_id;
$$ LANGUAGE SQL
SECURITY DEFINER -- 함수 소유자 권한으로 실행
SET search_path = public, pg_temp; -- search_path 고정 (보안)

-- IMMUTABLE: 인덱스에서 사용 가능
CREATE FUNCTION normalize_email(email TEXT)
RETURNS TEXT AS $$
  SELECT LOWER(TRIM(email));
$$ LANGUAGE SQL IMMUTABLE;

-- 표현식 인덱스에 활용
CREATE INDEX idx_users_email_norm ON users(normalize_email(email));

SECURITY DEFINER 주의사항
SECURITY DEFINER 함수는 권한 상승 공격에 취약할 수 있다. 반드시 SET search_path를 설정하고, 입력 값 검증을 철저히 해야 한다.
(출처: PostgreSQL 17 - Function Security)

 

6-4. 함수 관리

-- 함수 목록 (psql)
\df
\df+ calculate_discount

-- 함수 정의 보기
SELECT pg_get_functiondef('calculate_discount'::regproc);

-- 함수 삭제 (시그니처 지정 필요)
DROP FUNCTION calculate_discount(NUMERIC, VARCHAR);

-- 함수 권한 부여
GRANT EXECUTE ON FUNCTION calculate_discount(NUMERIC, VARCHAR) TO app_user;

-- 함수 권한 회수
REVOKE EXECUTE ON FUNCTION calculate_discount(NUMERIC, VARCHAR) FROM PUBLIC;

기본 EXECUTE 권한
PostgreSQL에서 함수는 기본적으로 PUBLIC에 EXECUTE 권한이 부여된다. 민감한 함수는 명시적으로 권한을 제한해야 한다.
REVOKE EXECUTE ON FUNCTION sensitive_func FROM PUBLIC;

 

6-5. 저장 프로시저 (Stored Procedures)

PostgreSQL 11부터 프로시저(PROCEDURE)가 도입되었다. 함수와 달리 트랜잭션 제어(COMMIT/ROLLBACK)가 가능하며, 값을 반환하지 않는다.

표 6-2. 함수 vs 프로시저

특성	FUNCTION	PROCEDURE
값 반환	RETURNS로 반환 필수	반환값 없음 (OUT 파라미터는 가능)
트랜잭션 제어	불가 (호출자 트랜잭션 내에서 실행)	COMMIT/ROLLBACK 가능
호출 방식	SELECT func() 또는 표현식 내	CALL proc() 전용
SQL에서 사용	SELECT, WHERE 등에서 사용 가능	CALL 문으로만 호출
사용 사례	계산, 변환, 데이터 조회	배치 처리, 대량 작업, ETL

-- 프로시저 생성: 대량 데이터 처리
CREATE OR REPLACE PROCEDURE process_orders_batch(
  p_batch_size INT DEFAULT 1000
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_processed INT := 0;
  v_total INT := 0;
BEGIN
  -- 처리할 총 건수 확인
  SELECT COUNT(*) INTO v_total FROM orders WHERE status = 'pending';

  WHILE v_processed < v_total LOOP
    -- 배치 단위로 처리
    UPDATE orders
    SET status = 'processed', processed_at = NOW()
    WHERE id IN (
      SELECT id FROM orders
      WHERE status = 'pending'
      LIMIT p_batch_size
      FOR UPDATE SKIP LOCKED
    );

    v_processed := v_processed + p_batch_size;

    -- 중간 커밋 (프로시저에서만 가능!)
    COMMIT;

    RAISE NOTICE 'Processed % / % orders', v_processed, v_total;
  END LOOP;
END;
$$;

-- 프로시저 호출
CALL process_orders_batch(500);

-- OUT 파라미터가 있는 프로시저
CREATE PROCEDURE get_stats(OUT total_count INT, OUT avg_amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT COUNT(*), AVG(amount)
  INTO total_count, avg_amount
  FROM orders;
END;
$$;

-- OUT 파라미터 프로시저 호출
CALL get_stats(NULL, NULL);

프로시저 내 트랜잭션 제어 주의사항

• COMMIT/ROLLBACK 후에는 이전에 열린 커서가 닫힘
• EXCEPTION 블록 내에서는 COMMIT 불가
• 호출자가 트랜잭션 내에서 호출하면 트랜잭션 제어 불가
• 중첩 프로시저에서는 최상위 프로시저만 트랜잭션 제어 가능

(출처: PostgreSQL 17 - CREATE PROCEDURE)

 

7. 트리거(Trigger)

트리거는 특정 이벤트(INSERT, UPDATE, DELETE, TRUNCATE)가 발생할 때 자동으로 실행되는 함수이다. 데이터 검증, 감사 로그, 자동 계산 등에 활용된다.

 

7-1. 트리거 유형

표 7-1. 트리거 타이밍과 레벨

구분	옵션	설명
타이밍	BEFORE	작업 전 실행, 제약조건 검사 전
	AFTER	작업 후 실행, 제약조건 검사 후
	INSTEAD OF	작업 대신 실행 (뷰 전용)
레벨	FOR EACH ROW	영향받는 각 행마다 실행
	FOR EACH STATEMENT	SQL 문당 한 번만 실행 (기본값)

 

7-2. 트리거 함수와 특수 변수

트리거 함수는 RETURNS trigger로 정의하며, 행 레벨 트리거에서 NEW와 OLD 특수 변수에 접근할 수 있다.

표 7-2. NEW/OLD 변수 가용성

이벤트	OLD	NEW
INSERT	NULL	삽입될 행
UPDATE	변경 전 행	변경 후 행
DELETE	삭제될 행	NULL

 

7-3. 트리거 예제

-- 1. 트리거 함수 생성: 감사 로그
CREATE OR REPLACE FUNCTION audit_log_trigger()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, old_data, new_data, changed_at, changed_by)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    row_to_json(OLD),
    row_to_json(NEW),
    NOW(),
    current_user
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 트리거 생성
CREATE TRIGGER trg_users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW
  EXECUTE FUNCTION audit_log_trigger();

-- 3. BEFORE 트리거: 자동 업데이트 시간 설정
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- 4. 조건부 트리거 (WHEN 절)
CREATE TRIGGER trg_orders_status_change
  AFTER UPDATE ON orders
  FOR EACH ROW
  WHEN (OLD.status IS DISTINCT FROM NEW.status)
  EXECUTE FUNCTION notify_status_change();

 

7-4. Transition Relations (REFERENCING 절)

PostgreSQL 10부터 AFTER ... FOR EACH STATEMENT 트리거에서 변경된 모든 행에 접근할 수 있다.

-- REFERENCING으로 변경 전/후 전체 행 접근
CREATE OR REPLACE FUNCTION summary_update_trigger()
RETURNS trigger AS $$
BEGIN
  -- 삭제된 행들의 합계 차감
  IF TG_OP IN ('DELETE', 'UPDATE') THEN
    UPDATE summary
    SET total_amount = total_amount - (
      SELECT COALESCE(SUM(amount), 0) FROM old_rows
    );
  END IF;

  -- 추가된 행들의 합계 가산
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    UPDATE summary
    SET total_amount = total_amount + (
      SELECT COALESCE(SUM(amount), 0) FROM new_rows
    );
  END IF;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_summary
  AFTER INSERT OR UPDATE OR DELETE ON orders
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION summary_update_trigger();

 

7-5. 트리거 관리

-- 트리거 목록 (psql)
\dft -- 또는
\dft+ users

-- SQL: 트리거 정보 조회
SELECT
  tgname AS trigger_name,
  tgtype::int::bit(7) AS trigger_type,
  tgenabled AS enabled,
  pg_get_triggerdef(oid) AS definition
FROM pg_trigger
WHERE tgrelid = 'users'::regclass
  AND NOT tgisinternal;

-- 트리거 비활성화/활성화
ALTER TABLE users DISABLE TRIGGER trg_users_audit;
ALTER TABLE users ENABLE TRIGGER trg_users_audit;

-- 모든 트리거 비활성화 (주의: 데이터 정합성 문제 가능)
ALTER TABLE users DISABLE TRIGGER ALL;

-- 트리거 삭제
DROP TRIGGER trg_users_audit ON users;

트리거 사용 시 주의사항

• 성능 영향: 행 레벨 트리거는 대량 작업에서 성능 저하 유발
• 디버깅 어려움: 트리거 로직은 숨겨져 있어 문제 추적이 어려움
• 재귀 트리거: 트리거가 다른 트리거를 유발하면 무한 루프 가능
• 복제: 논리 복제에서 트리거 동작이 예상과 다를 수 있음

(출처: PostgreSQL 17 - CREATE TRIGGER)

마무리

이번 글에서는 PostgreSQL의 핵심 오브젝트를 다루었다.

핵심 요약:

• 테이블: IDENTITY 컬럼 권장, Generated Column으로 파생값 자동 계산, 시스템 컬럼(ctid, xmin 등)으로 MVCC 추적
• 인덱스: INCLUDE로 커버링 인덱스 생성 → Index-Only Scan 가능, 운영 중에는 CONCURRENTLY 필수
• 파티션: 서브파티셔닝으로 다단계 분할 가능, DETACH CONCURRENTLY로 락 최소화 (PostgreSQL 14+)
• 뷰: 일반 뷰는 쿼리 단순화, Materialized View는 집계/리포트 성능 향상
• 시퀀스: 자동 증가 숫자 생성, 갭 발생 가능, CACHE로 성능 향상
• 함수/프로시저: 함수는 값 반환, 프로시저는 COMMIT/ROLLBACK 가능 (PostgreSQL 11+)
• 트리거: BEFORE/AFTER 타이밍, ROW/STATEMENT 레벨, REFERENCING으로 변경 행 집합 접근

 

참고 자료

공식 문서

• Data Definition (DDL)
• CREATE TABLE
• Indexes
• Table Partitioning
• Views
• CREATE SEQUENCE
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE TRIGGER
• Generated Columns
• Index-Only Scans
• System Columns