PostgreSQL Operations: interview questions

What does an SRE need to know to operate a PostgreSQL database they do not own? Mid

How it is built: a postmaster forking one process per connection, shared memory, and the WAL every change goes through. The ways it fails and the first checks for each: connection exhaustion (max_connections, pools x replicas, PgBouncer), lock chains (pg_stat_activity, pg_blocking_pids), vacuum held back by long transactions (bloat, XID age), disk filling with WAL (slots, a failing archive), replication lag, slow queries (pg_stat_statements, EXPLAIN), risky migrations (lock_timeout, CONCURRENTLY), and backups that were never restored. Plus the difference between a reload and a restart, and monitoring that alerts on saturation before the outage.

Also asked: How would you find what is making a PostgreSQL server slow right now? · What PostgreSQL metrics would you alert on? · How do you make sure database backups actually work?

Describe PostgreSQL's process model. Why does it matter when operating it? Mid

The postmaster listens on the port and forks one backend process per client connection; background processes (checkpointer, background writer, walwriter, autovacuum launcher, io workers in 18) do shared work, and all of them share memory. It matters because every connection is a process and a slot in max_connections, so you pool connections instead of opening thousands; because one backend can be ended with pg_terminate_backend without touching others, while kill -9 on a backend makes the postmaster restart every connection; and because pg_stat_activity shows one row per process - the first place to look in any incident.

Also asked: What is the WAL and why does PostgreSQL write it before changing data files? · What are the first checks you run when a PostgreSQL alert fires? · How do minor and major PostgreSQL upgrades differ?

Learn it: 25.1 What an SRE owns in PostgreSQL

You changed a PostgreSQL setting and nothing happened. Why might that be? Junior

Check the setting's context in pg_settings: a postmaster setting such as max_connections or shared_buffers only applies after a restart - a reload logs "cannot be changed without restarting the server" and sets pending_restart. If it is a reloadable setting, something else may override it: postgresql.auto.conf (from ALTER SYSTEM) is read after postgresql.conf, a later file in conf.d wins, or a role or database has its own SET. pg_file_settings shows each file and line and whether it applied. Finally, confirm the reload actually happened by reading the server log.

Also asked: Where are the PostgreSQL configuration files and logs on Ubuntu? · What is peer authentication and why does sudo -u postgres psql work? · What is the difference between a fast and an immediate shutdown?

Learn it: 25.2 Install: clusters, files, the service and settings

How do you run a SQL script safely from CI or cron with psql? Junior

psql -X -v ON_ERROR_STOP=1 -1 -f script.sql. -X skips ~/.psqlrc so personal settings cannot change the behaviour; ON_ERROR_STOP makes psql stop at the first error and exit with code 3 - without it psql runs every statement and exits 0, so a failed migration looks successful; -1 wraps the file in one transaction so a failure leaves nothing half applied. For single values, -XAtc gives the bare result. Keep passwords out of the command line with ~/.pgpass (mode 0600) or identity-based auth.

Also asked: How do you see the definition and indexes of a table in psql? · What is the difference between COPY and \copy? · How do you avoid leaving a transaction open in an interactive session?

Learn it: 25.4 psql fluency

A new app on Kubernetes cannot connect to PostgreSQL. How do you debug it? Junior

Read the exact error, then the server log. connection refused means nothing listens on that address: the server is down, the port is wrong, or listen_addresses is still localhost (a restart setting). no pg_hba.conf entry for host ... means no rule matched: add a narrow host <db> <role> <pod CIDR> scram-sha-256 line above any broader reject rule and reload, checking pg_hba_file_rules first. password authentication failed means read the log's DETAIL (wrong password, missing role, expired VALID UNTIL). permission denied for database means no CONNECT privilege.

Also asked: How would you design database roles for an application? · What does "first match wins" mean in pg_hba.conf? · Why should an application never connect as a superuser?

Learn it: 25.7 Roles, privileges and pg_hba.conf

Your app gets "FATAL: sorry, too many clients already" after scaling out. What happened and how do you fix it? Mid

