PostgreSQL DBA Interview —
Questions & Answers

How do you identify slow-running queries in PostgreSQL?

Use pg_stat_activity to see currently executing queries, sorted by how long they've been running:

sqlSELECT pid,
       usename,
       datname,
       state,
       now() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

For historical slow queries — not just ones running right now — enable and query pg_stat_statements:

sqlCREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

pg_stat_activity shows what's slow right now; pg_stat_statements shows what's slow on average, over time — a much better source for tuning decisions, since a single snapshot can be misleading.

What information does EXPLAIN ANALYZE provide?

EXPLAIN shows the planner's chosen execution plan without running the query. EXPLAIN ANALYZE actually executes it and adds real timing and row counts, so you can compare estimated vs. actual behavior:

sqlEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 105;

outputIndex Scan using idx_orders_customer_id on orders
  (cost=0.43..8.45 rows=12 width=64)
  (actual time=0.021..0.034 rows=15 loops=1)
  Index Cond: (customer_id = 105)
  Buffers: shared hit=5
Planning Time: 0.112 ms
Execution Time: 0.058 ms

It tells you the access path for every node (seq scan, index scan, bitmap scan…), the estimated vs. actual rows and cost — a big gap signals stale statistics — the join strategy chosen, per-node and total execution time, and, with the BUFFERS option, shared/local hits vs. reads for spotting I/O-bound steps.

Which PostgreSQL views or tools do you use for performance monitoring?

sql-- What's occupying shared_buffers right now
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT c.relname, count(*) AS buffers,
       pg_size_pretty(count(*) * 8192) AS size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

Difference between sequential scan and index scan

Sequential scan reads every row in the table, block by block, filtering in memory — efficient when a large fraction of the table matches the predicate, or the table is small.

Index scan uses a B-tree (or other) index to jump directly to matching rows, then optionally fetches them from the heap — efficient when the predicate is selective.

sqlSET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 105;

SET enable_indexscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 105;

The planner picks whichever is cheaper based on statistics, selectivity, and cost parameters (random_page_cost, seq_page_cost). A missing index, stale statistics, or a low-selectivity predicate are the usual reasons a query "should" use an index scan but doesn't.

What is table bloat?

Table bloat is dead space accumulated inside a table's (or index's) physical storage — pages containing dead tuples or unused free space that hasn't been reclaimed. The table on disk grows larger than the live data it actually holds, wasting disk and slowing scans because more pages have to be read.

What causes table bloat?

UPDATE and DELETE operations. PostgreSQL uses MVCC: an UPDATE doesn't overwrite a row in place — it writes a new version and marks the old one as a dead tuple. A DELETE also just marks the row dead rather than removing it immediately, since other transactions may still need the old version. Dead tuples accumulate until VACUUM reclaims the space.

sqlSELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup,1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

What happens if autovacuum is disabled?

Dead tuples are never cleaned up, which causes table and index bloat to grow unbounded, stale planner statistics (no ANALYZE) leading to bad plans, and eventually transaction ID wraparound risk — if VACUUM FREEZE never runs, PostgreSQL will force the database into read-only/shutdown mode to protect data integrity.

sqlSHOW autovacuum;

-- Per-table override, if ever needed
ALTER TABLE orders SET (autovacuum_enabled = off);

How do you identify bloated tables?

Primary source: pg_stat_all_tables, tracking live/dead tuple counts and last vacuum/analyze times.

sqlSELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum
FROM pg_stat_all_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

For a precise, size-based estimate rather than just tuple counts, use the pgstattuple extension:

sqlCREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('orders');
-- table_len, dead_tuple_count, dead_tuple_percent, free_percent…

Have you tuned autovacuum parameters in production? How?

Yes. Three levers come up most often in production tuning:

Cost-based delay — controls how aggressively autovacuum runs vs. how much it throttles itself to avoid impacting production I/O:

sqlALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;

More workers, for databases with many tables competing for autovacuum attention:

sqlALTER SYSTEM SET autovacuum_max_workers = 6;

Lower scale factor / threshold for large, high-churn tables so vacuum triggers sooner — the default 0.2 scale factor means a 100M-row table waits for 20M dead tuples, often too late:

sqlALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.02
);

Run SELECT pg_reload_conf(); after ALTER SYSTEM changes — no restart needed for these GUCs.

Scenario: it's Black Friday and the primary database crashed. What are your immediate actions?

1. Check for a standby. If streaming replication exists, promote it to primary: SELECT pg_promote();
2. No replica? Perform Point-In-Time Recovery (PITR) from the last base backup and WAL archive.
3. Redirect application traffic to the new primary once it's confirmed healthy.
4. Root cause analysis once service is restored — check logs, disk space, OOM killer, WAL directory, and long-running transactions.
5. Implement preventive measures — monitoring/alerting, automated failover (Patroni/repmgr) if not already in place.

Priority order: restore availability first, investigate root cause second — don't debug a crashed production primary while customers are down.

How do you check replication status or lag?

sql — on primarySELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS send_lag_bytes,
       pg_wal_lsn_diff(sent_lsn, flush_lsn)              AS flush_lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

sql — on standbySELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

pg_stat_replication (on the primary) shows per-standby state and LSN gaps; pg_last_xact_replay_timestamp() (on the standby) is the simplest way to see lag as a wall-clock duration.

Difference between synchronous and asynchronous replication

Synchronous: the primary waits for standby acknowledgment (per synchronous_commit) before reporting a commit to the client — zero data loss on failover, at the cost of commit latency.

Asynchronous (default): the primary commits and returns immediately, without waiting on the standby — faster, but a small window of data loss is possible if the primary fails before the standby catches up.

sqlALTER SYSTEM SET synchronous_standby_names = 'standby1';
ALTER SYSTEM SET synchronous_commit = on;
SELECT pg_reload_conf();

Scenario: a table was accidentally deleted at 2 PM, last backup was midnight. How do you recover just that table?

1. Don't restore straight into production — recover into an isolated environment first.
2. Restore the midnight base backup to a separate/test instance.
3. Perform PITR, replaying WAL up to just before the incident:

   confrestore_command = 'cp /archive/%f %p'
   recovery_target_time = '2024-05-17 13:59:00'
   recovery_target_action = 'promote'

4. Extract only the affected table from the recovered instance:

   bashpg_dump -h recovery-host -d mydb -t public.orders -Fc -f orders_recovered.dump

5. Restore that table into production:

   bashpg_restore -h prod-host -d mydb --data-only -t orders orders_recovered.dump

6. Verify row counts / checksums before calling it resolved.

This avoids a full-database rollback, which would lose everything else committed between midnight and 2 PM, and isolates the blast radius to just the affected object.

What is WAL (Write-Ahead Logging)?

WAL is PostgreSQL's mechanism for durability: every change is written to a log before the data file is modified. If the server crashes mid-write, PostgreSQL replays the WAL on startup to redo any logged-but-not-yet-flushed changes — guaranteeing no committed transaction is lost (the "D" in ACID).

sqlSHOW wal_level;
SELECT pg_current_wal_lsn();

Why does PostgreSQL write to WAL before the data file?

Writing to the heap happens in-place on disk pages, and a crash mid-write could leave a page half-written and corrupted. WAL records are appended sequentially and each one fully describes the change, so replaying them after a crash safely reconstructs the correct final state. Log first, apply later — never the reverse.

What is a WAL segment?

A physical file on disk — 16 MB by default, configurable at initdb time — that stores a sequential chunk of the write-ahead log. Segments live in pg_wal/ inside the data directory, named as 24-character hex filenames.

bashls $PGDATA/pg_wal/
# 000000010000000000000001

What happens if the pg_wal volume suddenly fills up?

The database shuts down / refuses writes — PostgreSQL cannot safely accept new transactions if it can't write WAL. Space in the WAL volume has to be freed (by fixing whatever is preventing WAL recycling) before the instance can resume.

bashdf -h $PGDATA/pg_wal

If the WAL directory is filling up rapidly, what would you check?

Archiving failing or disabled — WAL segments can't be recycled if archive_command keeps failing:

sqlSHOW archive_mode;
SELECT * FROM pg_stat_archiver;

