How do you implement PostgreSQL performance optimization for production databases?
PostgreSQL performance optimization spans query tuning, indexing, configuration, and architecture. Query analysis: use EXPLAIN ANALYZE to understand query execution plans, identify sequential scans on large tables (create indexes), spot nested loop joins on large datasets (consider hash joins via workmem increase), and detect sort operations spilling to disk (increase workmem). Indexing: B-tree for equality and range queries (the default, covers most cases), GIN for array contains, full-text search, and JSONB queries, GiST for geometric and range type queries, BRIN for large sorted tables (time-series data), and partial indexes for filtered queries (CREATE INDEX ON orders(createdat) WHERE status = 'active'). Configuration tuning: sharedbuffers (25% of available RAM for dedicated database servers), effectivecachesize (50-75% of available RAM), workmem (RAM per sort/hash operation, set based on concurrent queries: availablememory / maxconnections / 4), maintenanceworkmem (for VACUUM, CREATE INDEX, typically 256MB-1GB), walbuffers (64MB for write-heavy workloads), and randompagecost (1.1 for SSD storage, default 4.0 is for spinning disk). Connection management: implement connection pooling (PgBouncer in transaction mode, Supavisor for Supabase), set maxconnections appropriately (most applications need far fewer than the default 100), and monitor connection usage with pgstatactivity. Vacuum and maintenance: autovacuum must be enabled (handles dead tuple cleanup and statistics updates), tune autovacuum for high-update tables (lower threshold, higher scale factor), and monitor bloat with pgstattuple. Partitioning: use declarative partitioning for large tables (range partition by date is most common), enables efficient partition pruning and maintenance. Monitoring: use pgstatstatements for query performance tracking, pgstatusertables for table access patterns, and implement alerting on long-running queries, lock waits, and replication lag.