By Matthew Rathbone · October 29, 2026

PostgreSQL interviews start with general SQL (joins, aggregates, subqueries) and then move to what makes Postgres different: MVCC, VACUUM, rich index types, JSONB, and features like RETURNING and ON CONFLICT. Interviewers want to hear that you know why Postgres behaves the way it does, not just the syntax.

This page has 25 questions in that order. For database-neutral questions (keys, normalization, DELETE vs TRUNCATE), see our 30 SQL interview questions and answers. Interviewing for a Microsoft shop instead? Read SQL Server interview questions.

Jump to: basics · queries · JSONB and data types · indexes and performance · MVCC and maintenance · interview tips · FAQ

Examples use a small employees table (id, name, department, salary, manager_id, profile as JSONB) and were run on PostgreSQL 16. To follow along, install PostgreSQL and connect with a client such as Beekeeper Studio or psql.

PostgreSQL basics

1. What is PostgreSQL, and why would you choose it?

PostgreSQL (Postgres) is a free, open-source object-relational database that follows the SQL standard closely. People choose it for its reliability, ACID compliance, extensibility (custom types, functions and extensions such as PostGIS) and strong support for JSONB, window functions, CTEs and full-text search. New to the language? Start with what is SQL and how to use PostgreSQL.

2. How is PostgreSQL different from MySQL?

Both are open-source relational databases, but Postgres is stricter and more feature-rich. It has RETURNING, JSONB with indexing, arrays, partial and expression indexes, and more complete window function and CTE support. Its concurrency model (MVCC) never blocks readers with writers. MySQL has historically been seen as simpler to set up and fast for simple read-heavy workloads. Answer honestly: either can be right depending on the workload.

3. What is a schema in PostgreSQL?

A namespace inside a database that holds tables, views, functions and types. Every database starts with a public schema. Schemas let you organize objects and separate tenants or applications, and search_path controls which schemas are searched when you write an unqualified name like employees.

4. What are the common data types you should know?

  • Numbers: integer, bigint, numeric(p,s) for exact money values, double precision.
  • Text: text and varchar(n) (in Postgres there is no performance difference, so text is common).
  • Dates: date, timestamp, timestamptz (use this one for most event times), interval.
  • Others: boolean, uuid, jsonb, arrays such as text[], and serial / GENERATED ... AS IDENTITY for auto-incrementing keys.

5. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows matching a WHERE clause and can be rolled back. TRUNCATE empties the whole table quickly, and unlike most databases it is also transactional in Postgres. DROP TABLE removes the table itself. Both DELETE and TRUNCATE leave the table definition in place. See how to delete a table in PostgreSQL.

Writing queries

6. How do you find the highest-paid employee in each department?

Use a window function. RANK() numbers rows within each PARTITION, and ties share a rank.

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r
FROM employees;
 name | department  | salary | r
------+-------------+--------+---
 Cleo | Engineering |   7500 | 1
 Dev  | Engineering |   7100 | 2
 Eli  | Engineering |   7100 | 2
 Ana  | Sales       |   5200 | 1
 Ben  | Sales       |   4800 | 2

Wrap it in a subquery or CTE and filter r = 1 to keep only the top earner per department. A follow-up is the difference between RANK, DENSE_RANK and ROW_NUMBER: ties get the same rank (with gaps for RANK, without gaps for DENSE_RANK), while ROW_NUMBER always gives unique numbers.

7. What is DISTINCT ON?

A Postgres-only shortcut that keeps the first row for each distinct value of the listed columns, according to ORDER BY. It is the concise way to get the top row per group.

SELECT DISTINCT ON (department) department, name, salary
FROM employees
ORDER BY department, salary DESC;
 department  | name | salary
-------------+------+--------
 Engineering | Cleo |   7500
 Sales       | Ana  |   5200

8. What is a CTE, and what is a recursive CTE?

A common table expression is a named temporary result defined with WITH, used to make queries readable. A recursive CTE refers to itself and is the standard way to walk hierarchies such as an org chart. Since PostgreSQL 12, plain CTEs are inlined by default when they are referenced once, so they are not a performance barrier any more.

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS lvl
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.lvl + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT name, lvl FROM chain ORDER BY lvl, name;
 name | lvl
