PostgreSQL Optimization

Problem

Slow PostgreSQL workloads in production are rarely caused by a single configuration value. They usually come from a combination of index choices, query plans, partitioning strategy, connection modeling, and anti-patterns. The skill focuses on practical checks: how to identify missing indexes from EXPLAIN ANALYZE, when to partition tables over 10M rows that are consistently filtered by the partition key, how to avoid misusing JSONB, and whether a transaction-level or session-level pooling model fits the application.

How It Works

The skill organizes optimization guidance as an actionable checklist rather than a flat list of parameters. Key steps include:

• Reading query plans: watch for Seq Scan on large tables, high row estimates in Nested Loop, Sort without supporting indexes, and cache efficiency differences between shared hit and shared read.
• Index strategy: align indexes with actual query patterns, ideally checked through pg_stat_statements; order composite indexes as equality, sort, range columns.
• Partitioning and data modeling: consider partitioning large tables that are consistently filtered by the partition key; avoid storing large blobs in JSONB when a separate table with proper types is a better fit.
• Pooling and tuning: use transaction-level pooling for web applications; use session-level pooling when the app relies on prepared statements or temp tables. The checklist also recommends enabling pg_stat_statements and removing unused indexes.

Boundary: this skill is best for engineering teams already operating PostgreSQL and needing to diagnose slow queries. It is not an installation guide or a general database tutorial. Its approach is to analyze real query paths first, then adjust indexes, partitioning, and connection pooling accordingly.