Operate and tune Postgres at scale.
2 topics
PostgreSQL is the most advanced open-source relational database. Master its SQL: window functions, CTEs, recursive CTEs, LATERAL joins, UPSERT, and RETURNING. Data modeling: normalization, appropriate data types (timestamptz, JSONB, arrays, range types), constraints, and generated columns.
2 resources
Index types: B-tree (default), Hash, GiST (geometric/full-text), GIN (arrays/JSONB), BRIN (large ordered tables). Use EXPLAIN ANALYZE to understand execution plans. Strategies: index WHERE/JOIN/ORDER BY columns, partial indexes, covering indexes (INCLUDE), and avoid index-unfriendly patterns. Monitor with pg_stat_statements.
Key settings: shared_buffers (25% RAM), effective_cache_size, work_mem, max_connections. Security: scram-sha-256 auth, pg_hba.conf restrictions, SSL/TLS, row-level security (RLS), least-privilege GRANT/REVOKE. Connection pooling with PgBouncer is essential for production workloads.
Backup: pg_dump (logical), pg_basebackup (physical), WAL archiving for PITR. Use pgBackRest or Barman in production. Replication: streaming (physical HA), logical (selective, cross-version). High availability: Patroni for automatic failover. Always test restores — untested backups are not backups.
PostGIS (geospatial), pgvector (AI embeddings), TimescaleDB (time-series), Citus (distributed), pg_stat_statements (query stats). Full-text search with tsvector/tsquery + GIN indexes. JSONB for document storage with indexing. Partitioning (range, list, hash) for managing large tables.
Monitor: connections, cache hit ratio (>99%), replication lag, locks, table bloat. Tools: pg_stat_activity, pg_stat_user_tables, Grafana + postgres_exporter. Maintenance: VACUUM (autovacuum tuning), ANALYZE (planner statistics), REINDEX, and pg_repack for online table reorganization.