PostgreSQL Monitoring: Performance Tuning สำหรับ Postgres

PostgreSQL ขึ้นชื่อเรื่องความเสถียรและประสิทธิภาพสูง แต่การทำให้ระบบทำงานได้อย่างเต็มศักยภาพนั้นต้องอาศัยการ Monitoring ที่ครอบคลุมและการ Tuning ที่เหมาะสมกับ Workload จริง การปล่อยให้ฐานข้อมูลทำงานด้วยค่า Default ที่ติดตั้งมาตั้งแต่เริ่มต้นมักนำไปสู่ปัญหา Slow Query, Connection Exhaustion, หรือ Disk I/O พุ่งสูงในวันที่มี Traffic เยอะ

บทความนี้จะอธิบาย Metrics สำคัญที่ต้องติดตาม Extension ที่จำเป็น เช่น pg_stat_statements และแนวทางปรับ Parameter หลัก เช่น shared_buffers, work_mem, effective_cache_size ให้เหมาะกับ Workload โดยยึดหลัก Data-driven Tuning ไม่ใช่เดาสุ่ม

Metrics สำคัญที่ต้องติดตาม

การ Monitoring ฐานข้อมูลที่ดีไม่ใช่เก็บทุกอย่าง แต่เลือกเก็บ metric ที่สามารถนำไปปรับปรุงได้จริง (actionable) แบ่งเป็น 4 กลุ่มหลักตามลำดับความสำคัญดังนี้

กลุ่ม	Metric	ความสำคัญ
Availability	pg_up, connection success rate	รู้ทันทีเมื่อระบบล่ม
Performance	query latency, slow queries, cache hit ratio	จุดเริ่มต้นของการ tuning
Resource	connection count, transaction rate, lock waits	ตัดสินใจเรื่อง capacity
Replication	lag, WAL sender/receiver status	HA/DR readiness

ติดตั้ง pg_stat_statements

pg_stat_statements เป็น Extension ที่ทำให้เห็นรายละเอียดของทุก query ที่รัน ตั้งแต่ execution time, rows affected, buffer hits, shared blocks read ถือเป็นเครื่องมือพื้นฐานสำหรับ DBA ที่ต้องการหา slow query และ query pattern ที่ใช้งานบ่อย

# แก้ไขไฟล์ postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

# restart PostgreSQL
sudo systemctl restart postgresql

# สร้าง extension ในฐานข้อมูลที่ต้องการ monitor
psql -U postgres -d mydb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Query ที่ใช้บ่อย

-- Top 10 slowest queries โดยเวลาเฉลี่ยต่อครั้ง
SELECT
    substring(query, 1, 80) AS short_query,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Query ที่กิน I/O มากที่สุด
