What is the difference between
VACUUM,VACUUM FULLand autovacuum in PostgreSQL?UPDATEandDELETEleave dead row versions behind (MVCC). PlainVACUUMmarks their space reusable and runs alongside normal traffic, but shrinks the file only when pages at its end are empty.VACUUM FULLrewrites the table and returns the space to the operating system, but holds anACCESS EXCLUSIVElock (no reads or writes) and needs disk for the copy. Autovacuum runsVACUUMandANALYZEin the background as rows change.PostgreSQLCREATE 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
VACUUMand drops to 87 afterVACUUM FULL. To shrink without the long lock, use thepg_repackextension.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_limitif workers are throttled.idle_in_transaction_session_timeoutends forgotten sessions; a standby withhot_standby_feedback = oncan pin rows too.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.
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 nearsmax_wal_size(default 1GB). Frequent checkpoints cost I/O; rare ones lengthen crash recovery. Frequent-checkpoint warnings in the log meanmax_wal_sizeis too small.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.
Streaming Logical Copies the whole cluster chosen tables Replica read-only hot standby writable database Versions same major version may differ DDL and sequences included not replicated Setting wal_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.
What is the difference between
pg_dumpandpg_basebackup, and how does point-in-time recovery work?pg_dumpis a logical backup of one database: consistent, selective, and restorable into a newer major version.pg_basebackupcopies 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 clusterPITR needs a base backup plus archived WAL (
archive_mode = on). To undo a badDELETE, restore the base backup, setrestore_commandandrecovery_target_timeinpostgresql.conf, createrecovery.signaland start: it replays WAL up to that moment (norecovery.confsince PostgreSQL 12). PostgreSQL 17 added incremental backups (pg_basebackup --incremental,pg_combinebackup). See continuous archiving.What is
pg_hba.conf, and how is it different fromGRANTin PostgreSQL?pg_hba.confdecides who may connect (authentication);GRANTdecides what a connected role may do (authorization). Eachpg_hba.confline 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 rejectThe first matching line wins, and no match means rejected. Reload after editing with
SELECT pg_reload_conf();.scram-sha-256has 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 TABLEScovers existing tables only;ALTER DEFAULT PRIVILEGEScovers tables the running role creates later.Does row-level security apply to the table owner in PostgreSQL?
PostgreSQLCREATE 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
BYPASSRLSand the table's owner skip it, and this session owns the table.FORCE ROW LEVEL SECURITYmakes the owner obey too:PostgreSQLCREATE 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 useUSING (tenant_id = current_setting('app.tenant_id')::int). Test policies as the application role, never as the owner. See row security policies.Why use PgBouncer instead of raising
max_connectionsin 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 mode Server connection held for Breaks session the client session little, saves little transaction one transaction SET, advisory locks,LISTENstatement one statement multi-statement transactions Web apps usually run transaction mode, with
SET LOCAL.How do you find and stop a long-running query in PostgreSQL (
pg_cancel_backendvspg_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.PostgreSQLSELECT 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 transactionsession has no query to cancel, so terminate it. Signaling another role's session needs membership inpg_signal_backend(or superuser);statement_timeoutprevents runaways.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:
Method Downtime pg_dumpand restoregrows with data size pg_upgrademinutes with --linkor--clonelogical replication a 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-stagespg_upgradeto 17 keeps the data files but not the planner statistics, so runvacuumdbbefore sending traffic. With--link, the old cluster cannot be started safely once the new one has run. See pg_upgrade.
PostgreSQL DBA Interview Questions
Vacuum and bloat, wraparound, WAL, replication, backups and recovery, access control, pooling, session monitoring and major version upgrades.