------+-----
 Ana  |   1
 Cleo |   1
 Ben  |   2
 Dev  |   2
 Eli  |   2

9. What does RETURNING do?

It sends back the rows an INSERT, UPDATE or DELETE just changed, in the same statement. That saves a second query to fetch generated ids or confirm what changed.

UPDATE employees SET salary = salary * 1.1
WHERE department = 'Sales'
RETURNING name, salary;

10. How do you do an upsert in PostgreSQL?

Use INSERT ... ON CONFLICT. If the insert would violate a unique constraint, you choose between DO NOTHING and DO UPDATE. EXCLUDED refers to the row you tried to insert.

INSERT INTO employees (name, department, salary)
VALUES ('Ana', 'Sales', 5300)
ON CONFLICT (name)
DO UPDATE SET salary = EXCLUDED.salary
RETURNING id, name, salary;
 id | name | salary
----+------+--------
  1 | Ana  |   5300

This needs a unique index on name. PostgreSQL 15 also added the standard MERGE statement.

11. What is the difference between INNER JOIN, LEFT JOIN and FULL OUTER JOIN?

An inner join returns only matching rows. A left join returns every row from the left table, with NULL where there is no match. A full outer join returns all rows from both sides. Joins come up in almost every interview, so practice them: how to join two tables and the interactive joins lesson.

12. How do you aggregate with a condition?

Postgres supports the FILTER clause on aggregates, which is cleaner than CASE inside SUM. array_agg and string_agg collect values into one cell.

SELECT department,
       array_agg(name ORDER BY name) AS names,
       count(*) FILTER (WHERE salary > 5000) AS high_earners
FROM employees
GROUP BY department;

JSONB and data types

13. What is the difference between JSON and JSONB?

JSON stores the exact text you gave it (whitespace, key order and duplicates included) and reparses it on every read. JSONB stores a decomposed binary form: slightly slower to write, much faster to query, and indexable. Use JSONB unless you need to preserve the original text. See how to query a JSON column in PostgreSQL.

14. How do you query inside a JSONB column?

-> returns JSON, ->> returns text, @> tests containment and ? tests whether a key or array element exists.

SELECT name, profile->>'city' AS city
FROM employees
WHERE profile @> '{"skills": ["sql"]}';
 name |  city
------+--------
 Ana  | Austin
 Cleo | Austin
 Eli  | Denver

15. How do you index a JSONB column?

With a GIN index, which speeds up @>, ? and similar operators: CREATE INDEX idx_profile ON employees USING gin (profile);. If you only ever filter on one key, a B-tree expression index such as CREATE INDEX ON employees ((profile->>'city')); is smaller and faster.

16. timestamp vs timestamptz: which should you use?

Usually timestamptz. Despite the name, it does not store a time zone. It stores a UTC instant and converts to the session time zone on output, so the same event looks right for every user. Plain timestamp stores a wall-clock value with no zone and is easy to misinterpret. See how to get the current date in PostgreSQL.

Indexes and performance

17. What index types does PostgreSQL support?

  • B-tree (default): equality and range queries, sorting.
  • Hash: equality only.
  • GIN: multi-valued data such as JSONB, arrays and full-text search.
  • GiST: geometric data, ranges, nearest-neighbour search.
  • BRIN: tiny indexes for huge tables whose values correlate with physical order, such as append-only timestamps.

Basics are in how to create an index in PostgreSQL.

18. What is a partial index, and when is it useful?

An index built on only the rows that match a WHERE condition. It is smaller and cheaper to maintain. Typical use: CREATE INDEX ON orders (customer_id) WHERE status = 'open'; when queries only look at open orders. A partial unique index also enforces "only one active row per user".

19. How do you find out why a query is slow?

Run EXPLAIN ANALYZE. EXPLAIN shows the plan the planner chose, and ANALYZE actually runs the query and adds real timings and row counts.

EXPLAIN SELECT * FROM employees WHERE id = 3;
 Seq Scan on employees  (cost=0.00..1.07 rows=1 width=136)
   Filter: (id = 3)

