[Epic] 🐘 Performance - PostgreSQL Database Tuning & Optimization #1354
Description
🐘 Performance - PostgreSQL Database Tuning & Optimization
Goal
Implement comprehensive PostgreSQL tuning strategy to optimize database performance:
- Optimize PostgreSQL server configuration (work_mem, shared_buffers, effective_cache_size)
- Tune connection pooling for gunicorn multi-worker environment
- Configure query optimization settings (statement_timeout, random_page_cost, etc.)
- Optimize autovacuum and maintenance for production workloads
- Enable prepared statement caching for frequently executed queries
- Configure slow query logging and performance monitoring
- Optimize WAL and checkpoint settings for write performance
- Implement connection management best practices
This dramatically improves database performance, scalability, and resource efficiency by 30-70% through proper PostgreSQL configuration and tuning.
Why Now?
PostgreSQL tuning is critical for production deployments:
- Query Performance: Reduce query time by 30-50% with proper memory settings
- Scalability: Support 5-10x more concurrent connections with optimized pooling
- Resource Efficiency: Reduce database CPU/memory usage by 30-40%
- Write Performance: Improve write throughput by 50-100% with WAL tuning
- Reliability: Prevent connection exhaustion and query timeouts
- Cost Savings: Right-size database resources based on actual workload
📖 User Stories
US-1: DevOps Engineer - Optimized Production Database
As a DevOps Engineer
I want PostgreSQL properly tuned for production workloads
So that the database performs efficiently under high load
Acceptance Criteria:
Given I deploy MCP Gateway to production
When the database receives 100+ concurrent connections
Then PostgreSQL should handle the load without connection exhaustion
And query performance should remain consistent
And database CPU usage should stay below 70%
Given I monitor database performance
When checking PostgreSQL configuration
Then shared_buffers should be 25% of RAM (up to 8GB)
And effective_cache_size should be 50-75% of RAM
And work_mem should be tuned for the workload
And autovacuum should be properly configured
Given I review slow query logs
When queries exceed statement_timeout
Then the database should terminate long-running queries
And slow queries should be logged for analysis
And query plans should be optimized based on logsTechnical Requirements:
- PostgreSQL configuration file with production settings
- Connection pooling optimized for gunicorn workers
- Statement timeout to prevent runaway queries
- Autovacuum tuned for write-heavy workloads
- Monitoring and alerting for database health
US-2: API Client - Reliable Database Connections
As an API Client
I want database connections to be reliable and not time out
So that my API requests succeed consistently
Acceptance Criteria:
Given I make API requests to MCP Gateway
When the application uses connection pooling
Then connections should be reused efficiently
And stale connections should be detected and recycled
And connection pool should not exhaust under load
Given I make a long-running API request
When the query takes >30 seconds
Then the database should terminate the query with statement_timeout
And the API should return a timeout error
And the connection should be returned to the pool
Given the application restarts
When new connections are established
Then PostgreSQL should accept connections immediately
And connection pool should warm up properly
And no connection errors should occur during startupTechnical Requirements:
- Connection pool size tuned for gunicorn workers
- pool_pre_ping enabled to detect stale connections
- pool_recycle configured to refresh connections
- Statement timeout configured at connection level
- Graceful handling of connection errors
US-3: Database Administrator - Optimized Maintenance
As a Database Administrator
I want PostgreSQL maintenance optimized for production
So that VACUUM, ANALYZE, and backups don't impact performance
Acceptance Criteria:
Given I review autovacuum settings
When tables are updated frequently
Then autovacuum should run regularly without blocking queries
And table bloat should be minimal (<10%)
And statistics should be up-to-date for query planner
Given I perform database backups
When pg_dump or WAL archiving runs
Then backups should complete in <30 minutes for 10GB database
And backup process should not impact API performance
And WAL archiving should not fill up disk space
Given I analyze database statistics
When checking index usage
Then unused indexes should be identified
And index bloat should be minimal
And ANALYZE should run after major data changesTechnical Requirements:
- Autovacuum parameters tuned (scale_factor, threshold, cost_limit)
- WAL archiving configured for backups
- Checkpoint settings optimized for write workload
- pg_stat_statements enabled for query analysis
- Regular ANALYZE scheduled after data imports
🏗 Architecture
Current State (Default Settings)
# Default PostgreSQL settings (suboptimal for production)
shared_buffers = 128MB # Too small for production
effective_cache_size = 4GB # Generic default
work_mem = 4MB # Too small for complex queries
maintenance_work_mem = 64MB # Too small for VACUUM
max_connections = 100 # May be insufficient
# SQLAlchemy defaults
pool_size = 200 # Too large (200 * workers = 1000+ connections!)
max_overflow = 10 # Additional connections
pool_timeout = 60 # 60 second timeout
pool_recycle = 3600 # 1 hour connection recycling
Problems:
- ❌ Connection pool size too large for multi-worker setup (200 * 4 workers = 800 connections!)
- ❌ No statement timeout → runaway queries can block the database
- ❌ Default autovacuum settings → table bloat on write-heavy workloads
- ❌ No prepared statement cache → repeated query planning overhead
- ❌ No slow query logging → can't identify performance bottlenecks
Optimized Configuration
# Optimized PostgreSQL settings (postgresql.conf)
# Assuming 16GB RAM server
# Memory Settings
shared_buffers = 4GB # 25% of RAM (capped at 8GB)
effective_cache_size = 12GB # 75% of RAM (OS + PG cache)
work_mem = 32MB # Per-operation memory (tune based on workload)
maintenance_work_mem = 1GB # For VACUUM, CREATE INDEX, etc.
max_connections = 200 # Accommodate all app connections
# Query Planner
random_page_cost = 1.1 # Assumes SSD storage
effective_io_concurrency = 200 # Concurrent I/O operations (SSD)
default_statistics_target = 100 # Query planner statistics accuracy
# WAL and Checkpoints
wal_buffers = 16MB # WAL buffer size
checkpoint_completion_target = 0.9 # Smooth checkpoints over 90% of interval
checkpoint_timeout = 15min # Checkpoint frequency
max_wal_size = 4GB # Maximum WAL size before checkpoint
min_wal_size = 1GB # Minimum WAL size to keep
# Autovacuum (tuned for write-heavy workload)
autovacuum_max_workers = 4 # Parallel vacuum workers
autovacuum_naptime = 10s # Check interval (default: 1min)
autovacuum_vacuum_scale_factor = 0.05 # Vacuum when 5% of table changes (default: 20%)
autovacuum_analyze_scale_factor = 0.025 # Analyze when 2.5% changes (default: 10%)
autovacuum_vacuum_cost_limit = 2000 # I/O limit for autovacuum (default: 200)
# Logging and Monitoring
log_min_duration_statement = 1000 # Log queries > 1 second
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on # Log checkpoint activity
log_connections = on # Log new connections
log_disconnections = on # Log disconnections
log_lock_waits = on # Log lock waits
shared_preload_libraries = 'pg_stat_statements' # Query statistics
# Statement Timeout (set at connection level in SQLAlchemy)
statement_timeout = 30000 # 30 seconds (can override per query)
# Prepared Statements
max_prepared_transactions = 100 # For 2PC if needed
# Optimized SQLAlchemy connection pool settings
# config.py
# Connection Pool Configuration (tuned for 4 gunicorn workers)
DB_POOL_SIZE = 20 # 20 connections per worker
DB_MAX_OVERFLOW = 5 # +5 overflow connections
DB_POOL_TIMEOUT = 30 # 30 second timeout
DB_POOL_RECYCLE = 1800 # 30 minute connection recycling
DB_POOL_PRE_PING = True # Test connections before use
# PostgreSQL-Specific Settings
PG_STATEMENT_TIMEOUT = 30000 # 30 seconds (milliseconds)
PG_CONNECT_TIMEOUT = 10 # 10 second connection timeout
PG_KEEPALIVES_IDLE = 30 # TCP keepalive (already configured)
PG_PREPARED_STATEMENT_CACHE = 100 # Cache up to 100 prepared statements
# Total connections: (20 + 5) * 4 workers = 100 connections (well below max_connections=200)Connection Pool Architecture
graph TD
A[Gunicorn Master] --> W1[Worker 1]
A --> W2[Worker 2]
A --> W3[Worker 3]
A --> W4[Worker 4]
W1 --> P1[Pool: 20 + 5 overflow]
W2 --> P2[Pool: 20 + 5 overflow]
W3 --> P3[Pool: 20 + 5 overflow]
W4 --> P4[Pool: 20 + 5 overflow]
P1 --> PG[(PostgreSQL<br/>max_connections=200)]
P2 --> PG
P3 --> PG
P4 --> PG
PG --> SB[shared_buffers<br/>4GB]
PG --> EC[effective_cache_size<br/>12GB]
style PG fill:#336791
style SB fill:#51cf66
style EC fill:#51cf66
Total Connections:
- 4 workers × (20 pool + 5 overflow) = 100 connections
- Well below PostgreSQL `max_connections = 200`
- Leaves headroom for admin connections and other services
📋 Implementation Tasks
Phase 1: PostgreSQL Server Configuration ✅
Memory Settings
- Calculate optimal `shared_buffers` (25% of RAM, max 8GB)
- Calculate optimal `effective_cache_size` (50-75% of RAM)
- Calculate optimal `work_mem` based on workload (start with 32MB)
- Set `maintenance_work_mem` for VACUUM/INDEX (1GB for 16GB RAM)
- Document calculations in configuration guide
Query Planner Settings
- Set `random_page_cost = 1.1` for SSD storage
- Set `effective_io_concurrency = 200` for SSD
- Set `default_statistics_target = 100` for better query plans
- Enable `jit = on` for query JIT compilation (PostgreSQL 11+)
Connection Settings
- Set `max_connections = 200` to accommodate all workers
- Set `max_prepared_transactions = 100` if using 2PC
- Configure connection limits per user/database if needed
Phase 2: WAL and Checkpoint Optimization ✅
WAL Configuration
- Set `wal_buffers = 16MB` (default is often too small)
- Set `max_wal_size = 4GB` to reduce checkpoint frequency
- Set `min_wal_size = 1GB` to prevent WAL file churn
- Set `wal_compression = on` to reduce WAL size (PostgreSQL 9.5+)
- Set `wal_level = replica` for replication/backup
Checkpoint Tuning
- Set `checkpoint_timeout = 15min` for production workload
- Set `checkpoint_completion_target = 0.9` to smooth I/O
- Set `max_wal_senders = 3` if using streaming replication
- Monitor checkpoint frequency and adjust if needed
Phase 3: Autovacuum Optimization ✅
Autovacuum Configuration
- Set `autovacuum = on` (should be enabled by default)
- Set `autovacuum_max_workers = 4` for parallel vacuum
- Set `autovacuum_naptime = 10s` for frequent checks
- Set `autovacuum_vacuum_scale_factor = 0.05` (5% threshold)
- Set `autovacuum_analyze_scale_factor = 0.025` (2.5% threshold)
- Set `autovacuum_vacuum_cost_limit = 2000` for faster vacuum
- Set `autovacuum_vacuum_cost_delay = 2ms` to reduce impact
Table-Specific Tuning (if needed)
- Identify high-churn tables (tools, resources, team_members)
- Set aggressive autovacuum for specific tables via ALTER TABLE
- Monitor table bloat with pg_stat_user_tables
- Schedule manual VACUUM ANALYZE after bulk operations
Phase 4: Connection Pool Optimization ✅
Calculate Optimal Pool Size
- Determine number of gunicorn workers (default: 4)
- Calculate pool size per worker: `pool_size = max_connections / (workers + buffer)`
- Set `DB_POOL_SIZE = 20` (per worker)
- Set `DB_MAX_OVERFLOW = 5` (emergency overflow)
- Total connections: (20 + 5) × 4 = 100 (leaves headroom)
Connection Pool Settings
- Enable `pool_pre_ping = True` in db.py (already enabled)
- Set `pool_recycle = 1800` (30 minutes) in config.py
- Set `pool_timeout = 30` (30 seconds) in config.py
- Configure TCP keepalive (already configured in db.py)
- Add connection pool metrics to Prometheus
PostgreSQL Connection Parameters
- Add `statement_timeout=30000` to connection string
- Add `connect_timeout=10` to connection string
- Add `application_name=mcpgateway` for monitoring
- Add `options='-c statement_timeout=30s'` via connect_args
Phase 5: Prepared Statement Cache ✅
Enable Prepared Statements
- Add `use_prepared_statements=True` to SQLAlchemy engine
- Set `prepared_statement_cache_size=100` in connect_args
- Configure `max_prepared_transactions` in postgresql.conf
- Monitor prepared statement usage in pg_prepared_statements
Query Optimization
- Identify frequently executed queries in service layer
- Use SQLAlchemy compiled_cache for query compilation
- Enable query result caching for expensive queries
- Monitor query cache hit ratio
Phase 6: Logging and Monitoring ✅
Slow Query Logging
- Enable `log_min_duration_statement = 1000` (1 second)
- Configure `log_line_prefix` with useful metadata
- Enable `log_checkpoints = on` to monitor checkpoint frequency
- Enable `log_connections = on` for connection tracking
- Enable `log_disconnections = on` for connection tracking
- Enable `log_lock_waits = on` to detect lock contention
pg_stat_statements Extension
- Add `shared_preload_libraries = 'pg_stat_statements'` to postgresql.conf
- Create extension: `CREATE EXTENSION pg_stat_statements;`
- Set `pg_stat_statements.track = all` to track all queries
- Set `pg_stat_statements.max = 10000` for query history
- Create monitoring query to view top slow queries
- Document how to query pg_stat_statements
Prometheus Metrics
- Export connection pool metrics (active, idle, overflow)
- Export query duration histogram
- Export database connection errors
- Export checkpoint and autovacuum metrics (via postgres_exporter)
- Create Grafana dashboard for database monitoring
Phase 7: Configuration Management ✅
Add Environment Variables
- Add `PG_SHARED_BUFFERS` to .env.example
- Add `PG_WORK_MEM` to .env.example
- Add `PG_MAINTENANCE_WORK_MEM` to .env.example
- Add `PG_EFFECTIVE_CACHE_SIZE` to .env.example
- Add `PG_STATEMENT_TIMEOUT` to .env.example (default: 30000ms)
- Add `PG_MAX_CONNECTIONS` to .env.example
Update config.py
- Add PostgreSQL-specific settings section
- Add validator for PostgreSQL connection string
- Add property to generate connection string with options
- Add method to calculate optimal pool size
- Document all PostgreSQL settings
Connection String Builder
- Create utility function to build optimized connection string
- Add statement_timeout to connect_args
- Add connect_timeout to connect_args
- Add application_name to connect_args
- Support passing custom PostgreSQL options
Phase 8: Documentation ✅
Create PostgreSQL Tuning Guide
- Document memory settings calculation (shared_buffers, work_mem)
- Document connection pool sizing for gunicorn
- Document autovacuum tuning process
- Document how to analyze slow queries
- Document backup and WAL archiving setup
- Add troubleshooting section
Update CLAUDE.md
- Add section on PostgreSQL optimization
- Document connection pool configuration
- Document how to monitor database performance
- Add guidelines for production deployment
- Document common performance issues and solutions
Create Operations Runbook
- Document how to check database health
- Document how to identify slow queries
- Document how to tune autovacuum
- Document how to handle connection exhaustion
- Document backup and recovery procedures
Phase 9: Testing and Benchmarking ✅
Before/After Benchmarks
- Benchmark query performance before tuning
- Benchmark connection pool exhaustion threshold
- Benchmark write throughput (inserts/updates per second)
- Benchmark autovacuum impact on performance
- Document baseline metrics
Load Testing with Optimized Settings
- Run wrk load test: `wrk -t4 -c100 -d60s http://localhost:4444/tools\`
- Monitor PostgreSQL metrics during load test
- Monitor connection pool utilization
- Verify no connection exhaustion under load
- Verify query performance remains consistent
Stress Testing
- Test with 4x normal load (400 concurrent connections)
- Test connection pool overflow behavior
- Test statement timeout enforcement
- Test autovacuum impact during writes
- Verify graceful degradation under extreme load
Performance Comparison
- Compare default settings vs optimized settings
- Measure query latency improvement (p50, p95, p99)
- Measure throughput improvement (requests/second)
- Measure database CPU usage reduction
- Document performance improvements
Phase 10: Container and Deployment ✅
Docker PostgreSQL Configuration
- Create optimized postgresql.conf for container
- Mount configuration as volume in docker-compose.yml
- Configure PostgreSQL environment variables
- Set proper resource limits (memory, CPU)
- Document container deployment best practices
Kubernetes Deployment
- Create ConfigMap for postgresql.conf
- Configure persistent volume for data
- Set resource requests and limits
- Configure liveness and readiness probes
- Document Kubernetes deployment
Production Deployment Checklist
- Verify PostgreSQL version (>= 13 recommended)
- Apply optimized postgresql.conf
- Restart PostgreSQL to apply settings
- Verify connection pool configuration
- Enable pg_stat_statements extension
- Configure backup and WAL archiving
- Set up monitoring and alerting
Phase 11: Monitoring and Alerting ✅
Database Health Checks
- Monitor connection pool saturation (alert at 80%)
- Monitor query duration (alert if p95 > 1s)
- Monitor database connections (alert at 150/200)
- Monitor table bloat (alert if >20%)
- Monitor autovacuum lag (alert if behind)
Performance Metrics
- Track query execution time by endpoint
- Track connection pool metrics (active, idle, overflow)
- Track cache hit ratio (should be >99%)
- Track transaction rate (commits, rollbacks)
- Track checkpoint frequency and duration
Alerts and Thresholds
- Alert: Connection pool exhaustion (>80% utilization)
- Alert: Slow queries (>5s duration)
- Alert: High database CPU (>80% for 5 minutes)
- Alert: Autovacuum lag (last run >1 hour ago)
- Alert: Table bloat (>20% dead tuples)
Phase 12: Quality Assurance ✅
Code Quality
- Run `make autoflake isort black` to format code
- Run `make flake8` and fix any issues
- Run `make pylint` and address warnings
- Pass `make verify` checks
Testing
- Add unit tests for connection pool configuration
- Add integration tests for PostgreSQL-specific features
- Test connection timeout behavior
- Test statement timeout enforcement
- Verify all existing tests pass with PostgreSQL
- Test with both SQLite and PostgreSQL
✅ Success Criteria
- PostgreSQL configuration optimized for production
- Connection pool sized appropriately for gunicorn workers
- Statement timeout configured to prevent runaway queries
- Autovacuum tuned for write-heavy workloads
- Slow query logging enabled and configured
- pg_stat_statements extension enabled
- Query performance improved by 30-50%
- Database CPU usage reduced by 30-40%
- Connection pool never exhausts under normal load
- Documentation complete and accurate
- Monitoring and alerting configured
🏁 Definition of Done
- PostgreSQL configuration file created with optimized settings
- Connection pool settings updated in config.py
- Statement timeout configured at connection level
- Autovacuum settings tuned for production
- Prepared statement cache enabled
- Slow query logging enabled
- pg_stat_statements extension configured
- Connection pool metrics exported to Prometheus
- Before/after performance benchmarks documented
- Query performance improved 30-50%
- Database CPU usage reduced 30-40%
- No connection exhaustion under load testing
- Documentation complete (tuning guide, operations runbook)
- Code passes `make verify` checks
- Ready for production deployment
📝 Additional Notes
🔹 Memory Settings Calculation:
- shared_buffers: 25% of RAM, capped at 8GB (e.g., 4GB for 16GB server)
- effective_cache_size: 50-75% of RAM (e.g., 12GB for 16GB server)
- work_mem: Start with 32MB, tune based on query complexity (complex queries may need 128MB+)
- maintenance_work_mem: 5-10% of RAM for VACUUM/INDEX operations (e.g., 1GB for 16GB server)
🔹 Connection Pool Sizing Formula:
pool_size_per_worker = (max_connections - buffer) / (workers + 1)
Example:
max_connections = 200
workers = 4
buffer = 20 (admin + monitoring)
pool_size_per_worker = (200 - 20) / (4 + 1) = 36 connections per worker
Use: pool_size=30, max_overflow=6 per worker = 36 total per worker
Total: 36 * 4 workers = 144 connections (leaves 56 headroom)
🔹 When to Tune Autovacuum:
- ✅ Tables with frequent UPDATEs/DELETEs (team_members, tools, resources)
- ✅ Tables with >10% dead tuples (check pg_stat_user_tables)
- ✅ Tables showing bloat (pg_stat_user_tables.n_dead_tup / n_live_tup > 0.2)
- ❌ Small tables (<1000 rows) - default autovacuum is fine
- ❌ Append-only tables (metrics, logs) - VACUUM not needed
🔹 PostgreSQL Version Considerations:
- PostgreSQL 13+: Recommended, improved index performance
- PostgreSQL 14+: Better query parallelism and monitoring
- PostgreSQL 15+: MERGE statement, improved VACUUM
- PostgreSQL 16+: Improved logical replication and parallel query
- Use latest stable version for best performance
🔹 SSD vs HDD Settings:
- SSD: `random_page_cost=1.1`, `effective_io_concurrency=200`
- HDD: `random_page_cost=4.0`, `effective_io_concurrency=2`
- Modern production deployments should always use SSD
🔹 Common Pitfalls to Avoid:
- ❌ Setting `shared_buffers` > 8GB (diminishing returns, hurts performance)
- ❌ Setting `work_mem` too high (multiplied by concurrent queries = OOM)
- ❌ Pool size too large for workers (200 * 4 = 800 connections!)
- ❌ Forgetting to restart PostgreSQL after config changes
- ❌ Not monitoring connection pool utilization
- ❌ Disabling autovacuum (leads to table bloat and performance degradation)
🔹 Testing Checklist:
- ✅ EXPLAIN ANALYZE shows improved query plans
- ✅ Connection pool never exhausts (monitor active connections)
- ✅ Queries timeout after statement_timeout
- ✅ Autovacuum runs regularly without blocking queries
- ✅ No connection errors during high load
- ✅ Query latency improved by 30-50%
- ✅ Database CPU usage reduced by 30-40%
🔗 Related Issues
- [Epic] 💾 Performance - Database Indexing Optimization #1353 Database Indexing Optimization (complementary optimization)
- [Epic] 💾 Performance - Redis Endpoint Response Caching #1296 Redis Caching (reduces database load)
- [Epic] ⚙️ Performance - Production Server Tuning #1297 Server Tuning (gunicorn worker configuration)
- Feature Request: Implement Session Persistence & Pooling for Improved Performance and State Continuity #975 Session Pooling (connection management)
- Epic: REST API and UI Pagination for Large-Scale Multi-Tenant Deployments #1224 API Pagination (efficient query patterns)