Title: Postgresql Performance Tuning — LearnCoachAssist

Q: How do you find the slowest queries in Postgres?
A: Enable pg_stat_statements extension. Then:
SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

Q: How do you read an EXPLAIN plan?
A: Read bottom-up: leaf nodes first (table scans / index scans), then joins, then aggregates. Look for high cost, rows, and actual time.

Q: Difference between EXPLAIN, EXPLAIN ANALYZE, EXPLAIN (BUFFERS, ANALYZE)?
A: EXPLAIN: planner's estimate only.
EXPLAIN ANALYZE: actually runs the query and reports real times.
EXPLAIN (BUFFERS, ANALYZE): + buffer hits/misses — tells you I/O vs cache.

Q: Why is a Seq Scan faster than an Index Scan sometimes?
A: When the planner expects to return a large fraction of the table — random I/O via the index would be slower than reading sequentially.

Q: Function used to add a B-tree index for equality + range queries?
A: CREATE INDEX ON t (col); — default is B-tree.

Q: Index type for full-text search?
A: GIN on to_tsvector columns.

Q: Index type for geometric / array containment?
A: GiST or GIN.

Q: How to index JSONB key lookups?
A: CREATE INDEX ON t USING GIN (col jsonb_path_ops);

Q: How to make a unique index that ignores NULLs?
A: Postgres treats NULLs as distinct in unique indexes by default — use partial: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL;

Q: Vacuum vs Autovacuum?
A: VACUUM: reclaim dead tuples, update stats. Autovacuum: background daemon that triggers based on dead-tuple thresholds. Tune autovacuum_vacuum_scale_factor for hot tables.

Q: What is bloat?
A: Dead tuples + free space in a table after many UPDATE/DELETEs that VACUUM hasn't reclaimed; causes larger reads.

Q: Tool to inspect bloat?
A: pgstattuple extension or query views like pg_stat_user_tables.

Q: Difference between VACUUM and VACUUM FULL?
A: VACUUM: non-blocking, marks space reusable.
VACUUM FULL: rewrites the table, takes an ACCESS EXCLUSIVE lock, reclaims OS disk space — use sparingly.

Q: Pro alternative to VACUUM FULL without locking?
A: pg_repack extension — rewrites table online.

Q: What does ANALYZE do?
A: Refreshes table statistics used by the planner. Run after large data changes; autovacuum triggers it but you can force manually.

Q: How to make Postgres prefer an index for low-cardinality columns?
A: Often a Bitmap Index Scan is correct. If you really need it, you can SET enable_seqscan = off for testing — but fix the stats or rewrite the query in prod.

Q: Common cause of slow IN (SELECT ...) subquery?
A: Planner may turn it into a hash semi-join. Rewrite as EXISTS or LEFT JOIN ... WHERE NULL for better selectivity.

Q: How to throttle a slow migration?
A: Batch in chunks (UPDATE ... WHERE id BETWEEN ...), commit between batches, and pause for VACUUM.

Q: What's a CTE inlining gotcha pre-PG12?
A: CTEs were optimization fences — the planner couldn't push predicates into them. PG12+ inlines non-recursive CTEs by default; use MATERIALIZED to force the old behavior.

Q: How to find missing indexes?
A: Check pg_stat_user_tables for tables with high seq_scan vs idx_scan ratios, or pg_stat_statements for queries with high mean time + Seq Scan in plan.

Q: Unused index detection?
A: SELECT relname, indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0; — but exclude unique/PK indexes that enforce constraints.

Q: Best work_mem setting?
A: Per-operation memory for sorts/hashes. Set high enough that complex queries don't spill to disk, low enough that 100 concurrent queries don't OOM. Typical: 16–64 MB.

Q: shared_buffers recommendation?
A: ~25% of RAM for dedicated DB servers (with OS cache covering the rest).

Q: effective_cache_size?
A: Hint to the planner about total OS+DB cache — usually ~50–75% of RAM. Doesn't actually allocate memory.

