PostgreSQL interview questions: answers checked against the docs
The database questions back-end developers and DBAs get about PostgreSQL, answered in plain words and checked against the PostgreSQL documentation.
PostgreSQL interviews cover MVCC, VACUUM and autovacuum, isolation levels, index types and partial indexes, EXPLAIN ANALYZE, json versus jsonb, upserts, the write-ahead log, streaming and logical replication, partitioning and connection pooling. Below are 12 questions; each answer lists the PostgreSQL 18 documentation pages it was checked against.
Basic PostgreSQL interview questions
Basic1. What is MVCC in PostgreSQL?
Multiversion concurrency control: an update writes a new version of the row instead of overwriting it, and each statement or transaction reads from a snapshot of committed data. As a result, readers and writers do not block each other. The cost is that old row versions remain until VACUUM cleans them up.
Checked against: PostgreSQL docs: MVCC introduction
Basic2. What does VACUUM do, and what is autovacuum?
VACUUM reclaims the space taken by dead row versions so it can be reused, updates the statistics the planner uses (with ANALYZE), and protects against transaction ID wraparound, which would otherwise eventually force the database to stop accepting writes. The autovacuum daemon runs this automatically and is enough for many installations; heavy-write tables may need tuned settings. VACUUM FULL rewrites the whole table and locks it, so it is rarely the right fix.
SQL snippet · illustrative, not run on this pageVACUUM (VERBOSE, ANALYZE) orders;Checked against: PostgreSQL docs: routine vacuuming
Basic3. Which isolation level does PostgreSQL use by default?
Read Committed: each statement sees data committed before that statement began. Repeatable Read gives the whole transaction one snapshot taken at its start. Serializable additionally ensures the result is the same as running the transactions one at a time; transactions that would break that fail with a serialization error and must be retried by the application.
SQL snippet · illustrative, not run on this pageBEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... reads and writes ...
COMMIT; -- retry the whole transaction if it fails with a serialization errorChecked against: PostgreSQL docs: transaction isolation
Intermediate PostgreSQL interview questions
Intermediate4. What index types does PostgreSQL have, and when do you use each?
B-tree (the default) for equality and range queries on sortable data. GIN for values containing many elements, such as arrays, jsonb and full-text search. GiST is a framework for geometric data, ranges and nearest-neighbor search. BRIN stores small summaries per block range and suits very large tables whose values follow the physical order, such as timestamps in an append-only log. Hash for equality only.
SQL snippet · illustrative, not run on this pageCREATE INDEX ON events USING brin (created_at);
CREATE INDEX ON jobs USING gin (skills);Checked against: PostgreSQL docs: index types
Intermediate5. How do multicolumn and partial indexes help?
A multicolumn B-tree index works best when the query constrains its leading columns, so put the columns used for equality first. A partial index covers only the rows matching a condition, for example only open jobs, which keeps it small and fast when queries always use that condition.
SQL snippet · illustrative, not run on this pageCREATE INDEX jobs_open_posted ON jobs (company_id, posted_at) WHERE status = 'open';Checked against: PostgreSQL docs: multicolumn indexes, PostgreSQL docs: partial indexes
Intermediate6. How do you read EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the plan the planner chose, with estimated costs and row counts. EXPLAIN ANALYZE also runs the query and adds the actual row counts and times for each step, so you can spot a sequential scan where you expected an index scan, or estimates far from reality, which usually means statistics need updating. Because ANALYZE executes the statement, wrap data-changing statements in a transaction you roll back.
SQL snippet · illustrative, not run on this pageEXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM jobs WHERE company_id = 42 ORDER BY posted_at DESC LIMIT 20;Checked against: PostgreSQL docs: using EXPLAIN
Intermediate7. What is the difference between json and jsonb?
json stores the exact input text and must reparse it on each use; jsonb stores a decomposed binary form that is slightly slower to write but much faster to query, and it supports indexing (for example GIN) and containment queries. json keeps whitespace, key order and duplicate keys; jsonb does not. jsonb is the usual choice for applications.
SQL snippet · illustrative, not run on this pageSELECT id FROM candidates WHERE profile @> '{"skills": ["SQL"]}';
CREATE INDEX ON candidates USING gin (profile);Checked against: PostgreSQL docs: JSON types
Intermediate8. How do you write an upsert in PostgreSQL?
INSERT ... ON CONFLICT: when the insert would violate a unique constraint, do nothing or update the existing row instead. EXCLUDED refers to the row that was proposed for insertion.
SQL snippet · illustrative, not run on this pageINSERT INTO job_views (job_id, views) VALUES (7, 1)
ON CONFLICT (job_id) DO UPDATE SET views = job_views.views + EXCLUDED.views;Checked against: PostgreSQL docs: INSERT
Advanced PostgreSQL interview questions
Advanced9. What is the write-ahead log (WAL)?
Every change is recorded in the WAL and flushed to durable storage before the data files are changed. After a crash, PostgreSQL replays the WAL to restore a consistent state, so data files do not need to be flushed at every commit. The same WAL stream powers physical replication and point-in-time recovery.
Checked against: PostgreSQL docs: write-ahead logging
Advanced10. How does replication work in PostgreSQL?
With streaming (physical) replication, a standby connects to the primary and receives WAL records as they are generated, keeping a byte-for-byte copy that can serve read-only queries (hot standby). It is asynchronous by default, so a failover can lose the last few transactions; synchronous replication trades latency for no loss. Logical replication publishes changes to selected tables to subscribers, which suits upgrades and partial copies.
Checked against: PostgreSQL docs: logical replication, PostgreSQL docs: log-shipping standby servers
Advanced11. When would you partition a table?
When a table is very large and queries or maintenance naturally work on slices of it, such as by date: queries that filter on the partition key only scan the relevant partitions, and old data can be dropped by detaching a partition instead of a slow DELETE. PostgreSQL supports range, list and hash partitioning.
SQL snippet · illustrative, not run on this pageCREATE TABLE events (id bigint, created_at timestamptz NOT NULL, payload jsonb)
PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_11 PARTITION OF events
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');Checked against: PostgreSQL docs: table partitioning
Advanced12. Why do applications use a connection pooler with PostgreSQL?
Each connection is served by its own backend server process, with its own memory, and max_connections caps how many can exist at once. Applications with many short requests, or many application instances, use a pooler such as PgBouncer (or the pool in their driver) to share a small number of database connections. Raising max_connections a lot instead uses more memory and can reduce performance.
Checked against: PostgreSQL docs: connection settings, PostgreSQL docs: how connections are established
How to practice with this page
- Run EXPLAIN ANALYZE on a slow query and explain each node.
- Explain what VACUUM does with an UPDATE-heavy table as the example.
- Design one index for a query with a filter and a sort.
- Review generic SQL on the SQL interview page.
Related interview prep and tools
Joins and window functions with real output.
Open →QuestionsMySQL interview questionsInnoDB, indexes and replication.
Open →QuestionsMongoDB interview questionsThe document database alternative.
Open →RoleDatabase administrator interview questionsFor DBA roles: backups, recovery and monitoring.
Open →Free toolResume summary generatorDraft a summary for a Database Developer role; the title is filled in, you add your experience.
Open →Free toolLinkedIn headline generatorWrite a headline for a Database Developer search.
Open →Disclosure: TryHired makes HIRED, a paid job-search program, so we have a conflict of interest when we mention it. This question bank is free, with no TryHired signup.
Questions people ask
What PostgreSQL topics come up often in interviews?
How is PostgreSQL different from MySQL in an interview answer?
Were these queries run?
How this page is built
TryHired wrote these questions and answers. Statements about the technology were checked against the official documentation pages listed below, saved when the page was built (2026-11-16). The code snippets are short illustrations and were not run on this page. The question list is general practice: it does not predict what a particular employer will ask.
- Interviewers ask different questions for different roles and levels.
- Answers are short explanations, not complete references.
- No PostgreSQL server was used; the answers follow the PostgreSQL 18 documentation, and defaults can differ in older versions and managed services.
- PostgreSQL docs: connection settings (read 2026-11-15)
- PostgreSQL docs: how connections are established (read 2026-11-15)
- PostgreSQL docs: using EXPLAIN (read 2026-11-15)
- PostgreSQL docs: index types (read 2026-11-15)
- PostgreSQL docs: transaction isolation (read 2026-11-15)
- PostgreSQL docs: JSON types (read 2026-11-15)
- PostgreSQL docs: logical replication (read 2026-11-15)
- PostgreSQL docs: multicolumn indexes (read 2026-11-15)
- PostgreSQL docs: MVCC introduction (read 2026-11-15)
- PostgreSQL docs: partial indexes (read 2026-11-15)
- PostgreSQL docs: table partitioning (read 2026-11-15)
- PostgreSQL docs: log-shipping standby servers (read 2026-11-15)
- PostgreSQL docs: INSERT (read 2026-11-15)
- PostgreSQL docs: routine vacuuming (read 2026-11-15)
- PostgreSQL docs: write-ahead logging (read 2026-11-15)