PostgreSQL Performance Tuning — คู่มือปรับแต่งสำหรับ Production ฉบับสมบูรณ์ 2026

PostgreSQL เป็น relational database ที่ทรงพลังที่สุดในโลก open source แต่ default configuration ของ PostgreSQL ถูกตั้งมาให้ทำงานได้บนเครื่องที่มี RAM แค่ 128MB — ซึ่งเหมาะกับ development แต่ไม่เหมาะกับ production เลย ผมเคยเห็น PostgreSQL ที่รัน production workload ด้วย default config แล้วช้าจนลูกค้าหนีไป เมื่อปรับ config ให้เหมาะสม performance ดีขึ้น 10-50 เท่า โดยไม่ต้องเปลี่ยน hardware

ผมดูแล PostgreSQL databases ที่ handle 50,000+ queries per second มากว่า 15 ปี ตั้งแต่ PostgreSQL 8.4 จนถึง 16 ในปัจจุบัน บทความนี้จะแชร์ทุกเทคนิคที่ผมใช้จริงใน production

สิ่งที่จะได้เรียนรู้:

• postgresql.conf — ทุก parameter ที่ต้องปรับ
• Memory Configuration — shared_buffers, work_mem, effective_cache_size
• WAL Tuning — ลด I/O overhead
• Connection Pooling ด้วย PgBouncer
• Index Strategy — เมื่อไหร่ควรสร้าง ลบ หรือ rebuild
• EXPLAIN ANALYZE — อ่าน query plan
• Vacuum และ Autovacuum Tuning
• Partitioning สำหรับ tables ขนาดใหญ่
• Monitoring ด้วย pg_stat_statements

postgresql.conf — Configuration ที่ต้องปรับทันที

สมมติ server มี spec: 32GB RAM, 8 CPU cores, SSD storage

# /etc/postgresql/16/main/postgresql.conf

# ═══════════════════════════════════════
# MEMORY
# ═══════════════════════════════════════

# Shared buffers — PostgreSQL's own cache
# กฎ: 25% ของ RAM (แต่ไม่เกิน 8GB บน Linux)
shared_buffers = 8GB

# Work memory — per-operation memory สำหรับ sort, hash
# กฎ: RAM / (max_connections * 4)
# ระวัง: ค่านี้เป็น per-operation ไม่ใช่ per-connection
# 1 query อาจใช้หลาย work_mem (เช่น join 3 tables = 3x work_mem)
work_mem = 64MB

# Maintenance work memory — สำหรับ VACUUM, CREATE INDEX
maintenance_work_mem = 2GB

# Effective cache size — บอก planner ว่า OS cache มีเท่าไหร่
# กฎ: 75% ของ RAM
effective_cache_size = 24GB

# ═══════════════════════════════════════
# WAL (Write-Ahead Log)
# ═══════════════════════════════════════

# WAL level — ต้องเป็น replica สำหรับ replication
wal_level = replica

# WAL buffers
wal_buffers = 64MB

# Checkpoint settings
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB

# ═══════════════════════════════════════
# QUERY PLANNER
# ═══════════════════════════════════════

# Random page cost — ลดสำหรับ SSD (default 4.0 สำหรับ HDD)
random_page_cost = 1.1

# Sequential page cost
seq_page_cost = 1.0

# Effective IO concurrency — เพิ่มสำหรับ SSD
effective_io_concurrency = 200

# Parallel query
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
parallel_tuple_cost = 0.01
parallel_setup_cost = 100

# ═══════════════════════════════════════
# CONNECTIONS
# ═══════════════════════════════════════

# Max connections — ใช้ PgBouncer แทนการเพิ่มค่านี้!
max_connections = 200

# ═══════════════════════════════════════
# LOGGING
# ═══════════════════════════════════════

# Log slow queries
log_min_duration_statement = 500  # log queries ที่ใช้เวลา > 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0  # log ทุก temp file
log_autovacuum_min_duration = 0  # log ทุก autovacuum

# ═══════════════════════════════════════
# AUTOVACUUM
# ═══════════════════════════════════════

autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05  # default 0.2 ช้าเกินไป
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.025
autovacuum_vacuum_cost_delay = 2ms  # default 20ms ช้าเกินไป
autovacuum_vacuum_cost_limit = 1000  # default 200 ช้าเกินไป

ทำไมต้องปรับ autovacuum? Default autovacuum ช้ามากสำหรับ tables ขนาดใหญ่ ตัวอย่าง: table ที่มี 100 ล้าน rows ด้วย default autovacuum_vacuum_scale_factor = 0.2 ต้องมี dead tuples 20 ล้าน rows ก่อนที่ autovacuum จะทำงาน ซึ่งนานเกินไป ทำให้ table bloat