Each instance opens its own connection pool, so connections = replicas x pool size plus every other client; the scale-out pushed that past max_connections minus superuser_reserved_connections. Short term: log in through a reserved superuser slot, count connections by application_name in pg_stat_activity, cap the app with ALTER ROLE ... CONNECTION LIMIT or shrink its pool, and free idle connections so critical clients get back in. Proper fix: PgBouncer in transaction mode (or the platform's pooler) so many clients share a few server connections, pools sized from the server's cores, and an alert at about 80% of max_connections.

Also asked: What does PgBouncer transaction pooling break? · How do you size an application connection pool? · What are superuser_reserved_connections for?

Learn it: 25.15 Connections, pools and PgBouncer

Requests time out but the database CPU is idle. What do you check? Mid

Waiting, not working - almost always locks. In pg_stat_activity look for many sessions active with wait_event_type = Lock and for sessions idle in transaction with an old xact_start. Use pg_blocking_pids(pid) to walk the chain to its root (a blocker that is not blocked itself), and pg_locks with granted = false for detail. Save what the root was doing, then end it with pg_terminate_backend - an idle session has no query to cancel. Prevent it with idle_in_transaction_session_timeout for the app role, lock_timeout for migrations and log_lock_waits.

Also asked: What is MVCC and why do readers not block writers? · What is the difference between pg_cancel_backend and pg_terminate_backend? · How should an application handle deadlocks?

Learn it: 25.21 Transactions, MVCC and locks

Autovacuum runs all the time but tables keep bloating. Why? Mid

Because VACUUM can only remove dead tuples older than the xmin horizon, and something holds it back - VACUUM VERBOSE reports them as dead but not yet removable. The usual holders, in order: a long or idle in transaction session (backend_xmin and xact_start in pg_stat_activity), a forgotten prepared transaction, a replica with hot_standby_feedback running a long query, or a replication slot. Remove the holder, then let autovacuum or a manual VACUUM catch up. Also check that autovacuum is not disabled per table (autovacuum_enabled = false) and that big tables have a smaller scale factor.

Also asked: What does VACUUM do, and how is it different from VACUUM FULL? · What is transaction ID wraparound and how do you monitor it? · How would you reclaim disk space from a bloated table without downtime?

Learn it: 25.24 VACUUM, autovacuum, bloat and XID wraparound

A query got slow. How do you find out why? Mid

Run it with EXPLAIN (ANALYZE, BUFFERS) (inside BEGIN/ROLLBACK if it writes) and read from the innermost node out. Look for a Seq Scan with a large Rows Removed by Filter, estimated rows far from actual rows (stale statistics - run ANALYZE), a sort spilling to disk (work_mem), or nested loops over many rows. Fix it with an index that matches the query - a composite index with equality columns first and then the ORDER BY column, an expression index for functions like lower(email), a partial index for a hot subset - built with CREATE INDEX CONCURRENTLY, then compare the new plan.

Also asked: What is the difference between EXPLAIN and EXPLAIN ANALYZE? · When is a sequential scan the right plan? · How do you find unused indexes?

Learn it: 25.32 Indexes and EXPLAIN

The database is slow overall and nothing shows up in the slow-query log. How do you find the responsible query? Mid

pg_stat_statements: it keeps calls, total and mean time, rows and buffers per normalized query. It needs shared_preload_libraries = 'pg_stat_statements' (a restart) and CREATE EXTENSION. Sort by total_exec_time to find what costs the server the most - often a query of a few milliseconds called thousands of times, which a log_min_duration_statement threshold never catches. Then EXPLAIN the top query, fix it (usually an index), call pg_stat_statements_reset() and compare the same load before and after.

Also asked: What is the difference between total and mean execution time? · How do you set up slow-query logging in PostgreSQL? · What else besides a bad query can make a database slow?

Learn it: 25.35 Slow queries: the log and pg_stat_statements

A migration that only adds a column took the site down. What happened? Mid

ALTER TABLE needs an ACCESS EXCLUSIVE lock. A long-running transaction held an ACCESS SHARE lock on the table, so the ALTER waited - and in the lock queue every later query on the table, even plain SELECTs, waited behind the ALTER. The column itself is a catalog-only change that takes milliseconds. In the incident, cancel the migration so the queue drains, then find the long transaction. For the future: run DDL with SET lock_timeout = '3s' and retries, check for long transactions before deploying, use CREATE INDEX CONCURRENTLY and NOT VALID + VALIDATE CONSTRAINT for the expensive parts.

Also asked: Which ALTER TABLE operations rewrite the table? · Why can CREATE INDEX CONCURRENTLY not run in a transaction? · How would you backfill a new column on a table with hundreds of millions of rows?

Learn it: 25.38 Safe migrations on a live database

How do you know your PostgreSQL backups work? Mid

By restoring them, regularly and automatically. A nightly job restores the newest pg_dump archive into a scratch database (after the globals from pg_dumpall --globals-only), checks row counts, the newest timestamps and an application query, and alerts when anything fails. The backup job itself must fail loudly (set -euo pipefail, alerts on failure and on tiny files). And you know what each backup gives you: pg_dump is a consistent logical snapshot of one database with an RPO of the time since the dump; physical base backups with archived WAL give point-in-time recovery. Measure the restore time against the RTO.

Also asked: What is the difference between a logical and a physical backup? · What does pg_dump lock while it runs? · What are RPO and RTO?

Learn it: 25.43 Backups: pg_dump, pg_restore and testing restores

Someone deleted rows an hour ago. How do you recover them? Mid

With PITR: restore the most recent base backup (pg_basebackup) into a separate cluster, set restore_command to fetch the archived WAL and recovery_target_time to just before the delete (recovery_target_inclusive = off), create recovery.signal and start it. The log should say "recovery stopping before commit of transaction ...". Check the rows are there, copy only the lost rows back into production, and drop the recovery cluster. This needs continuous WAL archiving and base backups set up before the accident; a nightly pg_dump would only give you last night's data.

Also asked: What is the WAL and what is a checkpoint? · How do you know WAL archiving is working? · What is the difference between promoting a recovered server and pausing it?

Learn it: 25.47 WAL, archiving and point-in-time recovery

The primary's disk is filling with WAL. What do you check? Mid

First replication slots: pg_replication_slots with active = false and a large pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) - a slot whose replica is gone keeps WAL forever. Then the archiver: pg_stat_archiver failed_count and "archive command failed" in the log, because unarchived WAL is kept too. Drop the stale slot (its replica will need a new base backup), fix archiving, CHECKPOINT so old segments are recycled, and set max_slot_wal_keep_size plus alerts on inactive slots. Never delete files from pg_wal by hand.

