PostgreSQL Tuning

Optimizes PostgreSQL server configurations and query performance for production workloads. Covers memory allocation (shared_buffers, work_mem, effective_cache_size), connection pooling with PgBouncer, WAL and checkpoint tuning, autovacuum configuration, parallel query settings, statistics analysis with pg_stat views, and workload-specific tuning for OLTP, OLAP, and mixed workloads.

Usage

Describe your PostgreSQL setup: version, server specs (RAM, CPU, disk type), workload type (OLTP, OLAP, mixed), number of connections, database size, and current performance issues. Provide current postgresql.conf settings if available. The skill provides tuned configuration values with explanations and monitoring queries to verify improvements.

Examples

• "Tune PostgreSQL 16 on a 32GB RAM / 8 CPU server running an OLTP workload with 200 connections"
• "My autovacuum can't keep up with a high-write table (50M rows, 100k updates/hour) — how do I fix it?"
• "Configure WAL and checkpoint settings to reduce I/O spikes on a write-heavy workload with SSD storage"
• "Diagnose why my PostgreSQL queries suddenly slowed down — what pg_stat queries should I run?"

Guidelines

• Set shared_buffers to 25% of RAM (max ~8GB), effective_cache_size to 75% of RAM for query planner hints
• Size work_mem carefully: it is per-sort-operation, not per-query — too high causes OOM with many connections
• Use PgBouncer in transaction mode for connection pooling when you have more app connections than needed
• Tune checkpoint_completion_target to 0.9 and increase max_wal_size to reduce checkpoint frequency on write-heavy loads
• Configure per-table autovacuum settings for high-churn tables instead of relying on global defaults
• Monitor pg_stat_user_tables for dead tuple ratio and last autovacuum times to catch vacuum issues early
• Use pg_stat_statements to identify the most time-consuming queries by total execution time
• Set random_page_cost to 1.1 for SSD storage (default 4.0 assumes spinning disks) for better index usage