A long-running transaction holding back the WAL segments needed for its snapshot:

sqlSELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC;

A standby out of sync, or a stale replication slot — PostgreSQL retains WAL for a slot until it's consumed, so a disconnected standby holding a slot causes unbounded growth:

sqlSELECT slot_name, active, restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

Difference between row-level and table-level locks

Row-level: locks only the specific row(s) being modified, allowing concurrent access to the rest of the table — e.g. UPDATE orders SET status='shipped' WHERE id=5; locks only row 5.

Table-level: locks the entire table — e.g. VACUUM FULL orders; or ALTER TABLE orders ADD COLUMN …; typically take an ACCESS EXCLUSIVE lock, blocking all reads and writes until it completes.

sqlSELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY relation;

Describe a production outage you handled end-to-end

We had severe table bloat on a high-write table that eventually caused storage exhaustion, and the table became unusable. We had to perform a full Point-In-Time Recovery to restore it to a consistent state — production was down for the duration. Post-incident, we implemented scheduled bloat monitoring and more aggressive autovacuum settings on that table to prevent recurrence.

Fill in with your real specifics — interviewers look for: detection → immediate mitigation → recovery method → downtime impact → root cause → preventive fix.

Describe a P1 incident: what happened, how you resolved it, and the RCA

Incident: a standby replica fell out of sync because a required WAL segment had already been removed from the primary before the standby consumed it.

Immediate fix: recreated the replica from a fresh base backup:

bashpg_basebackup -h primary-host -D /var/lib/postgresql/data -U replicator -P -R

Root cause: WAL segments were recycled before the standby applied them — no replication slot was in use, so nothing prevented recycling.

Fix implemented: moved to replication-slot-based replication, so the primary retains WAL until the standby confirms consumption:

sql — on primarySELECT pg_create_physical_replication_slot('standby1_slot');

conf — on standbyprimary_slot_name = 'standby1_slot'

Also created a shell script + cron job to proactively catch bloat on business-critical tables instead of relying solely on autovacuum timing:

bash — bloat_check.sh#!/bin/bash
psql -d mydb -t -c "
  SELECT relname FROM pg_stat_user_tables
  WHERE n_dead_tup > 50000
" | while read -r tbl; do
    [ -n "$tbl" ] && psql -d mydb -c "VACUUM (VERBOSE, ANALYZE) ${tbl};"
done

crontab0 * * * * /opt/scripts/bloat_check.sh >> /var/log/bloat_check.log 2>&1

RCA framing that works well in interviews: state the problem → what you did immediately → why it happened → what you changed so it can't happen again.

How did you perform a PostgreSQL 14 → 16 upgrade?

pg_upgrade was used, with the method chosen by database size:

• Copy method (default) — copies all data files to the new cluster. Safe but slow and needs ~2x disk space temporarily.
• Link method (--link) — hard-links data files instead of copying. Much faster, no extra disk space, but the old cluster becomes unusable afterward.

bash/usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/14/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/14/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --link

For a ~2 TB database, the link method was chosen specifically to avoid doubling storage requirements and to keep the maintenance window short. After upgrading, always rebuild planner statistics:

bashvacuumdb --all --analyze-in-stages

If CPU is consistently ~90%, how would you troubleshoot?

1. top / htop at the OS level to confirm which process is consuming CPU and get its PID.

   bashtop -c

2. Map the PID to a query via pg_stat_activity:

   sqlSELECT pid, usename, state, query, now() - query_start AS duration
   FROM pg_stat_activity
   WHERE pid = <high_cpu_pid>;

3. Run EXPLAIN ANALYZE on the offending query to look for missing indexes, bad plans, or sequential scans on large tables driving CPU load.

Replication lag has grown to 2+ hours — how do you fix it?

Check, in order: long-running queries on the primary delaying WAL generation/consumption, network bandwidth or latency between primary and standby, general network issues (packet loss, intermittent connectivity), and any abnormally large or long transactions generating WAL faster than the standby can apply it.

sqlSELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

sql-- Standby catch-up state
SELECT application_name, state, sync_state, replay_lag
FROM pg_stat_replication;