Advanced PostgreSQL Performance Tuning

Act as a PostgreSQL database reliability engineer who has tuned databases for companies like Instagram, Reddit, and GitHub, handling petabyte-scale deployments with 99.999% uptime requirements. Generate a comprehensive PostgreSQL performance tuning strategy for a specific workload type (OLTP, OLAP, or hybrid) including configuration changes, indexing strategies, query optimization, and monitoring setup. Begin with configuration tuning for postgresql.conf including shared_buffers calculation (15-25% of total RAM for dedicated server), effective_cache_size estimation (50-75% of total RAM), work_mem adjustment for sorts and hash tables (1-16MB per operation based on concurrent connections), maintenance_work_mem for vacuum and index creation (64MB-2GB), max_connections limiting to 50-500 (each connection consumes work_mem), checkpoint settings including checkpoint_timeout (5-15 minutes), max_wal_size (1-3x of checkpoint segment count), and checkpoint_completion_target (0.7-0.9). Create indexing strategy including B-tree indexes for equality and range queries, covering indexes for index-only scans, partial indexes for subset of rows (e.g., WHERE active = true), expression indexes for functions (e.g., LOWER(email)), GIN indexes for full-text search and array operations, GiST indexes for geospatial and range types, and BRIN indexes for very large tables with natural ordering. Implement query optimization including ANALYZE frequency for updated statistics, auto-vacuum tuning for dead tuple cleanup, statement timeout settings for query kill switches, prepared statements for repeated query plans, and query rewriting avoiding functions in WHERE clauses, using EXISTS over IN for subqueries, and eliminating SELECT * in production queries. Add monitoring setup including pg_stat_statements for query performance tracking, pg_stat_activity for connection monitoring, pg_stat_user_tables for table access patterns, pg_stat_bgwriter for checkpoint efficiency, pg_locks for deadlock detection, pg_statio_user_indexes for index usage analysis, and custom metrics for connection pooler monitoring. Include maintenance strategies including regular VACUUM ANALYZE scheduling, autovacuum tuning with scale factor and threshold adjustments, index maintenance with REINDEX during low-traffic periods, table partitioning for time-series data, and table bloat monitoring with remediation. Provide backup and recovery strategy including pg_basebackup for physical backups, pg_dump for logical backups, WAL archiving for point-in-time recovery, and restore testing schedule.