Q: Connection pool — why?
A: Postgres forks a process per connection. Pool with PgBouncer or app-level pool to avoid context-switch storms.

Q: PgBouncer pool modes?
A: Session: client owns connection until disconnect.
Transaction: connection released after COMMIT — most efficient.
Statement: released after each statement; breaks prepared statements.

Q: How to detect lock waits?
A: SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'; or look at pg_locks joined with pg_stat_activity.

Q: Foreign key without index on FK column — problem?
A: Cascading deletes and updates scan the child table; FK columns should usually be indexed.

Q: Difference between SERIAL and IDENTITY in Postgres?
A: SERIAL: old-style, uses sequence + default; permissions can be tricky.
IDENTITY: SQL-standard, cleaner; use GENERATED BY DEFAULT AS IDENTITY.

Q: UUID v4 as primary key — performance concern?
A: Random UUIDs cause index fragmentation. Use UUID v7 (time-ordered) or a separate sortable column for B-tree-friendly inserts.

Q: How to safely add a NOT NULL column to a big table in PG11+?
A: Add column with default (instant metadata-only since PG11), then backfill in chunks, then add NOT NULL.

Q: LATERAL JOIN use case?
A: Evaluate a subquery per outer row — useful for 'top N per group' patterns.

Q: Window function for running totals?
A: SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q: DISTINCT ON vs window function?
A: DISTINCT ON: PG-specific, picks first row per group based on ORDER BY.
ROW_NUMBER() OVER (PARTITION BY ...): portable; filter WHERE rn = 1.

Q: How to enforce serializable isolation?
A: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; — be ready to retry on serialization failures (SQLSTATE 40001).

Q: Common cause of deadlocks?
A: Two transactions locking rows in different orders. Always update in a consistent order (e.g., by PK).

Q: Find current locks blocking a query?
A: SELECT pid, state, query, wait_event FROM pg_stat_activity WHERE state = 'active'; joined with pg_locks.

Q: How to cancel a runaway query?
A: SELECT pg_cancel_backend(pid); — try first. Last resort: pg_terminate_backend(pid);.

Q: Setting that logs slow queries?
A: log_min_duration_statement = 1000 (ms) — logs anything above 1s.

Q: Configuring statement timeout per role?
A: ALTER ROLE app SET statement_timeout = '30s';

Q: How to do read-mostly scaling?
A: Streaming replication to read replicas; route SELECTs to replicas (with awareness of replication lag).

Q: Synchronous vs asynchronous replication?
A: Sync: commit waits for at least one replica — zero data loss, higher latency.
Async: commit returns immediately — possible data loss on failover.

Q: Logical vs physical replication?
A: Physical: WAL stream, byte-for-byte.
Logical: replicates rows by primary key; supports cross-version, subset replication.

Q: Tool to monitor replication lag?
A: SELECT * FROM pg_stat_replication; — look at replay_lag.

Q: Partitioning use case?
A: Huge tables (1B+ rows) with a clear partition key (date, tenant) — improves vacuum, query planning, and lets you drop old data with DROP PARTITION.

Q: Partitioning types?
A: RANGE (dates), LIST (regions), HASH (shard by id).

Q: Common JSONB indexing pitfall?
A: GIN index supports containment (@>) but not arbitrary equality on nested keys; use expression indexes for specific paths.

Q: How to enforce row-level security?
A: CREATE POLICY ... USING (...) + ALTER TABLE ... ENABLE ROW LEVEL SECURITY; — filters rows based on session settings.

Q: Difference between pg_dump and pg_basebackup?
A: pg_dump: logical SQL dump, portable across versions.
pg_basebackup: physical copy of the data dir, used for replicas/restore on same version.

Q: Recovery target options for PITR?
A: recovery_target_time, recovery_target_xid, recovery_target_lsn, recovery_target_name.

