PostgreSQL interview questions and answers

1. Naming a test that uses a real DB

A test starts PostgreSQL in a Docker container, calls your repository's saveOrder() function, and then queries the table to check the row. What kind of test is this?

1. A.Unit test
2. B.End-to-end UI test
3. C.Contract test
4. D.Integration test

2. Including Postgres in your chart

Your app chart should install a Postgres chart from a repository as part of the same release. Where do you declare that?

1. A.In a separate values-postgres.yaml file
2. B.In the templates folder as a Deployment
3. C.In the dependencies list in Chart.yaml
4. D.In the .helmignore file

3. Spotting a single point of failure

Your app runs on 6 servers across 3 availability zones, but they all connect to one PostgreSQL instance on one VM. What is the single point of failure?

1. A.The 6 app servers
2. B.The 3 availability zones
3. C.The one database instance
4. D.The load balancer health checks

4. Turning a standby into the primary

The primary has failed for good and you must promote a PostgreSQL standby by hand. What do you run on the standby?

1. A.pg_resetwal -f
2. B.SELECT pg_reload_conf();
3. C.SELECT pg_promote();
4. D.VACUUM FULL on all tables

5. Restoring one dropped table

Someone dropped a small lookup table. You have nightly pg_dump custom-format backups and weekly physical backups. What is quickest for just that table?

1. A.Restore the whole physical backup over production
2. B.pg_restore -t tablename from the latest dump
3. C.Promote a replica to primary
4. D.Run VACUUM to undo the DROP

6. What a hash index supports

A PostgreSQL table has a hash index on session_token. Which query can this index serve?

1. A.WHERE session_token > 'abc'
2. B.ORDER BY session_token LIMIT 10
3. C.WHERE session_token LIKE 'abc%'
4. D.WHERE session_token = 'abc123'

7. EXPLAIN versus EXPLAIN ANALYZE

You want to check the plan of a DELETE on a production PostgreSQL table without changing any data. Why is plain EXPLAIN ANALYZE dangerous here?

1. A.EXPLAIN ANALYZE really executes the statement, so rows get deleted
2. B.EXPLAIN ANALYZE locks the table until the server restarts
3. C.EXPLAIN ANALYZE drops the indexes it does not use
4. D.EXPLAIN ANALYZE only works on SELECT and errors on DELETE

8. What ANALYZE does

What does running ANALYZE orders; do in PostgreSQL?

1. A.Samples the table and updates statistics the planner uses for estimates
2. B.Rebuilds all indexes on orders to remove bloat
3. C.Runs every query on orders and reports the slow ones
4. D.Removes dead rows and returns space to the operating system

9. What Seq Scan means

A PostgreSQL plan shows "Seq Scan on invoices". What is the database doing?

1. A.Reading only the rows in sequence-number order
2. B.Using the primary key index to find rows
3. C.Reading every page of the invoices table in order
4. D.Waiting for another query to finish

10. What UPDATE does under MVCC

In PostgreSQL, you run UPDATE on one row of a table. What happens to the table on disk?

1. A.The row is changed in place and the old value is lost
2. B.The whole page is copied to a new file
3. C.A new row version is written and the old one is marked dead
4. D.The old version is moved to a separate undo log

11. Applying a pg_hba.conf change

You added a line to pg_hba.conf to let a new app subnet connect. How do you make PostgreSQL use it without dropping existing sessions?

1. A.Restart the server with pg_ctl restart
2. B.Run VACUUM on the pg_hba table
3. C.Nothing; the file is read on every query
4. D.Run SELECT pg_reload_conf() or pg_ctl reload

12. VACUUM vs ANALYZE

What is the difference between VACUUM and ANALYZE in PostgreSQL?

1. A.VACUUM reclaims dead rows; ANALYZE updates planner statistics
2. B.VACUUM updates statistics; ANALYZE reclaims dead rows
3. C.Both do the same thing with different lock levels
4. D.ANALYZE rebuilds indexes; VACUUM rebuilds tables

13. Role for a monitoring agent

A monitoring agent needs to read pg_stat_activity for all sessions and other stats views, but should not be a superuser. What should you grant it?

1. A.The predefined role pg_monitor
2. B.The SUPERUSER attribute
3. C.Ownership of every table
4. D.The REPLICATION attribute

14. Client TLS mode that stops spoofing