Notice a Seq Scan here despite filtering on the primary key: with only a handful of rows, scanning the table is cheaper than using the index, and the planner knows it. That is a good interview point. Scans are not always bad. Look for large gaps between estimated and actual rows (stale statistics, fix with ANALYZE), sequential scans on big tables, and sorts that spill to disk. More techniques in how to improve SQL query performance.

20. Why might Postgres ignore an index?

  • The table is small, or the query returns a large share of rows, so a sequential scan is cheaper.
  • A function wraps the column (WHERE lower(email) = ... needs an expression index on lower(email)).
  • Data type mismatch forces a cast.
  • Statistics are stale and the planner misjudges row counts.
  • A LIKE '%term' pattern with a leading wildcard cannot use a plain B-tree.

MVCC, transactions and maintenance

21. What is MVCC?

Multi-Version Concurrency Control. Instead of locking a row for reading, Postgres keeps multiple versions of it. Each transaction sees a consistent snapshot, so readers never block writers and writers never block readers. An UPDATE writes a new row version and marks the old one as dead.

22. What does VACUUM do, and what is autovacuum?

Because MVCC leaves dead row versions behind, VACUUM reclaims that space for reuse and updates visibility information. VACUUM ANALYZE also refreshes planner statistics. VACUUM FULL rewrites the table to return space to the operating system but takes an exclusive lock. The autovacuum daemon runs these automatically, and "table bloat" is the classic symptom when it falls behind.

23. What are the transaction isolation levels?

Postgres supports READ COMMITTED (the default), REPEATABLE READ and SERIALIZABLE. READ UNCOMMITTED is accepted but behaves as READ COMMITTED, so dirty reads never happen. A good answer names the anomalies: dirty reads, non-repeatable reads and phantom reads.

24. What is the difference between a view and a materialized view?

A view stores a query and runs it every time. A materialized view stores the result on disk, so reads are fast but the data is stale until you run REFRESH MATERIALIZED VIEW (add CONCURRENTLY, which needs a unique index, to avoid blocking readers). See how to create views in SQL.

25. How do you back up and scale a PostgreSQL database?

pg_dump makes logical backups of one database, pg_basebackup plus WAL archiving gives physical backups with point-in-time recovery. To scale reads use streaming replication with read replicas, add a connection pooler such as PgBouncer, and consider partitioning large tables. Details: how to back up a PostgreSQL database and how to scale PostgreSQL.

How to prepare for a PostgreSQL interview

  • Run everything yourself. Install Postgres locally and try EXPLAIN ANALYZE, VACUUM and JSONB queries. Hands-on stories beat memorized definitions.
  • Practice writing queries by hand. Many interviews use a shared editor without autocomplete. Rehearse with SQL practice problems and the 38 SQL query examples.
  • Be ready for one performance story. Describe a slow query, what EXPLAIN ANALYZE showed and what you changed.
  • State assumptions. Ask about duplicates, nulls and ties before writing the query.
  • Know the Postgres-specific features. RETURNING, ON CONFLICT, DISTINCT ON, FILTER, JSONB and partial indexes set experienced candidates apart.

Where to go next

FAQ

What are the most common PostgreSQL interview questions?

Joins, window functions, CTEs, indexes and when Postgres uses them, EXPLAIN ANALYZE, MVCC and VACUUM, JSON vs JSONB, and transaction isolation levels.

What PostgreSQL questions are asked of experienced candidates?

Expect questions on query plans, index types (GIN, GiST, BRIN, partial), autovacuum tuning and bloat, replication, partitioning, connection pooling and locking. Questions 17 to 25 above are the core of it.

How should I prepare as a beginner?

Get comfortable with SELECT, joins, GROUP BY and subqueries first, then learn indexes and transactions. Our free interactive lessons cover the fundamentals in your browser.

Is PostgreSQL the same as SQL?

No. SQL is the query language, and PostgreSQL is a database that implements it, with extra features of its own. See what is SQL.

How long does it take to prepare?

With basic SQL knowledge, one to two weeks of daily practice covers this list. Starting from zero, plan for a month. See is SQL easy to learn?