Postgresql Performance Tuning Practice Exam

Question 1 Postgresql Performance Tuning

Setting that logs slow queries?

pgbackrest, WAL-G, barman. Each row version gets xmin (creating tx) and xmax (deleting tx). Visibility is determined by transaction snapshot; old versions cleaned by VACUUM. log_min_duration_statement = 1000 (ms) — logs anything above 1s. cancel: tries to abort the current query.
terminate: kills the entire backend (closes connection).

Question 2 Postgresql Performance Tuning

Logical vs physical replication?

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. Postgres treats NULLs as distinct in unique indexes by default — use partial: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL; Physical: WAL stream, byte-for-byte.
Logical: replicates rows by primary key; supports cross-version, subset replication. Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress.

Question 3 Postgresql Performance Tuning

Maintenance window tip?

Batch in chunks (UPDATE ... WHERE id BETWEEN ...), commit between batches, and pause for VACUUM. 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. Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress. 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;

Question 4 Postgresql Performance Tuning

Stale planner stats — symptom?

Each row version gets xmin (creating tx) and xmax (deleting tx). Visibility is determined by transaction snapshot; old versions cleaned by VACUUM. Plans that pick wrong join order or wrong index. Run ANALYZE table_name;. When the planner expects to return a large fraction of the table — random I/O via the index would be slower than reading sequentially. 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.

Question 5 Postgresql Performance Tuning

effective_cache_size?

Dead tuples + free space in a table after many UPDATE/DELETEs that VACUUM hasn't reclaimed; causes larger reads. log_min_duration_statement = 1000 (ms) — logs anything above 1s. Hint to the planner about total OS+DB cache — usually ~50–75% of RAM. Doesn't actually allocate memory. RANGE (dates), LIST (regions), HASH (shard by id).

Question 6 Postgresql Performance Tuning

How to atomically swap a big table?

32-bit transaction IDs wrap around at ~4 billion. Autovacuum freezes old tuples; if it falls behind, the DB shuts down to protect data. 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; recovery_target_time, recovery_target_xid, recovery_target_lsn, recovery_target_name. Join pg_stat_statements against table names (parse with regex) or use the queryid over time to track patterns.

Question 7 Postgresql Performance Tuning

idle_in_transaction_session_timeout?

GIN on to_tsvector columns. Host-Based Auth file: who can connect from where with which auth method (trust, md5, scram-sha-256, peer, cert, ident). Kills sessions sitting idle in a transaction; safety net against forgotten ROLLBACKs. wal_level=replica, max_wal_senders, archive_mode, pg_basebackup to seed standby, primary_conninfo in standby.

Question 8 Postgresql Performance Tuning

Vacuum vs Autovacuum?

Most selective and most-used-in-WHERE first; respects equality before range. 32-bit transaction IDs wrap around at ~4 billion. Autovacuum freezes old tuples; if it falls behind, the DB shuts down to protect data. VACUUM: reclaim dead tuples, update stats. Autovacuum: background daemon that triggers based on dead-tuple thresholds. Tune autovacuum_vacuum_scale_factor for hot tables. Read bottom-up: leaf nodes first (table scans / index scans), then joins, then aggregates. Look for high cost, rows, and actual time.

Question 9 Postgresql Performance Tuning

How to safely add a NOT NULL column to a big table in PG11+?

Each row version gets xmin (creating tx) and xmax (deleting tx). Visibility is determined by transaction snapshot; old versions cleaned by VACUUM. Replication slot stuck, archive_command failing, very long transactions. Add column with default (instant metadata-only since PG11), then backfill in chunks, then add NOT NULL. colname int GENERATED ALWAYS AS (other_col * 2) STORED; — STORED only (PG12+); virtual not yet supported.

Question 10 Postgresql Performance Tuning

How to do read-mostly scaling?

ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (...) REFERENCES parent(...) NOT VALID; then ALTER TABLE child VALIDATE CONSTRAINT fk; — validate scans without blocking writes. VACUUM: reclaim dead tuples, update stats. Autovacuum: background daemon that triggers based on dead-tuple thresholds. Tune autovacuum_vacuum_scale_factor for hot tables. Streaming replication to read replicas; route SELECTs to replicas (with awareness of replication lag). Hint to the planner about total OS+DB cache — usually ~50–75% of RAM. Doesn't actually allocate memory.