Which libpq sslmode encrypts the connection and also checks that the server certificate matches the host name?

1. A.require
2. B.verify-full
3. C.prefer
4. D.verify-ca

15. Which postgresql.conf is in use

A server has several postgresql.conf files from old installs. How do you find the one the running server actually reads?

1. A.Run SELECT version()
2. B.Look at the newest file on disk
3. C.Read pg_hba.conf for its path
4. D.Run SHOW config_file in psql

16. Does VACUUM block the app

Will a plain VACUUM (not FULL) on the orders table block the app's SELECT, INSERT and UPDATE statements?

1. A.No, its lock allows normal reads and writes
2. B.Yes, it blocks all reads until it ends
3. C.Yes, it blocks writes but allows reads
4. D.Only if the table has foreign keys on it

17. When others see your changes

Session A runs BEGIN; UPDATE products SET price = 99 WHERE id = 7; and has not committed. Session B, in Read Committed, reads product 7. What does B see?

1. A.The new price of 99
2. B.The old price, until A commits
3. C.An error that the row is locked
4. D.Nothing; the read waits for A

18. How committed work survives a crash

PostgreSQL crashes right after a COMMIT returned, before the changed data pages were written to the table files. What happens at restart?

1. A.The committed change is lost
2. B.WAL from the last checkpoint is replayed
3. C.The table is restored from the last backup
4. D.The change is rolled back for safety

19. Operator, task and task instance

A DAG defines load = PostgresOperator(...) and runs daily. What is a task instance?

1. A.The Python class PostgresOperator itself
2. B.One run of the load task for one DAG run
3. C.The worker machine that executes the task
4. D.The DAG file that contains the load task

20. What Kafka Connect is for

You need to copy rows from Postgres into Kafka and from Kafka into S3, without writing custom consumers. What is the usual tool?

1. A.Kafka Streams
2. B.Kafka Connect
3. C.The console consumer
4. D.MirrorMaker

21. Committed orders missing after failover

A PostgreSQL primary with asynchronous replication crashes, and a replica is promoted. A few orders that customers saw as confirmed are missing. Why?

1. A.Promoted replicas delete the most recent orders
2. B.Those orders were only ever stored on the replica
3. C.The primary encrypted the last few orders it saved
4. D.The replica lagged, so the last commits never arrived

22. Why PostgreSQL needs VACUUM

Why does PostgreSQL need VACUUM (or autovacuum) on tables with many UPDATEs?

1. A.To rebuild all indexes from scratch every night
2. B.To copy the table to replicas
3. C.To compress rows so they use less memory in shared_buffers
4. D.Because UPDATE writes a new row version, and the old dead versions must be cleaned up so their space can be reused

23. Why use a connection pooler

Hundreds of app containers each open 20 connections to PostgreSQL. Why is a pooler like PgBouncer helpful?

1. A.Each PostgreSQL connection is a separate backend process with its own memory, so a pooler lets many clients share a small number of server connections
2. B.PgBouncer caches query results so the database is never queried twice
3. C.PgBouncer turns asynchronous replication into synchronous replication
4. D.PostgreSQL cannot accept more than 10 connections without a pooler

24. Workers fighting over jobs

Ten workers pick jobs with SELECT ... WHERE status = 'pending' LIMIT 1 FOR UPDATE, and they mostly wait on each other. Which change lets them grab different jobs in parallel?

1. A.Add FOR UPDATE SKIP LOCKED so each worker skips rows already locked by others
2. B.Use SERIALIZABLE isolation
3. C.Remove FOR UPDATE completely
4. D.Add NOWAIT and retry in a tight loop

25. Measuring replica lag

On a PostgreSQL primary, which query best shows how far each streaming replica is behind?

1. A.SELECT * FROM pg_stat_user_tables;
2. B.SELECT application_name, write_lag, flush_lag, replay_lag FROM pg_stat_replication;
3. C.SELECT now() - pg_postmaster_start_time();
4. D.SHOW max_wal_senders;

26. Reports cancelled on the replica

Long reports on a hot standby fail with "canceling statement due to conflict with recovery". What is happening?

1. A.The standby ran out of disk space
2. B.The report used too much work_mem
3. C.Replayed changes, such as vacuum removing old rows, conflict with rows the report still needs, so the standby cancels it
4. D.The primary is down and the standby is promoting

27. Why a Bitmap Heap Scan