Q: Foreign tables — what for?
A: Query external data sources via FDW (file_fdw, postgres_fdw, etc.) as if local tables.

Q: HOT update?
A: Heap-Only-Tuple — update fits on same page and doesn't change indexed columns → no index update. Big perf win for narrow updates.

Q: How to enforce idempotent inserts?
A: INSERT ... ON CONFLICT (key) DO NOTHING or DO UPDATE SET ....

Q: Common pitfall with random sampling SELECT ... ORDER BY random() LIMIT n?
A: Full sort of the whole table. Use TABLESAMPLE BERNOULLI(1) or pre-sample with random values.

Q: Function volatility categories?
A: IMMUTABLE: same input → same output, no DB read.
STABLE: same within a tx.
VOLATILE: anything; can't be inlined or used in indexes.

Q: Why a function index on now() doesn't work?
A: now() is STABLE, not IMMUTABLE — Postgres requires IMMUTABLE for index expressions.

Q: How to expire old data efficiently?
A: Use range partitioning by date and DROP old partitions.

Q: Connection limit options?
A: max_connections (server) and per-database/per-user via ALTER DATABASE / ALTER ROLE.

Q: track_io_timing — what's it for?
A: Enables I/O timing in EXPLAIN (BUFFERS, ANALYZE) and pg_stat_statements. Small overhead, big debug value.

Q: How to migrate a big schema change online?
A: Use tools like pg_repack, gh-ost equivalents, or shadow tables: write to both old + new, backfill, swap.

Q: pg_buffercache — for what?
A: Inspect what's in shared_buffers; tells you cache hit rates per table/index.

Q: Difference between bitmap heap scan and index scan?
A: Index Scan: one row at a time, walks tuples in index order.
Bitmap Heap Scan: builds a bitmap of matching pages, then visits them in physical order — better for many matches scattered across the table.

Q: Stale planner stats — symptom?
A: Plans that pick wrong join order or wrong index. Run ANALYZE table_name;.

Q: default_statistics_target?
A: How many samples ANALYZE collects per column. Default 100; raise to 1000+ for high-cardinality columns where planner is making bad estimates.

Q: Maintenance window tip?
A: Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress.

Q: What blocks autovacuum?
A: Long-running transactions hold a snapshot, preventing dead tuples from being reclaimed — find via SELECT * FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)');

Q: Replication slot risks?
A: Inactive slots prevent WAL deletion → disk fills. Drop unused slots.

Q: pg_terminate_backend vs pg_cancel_backend?
A: cancel: tries to abort the current query.
terminate: kills the entire backend (closes connection).

Q: Streaming replication setup essentials?
A: wal_level=replica, max_wal_senders, archive_mode, pg_basebackup to seed standby, primary_conninfo in standby.

Q: WAL bloat causes?
A: Replication slot stuck, archive_command failing, very long transactions.

Q: How to find idle transactions?
A: SELECT pid, state, age(now(), xact_start) FROM pg_stat_activity WHERE state = 'idle in transaction';

Q: idle_in_transaction_session_timeout?
A: Kills sessions sitting idle in a transaction; safety net against forgotten ROLLBACKs.

Q: Common cause of plan flips with same query?
A: Statistics changed (autovacuum/ANALYZE) → planner estimates differ → different plan.

Q: How to lock a plan in place?
A: Stored procedures with PLAN CACHE MODE hints; or plan_cache_mode = force_generic_plan / force_custom_plan; or use prepared statements judiciously.

Q: Find tables ranked by total time spent in their queries?
A: Join pg_stat_statements against table names (parse with regex) or use the queryid over time to track patterns.

Q: pg_hba.conf — what?
A: Host-Based Auth file: who can connect from where with which auth method (trust, md5, scram-sha-256, peer, cert, ident).

Q: scram-sha-256 vs md5?
A: SCRAM is the modern default — better security, mutual challenge.

Q: Tablespace use case?
A: Place hot tables on fast SSD and archive tables on cheap disks — different physical locations within one cluster.

