✍️ 필사 모드: PostgreSQL Production Operations Complete Guide 2025: Extensions, Tuning, Monitoring, HA

TL;DR

• Extensions are PostgreSQL's true power: pg_stat_statements (query analysis), pgvector (AI), PostGIS (geo), TimescaleDB (time-series), pg_partman (partitioning)
• Three key tuning parameters: shared_buffers (25% RAM), work_mem (sort/hash memory), effective_cache_size (50-75% RAM)
• Autovacuum is critical: Misconfigured causes table bloat → disk explosion → slow queries. Tuning autovacuum_vacuum_scale_factor is essential
• Patroni: De facto standard for PostgreSQL HA. Auto failover within 30 seconds
• Backup: pgBackRest is 10x+ faster than pg_dump. WAL archiving + PITR is essential

1. Why PostgreSQL Remains Loved in 2025

1.1 Five Key Reasons

1. Extensibility — 200+ official extensions. Vector, time-series, geo, all of them
2. JSONB performance — Comparable or faster than MongoDB for JSON
3. MVCC — No read-write conflicts (excellent concurrency)
4. Standards compliant — Most faithful ANSI SQL implementation
5. Open source — Zero license costs vs Oracle/SQL Server

1.2 Why PostgreSQL Is Standard Even in NewSQL Era

CockroachDB, YugabyteDB, Spanner emerged, but most use PostgreSQL wire protocol compatibility. This means PostgreSQL itself is in a stable position.

2. Essential Extensions Top 10

2.1 Operations Must-Have: pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000

Usage:

-- TOP 10 slowest queries
SELECT
  substring(query, 1, 80) as query,
  calls,
  total_exec_time::int as total_ms,
  mean_exec_time::int as avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This single query solves 80% of performance issues. You immediately see which queries consume the most time.

2.2 AI Era Essential: pgvector

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(768)
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

SELECT id, content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Why pgvector instead of dedicated Vector DB?

• Leverage existing PostgreSQL ops knowledge
• ACID transactions (vector + metadata consistency)
• Cost savings (no separate infrastructure)

2.3 Time-Series: TimescaleDB

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id TEXT,
  temperature FLOAT
);

SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');

SELECT add_retention_policy('metrics', INTERVAL '30 days');

2.4 Other Core Extensions

3. Core Performance Tuning

3.1 Memory Parameters

Formula: shared_buffers + (work_mem × max_connections) ≤ RAM × 0.5

3.2 Checkpoint Tuning

checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

3.3 Query Planner

random_page_cost = 1.1              # SSD (HDD is 4.0)
effective_io_concurrency = 200      # SSD/NVMe
default_statistics_target = 100

4. VACUUM and Autovacuum

4.1 Why VACUUM Is Needed

PostgreSQL's MVCC handles UPDATE as "INSERT new row + mark old row". Old rows aren't deleted immediately but remain as "dead tuples". VACUUM cleans them.

Without VACUUM:

1. Table size keeps growing (disk explosion)
2. Indexes point to dead tuples → slow queries
3. Transaction ID wraparound — data corruption risk!

4.2 Autovacuum Monitoring

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

dead_ratio above 0.2 means autovacuum can't keep up.

4.3 Autovacuum Tuning

autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 2000

5. Monitoring and Debugging

5.1 Real-time Activity

-- Currently running queries
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

5.2 EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 12345;

Key indicators:

• Seq Scan vs Index Scan — index usage
• actual rows vs estimated rows — statistics accuracy
• Buffers: shared hit/read — cache hit rate
• Execution Time — actual time

5.3 pgBadger — Log Analysis

log_min_duration_statement = 1000
pgbadger -j 4 /var/log/postgresql/postgresql-*.log -o report.html

6. High Availability — Patroni

6.1 How Patroni Works

1. Each PostgreSQL node runs Patroni daemon
2. Patroni renews leader lock in etcd
3. Primary fails → lock expires → Patroni elects new Primary
4. HAProxy/PgBouncer auto-routes to new Primary

6.2 Patroni Alternatives

7. Backup and Recovery

7.1 pg_dump vs pgBackRest

7.2 PITR (Point-in-Time Recovery)

pgbackrest --stanza=main \
  --type=time \
  --target="2025-04-15 14:30:00" \
  restore

This enables responses like "rewind to yesterday at 3:25:30 PM".

8. Common Pitfalls

8.1 Index Bloat

-- Unused indexes
SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS unused_index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

8.2 N+1 Queries

ORM's most common problem. Use pg_stat_statements to find duplicate queries called thousands of times.

8.3 Connection Explosion

PostgreSQL connection = separate process (~10MB each). Solution: PgBouncer (transaction mode recommended).

9. PostgreSQL 17 New Features (2024-09)

• Improved incremental sort
• Logical replication: failover slot sync
• pg_basebackup: incremental backup support
• VACUUM: memory-based dead tuple tracking (faster)
• MERGE command: RETURNING clause added

Quiz

References

• PostgreSQL Documentation
• Use The Index, Luke!
• Postgres Weekly
• Patroni
• pgBackRest
• pgBadger
• pgvector
• TimescaleDB
• PostGIS
• pgtune
• PoWA