// Performance Tuning
PostgreSQL Performance Tuning
เพิ่มประสิทธิภาพ PostgreSQL ในทุกระดับ — ตั้งแต่ Server Configuration ไปจนถึง Query Optimization และ Index Strategy
// Tuning Levels
3 ระดับของ PostgreSQL Performance Tuning
Server Configuration
ปรับ postgresql.conf ให้ใช้ Resources ของเครื่องได้อย่างเต็มประสิทธิภาพ
Query Optimization
วิเคราะห์ Query ด้วย EXPLAIN ANALYZE และปรับปรุง Execution Plan
Maintenance
VACUUM, ANALYZE และ autovacuum สำหรับรักษาสุขภาพของ Database
// postgresql.conf
Key Configuration Settings
# postgresql.conf — Key Performance Settings
# ปรับตามขนาด RAM ของเครื่อง (ตัวอย่าง: RAM 16GB)
# Memory
shared_buffers = 4GB # 25% ของ RAM
effective_cache_size = 12GB # 75% ของ RAM
work_mem = 64MB # per sort/hash — ระวัง max_connections!
maintenance_work_mem = 1GB # สำหรับ VACUUM, CREATE INDEX
# Write Performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
# Query Planner
random_page_cost = 1.1 # SSD: 1.1 / HDD: 4.0
effective_io_concurrency= 200 # SSD: 200+ / HDD: 2
default_statistics_target = 100
# Connections — ใช้ PgBouncer แทนการเพิ่ม max_connections
max_connections = 100
# Logging (สำหรับ Slow Query Analysis)
log_min_duration_statement = 1000 # Log queries > 1 วินาที
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a '
// Query Analysis
EXPLAIN ANALYZE — วิเคราะห์ Query
-- วิเคราะห์ Query Plan + Actual Time
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'TH'
GROUP BY u.name;
-- เปิด pg_stat_statements
CREATE EXTENSION pg_stat_statements;
-- ดู Top Slow Queries
SELECT query,
round(mean_exec_time::numeric, 2) AS avg_ms,
calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
Seq Scan = ปัญหา
ถ้า EXPLAIN แสดง Seq Scan บน Table ใหญ่ ควรพิจารณาเพิ่ม Index บน Column ที่ใช้ใน WHERE
Index Scan = ดี
Index Scan หรือ Bitmap Index Scan แสดงว่า Query ใช้ Index อย่างมีประสิทธิภาพ
Hash Join vs Nested Loop
Hash Join เหมาะกับ Large Tables / Nested Loop เหมาะกับ Small Tables ที่มี Index
// Index Strategy
Index Types ใน PostgreSQL
B-Tree (Default)
ใช้ได้กับ =, <, >, BETWEEN, LIKE 'prefix%' — Index ทั่วไปที่ใช้บ่อยที่สุด
GIN (Generalized Inverted)
เหมาะกับ JSONB, Array, Full-Text Search — ค้นหาภายใน Container Types
GiST (Generalized Search Tree)
Geometric Types, Full-Text, Range Types — ยืดหยุ่นสูง
BRIN (Block Range)
ตาราง Sequential ขนาดใหญ่มาก เช่น Log Tables, Time-Series — ขนาด Index เล็กมาก
Hash Index
เฉพาะ = Query — เร็วกว่า B-Tree สำหรับ Equality ล้วนๆ
Partial Index
Index เฉพาะ Rows ที่ตรงเงื่อนไข ลดขนาด Index และเพิ่มประสิทธิภาพ
-- B-Tree (ทั่วไป)
CREATE INDEX idx_users_email ON users(email);
-- GIN สำหรับ JSONB
CREATE INDEX idx_attrs ON products USING GIN(attrs);
-- Partial Index — เฉพาะ Active Users
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = TRUE;
-- Composite Index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- BRIN สำหรับ Log Table ขนาดใหญ่
CREATE INDEX idx_logs_ts ON logs USING BRIN(created_at);
// Maintenance
VACUUM & ANALYZE
ทำไมต้อง VACUUM?
PostgreSQL ใช้ MVCC (Multi-Version Concurrency Control) ซึ่งไม่ลบข้อมูลเก่าทันทีเมื่อ UPDATE หรือ DELETE แต่จะสร้าง Row ใหม่และทำให้ Row เก่า "ตาย" (Dead Tuple)
VACUUM ทำหน้าที่เก็บกวาด Dead Tuples เหล่านี้ คืนพื้นที่ Storage และอัปเดต Statistics เพื่อให้ Query Planner ทำงานได้อย่างแม่นยำ
autovacuum ทำงานอัตโนมัติเบื้องหลัง แต่ควรปรับ Settings ให้เหมาะกับ Workload ของระบบ
-- VACUUM ทั่วไป
VACUUM users;
-- VACUUM FULL — คืนพื้นที่กลับ OS
-- ⚠️ Lock Table! ใช้ช่วง Maintenance
VACUUM FULL users;
-- ANALYZE อัปเดต Statistics
ANALYZE users;
-- VACUUM + ANALYZE พร้อมกัน
VACUUM ANALYZE users;
-- ตรวจสอบ Dead Tuples
SELECT relname, n_dead_tup, n_live_tup,
last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
ต้องการเรียนรู้เพิ่มเติมเกี่ยวกับ PostgreSQL?
ดู Extensions และเครื่องมือที่ช่วย Optimize PostgreSQL