Split View: PostgreSQL 쿼리 최적화와 성능 튜닝 실전 가이드: EXPLAIN부터 파티셔닝까지

-- Basic EXPLAIN ANALYZE usage
EXPLAIN ANALYZE
SELECT u.id, u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed'
ORDER BY o.total_amount DESC
LIMIT 100;

Limit  (cost=15234.56..15234.81 rows=100 width=52) (actual time=45.123..45.156 rows=100 loops=1)
  ->  Sort  (cost=15234.56..15347.89 rows=45332 width=52) (actual time=45.121..45.142 rows=100 loops=1)
        Sort Key: o.total_amount DESC
        Sort Method: top-N heapsort  Memory: 35kB
        ->  Hash Join  (cost=3456.78..13890.12 rows=45332 width=52) (actual time=12.345..38.901 rows=45332 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..9876.54 rows=45332 width=20) (actual time=0.015..18.234 rows=45332 loops=1)
                    Filter: ((created_at >= '2026-01-01') AND (status = 'completed'))
                    Rows Removed by Filter: 154668
              ->  Hash  (cost=2345.00..2345.00 rows=100000 width=36) (actual time=12.123..12.123 rows=100000 loops=1)
                    Buckets: 131072  Batches: 1  Memory Usage: 6234kB
                    ->  Seq Scan on users u  (cost=0.00..2345.00 rows=100000 width=36) (actual time=0.008..5.678 rows=100000 loops=1)
Planning Time: 0.456 ms
Execution Time: 45.234 ms

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders
WHERE user_id = 12345
  AND created_at BETWEEN '2026-01-01' AND '2026-03-14';

Index Scan using idx_orders_user_created on orders  (cost=0.43..8.45 rows=1 width=64) (actual time=0.023..0.025 rows=3 loops=1)
  Index Cond: ((user_id = 12345) AND (created_at >= '2026-01-01') AND (created_at <= '2026-03-14'))
  Buffers: shared hit=4
Planning Time: 0.123 ms
Execution Time: 0.045 ms

-- Single column B-tree index
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Composite index: leading column order matters
-- Optimal for queries like WHERE user_id = ? AND created_at >= ?
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- Covering index: enables Index-Only Scan
CREATE INDEX idx_orders_covering ON orders (user_id, created_at)
  INCLUDE (status, total_amount);

-- Partial index: indexes only rows matching specific conditions to reduce size
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
  WHERE status = 'active';

-- GIN index on JSONB field
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- jsonb_path_ops: more efficient for specific operators with smaller index size
CREATE INDEX idx_products_metadata_path ON products USING GIN (metadata jsonb_path_ops);

-- GIN index for full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (
  to_tsvector('korean', title || ' ' || content)
);

-- GIN on array column
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- PostGIS spatial index
CREATE INDEX idx_stores_location ON stores USING GiST (location);

-- Range type index
CREATE INDEX idx_reservations_period ON reservations USING GiST (
  tstzrange(start_time, end_time)
);

-- Range overlap search
SELECT * FROM reservations
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-14 09:00', '2026-03-14 18:00');

-- BRIN index on time-series log table
CREATE INDEX idx_logs_created_brin ON access_logs USING BRIN (created_at)
  WITH (pages_per_range = 32);

-- Verify BRIN effectiveness: correlation should be close to 1
SELECT correlation
FROM pg_stats
WHERE tablename = 'access_logs' AND attname = 'created_at';

-- Bad: Queries executed in N+1 pattern from application
-- 1) SELECT * FROM users WHERE department = 'engineering';  -- returns 50
-- 2) SELECT * FROM orders WHERE user_id = 1;  -- repeated 50 times
-- 3) SELECT * FROM orders WHERE user_id = 2;
-- ...

-- Good: Fetch in one go with JOIN
SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.department = 'engineering';

-- Good: LATERAL JOIN to get only 3 most recent orders per user
SELECT u.id, u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
  SELECT o.id, o.total_amount, o.created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent_orders
WHERE u.department = 'engineering';

-- Bad: Correlated subquery - subquery may execute for each outer row
SELECT u.id, u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE u.status = 'active';

-- Good: Using GROUP BY with JOIN
SELECT u.id, u.name, COALESCE(oc.cnt, 0) AS order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY user_id
) oc ON u.id = oc.user_id
WHERE u.status = 'active';

-- EXISTS is often advantageous for large datasets
-- EXISTS stops immediately upon finding the first match
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.created_at >= '2026-01-01'
);

-- IN is advantageous when the subquery result is small
SELECT u.id, u.name
FROM users u
WHERE u.department_id IN (
  SELECT id FROM departments WHERE region = 'APAC'
);

-- Bad: Gets slower as OFFSET increases
SELECT * FROM orders
ORDER BY id
OFFSET 1000000 LIMIT 10;

-- Good: Keyset pagination - consistent performance
SELECT * FROM orders
WHERE id > 1000000  -- last id from previous page
ORDER BY id
LIMIT 10;

-- Keyset pagination with composite sort criteria
SELECT * FROM orders
WHERE (created_at, id) > ('2026-03-13 15:30:00', 98765)
ORDER BY created_at, id
LIMIT 10;

# postgresql.conf - Recommended Autovacuum tuning settings

# Enable Autovacuum (never disable this)
autovacuum = on

# Number of concurrent autovacuum workers
# Set considering CPU cores and table count
autovacuum_max_workers = 5

