99 questions in 9 topics
PostgreSQL Interview Questions for Freshers
What PostgreSQL is, psql, schemas, identity columns, NULL, pattern matching, pagination and the commands that remove data.
- What is PostgreSQL?
- What is the difference between PostgreSQL and MySQL?
- What is psql, and which psql commands should you know?
- What is the difference between a database and a schema in PostgreSQL, and what is
search_path?
PostgreSQL Data Types and JSONB Interview Questions
Numbers, text, dates and time zones, casts, arrays, JSON and JSONB, UUIDs, booleans and enums, with the trap in each.
- What is the difference between
numericanddouble precisionin PostgreSQL? - What is the difference between
text,varchar(n)andchar(n)in PostgreSQL? - What is the difference between
timestampandtimestamptzin PostgreSQL? - What do you get when you subtract one date from another in PostgreSQL?
PostgreSQL Query Interview Questions
Joins, DISTINCT ON, upserts, RETURNING, CTEs, LATERAL, window functions, FILTER, string and array aggregates, and set operators.
- What types of joins does PostgreSQL support?
- What does
DISTINCT ONdo in PostgreSQL? - How do you do an upsert in PostgreSQL with
ON CONFLICT? - How do you get the id of an inserted row in PostgreSQL?
PostgreSQL Constraints and Schema Design Interview Questions
Foreign keys, CHECK and unique constraints, exclusion constraints, sequences, views, partitioning, generated columns and domains in PostgreSQL.
- What does
ON DELETE CASCADEdo on a foreign key in PostgreSQL? - How does a
CHECKconstraint treat NULL in PostgreSQL? - Can a
UNIQUEcolumn hold more than one NULL in PostgreSQL? - What is an exclusion constraint in PostgreSQL?
PostgreSQL Indexes and Performance Interview Questions
B-tree and other index types, partial, expression and covering indexes, reading EXPLAIN, planner statistics and indexing without blocking writes.
- What is the default index type in PostgreSQL, and which queries can it serve?
- When do you use GIN, GiST, BRIN or hash indexes in PostgreSQL?
- Can a unique index allow duplicates for soft-deleted rows in PostgreSQL?
- Why does
WHERE lower(email) = ...not use the index onemail, and how do you fix it?
PostgreSQL Transactions and MVCC Interview Questions
ACID, MVCC, isolation levels, row locks and job queues, deadlocks, savepoints, advisory locks, transactional DDL and long-running transactions.
- What are the ACID properties, and how does PostgreSQL provide them?
- Does
UPDATEchange a row in place in PostgreSQL? - What are the transaction isolation levels in PostgreSQL, and which is the default?
- What is a serialization failure in PostgreSQL, and how should an application handle it?
PostgreSQL Functions, Procedures and Triggers Interview Questions
Functions and procedures, PL/pgSQL, volatility, triggers, set-returning functions, error handling, SECURITY DEFINER and extensions.
- What is the difference between a function and a procedure in PostgreSQL?
- What is PL/pgSQL, and when is a plain SQL function enough?
- What is a
DOblock in PostgreSQL? - How do you write a trigger that keeps an
updated_atcolumn current in PostgreSQL?
PostgreSQL DBA Interview Questions
Vacuum and bloat, wraparound, WAL, replication, backups and recovery, access control, pooling, session monitoring and major version upgrades.
- What is the difference between
VACUUM,VACUUM FULLand autovacuum in PostgreSQL? - Autovacuum is running but a PostgreSQL table keeps bloating. What do you check?
- What is transaction ID wraparound in PostgreSQL, and how do you prevent it?
- What is WAL in PostgreSQL, and what does a checkpoint do?
PostgreSQL Interview Questions for Experienced Developers
Classic query problems solved in PostgreSQL: ranking, duplicates, running totals, streaks, pivots, the NOT IN trap, medians, reports and pagination.
- How do you find the second highest salary in PostgreSQL, and why can
LIMIT 1 OFFSET 1be wrong? - How do you get the top N rows per group in PostgreSQL (
DISTINCT ONvsROW_NUMBER)? - How do you delete duplicate rows in PostgreSQL and keep one of each?
- What does
SUM(amount) OVER (ORDER BY day)return when two rows share the same day?
Frequently Asked Questions
How do I prepare for a PostgreSQL interview?
Write queries on a real PostgreSQL, not only standard SQL: RETURNING, ON CONFLICT, DISTINCT ON, JSONB and window functions. Then learn how it works inside: MVCC, VACUUM, isolation levels and reading EXPLAIN ANALYZE. In the interview, say what you assume about NULLs, ties and concurrency.
What PostgreSQL questions do freshers and experienced candidates get?
Freshers get definitions and short queries: data types, keys, DELETE vs TRUNCATE, joins and the differences from MySQL. At 3 to 5 years expect JSONB, indexes, query plans and transactions. Senior and DBA roles add MVCC and VACUUM, replication, backups and fixing a slow production query.
Do I need to know SQL before PostgreSQL interview questions?
Yes. PostgreSQL interviews assume standard SQL (joins, GROUP BY, subqueries) and ask what PostgreSQL adds on top. If those are shaky, start with the SQL interview questions.
What should a PostgreSQL DBA know for an interview?
How MVCC leaves dead rows and how autovacuum removes them, WAL and checkpoints, and streaming vs logical replication. Then backups (pg_dump, base backups, point-in-time recovery), roles and pg_hba.conf, connection pooling, and how to find and stop a runaway query with pg_stat_activity.
Where can I practice PostgreSQL queries?
Run the examples on these pages, or any query of your own, on PostgreSQL 17 in the browser. For quick syntax, keep the PostgreSQL cheat sheet open.