Postgres Expert-AI Postgres expert assistant

Example

Workflow: 1) Identify heavy queries with pg_stat_statements: SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20; 2) Reproduce the query and gather plan: EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <problem_query>; 3) Apply a fix: CREATE INDEX CONCURRENTLY idx_orders_customer_date ON orders (customer_id, created_at); 4) Validate: re-run EXPLAIN ANALYZE and compare runtime and buffers; 5) If needed, change server parameters (example): set work_mem = '64MB' for complex sorts/hashes for a session, increase shared_buffers to ~25% of RAM, tune maintenance_work_mem for big VACUUM/REINDEX jobs. Tools and extensions used: pg_stat_statements, auto_explain, EXPLAIN (ANALYZE, BUFFERS), pgbadger (log analysis), pg_repack (bloat removal).

Scenario

E-commerce checkout is intermittently slow under load. Postgres Expert identifies that a complex join between orders and promotions is scanned repeatedly. Steps applied: capture slow SQL from pg_stat_activity, use EXPLAIN ANALYZE to show sequential scans, introduce a covering index created with CREATE INDEX CONCURRENTLY, recommend partitioning old orders by month to reduce working set, suggest using pgbouncer to limit connections and protect DB from connection storms. Result: median checkout latency drops from 350ms to 45ms and tail latency improves dramatically.

Example

Architectural patterns and commands: - Physical (streaming) replication: configure primary with wal_level = replica, max_wal_senders, archive_mode = on; create a standby with pg_basebackup: pg_basebackup -h primary -D /var/lib/postgresql/14/main -U replicator -P -X stream. - Logical replication for selective table replication: CREATE PUBLICATION mypub FOR TABLE orders; on subscriber: CREATE SUBSCRIPTION mysub CONNECTION 'host=primary dbname=app user=replicator' PUBLICATION mypub; - Backups and PITR with pgBackRest or native WAL archiving; an example archive_command (illustrative): archive_command = 'pgbackrest --stanza=main archive-push %p'. - Automated failover: use Patroni (etcd/consul) or repmgr to orchestrate promotion, plus HAProxy for transparent redirecting of app traffic.

Scenario

A SaaS platform needs 99.99% availability and zero data-loss for a critical region. Proposed design: primary in AZ-A with synchronous standby in AZ-B (synchronous_commit = local or 'remote_write' depending on version) to avoid acknowledged writes loss, asynchronous standby in AZ-C for cross-region reads and analytics. Use pgBackRest to keep compressed, incremental backups to S3 and enable WAL archiving for point-in-time recovery. Provide a failover runbook: how to detect promotion, reconfigure DNS/HAProxy, and reconfigure old primary as a follow-up standby. Also include regular chaos tests (controlled failover drills) and monitoring queries (SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), and SELECT application_name, state FROM pg_stat_replication;).

Example

Best-practice patterns and code snippets: - Partitioning (range example): CREATE TABLE measurement (id bigserial, device_id int, ts timestamptz, value double precision) PARTITION BY RANGE (ts); CREATE TABLE measurement_2025_09 PARTITION OF measurement FOR VALUES FROM ('2025-09-01') TO ('2025-10-01'); - Safe zero-downtime schema change pattern: 1) ALTER TABLE t ADD COLUMN new_col type NULL; 2) Backfill in controlled batches: UPDATE t SET new_col = <expr> WHERE <batch_condition>; 3) ALTER TABLE t ALTER COLUMN new_col SET DEFAULT <val>; 4) ALTER TABLE t ALTER COLUMN new_col SET NOT NULL; - JSONB/functional indexing: CREATE INDEX ON events USING gin (data jsonb_path_ops) or CREATE INDEX idx_user_lowername ON users (lower(username)); - Migration tools: pg_dump/pg_restore for same major versions, pg_upgrade for in-place major upgrades, pgloader or logical replication for cross-engine migrations.

Scenario

A telemetry system ingesting millions of rows per hour must keep query latency low for recent data and drop old data after 90 days. Postgres Expert prescribes monthly range partitions by ts, a background job that DETACHs and DROP PARTITION older than retention, primary index on (device_id, ts) per partition, COPY-based bulk ingest into current partition with COPY FROM STDIN for speed, and retention automation. Also: use partial indexes for hot queries and materialized views refreshed incrementally for expensive aggregations.