For a query that matches about 3% of a large table, PostgreSQL chooses a Bitmap Index Scan followed by a Bitmap Heap Scan. What is the benefit over a plain Index Scan?

1. A.It avoids reading the index entirely and scans the table
2. B.It visits matching table pages in physical order, once each
3. C.It returns rows already sorted by the index key
4. D.It locks fewer rows than a plain Index Scan does

28. Stopping overlapping room bookings

In PostgreSQL you want the database itself to reject two bookings of the same room whose time ranges overlap. What do you use?

1. A.A unique index on (room_id, start_time)
2. B.An exclusion constraint using a GiST index
3. C.A hash index on room_id and end_time
4. D.A BRIN index on the end_time column

29. Vacuuming one hot table more often

One 50 crore row events table builds up huge bloat between autovacuum runs, but other tables are fine. What is the targeted fix?

1. A.Set autovacuum = off and run VACUUM FULL nightly
2. B.Raise the global scale factor to 0.5
3. C.ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01)
4. D.Increase max_connections so vacuum gets a slot

30. Making pg_restore use 8 cores

You want pg_restore -j 8 to restore a 400 GB database in parallel. Which dump format allows this?

1. A.Plain SQL format (-Fp)
2. B.Custom (-Fc) or directory (-Fd) format
3. C.Any format if the dump is gzipped
4. D.Only a tar format with --inserts

31. I/O spikes at every checkpoint

Disk latency jumps every few minutes when a checkpoint flushes dirty pages, slowing queries. Which setting spreads the checkpoint writes over more time?

1. A.checkpoint_timeout = 30 seconds
2. B.bgwriter_lru_maxpages = 0 always
3. C.full_page_writes = off globally
4. D.checkpoint_completion_target = 0.9

32. Seeing the plan of a slow query in prod

A query is slow only sometimes in production, and by the time you run EXPLAIN it is fast. How can PostgreSQL log the plan of the slow runs?

1. A.Set log_statement = 'all' for a day
2. B.Run pg_stat_reset() every hour
3. C.Turn on log_checkpoints and log_lock_waits
4. D.Load auto_explain with a duration limit

33. What effective_cache_size does

A junior DBA is worried that setting effective_cache_size = 48GB on a 64 GB server will use up 48 GB of RAM. What is true?

1. A.It reserves 48 GB of shared memory at startup
2. B.It limits the OS page cache to 48 GB
3. C.It only tells the planner how much cache to expect
4. D.It sets the per-query sort memory

34. Deep pages load slowly

An API pages through orders with ORDER BY id LIMIT 50 OFFSET 500000, and later pages get slower. What is the better pattern?

1. A.Increase work_mem so OFFSET is faster
2. B.WHERE id > last_seen_id ORDER BY id LIMIT 50
3. C.ORDER BY random() LIMIT 50
4. D.Add OFFSET to the index definition

35. Big aggregate uses one CPU

A large SUM over a 100 GB table uses only one CPU core though the server has 32. EXPLAIN shows no Gather node. Which setting is most directly involved?

1. A.max_wal_senders
2. B.autovacuum_max_workers
3. C.max_prepared_transactions
4. D.max_parallel_workers_per_gather

36. Stopping one query filling the disk

A bad ad hoc query once wrote 400 GB of temp files and filled the data disk. Which setting stops a single session from doing this?

1. A.work_mem
2. B.max_wal_size
3. C.temp_file_limit
4. D.shared_buffers

37. Only one copy of a cron job

Three app servers run the same nightly settlement job. Only one should run it at a time, and the lock must free itself if the job's transaction ends. Which PostgreSQL feature fits?

1. A.LOCK TABLE settlements IN ACCESS EXCLUSIVE MODE for the night
2. B.A row in a jobs table with no locking
3. C.SELECT ... FOR SHARE on a dummy row
4. D.pg_try_advisory_xact_lock(key) at the start of the job

38. Preventing overlapping room bookings

Two users book the same meeting room for overlapping times at the same moment, and both succeed. Which PostgreSQL feature blocks overlaps in the database?

1. A.An exclusion constraint on room and range
2. B.A UNIQUE constraint on start_time
3. C.A CHECK that start_time < end_time
4. D.A B-tree index on (room_id, start)

39. RETURNING gives no row on conflict

Code runs the query below and reads id from the result. When the email already exists, the result is empty and the app crashes. Why?