Also asked: How do you measure replication lag? · Walk through a safe failover to a replica. · What is split brain and how do you prevent it?

Learn it: 25.52 Replication and failover

What would you monitor and alert on for a PostgreSQL server? Mid

With postgres_exporter (a pg_monitor role, not superuser) scraped by a metrics server: availability - pg_up and the exporter target's own up; saturation - client connections as a share of max_connections (alert around 80%), disk on the data and WAL volumes with predict_linear, XID age; the silent killers - the oldest transaction and idle-in-transaction sessions, inactive replication slots and their retained WAL, a failing WAL archive; and replication lag in seconds and bytes. Use for: durations, page only on user impact, ticket the rest, and keep a dashboard next to the alerts.

Also asked: Why should the monitoring role not be a superuser? · How do you avoid alerts going silent when the exporter dies? · How would you alert before the database runs out of connections?

Learn it: 25.58 Monitoring PostgreSQL

We moved to managed PostgreSQL. What is still our job? Mid

The provider runs the hardware, OS, binaries, minor patching, backups and HA failover. Still ours: connections and pooling (pool size x replicas against max_connections, the built-in PgBouncer or RDS Proxy); credentials (prefer identity-based tokens, rotate the rest); network exposure (private access, TLS); capacity (storage growth, IOPS limits); the data - schema, indexes, queries, vacuum health, safe migrations - which works exactly as on our own servers; and drills: point-in-time restore creates a new server that the apps must be pointed at, and planned failovers show whether apps reconnect. And we have no SUPERUSER, so configuration goes through server parameters.

Also asked: How do backups and point-in-time restore work on a managed PostgreSQL? · What changes about authentication on a managed database? · How would you compare the managed PostgreSQL offerings (RDS, Aurora, Cloud SQL)?

Learn it: 25.61 Managed PostgreSQL: what you still own