postgres

# PostgreSQL ## Purpose Operate PostgreSQL well: understand what MVCC costs you, why the table is bloated, why the connection count matters more than you think, and how to change a large table without locking it. ## When to Use - Designing schemas or indexes for Postgres. - Diagnosing bloat, slow queries, or lock contention. - Configuring connection pooling and replication. - Running a migration on a large table without downtime. ## Capabilities - Index types: B-tree, GIN, GiST, BRIN, partial, expression, covering. - MVCC, dead tuples, autovacuum tuning, and transaction ID wraparound. - Connection pooling with PgBouncer and the pooling modes. - Partitioning: declarative range and list partitions. - JSONB indexing and query patterns. - Streaming replication, replicas, and replication lag. ## Inputs - The schema, the table sizes, and the query patterns. - `pg_stat_statements` output for the slow-query question. - Server configuration and Postgres version. ## Outputs - Indexes that match the access patterns, with no unused ones. - Autovacuum settings appropriate to the write volume. - Migrations that acquire only brief locks. ## Workflow 1. **Find the real slow queries** — `pg_stat_statements` ordered by total time, not by mean. A 30ms query executed a million times costs more than a 4-second query run once. 2. **Index for the access pattern** — Equality columns first in a composite index, then the range or sort column. Add partial indexes for the filters that domina...