INSERT INTO users (email) VALUES ($1)
ON CONFLICT (email) DO NOTHING
RETURNING id;

1. A.RETURNING only works with DO UPDATE
2. B.The unique index was dropped
3. C.DO NOTHING returns no row when it skips
4. D.The INSERT is waiting for a lock

40. Defining a connection in an env var

In Kubernetes you set AIRFLOW_CONN_SALES_DB=postgresql://etl:pw@db.internal:5432/sales. What conn_id do tasks use for it?

1. A.sales_db
2. B.AIRFLOW_CONN_SALES_DB
3. C.postgresql
4. D.db.internal

41. Table bloat that VACUUM will not fix

A PostgreSQL table keeps growing and queries get slower. Autovacuum runs often, but the number of dead rows does not go down. What is the most likely cause?

1. A.Autovacuum is turned off by default and must be enabled
2. B.The table has too many indexes, so autovacuum gives up before finishing each run and leaves dead rows behind
3. C.The table has no primary key
4. D.An old open transaction (e.g. "idle in transaction") or replication slot holds back cleanup

42. Queries keep running after the client quits

An app sets a 3-second client timeout on reports. After timeouts, the slow queries keep running in PostgreSQL for minutes, holding locks and CPU. What should be added?

1. A.A longer client timeout of 10 minutes
2. B.A second read replica for the same queries
3. C.A retry so the client gets a result eventually
4. D.A server-side statement_timeout

43. Too many database connections

After moving the API to serverless functions, PostgreSQL starts failing with "too many connections" during traffic spikes. What is the best fix?

1. A.Open a new database connection inside every request and never close it
2. B.Use a connection pooler such as PgBouncer, RDS Proxy or a provider pooled endpoint, and create the client once outside the handler so warm instances reuse it
3. C.Increase max_connections to 100000
4. D.Switch every query to run inside one long transaction

44. Unique email with soft deletes

users has UNIQUE(email) and soft deletes via a deleted_at column. A user deletes their account and later signs up again with the same email, which fails with a unique violation. What is the best fix in PostgreSQL?

1. A.Replace it with UNIQUE(email, deleted_at)
2. B.Drop the constraint and check for duplicates in application code before inserting
3. C.CREATE UNIQUE INDEX ON users (email) WHERE deleted_at IS NULL
4. D.Switch to hard deletes; soft deletes cannot work with unique columns

45. pg_wal growing without limit

There are no replication slots, but pg_wal keeps growing and the disk will be full in hours. pg_stat_archiver shows failed_count rising. What is happening?

1. A.archive_command is failing, so PostgreSQL keeps every unarchived WAL segment; fix the archiving destination or credentials
2. B.max_wal_size is too small
3. C.Autovacuum writes extra WAL that is never removed
4. D.A checkpoint is stuck because of a long SELECT

46. Native incremental backups in PostgreSQL 17

PostgreSQL 17 added pg_basebackup --incremental. What else is needed to get a restorable data directory?

1. A.pg_combinebackup to merge the chain
2. B.pg_resetwal to join the backups
3. C.pg_dumpall to replay changes
4. D.Nothing; each incremental is complete

47. Running PostgreSQL on Kubernetes

A team runs PostgreSQL in a plain Deployment with one replica and a network volume, and calls it HA because Kubernetes restarts the pod. What is missing?

1. A.A larger CPU limit on the pod
2. B.A second Service object
3. C.An Ingress for the database port
4. D.A standby and managed failover

48. JIT makes a query slower

A PostgreSQL query that should take 30 ms now takes 400 ms. EXPLAIN ANALYZE shows "JIT: Functions: 120, Timing: Generation 20 ms, Optimization 180 ms, Emission 150 ms". What is the fix?

1. A.Increase shared_buffers to the maximum
2. B.Add more indexes so JIT has less to compile
3. C.Run ANALYZE on the tables more often
4. D.Raise jit_above_cost or turn jit off

49. Wrong results after an OS upgrade

After moving a PostgreSQL data directory to a new OS image with a newer glibc, some unique indexes on text columns return wrong results and allow duplicates. What happened?

1. A.The new OS turned off data checksums
2. B.Text sort order changed, so indexes need REINDEX
3. C.pg_hba.conf rules were reset by the OS
4. D.The WAL format changed with the new kernel