Connection Pooling ด้วย PgBouncer

PostgreSQL สร้าง process ใหม่สำหรับทุก connection ถ้ามี 500 connections = 500 processes ซึ่งกิน RAM มากและ context switching ช้า PgBouncer แก้ปัญหานี้โดยเป็น connection pool ที่ multiplex หลาย client connections ไปยัง PostgreSQL connections จำนวนน้อย

# ติดตั้ง PgBouncer
sudo apt install -y pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pool mode
pool_mode = transaction  # ดีที่สุดสำหรับ web apps

# Pool size
default_pool_size = 25
max_client_conn = 1000
max_db_connections = 50
reserve_pool_size = 5
reserve_pool_timeout = 3

# Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30
client_login_timeout = 15

# Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

# /etc/pgbouncer/userlist.txt
"myuser" "SCRAM-SHA-256$4096:salt$stored_key:server_key"

# สร้าง hash
psql -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow WHERE usename = 'myuser';"

ผลลัพธ์จริง: ก่อนใช้ PgBouncer — 500 connections ตรงไป PostgreSQL, RAM usage 4GB, response time 200ms หลังใช้ PgBouncer — 500 client connections → 25 PostgreSQL connections, RAM usage 1.5GB, response time 50ms

Index Strategy

หา Missing Indexes

-- Tables ที่มี sequential scan มาก (ควรมี index)
SELECT
    schemaname, relname,
    seq_scan, seq_tup_read,
    idx_scan, idx_tup_fetch,
    n_tup_ins, n_tup_upd, n_tup_del,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND seq_tup_read > 100000
  AND idx_scan < seq_scan
ORDER BY seq_tup_read DESC
LIMIT 20;

หา Unused Indexes (ลบได้)

-- Indexes ที่ไม่เคยถูกใช้ (กิน disk + ช้า write)
SELECT
    schemaname, tablename, indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- ไม่ลบ PK/Unique
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Partial Index — ประหยัด disk + เร็วขึ้น

-- แทนที่จะ index ทั้ง table
CREATE INDEX idx_orders_status ON orders(status);
-- 100 ล้าน rows, index size: 2GB

-- ใช้ partial index เฉพาะ rows ที่ query บ่อย
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- เฉพาะ pending orders (1% ของ table), index size: 20MB
-- เร็วกว่า 100 เท่า!

Covering Index (INCLUDE)

-- Query นี้ต้อง lookup table หลัง scan index
SELECT email, name FROM users WHERE email = 'john@example.com';

-- Covering index — ไม่ต้อง lookup table (index-only scan)
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name);
-- เร็วขึ้น 2-5 เท่า สำหรับ query ที่ SELECT แค่ columns ที่อยู่ใน index

อ่านต่อ: duckdb analytics performance tuning เพมความเรว

EXPLAIN ANALYZE — อ่าน Query Plan

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- Output:
-- Limit (cost=0.87..125.43 rows=50 width=52) (actual time=0.089..0.234 rows=50 loops=1)
--   Buffers: shared hit=156
--   -> Nested Loop (cost=0.87..12543.21 rows=5021 width=52) (actual time=0.088..0.228 rows=50 loops=1)
--        -> Index Scan Backward using idx_orders_pending on orders o
--             (cost=0.43..8234.12 rows=5021 width=36) (actual time=0.052..0.089 rows=50 loops=1)
--             Filter: (created_at > (now() - '7 days'::interval))
--             Buffers: shared hit=53
--        -> Index Scan using users_pkey on users u
--             (cost=0.43..0.86 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=50)
--             Index Cond: (id = o.user_id)
--             Buffers: shared hit=103
-- Planning Time: 0.312 ms
-- Execution Time: 0.267 ms

สิ่งที่ต้องดู:

• Seq Scan บน table ใหญ่ = ต้องสร้าง index
• Buffers: shared hit = อ่านจาก cache (ดี) vs shared read = อ่านจาก disk (ช้า)
• actual time vs cost — ถ้าต่างกันมาก = statistics ไม่ update ให้รัน ANALYZE
• rows (estimated) vs rows (actual) — ถ้าต่างกันมาก = planner เลือก plan ผิด

หากสนใจเพิ่มเติม อ่านได้ที่ dns over https performance tuning เพมความเรว

pg_stat_statements — หา Slow Queries

-- เปิด pg_stat_statements
-- postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 20 queries ที่ใช้เวลารวมมากที่สุด
SELECT
    substring(query, 1, 100) AS query,
    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
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Top queries ที่ช้าที่สุด (per call)
SELECT
    substring(query, 1, 100) AS query,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