# VACUUM trigger threshold: runs when dead tuples exceed 10% of table + 50 rows
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1

# For large tables, scale_factor should be lowered
# On a 100M row table, 0.2 (default) means 20M dead tuples before VACUUM runs

# ANALYZE trigger: updates statistics when changes exceed 5% of table + 50 rows
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

# Cost-based delay: higher means faster VACUUM but more I/O load
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000

# XID Wraparound prevention settings
autovacuum_freeze_max_age = 300000000

-- High-volume orders table: more frequent VACUUM execution
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,      -- VACUUM even at 1% changes
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 0             -- VACUUM at maximum speed
);

-- VACUUM status monitoring
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_ratio_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Create monthly range-partitioned table
CREATE TABLE events (
  id          BIGSERIAL,
  event_type  VARCHAR(50) NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Create index on each partition (auto-inherited when created on parent)
CREATE INDEX idx_events_type_created ON events (event_type, created_at);

-- Verify partition pruning: query only March 2026 data
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
  AND event_type = 'purchase';

-- Automatic management setup after installing pg_partman
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => '1 month',
  p_premake => 3  -- pre-create 3 months ahead
);

-- Automatic old partition deletion (12 month retention)
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false
WHERE parent_table = 'public.events';

-- Bad: Mass DELETE creates Dead Tuples and VACUUM load
DELETE FROM events WHERE created_at < '2025-01-01';

-- Good: Detach and drop entire partition - instant, no VACUUM needed
ALTER TABLE events DETACH PARTITION events_2024_12;
DROP TABLE events_2024_12;

;; pgbouncer.ini

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp_production

[pgbouncer]
;; Pooling mode: transaction is most efficient
pool_mode = transaction

;; Maximum client connections
max_client_conn = 1000

;; Server connections per database (actual PostgreSQL connections)
default_pool_size = 30
min_pool_size = 10
reserve_pool_size = 5

;; Idle connection timeout
server_idle_timeout = 300

;; Client connection queue timeout
client_login_timeout = 60

;; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

;; Monitoring port
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Check PgBouncer status
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

# Check active connections and wait status
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"

# Check actual connection count on PostgreSQL side
psql -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';"

# postgresql.conf - Slow query logging
log_min_duration_statement = 500       # Log queries over 500ms
log_statement = 'none'                  # Use 'ddl' for DDL only
log_lock_waits = on                     # Log lock waits
deadlock_timeout = 1s                   # Deadlock detection interval

# Enable pg_stat_statements extension (essential)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = 'all'

-- Install pg_stat_statements extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries by average execution time
SELECT
  queryid,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct,
  rows,
  query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Find unused indexes
SELECT
  schemaname || '.' || indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS scan_count,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find duplicate indexes
SELECT
  pg_size_pretty(SUM(pg_relation_size(idx))::bigint) AS total_size,
  (array_agg(idx))[1] AS idx1,
  (array_agg(idx))[2] AS idx2,
  (array_agg(indkey))[1] AS columns1,
  (array_agg(indkey))[2] AS columns2
FROM (
  SELECT indexrelid::regclass AS idx,
         indrelid, indkey,
         COALESCE(indexprs::text, '') AS exprs,
         COALESCE(indpred::text, '') AS preds
  FROM pg_index
) sub
GROUP BY indrelid, exprs, preds
HAVING COUNT(*) > 1;

-- Check current lock wait situations
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query,
  blocked_activity.wait_event_type
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

# postgresql.conf - Core memory parameters

# shared_buffers: Recommended 25% of total RAM (max 40%)
# Based on 64GB RAM server
shared_buffers = 16GB

# work_mem: Per-session memory for sorting, hash joins, etc.
# Caution: allocated per node per query, so don't set too high
# Consider (concurrent connections * nodes per query)
work_mem = 64MB

# maintenance_work_mem: Used for VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 2GB

# effective_cache_size: Total cache size including OS cache (planner hint)
# 50-75% of total RAM
effective_cache_size = 48GB

# WAL settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

-- 1. Identify sessions running the problematic query
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- 2. Gracefully cancel the problematic query
SELECT pg_cancel_backend(12345);

-- 3. Force kill if it doesn't terminate
SELECT pg_terminate_backend(12345);

-- CONCURRENTLY: Create index without table lock (essential for production)
CREATE INDEX CONCURRENTLY idx_reports_user_date
  ON reports (user_id, report_date);

-- Index creation can fail and remain in INVALID state. Always verify!
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'reports';

# pg_repack: Zero-downtime table restructuring (only brief lock needed)
pg_repack -d myapp_production -t orders --no-superuser-check

# Restructure specific index only
pg_repack -d myapp_production --index idx_orders_user_created

-- Check XID exhaustion status
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  ROUND(100 * age(datfrozenxid)::numeric / 2147483647, 2) AS pct_toward_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Find long-running transactions (autovacuum blocking cause)
SELECT
  pid,
  now() - xact_start AS xact_duration,
  now() - query_start AS query_duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
ORDER BY xact_start ASC;

-- After terminating problematic transaction, run manual VACUUM FREEZE
VACUUM FREEZE orders;

;; pgbouncer.ini - Connection exhaustion prevention
query_wait_timeout = 30        ;; Max 30s waiting for server connection
server_idle_timeout = 60       ;; Return server connection after 60s idle
server_lifetime = 3600         ;; Recreate server connection after max 1 hour
client_idle_timeout = 300      ;; Disconnect idle client after 5 minutes