50. Checking an index for corruption

You suspect a B-tree index is corrupt after a storage incident but want proof before a long REINDEX. Which tool checks it?

1. A.The amcheck extension's bt_index_check()
2. B.pg_stat_statements for the index's queries
3. C.pg_dumpall --globals-only
4. D.ANALYZE VERBOSE on the table

51. ADD COLUMN that rewrote the table

On PostgreSQL 16, ALTER TABLE t ADD COLUMN a int DEFAULT 0 finished instantly, but ADD COLUMN b uuid DEFAULT gen_random_uuid() locked the table for an hour. Why?

1. A.uuid columns always need a full table rewrite
2. B.The second command had no index on the column
3. C.gen_random_uuid() takes a lock on every row
4. D.A volatile default must be stored in every row

52. Disk full with a large pg_wal

The disk is 100% full and PostgreSQL has stopped. A teammate wants to delete old files in pg_wal by hand to free space. What should you do instead?

1. A.Delete every pg_wal file older than one hour
2. B.Add disk space, then fix why WAL is kept, e.g. a stuck slot
3. C.Run pg_resetwal so recovery is skipped
4. D.Delete the newest pg_wal files, since older ones are archived

53. Unlogged table empty after a crash

A team made a staging table UNLOGGED to load faster. After a server crash and restart the table is empty, and a replica never had its data. Why?

1. A.A crash always truncates every table in the database
2. B.Replicas copy unlogged tables only once a day
3. C.Unlogged tables skip WAL, so they are reset after a crash
4. D.The table was not vacuumed before the crash

54. Index-only scans slow on an insert-only table

On PostgreSQL 12, an insert-only audit table never had autovacuum run, and index-only scans keep visiting the heap. What changed in PostgreSQL 13 to address this?

1. A.Index-only scans no longer check visibility
2. B.Insert-only tables are frozen at insert time
3. C.Autovacuum now also runs after many inserts
4. D.The visibility map is kept by ANALYZE only

55. Standbys after pg_upgrade --link

You ran pg_upgrade --link on the primary of a cluster with two physical standbys. What is the documented way to upgrade the standbys quickly?

1. A.Start them on the new binaries; they catch up from WAL
2. B.Run pg_upgrade on each standby on its own
3. C.Convert them to logical replicas automatically
4. D.Use the rsync method from the docs before starting the primary

56. Phantoms at PostgreSQL Repeatable Read

The SQL standard allows phantom reads at Repeatable Read. What does PostgreSQL's Repeatable Read do when another transaction inserts and commits a matching row?

1. A.The new row appears in the next SELECT
2. B.The new row stays invisible for the whole transaction
3. C.The second SELECT fails with an error
4. D.The insert is blocked until the reader commits

57. How readers know a row committed

A row's xmin is transaction 9001. How does a reader in PostgreSQL find out whether 9001 committed or aborted?

1. A.It reads the WAL from the start
2. B.From pg_xact, or the row's hint bits
3. C.It asks the transaction's backend process
4. D.It assumes commit if the row is on disk

58. Median per department in PostgreSQL

An analyst writes PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept) in PostgreSQL and gets an error. Why?

1. A.In PostgreSQL it is an ordered-set aggregate, not a window function
2. B.PERCENTILE_CONT only accepts 0.25 and 0.75
3. C.It needs ROWS BETWEEN UNBOUNDED PRECEDING in the OVER
4. D.Median cannot be computed on salary data types

59. VACUUM and time travel

A Delta table runs VACUUM daily with the default 7-day retention. An auditor asks to query the table as it was 30 days ago. What happens?

1. A.The query fails or is incomplete, because data files needed by that old version were already deleted
2. B.It works, because the transaction log keeps all data forever
3. C.Delta automatically restores the files from the cloud provider
4. D.It works, but only for tables smaller than 1 GB

60. A buggy upcoming-renewals query

This PostgreSQL query should list active contracts ending in the next 90 days, but it returns contracts that ended last year. What is the bug?

SELECT account_name, end_date, arr
FROM contracts
WHERE status = 'active'
  AND end_date <= CURRENT_DATE + INTERVAL '90 days'
ORDER BY end_date;

1. A.INTERVAL cannot be added to CURRENT_DATE
2. B.No lower bound, so past end dates also match
3. C.ORDER BY must come before WHERE
4. D.The status filter removes every renewal