Question 11 Postgresql Performance Tuning

How to make a unique index that ignores NULLs?

Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress. INSERT ... ON CONFLICT (key) DO NOTHING or DO UPDATE SET .... SELECT pid, state, age(now(), xact_start) FROM pg_stat_activity WHERE state = 'idle in transaction'; Postgres treats NULLs as distinct in unique indexes by default — use partial: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL;

Question 12 Postgresql Performance Tuning

Function used to add a B-tree index for equality + range queries?

Sync: commit waits for at least one replica — zero data loss, higher latency.
Async: commit returns immediately — possible data loss on failover. CREATE INDEX ON t (col); — default is B-tree. Each row version gets xmin (creating tx) and xmax (deleting tx). Visibility is determined by transaction snapshot; old versions cleaned by VACUUM. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; — be ready to retry on serialization failures (SQLSTATE 40001).

Question 13 Postgresql Performance Tuning

Configuring statement timeout per role?

ALTER ROLE app SET statement_timeout = '30s'; pgstattuple on the index, or queries that compute index size vs expected size from row count. Query external data sources via FDW (file_fdw, postgres_fdw, etc.) as if local tables. GIN on to_tsvector columns.

Question 14 Postgresql Performance Tuning

Partitioning types?

TimescaleDB — hypertables, continuous aggregates, retention policies. 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. RANGE (dates), LIST (regions), HASH (shard by id). Join pg_stat_statements against table names (parse with regex) or use the queryid over time to track patterns.

Question 15 Postgresql Performance Tuning

How do you find the slowest queries in Postgres?

SELECT sum(heap_blks_hit)::float / (sum(heap_blks_hit) + sum(heap_blks_read)) FROM pg_statio_user_tables; — want ≥0.99. 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. 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;

Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress.

Question 16 Postgresql Performance Tuning

WAL bloat causes?

log_min_duration_statement = 1000 (ms) — logs anything above 1s. RANGE (dates), LIST (regions), HASH (shard by id). Replication slot stuck, archive_command failing, very long transactions. SELECT * FROM pg_stat_io; (PG16+), or use OS tools (iostat, atop).

Question 17 Postgresql Performance Tuning

Multi-column index column order rule?

Most selective and most-used-in-WHERE first; respects equality before range. Visibility map must show all tuples on the page are visible; runs VACUUM to keep it fresh. CREATE INDEX ... INCLUDE (col) — adds extra columns to the leaf for index-only scans. Replication slot stuck, archive_command failing, very long transactions.

Question 18 Postgresql Performance Tuning

Vector similarity search extension?

Stats catch up but pages may still be bloated — also run VACUUM (verbose) to reclaim and update visibility map. VACUUM: non-blocking, marks space reusable.
VACUUM FULL: rewrites the table, takes an ACCESS EXCLUSIVE lock, reclaims OS disk space — use sparingly. Use range partitioning by date and DROP old partitions. pgvector — IVFFlat and HNSW indexes for kNN.

Question 19 Postgresql Performance Tuning

What blocks autovacuum?

Full sort of the whole table. Use TABLESAMPLE BERNOULLI(1) or pre-sample with random values. Hint to the planner about total OS+DB cache — usually ~50–75% of RAM. Doesn't actually allocate memory. 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)'); SELECT datname, age(datfrozenxid) FROM pg_database; — warn above 1.5B, panic above 2B.

Question 20 Postgresql Performance Tuning

Tablespace use case?

Two transactions locking rows in different orders. Always update in a consistent order (e.g., by PK). Place hot tables on fast SSD and archive tables on cheap disks — different physical locations within one cluster. 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. CREATE INDEX ON t (col); — default is B-tree.

Question 21 Postgresql Performance Tuning

autocommit in Postgres?

SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'; or look at pg_locks joined with pg_stat_activity. When the planner expects to return a large fraction of the table — random I/O via the index would be slower than reading sequentially. On by default at the client level; every statement is its own transaction unless inside BEGIN..COMMIT. 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.