SELECT
    substring(query, 1, 80),
    shared_blks_read + shared_blks_hit AS total_blks,
    shared_blks_read,
    round(100.0 * shared_blks_hit /
          nullif(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

-- Reset สถิติเพื่อเริ่มเก็บข้อมูลใหม่
SELECT pg_stat_statements_reset();

Cache Hit Ratio

Cache Hit Ratio เป็นตัวชี้วัดว่า Postgres อ่านข้อมูลจาก shared_buffers (memory) ได้กี่เปอร์เซ็นต์ เทียบกับอ่านจาก disk ค่าที่ควรได้คือ 99% ขึ้นไปสำหรับ OLTP หากต่ำกว่า 95% เป็นสัญญาณว่าควรเพิ่ม shared_buffers หรือ RAM

-- Cache Hit Ratio ระดับฐานข้อมูล
SELECT
    datname,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

-- Cache Hit Ratio ระดับ table (หา table ที่ควรเพิ่ม index หรือ memory)
SELECT
    schemaname,
    relname,
    heap_blks_read,
    heap_blks_hit,
    round(100.0 * heap_blks_hit /
          nullif(heap_blks_hit + heap_blks_read, 0), 2) AS hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read + heap_blks_hit > 0
ORDER BY heap_blks_read DESC
LIMIT 10;

Connection และ Transaction Monitoring

การติดตาม connection จะช่วยให้เห็นภาพ pattern การใช้งาน ทั้งจำนวน connection ที่ active, idle, และ idle in transaction ซึ่งเป็นปัญหาที่พบบ่อยเพราะ connection ค้างทำให้ vacuum ไม่สามารถล้าง dead tuples ได้

-- Connection count แยกตาม state
SELECT
    state,
    count(*) AS connections,
    count(*) FILTER (WHERE query_start < now() - interval '5 min') AS long_running
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY connections DESC;

-- หา idle in transaction ที่ค้างนาน (ต้องฆ่า)
SELECT
    pid,
    usename,
    application_name,
    state,
    now() - xact_start AS xact_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '10 minutes'
ORDER BY xact_duration DESC;

-- ฆ่า connection ที่ค้าง
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - interval '30 minutes';

Lock Monitoring

Lock contention เป็นสาเหตุหลักที่ทำให้ query ช้าแม้ว่า resource ของเซิร์ฟเวอร์ยังเหลือ การเห็น lock waits ทันทีช่วยให้ debug ได้ตรงจุด

-- หา query ที่รอ lock และ query ที่ถือ lock
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 wait_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
     ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock'
ORDER BY wait_duration DESC;

-- Deadlock count
SELECT datname, deadlocks
FROM pg_stat_database
WHERE deadlocks > 0
ORDER BY deadlocks DESC;

Replication Lag

การ monitor replication lag มีหลายมุมมอง ทั้งในฝั่ง primary (byte lag ของ WAL sender) และฝั่ง replica (apply delay) การมี lag สูงผิดปกติมักเกิดจาก network saturation หรือ long-running transaction บน primary

-- บน Primary: ดู replica ที่เชื่อมต่อและ lag
SELECT
    application_name,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag_bytes,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
    now() - reply_time AS last_reply_ago
FROM pg_stat_replication;

-- บน Replica: ดู lag เป็นเวลา
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_lag_seconds,
    pg_is_in_recovery() AS is_replica;

Parameter Tuning ที่สำคัญ

การ tuning ที่ถูกต้องต้องอ้างอิงข้อมูลจริงจาก Monitoring ไม่ใช่ copy ค่าจาก blog ใด ๆ มาใช้ ต่อไปนี้คือ parameter หลักที่ส่งผลต่อประสิทธิภาพมากที่สุด พร้อมแนวทางกำหนดค่าเบื้องต้นสำหรับเซิร์ฟเวอร์ทั่วไป

Parameter	คำแนะนำเบื้องต้น	หมายเหตุ
shared_buffers	25% ของ RAM	Cache หลักสำหรับ data page
effective_cache_size	50-75% ของ RAM	บอก planner ว่ามี RAM ทั้งระบบเท่าไร
work_mem	RAM/max_connections/4	สำหรับ sort และ hash — ต้องระวัง OOM
maintenance_work_mem	1-2GB หรือ 5% RAM	ใช้ตอน VACUUM และ CREATE INDEX
max_connections	100-300	สูงเกินไปใช้ connection pooler แทน
wal_buffers	16MB (auto-tuned)	ค่า default มักเหมาะสม
checkpoint_timeout	15min	ลด write spike ของ checkpoint
random_page_cost	1.1 (SSD) / 4.0 (HDD)	ช่วย planner เลือก index scan vs seq scan

ตัวอย่าง Config สำหรับเซิร์ฟเวอร์ 16GB RAM

# postgresql.conf — Server 16GB RAM, 4 vCPU, SSD, OLTP workload
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 20MB
maintenance_work_mem = 1GB
max_connections = 200
wal_buffers = 16MB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
min_wal_size = 1GB
max_wal_size = 4GB

# Logging สำหรับ slow query
log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0

Autovacuum Tuning

Autovacuum เป็นกระบวนการสำคัญที่ป้องกัน table bloat และ transaction ID wraparound การ tuning ให้เหมาะสมเป็นหนึ่งในงานที่สร้างผลกระทบมากที่สุดต่อประสิทธิภาพระยะยาว

-- ดูสถิติ autovacuum ของแต่ละ table
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 10;

-- ปรับ autovacuum สำหรับ table ที่มี update บ่อย
ALTER TABLE hot_table SET (
    autovacuum_vacuum_scale_factor = 0.05,  -- default 0.2
    autovacuum_analyze_scale_factor = 0.02, -- default 0.1
    autovacuum_vacuum_cost_limit = 2000     -- default 200
);

ใช้ postgres_exporter กับ Prometheus

สำหรับระบบ Production ที่ต้องมี alerting และ historical data การใช้ postgres_exporter ร่วมกับ Prometheus และ Grafana เป็นวิธีมาตรฐาน

# docker-compose.yml
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    container_name: postgres_exporter
    restart: unless-stopped
    ports:
      - "9187:9187"
    environment:
      DATA_SOURCE_NAME: "postgresql://exporter:pass@db:5432/postgres?sslmode=disable"
      PG_EXPORTER_EXCLUDE_DATABASES: "template0,template1"
    networks:
      - monitoring

-- สร้าง user สิทธิ์จำกัดใน Postgres
CREATE USER exporter WITH PASSWORD 'pass';
GRANT pg_monitor TO exporter;
-- pg_monitor เป็น role ที่มีสิทธิ์อ่าน pg_stat_* ทั้งหมด (PostgreSQL 10+)

Alert Rules ที่แนะนำ

groups:
  - name: postgres_alerts
    interval: 30s
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Postgres instance down ({{ $labels.instance }})"

      - alert: PostgresHighConnections
        expr: |
          sum by (instance) (pg_stat_activity_count)
          / on(instance) pg_settings_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Postgres connections > 80%"

      - alert: PostgresReplicationLag
        expr: pg_replication_lag_seconds > 30
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Replication lag {{ $value }}s"

      - alert: PostgresDeadlocks
        expr: rate(pg_stat_database_deadlocks[5m]) > 0.1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Deadlock rate เพิ่มขึ้น"

      - alert: PostgresLowCacheHit
        expr: |
          rate(pg_stat_database_blks_hit[5m])
          / (rate(pg_stat_database_blks_hit[5m])
             + rate(pg_stat_database_blks_read[5m])) < 0.95
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "Cache hit ratio < 95%"

Best Practices สำหรับ Production

เปิด pg_stat_statements เสมอตั้งแต่วันแรก — ข้อมูลย้อนหลังวิเคราะห์ปัญหาได้มาก
ใช้ Connection Pooler เช่น PgBouncer เมื่อ application เปิด connection เยอะ
ตั้ง log_min_duration_statement = 1000 เพื่อ log query ที่ใช้เวลาเกิน 1 วินาที
ตรวจสอบ bloat ทุกไตรมาสด้วย pgstattuple หรือ pg_repack หาก bloat เกิน 20%
Backup พร้อม WAL archiving ให้สามารถ Point-in-time Recovery (PITR) ได้
ทดสอบ restore จาก backup เป็นระยะ — ไม่ใช่แค่สำรองไว้เฉย ๆ
แยก read query ไปยัง replica หาก read workload สูง ลดภาระ primary
Upgrade minor version สม่ำเสมอ (15.5 -> 15.6) โดยไม่ต้อง dump/restore

สรุป

การทำให้ PostgreSQL ทำงานได้เต็มประสิทธิภาพต้องประกอบด้วย 3 ขั้นตอนที่วนซ้ำต่อเนื่อง ได้แก่ Monitoring เพื่อเก็บข้อมูลจริง, Analysis เพื่อระบุ bottleneck, และ Tuning เพื่อปรับ parameter หรือ query ให้ดีขึ้น ห้ามปรับโดยไม่มีข้อมูล เพราะอาจทำให้ประสิทธิภาพแย่ลงกว่าเดิม

เครื่องมือที่แนะนำประกอบด้วย pg_stat_statements สำหรับ query analysis, postgres_exporter + Prometheus + Grafana สำหรับ metric collection และ Alertmanager สำหรับ notification เมื่อ integrate ทั้งหมดเข้าด้วยกันจะได้ระบบ Observability ที่ครบถ้วน พร้อมรับมือกับการเติบโตของระบบในระยะยาว