Menu
CoddyTech

PostgreSQL DBA Interview Questions

Vacuum and bloat, wraparound, WAL, replication, backups and recovery, access control, pooling, session monitoring and major version upgrades.

11 questions
  1. What is the difference between VACUUM, VACUUM FULL and autovacuum in PostgreSQL?

    UPDATE and DELETE leave dead row versions behind (MVCC). Plain VACUUM marks their space reusable and runs alongside normal traffic, but shrinks the file only when pages at its end are empty. VACUUM FULL rewrites the table and returns the space to the operating system, but holds an ACCESS EXCLUSIVE lock (no reads or writes) and needs disk for the copy. Autovacuum runs VACUUM and ANALYZE in the background as rows change.

    PostgreSQL
    CREATE TABLE logs (id int, msg text);
    INSERT INTO logs SELECT g, repeat('x', 100) FROM generate_series(1, 10000) g;
    DELETE FROM logs WHERE id % 2 = 0;
    CREATE TABLE pages (step text, n bigint);
    INSERT INTO pages SELECT 'after DELETE', pg_relation_size('logs') / 8192;
    VACUUM logs;
    INSERT INTO pages SELECT 'after VACUUM', pg_relation_size('logs') / 8192;
    VACUUM FULL logs;
    INSERT INTO pages SELECT 'after VACUUM FULL', pg_relation_size('logs') / 8192;
    SELECT * FROM pages;

    It stays at 173 pages after VACUUM and drops to 87 after VACUUM FULL. To shrink without the long lock, use the pg_repack extension.

  2. Autovacuum is running but a PostgreSQL table keeps bloating. What do you check?

    First, check whether vacuum may remove anything. It cannot remove a dead row that an open snapshot might still need, so one old transaction holds back cleanup in every table of its database.

    -- oldest open transactions, including 'idle in transaction'
    SELECT pid, state, xact_start, left(query, 60)
    FROM pg_stat_activity WHERE xact_start IS NOT NULL
    ORDER BY xact_start LIMIT 5;
    -- replication slots and prepared transactions also pin old rows
    SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;
    SELECT gid, prepared FROM pg_prepared_xacts;
    -- dead rows and the last autovacuum per table
    SELECT relname, n_dead_tup, last_autovacuum
    FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
    -- vacuum a big table after 1% of it changes instead of 20%
    ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);
    

    Autovacuum starts at 50 dead rows plus 20% of the table: 20 million dead rows on a 100 million row table. Lower the scale factor per table and raise autovacuum_vacuum_cost_limit if workers are throttled. idle_in_transaction_session_timeout ends forgotten sessions; a standby with hot_standby_feedback = on can pin rows too.

  3. What is transaction ID wraparound in PostgreSQL, and how do you prevent it?

    Transaction IDs are 32-bit and compared modulo 2^32, so a row can be about 2 billion transactions old before it would look like the future. Vacuum freezes old rows (marks them visible to everyone) so their XID stops mattering.

    Autovacuum forces an anti-wraparound vacuum on tables older than autovacuum_freeze_max_age (default 200 million), even when autovacuum is off. If vacuum still falls behind, PostgreSQL warns at 40 million transactions from wraparound and stops assigning XIDs below 3 million: writes stop.

    SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
    SELECT relname, age(relfrozenxid) FROM pg_class
    WHERE relkind = 'r' ORDER BY 2 DESC LIMIT 10;
    

    Alert on these ages and remove what blocks vacuum: long transactions, stale replication slots. See routine vacuuming.

  4. What is WAL in PostgreSQL, and what does a checkpoint do?

    The write-ahead log (WAL) records every change before the data files are written. A commit only flushes WAL; changed pages stay in memory, and after a crash PostgreSQL replays WAL from the last checkpoint.

    A checkpoint writes all dirty pages to the data files, so older WAL can be recycled. It runs every checkpoint_timeout (default 5 minutes) or when WAL nears max_wal_size (default 1GB). Frequent checkpoints cost I/O; rare ones lengthen crash recovery. Frequent-checkpoint warnings in the log mean max_wal_size is too small.

  5. What is the difference between streaming replication and logical replication in PostgreSQL?

    Streaming (physical) replication ships WAL and keeps an exact copy of the whole cluster; logical replication sends row changes of published tables to an ordinary database.

    StreamingLogical
    Copiesthe whole clusterchosen tables
    Replicaread-only hot standbywritable database
    Versionssame major versionmay differ
    DDL and sequencesincludednot replicated
    Settingwal_level = replicawal_level = logical
    -- publisher
    CREATE PUBLICATION shop_pub FOR TABLE orders, customers;
    -- subscriber (the tables must already exist there)
    CREATE SUBSCRIPTION shop_sub
      CONNECTION 'host=primary dbname=shop user=repl'
      PUBLICATION shop_pub;
    

    Use streaming for failover and read replicas, logical for low-downtime upgrades and copying a subset.

  6. What is the difference between pg_dump and pg_basebackup, and how does point-in-time recovery work?

    pg_dump is a logical backup of one database: consistent, selective, and restorable into a newer major version. pg_basebackup copies the whole cluster physically: same major version only, faster to restore, and the base for replicas and point-in-time recovery (PITR).

    pg_dump -Fc -d shop -f shop.dump         # custom format, one database
    pg_restore -d shop_copy -j 4 shop.dump   # parallel restore
    pg_dumpall --globals-only > globals.sql  # roles and tablespaces
    pg_basebackup -D /backups/base -Ft -z -P # whole cluster
    

    PITR needs a base backup plus archived WAL (archive_mode = on). To undo a bad DELETE, restore the base backup, set restore_command and recovery_target_time in postgresql.conf, create recovery.signal and start: it replays WAL up to that moment (no recovery.conf since PostgreSQL 12). PostgreSQL 17 added incremental backups (pg_basebackup --incremental, pg_combinebackup). See continuous archiving.

  7. What is pg_hba.conf, and how is it different from GRANT in PostgreSQL?

    pg_hba.conf decides who may connect (authentication); GRANT decides what a connected role may do (authorization). Each pg_hba.conf line matches a connection type, database, user and client address, and names a method:

    # TYPE  DATABASE  USER      ADDRESS      METHOD
    local   all       postgres               peer
    host    shop      app_user  10.0.0.0/8   scram-sha-256
    host    all       all       0.0.0.0/0    reject
    

    The first matching line wins, and no match means rejected. Reload after editing with SELECT pg_reload_conf();. scram-sha-256 has been the default password hashing since PostgreSQL 14.

    CREATE ROLE app_user LOGIN PASSWORD 'change-me';
    GRANT CONNECT ON DATABASE shop TO app_user;
    GRANT USAGE ON SCHEMA public TO app_user;
    GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;
    

    ON ALL TABLES covers existing tables only; ALTER DEFAULT PRIVILEGES covers tables the running role creates later.

  8. Does row-level security apply to the table owner in PostgreSQL?

    PostgreSQL
    CREATE TABLE docs (id int, owner text, body text);
    INSERT INTO docs VALUES (1, current_user, 'mine'), (2, 'bob', 'theirs');
    ALTER TABLE docs ENABLE ROW LEVEL SECURITY;
    CREATE POLICY own_rows ON docs USING (owner = current_user);
    SELECT count(*) FROM docs;

    It returns 2. Row-level security filters rows per role through policies, but superusers, roles with BYPASSRLS and the table's owner skip it, and this session owns the table. FORCE ROW LEVEL SECURITY makes the owner obey too:

    PostgreSQL
    CREATE TABLE docs (id int, owner text, body text);
    INSERT INTO docs VALUES (1, current_user, 'mine'), (2, 'bob', 'theirs');
    ALTER TABLE docs ENABLE ROW LEVEL SECURITY;
    ALTER TABLE docs FORCE ROW LEVEL SECURITY;
    CREATE POLICY own_rows ON docs USING (owner = current_user);
    SELECT id, body FROM docs;

    Now it returns only 1 | mine. With RLS enabled and no policy, everyone it applies to sees zero rows. Multi-tenant apps often use USING (tenant_id = current_setting('app.tenant_id')::int). Test policies as the application role, never as the owner. See row security policies.

  9. Why use PgBouncer instead of raising max_connections in PostgreSQL?

    Each connection is a separate server process with its own memory, so thousands of idle connections waste RAM and add contention; max_connections (default 100) also needs a restart to change. PgBouncer shares a few server connections among thousands of clients.

    Pool modeServer connection held forBreaks
    sessionthe client sessionlittle, saves little
    transactionone transactionSET, advisory locks, LISTEN
    statementone statementmulti-statement transactions

    Web apps usually run transaction mode, with SET LOCAL.

  10. How do you find and stop a long-running query in PostgreSQL (pg_cancel_backend vs pg_terminate_backend)?

    Find it in pg_stat_activity (one row per server process). pg_cancel_backend(pid) cancels its current query and keeps the session connected; pg_terminate_backend(pid) closes the connection and rolls back its open transaction.

    PostgreSQL
    SELECT pid = pg_backend_pid() AS is_me, state, left(query, 40) AS query
    FROM pg_stat_activity
    WHERE pid = pg_backend_pid();

    That shows your own session as active. On a server:

    SELECT pid, now() - query_start AS runtime, state, wait_event_type, query
    FROM pg_stat_activity
    WHERE state <> 'idle' ORDER BY runtime DESC;
    SELECT pg_cancel_backend(12345);     -- try this first
    SELECT pg_terminate_backend(12345);
    SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type = 'Lock';
    

    An idle in transaction session has no query to cancel, so terminate it. Signaling another role's session needs membership in pg_signal_backend (or superuser); statement_timeout prevents runaways.

  11. How do you upgrade PostgreSQL to a new major version?

    A minor release (17.1 to 17.2) only swaps binaries and restarts. A major version (16 to 17) changes the on-disk format, so pick a path:

    MethodDowntime
    pg_dump and restoregrows with data size
    pg_upgrademinutes with --link or --clone
    logical replicationa short switchover
    pg_upgrade --old-bindir /usr/lib/postgresql/16/bin --new-bindir /usr/lib/postgresql/17/bin \
      --old-datadir /var/lib/postgresql/16/main --new-datadir /var/lib/postgresql/17/main \
      --link --check        # dry run; run again without --check
    vacuumdb --all --analyze-in-stages
    

    pg_upgrade to 17 keeps the data files but not the planner statistics, so run vacuumdb before sending traffic. With --link, the old cluster cannot be started safely once the new one has run. See pg_upgrade.

Coddy programming languages illustration

Learn to code with Coddy

Get started