Question 22 Postgresql Performance Tuning

How to expire old data efficiently?

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)'); Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress. SELECT pid, state, age(now(), xact_start) FROM pg_stat_activity WHERE state = 'idle in transaction'; Use range partitioning by date and DROP old partitions.

Question 23 Postgresql Performance Tuning

How to detect index bloat?

pgbackrest, WAL-G, barman. Can't run inside a transaction; rebuilds in parallel + swaps. Mostly safe but requires extra disk. wal_level=replica, max_wal_senders, archive_mode, pg_basebackup to seed standby, primary_conninfo in standby. pgstattuple on the index, or queries that compute index size vs expected size from row count.

Question 24 Postgresql Performance Tuning

How to add an FK without long lock?

Visibility map must show all tuples on the page are visible; runs VACUUM to keep it fresh. Inspect what's in shared_buffers; tells you cache hit rates per table/index. Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress. ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (...) REFERENCES parent(...) NOT VALID; then ALTER TABLE child VALIDATE CONSTRAINT fk; — validate scans without blocking writes.

Question 25 Postgresql Performance Tuning

Connection pool — why?

Two transactions locking rows in different orders. Always update in a consistent order (e.g., by PK). Postgres forks a process per connection. Pool with PgBouncer or app-level pool to avoid context-switch storms. colname int GENERATED ALWAYS AS (other_col * 2) STORED; — STORED only (PG12+); virtual not yet supported. GIN index supports containment (@>) but not arbitrary equality on nested keys; use expression indexes for specific paths.

Question 26 Postgresql Performance Tuning

Difference between pg_dump and pg_basebackup?

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;

Stats catch up but pages may still be bloated — also run VACUUM (verbose) to reclaim and update visibility map. pg_dump: logical SQL dump, portable across versions.
pg_basebackup: physical copy of the data dir, used for replicas/restore on same version. On by default at the client level; every statement is its own transaction unless inside BEGIN..COMMIT.

Question 27 Postgresql Performance Tuning

Covering index?

CREATE INDEX ... INCLUDE (col) — adds extra columns to the leaf for index-only scans. Add column with default (instant metadata-only since PG11), then backfill in chunks, then add NOT NULL. RANGE (dates), LIST (regions), HASH (shard by id). SCRAM is the modern default — better security, mutual challenge.

Question 28 Postgresql Performance Tuning

Generated columns?

colname int GENERATED ALWAYS AS (other_col * 2) STORED; — STORED only (PG12+); virtual not yet supported. GIN on to_tsvector columns. 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. Stats catch up but pages may still be bloated — also run VACUUM (verbose) to reclaim and update visibility map.

Question 29 Postgresql Performance Tuning

Difference between VACUUM and VACUUM FULL?

VACUUM: reclaim dead tuples, update stats. Autovacuum: background daemon that triggers based on dead-tuple thresholds. Tune autovacuum_vacuum_scale_factor for hot tables. Inspect what's in shared_buffers; tells you cache hit rates per table/index. 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; VACUUM: non-blocking, marks space reusable.
VACUUM FULL: rewrites the table, takes an ACCESS EXCLUSIVE lock, reclaims OS disk space — use sparingly.

Question 30 Postgresql Performance Tuning

shared_buffers recommendation?

When the planner expects to return a large fraction of the table — random I/O via the index would be slower than reading sequentially. now() is STABLE, not IMMUTABLE — Postgres requires IMMUTABLE for index expressions. Inspect what's in shared_buffers; tells you cache hit rates per table/index. ~25% of RAM for dedicated DB servers (with OS cache covering the rest).

Question 31 Postgresql Performance Tuning

Why a function index on now() doesn't work?

now() is STABLE, not IMMUTABLE — Postgres requires IMMUTABLE for index expressions. Enables I/O timing in EXPLAIN (BUFFERS, ANALYZE) and pg_stat_statements. Small overhead, big debug value. ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (...) REFERENCES parent(...) NOT VALID; then ALTER TABLE child VALIDATE CONSTRAINT fk; — validate scans without blocking writes. Replication slot stuck, archive_command failing, very long transactions.