ผมเคยเขียนเรื่องที่เกี่ยวข้องไว้ใน llm fine tuning lora api integration เชอมตอระบบ

Table Partitioning

-- สร้าง partitioned table (by range)
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- สร้าง partitions รายเดือน
CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE orders_2026_02 PARTITION OF orders
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE orders_2026_03 PARTITION OF orders
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Auto-create partitions ด้วย pg_partman
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table := 'public.orders',
    p_control := 'created_at',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3
);

ผลลัพธ์: table 500 ล้าน rows → query ที่ filter by date เร็วขึ้น 50-100 เท่า เพราะ PostgreSQL scan แค่ partition ที่เกี่ยวข้อง ไม่ต้อง scan ทั้ง table

Backup Strategy

# Continuous Archiving + PITR (Point-in-Time Recovery)
# postgresql.conf:
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-pg-backups/wal/%f'

# Base backup ทุกวัน
pg_basebackup -h localhost -U replicator -D /backup/base \
  --wal-method=stream --checkpoint=fast --progress

# Restore to specific point in time
restore_command = 'aws s3 cp s3://my-pg-backups/wal/%f %p'
recovery_target_time = '2026-02-17 08:00:00+07'

📌 บทความแนะนำจาก siamlancard: | |

สำหรับรายละเอียดเพิ่มเติม ดู llm fine tuning lora remote work setup

Troubleshooting

ปัญหา 1: Lock contention

-- หา queries ที่ถูก block
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks bk ON bk.locktype = bl.locktype
    AND bk.database IS NOT DISTINCT FROM bl.database
    AND bk.relation IS NOT DISTINCT FROM bl.relation
    AND bk.page IS NOT DISTINCT FROM bl.page
    AND bk.tuple IS NOT DISTINCT FROM bl.tuple
    AND bk.pid != bl.pid
JOIN pg_stat_activity blocking ON bk.pid = blocking.pid
WHERE NOT bl.granted;

ปัญหา 2: Table bloat

-- ตรวจสอบ bloat
SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- แก้: VACUUM FULL (ล็อค table!) หรือ pg_repack (online)
-- pg_repack ดีกว่าเพราะไม่ lock table
pg_repack -d mydb -t bloated_table

ปัญหา 3: Connection exhaustion

-- ดู connections ปัจจุบัน
SELECT
    state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Kill idle connections ที่ค้างนาน
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < NOW() - INTERVAL '30 minutes';

คำถามที่พบบ่อย (FAQ)

Q: shared_buffers ตั้งเท่าไหร่ดี?

A: 25% ของ RAM เป็นจุดเริ่มต้นที่ดี ไม่เกิน 8GB บน Linux เพราะ OS file cache ทำงานร่วมกับ PostgreSQL ได้ดี

Q: work_mem ตั้งสูงๆ ได้ไหม?

A: ระวัง! work_mem เป็น per-operation ถ้าตั้ง 1GB และมี 100 connections ที่รัน complex query พร้อมกัน อาจใช้ RAM 100GB+ ทำให้ OOM kill

Q: PostgreSQL กับ MySQL เลือกอะไรดี?

A: PostgreSQL ดีกว่าสำหรับ complex queries, JSON, full-text search, geospatial MySQL ดีกว่าสำหรับ simple read-heavy workloads ที่ต้องการ replication ง่ายๆ

Q: ควรใช้ managed service (RDS, Cloud SQL) ไหม?

A: ถ้าทีมเล็ก (1-3 DBAs) ใช้ managed service ดีกว่า ประหยัดเวลา ops ถ้าต้องการ control เต็มที่หรือ cost-sensitive ใช้ self-managed

Q: VACUUM FULL กับ VACUUM ต่างกันอย่างไร?

A: VACUUM ปกติ mark dead tuples ให้ reuse ได้ ไม่ lock table VACUUM FULL rewrite table ใหม่ทั้งหมด ลดขนาดจริง แต่ lock table ตลอดเวลาที่ทำ ใช้ pg_repack แทน VACUUM FULL

สรุป — เริ่ม Tune PostgreSQL วันนี้

PostgreSQL performance tuning ไม่ยาก แต่ต้องทำ เริ่มจากปรับ postgresql.conf ตาม spec ของ server ติดตั้ง PgBouncer สำหรับ connection pooling เปิด pg_stat_statements หา slow queries สร้าง index ที่เหมาะสม ภายใน 1 ชั่วโมงคุณจะเห็นผลทันที

ถ้ามีคำถาม สอบถามได้ที่ SiamCafe Forum ครับ