Q: Backup tool that ships WAL?
A: pgbackrest, WAL-G, barman.

Q: Time-series workload — recommended extension?
A: TimescaleDB — hypertables, continuous aggregates, retention policies.

Q: Vector similarity search extension?
A: pgvector — IVFFlat and HNSW indexes for kNN.

Q: Common gotcha with sequence and INSERT?
A: Manual INSERTs that include id can skip sequence updates — later auto-INSERTs collide. Run SELECT setval('seq', (SELECT MAX(id) FROM t));

Q: How to detect index bloat?
A: pgstattuple on the index, or queries that compute index size vs expected size from row count.

Q: REINDEX CONCURRENTLY restrictions?
A: Can't run inside a transaction; rebuilds in parallel + swaps. Mostly safe but requires extra disk.

Q: Common ANALYZE pitfall on partitioned tables?
A: Stats live per partition; on PG14+ also on parent — make sure ANALYZE runs after big partition swaps.

Q: Why temp_buffers matter for big sorts?
A: Per-session memory for temp tables; bump for sessions doing lots of temp work.

Q: Quick sanity check on cache hit rate?
A: SELECT sum(heap_blks_hit)::float / (sum(heap_blks_hit) + sum(heap_blks_read)) FROM pg_statio_user_tables; — want ≥0.99.

Q: How does Postgres handle MVCC?
A: Each row version gets xmin (creating tx) and xmax (deleting tx). Visibility is determined by transaction snapshot; old versions cleaned by VACUUM.

Q: Transaction wraparound — what is it?
A: 32-bit transaction IDs wrap around at ~4 billion. Autovacuum freezes old tuples; if it falls behind, the DB shuts down to protect data.

Q: Detect wraparound risk?
A: SELECT datname, age(datfrozenxid) FROM pg_database; — warn above 1.5B, panic above 2B.

Q: How are pg_stat_statements queries normalized?
A: Parameters are replaced with $1, $2…; constants and identifiers may also be merged so different literals collapse to one query.

Q: autocommit in Postgres?
A: On by default at the client level; every statement is its own transaction unless inside BEGIN..COMMIT.

Q: How to atomically swap a big table?
A: Create new table, populate, then in one tx: BEGIN; LOCK old; ALTER TABLE old RENAME TO old_backup; ALTER TABLE new RENAME TO old; COMMIT;

Q: How to add an FK without long lock?
A: ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (...) REFERENCES parent(...) NOT VALID; then ALTER TABLE child VALIDATE CONSTRAINT fk; — validate scans without blocking writes.

Q: Generated columns?
A: colname int GENERATED ALWAYS AS (other_col * 2) STORED; — STORED only (PG12+); virtual not yet supported.

Q: Multi-column index column order rule?
A: Most selective and most-used-in-WHERE first; respects equality before range.

Q: Why a query may not use a composite index?
A: If your WHERE doesn't include the leading column, B-tree can't skip-scan (well, only loose-index-scan tricks). Reorder columns or add another index.

Q: Partial index example?
A: CREATE INDEX active_users_email ON users(email) WHERE deleted_at IS NULL; — smaller, faster, used by queries with the matching WHERE.

Q: Covering index?
A: CREATE INDEX ... INCLUDE (col) — adds extra columns to the leaf for index-only scans.

Q: Index-only scan requirement?
A: Visibility map must show all tuples on the page are visible; runs VACUUM to keep it fresh.

Q: Why ANALYZE alone may not help after a huge UPDATE?
A: Stats catch up but pages may still be bloated — also run VACUUM (verbose) to reclaim and update visibility map.

Q: How to monitor IO on a busy DB?
A: SELECT * FROM pg_stat_io; (PG16+), or use OS tools (iostat, atop).

Q: Common over-indexing symptom?
A: Slow writes, larger tables, bigger WAL — review pg_stat_user_indexes idx_scan = 0 indexes.