Question 32 Postgresql Performance Tuning

Difference between bitmap heap scan and index scan?

Query external data sources via FDW (file_fdw, postgres_fdw, etc.) as if local tables. Sync: commit waits for at least one replica — zero data loss, higher latency.
Async: commit returns immediately — possible data loss on failover. Stored procedures with PLAN CACHE MODE hints; or plan_cache_mode = force_generic_plan / force_custom_plan; or use prepared statements judiciously. 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.

Question 33 Postgresql Performance Tuning

default_statistics_target?

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)'); How many samples ANALYZE collects per column. Default 100; raise to 1000+ for high-cardinality columns where planner is making bad estimates. Inactive slots prevent WAL deletion → disk fills. Drop unused slots. Use tools like pg_repack, gh-ost equivalents, or shadow tables: write to both old + new, backfill, swap.

Question 34 Postgresql Performance Tuning

Window function for running totals?

Inactive slots prevent WAL deletion → disk fills. Drop unused slots. SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'; or look at pg_locks joined with pg_stat_activity. 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. SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING)

Question 35 Postgresql Performance Tuning

track_io_timing — what's it for?

Enables I/O timing in EXPLAIN (BUFFERS, ANALYZE) and pg_stat_statements. Small overhead, big debug value. SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING) ALTER ROLE app SET statement_timeout = '30s'; Join pg_stat_statements against table names (parse with regex) or use the queryid over time to track patterns.

Question 36 Postgresql Performance Tuning

Tool to monitor replication lag?

CREATE POLICY ... USING (...) + ALTER TABLE ... ENABLE ROW LEVEL SECURITY; — filters rows based on session settings. SELECT * FROM pg_stat_replication; — look at replay_lag. GIN index supports containment (@>) but not arbitrary equality on nested keys; use expression indexes for specific paths. cancel: tries to abort the current query.
terminate: kills the entire backend (closes connection).

Question 37 Postgresql Performance Tuning

Streaming replication setup essentials?

~25% of RAM for dedicated DB servers (with OS cache covering the rest). Use range partitioning by date and DROP old partitions. wal_level=replica, max_wal_senders, archive_mode, pg_basebackup to seed standby, primary_conninfo in standby. 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.

Question 38 Postgresql Performance Tuning

UUID v4 as primary key — performance concern?

Random UUIDs cause index fragmentation. Use UUID v7 (time-ordered) or a separate sortable column for B-tree-friendly inserts. Per-session memory for temp tables; bump for sessions doing lots of temp work. Parameters are replaced with $1, $2…; constants and identifiers may also be merged so different literals collapse to one query. Each row version gets xmin (creating tx) and xmax (deleting tx). Visibility is determined by transaction snapshot; old versions cleaned by VACUUM.

Question 39 Postgresql Performance Tuning

Partitioning use case?

Physical: WAL stream, byte-for-byte.
Logical: replicates rows by primary key; supports cross-version, subset replication. cancel: tries to abort the current query.
terminate: kills the entire backend (closes connection). Huge tables (1B+ rows) with a clear partition key (date, tenant) — improves vacuum, query planning, and lets you drop old data with DROP PARTITION. RANGE (dates), LIST (regions), HASH (shard by id).

Question 40 Postgresql Performance Tuning

Why a query may not use a composite index?

CREATE POLICY ... USING (...) + ALTER TABLE ... ENABLE ROW LEVEL SECURITY; — filters rows based on session settings. 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. Session: client owns connection until disconnect.
Transaction: connection released after COMMIT — most efficient.
Statement: released after each statement; breaks prepared statements. SELECT * FROM pg_stat_io; (PG16+), or use OS tools (iostat, atop).

Question 41 Postgresql Performance Tuning

DISTINCT ON vs window function?

On by default at the client level; every statement is its own transaction unless inside BEGIN..COMMIT. pgstattuple on the index, or queries that compute index size vs expected size from row count. DISTINCT ON: PG-specific, picks first row per group based on ORDER BY.
ROW_NUMBER() OVER (PARTITION BY ...): portable; filter WHERE rn = 1. Slow writes, larger tables, bigger WAL — review pg_stat_user_indexes idx_scan = 0 indexes.

Question 42 Postgresql Performance Tuning

Foreign tables — what for?

pg_dump: logical SQL dump, portable across versions.
pg_basebackup: physical copy of the data dir, used for replicas/restore on same version. Huge tables (1B+ rows) with a clear partition key (date, tenant) — improves vacuum, query planning, and lets you drop old data with DROP PARTITION. CREATE INDEX ON t USING GIN (col jsonb_path_ops); Query external data sources via FDW (file_fdw, postgres_fdw, etc.) as if local tables.

Question 43 Postgresql Performance Tuning

How to find missing indexes?

Most selective and most-used-in-WHERE first; respects equality before range. now() is STABLE, not IMMUTABLE — Postgres requires IMMUTABLE for index expressions. 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. Cascading deletes and updates scan the child table; FK columns should usually be indexed.

Question 44 Postgresql Performance Tuning

LATERAL JOIN use case?

ALTER ROLE app SET statement_timeout = '30s'; 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. Dead tuples + free space in a table after many UPDATE/DELETEs that VACUUM hasn't reclaimed; causes larger reads. Evaluate a subquery per outer row — useful for 'top N per group' patterns.

Question 45 Postgresql Performance Tuning

Common cause of deadlocks?

Dead tuples + free space in a table after many UPDATE/DELETEs that VACUUM hasn't reclaimed; causes larger reads. Place hot tables on fast SSD and archive tables on cheap disks — different physical locations within one cluster. Two transactions locking rows in different orders. Always update in a consistent order (e.g., by PK). Slow writes, larger tables, bigger WAL — review pg_stat_user_indexes idx_scan = 0 indexes.

Question 46 Postgresql Performance Tuning

Common JSONB indexing pitfall?

Two transactions locking rows in different orders. Always update in a consistent order (e.g., by PK). GIN index supports containment (@>) but not arbitrary equality on nested keys; use expression indexes for specific paths. IMMUTABLE: same input → same output, no DB read.
STABLE: same within a tx.
VOLATILE: anything; can't be inlined or used in indexes. ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (...) REFERENCES parent(...) NOT VALID; then ALTER TABLE child VALIDATE CONSTRAINT fk; — validate scans without blocking writes.

Question 47 Postgresql Performance Tuning

Recovery target options for PITR?

Postgres treats NULLs as distinct in unique indexes by default — use partial: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL; recovery_target_time, recovery_target_xid, recovery_target_lsn, recovery_target_name. Run REINDEX CONCURRENTLY for index bloat; VACUUM (FREEZE) before huge transactions hitting wraparound; check pg_stat_progress_vacuum for live vacuum progress. CREATE INDEX active_users_email ON users(email) WHERE deleted_at IS NULL; — smaller, faster, used by queries with the matching WHERE.

Question 48 Postgresql Performance Tuning

Index type for full-text search?

GIN on to_tsvector columns. 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)'); Join pg_stat_statements against table names (parse with regex) or use the queryid over time to track patterns. SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'; or look at pg_locks joined with pg_stat_activity.

Question 49 Postgresql Performance Tuning

How to make Postgres prefer an index for low-cardinality columns?

pg_dump: logical SQL dump, portable across versions.
pg_basebackup: physical copy of the data dir, used for replicas/restore on same version. RANGE (dates), LIST (regions), HASH (shard by id). SELECT pg_cancel_backend(pid); — try first. Last resort: pg_terminate_backend(pid);. 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.

Question 50 Postgresql Performance Tuning

How are pg_stat_statements queries normalized?

SELECT datname, age(datfrozenxid) FROM pg_database; — warn above 1.5B, panic above 2B. Parameters are replaced with $1, $2…; constants and identifiers may also be merged so different literals collapse to one query. Inactive slots prevent WAL deletion → disk fills. Drop unused slots. ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (...) REFERENCES parent(...) NOT VALID; then ALTER TABLE child VALIDATE CONSTRAINT fk; — validate scans without blocking writes.