50 PostgreSQL Interview Questions (With Answers)

PostgreSQL interviews test your knowledge of relational fundamentals, MVCC internals, indexing strategies, query optimisation, and advanced features like JSONB, window functions, and partitioning. This guide covers 50 of the most common questions — with concise answers and SQL examples.

Quick reference

Core concepts

1. What is MVCC and why does PostgreSQL use it?

MVCC (Multi-Version Concurrency Control) allows readers and writers to operate without blocking each other. Instead of locking rows, PostgreSQL stores multiple versions of each row:

• Every row has xmin (transaction that created it) and xmax (transaction that deleted/updated it).
• A reader sees the row if xmin committed before the reader's snapshot and xmax is not yet committed (or does not exist).
• Writers create a new row version; the old version remains visible to concurrent readers.

Key benefits: readers never block writers, writers never block readers. Only writer-writer conflicts require locking.

2. What are the four ACID properties? How does PostgreSQL implement them?

3. What isolation levels does PostgreSQL support?

PostgreSQL implements four standard SQL isolation levels:

* PostgreSQL never allows dirty reads even at Read Uncommitted.
** PostgreSQL Repeatable Read also prevents phantom reads.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- your queries
COMMIT;

4. What is the difference between CHAR, VARCHAR, and TEXT in PostgreSQL?

In PostgreSQL, TEXT and VARCHAR have identical performance. The only difference is the optional length constraint on VARCHAR. Prefer TEXT unless a max length has business meaning.

5. How does PostgreSQL handle NULL?

• NULL is not equal to anything, including itself: NULL = NULL → NULL (not TRUE).
• Use IS NULL / IS NOT NULL for comparisons.
• Aggregate functions ignore NULL values (except COUNT(*)).
• COALESCE(expr, default) returns the first non-NULL value.
• In ORDER BY, NULLs sort last by default (NULLS LAST for ascending, NULLS FIRST for descending — configurable).

SELECT COALESCE(phone, 'N/A') FROM customers;
SELECT * FROM orders ORDER BY shipped_at NULLS LAST;

SQL & queries

6. What is the difference between WHERE and HAVING?

• WHERE filters rows before grouping. It cannot reference aggregate functions.
• HAVING filters groups after GROUP BY. It can reference aggregates.

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE hire_date > '2020-01-01'   -- filter before group
GROUP BY department
HAVING COUNT(*) > 5;              -- filter after group

7. Explain the different types of JOINs.

-- Find employees and their managers (self join)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

8. What is a CTE and when would you use one?

A Common Table Expression (CTE) is a named subquery defined with WITH. Uses:

• Readability — break a complex query into logical steps.
• Recursion — tree/graph traversal with WITH RECURSIVE.
• Multiple references — refer to the same result set more than once.

-- Recursive CTE: org chart
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT depth, name FROM org ORDER BY depth, name;

Note: In PostgreSQL, non-recursive CTEs are an optimisation fence by default (they materialise). Add NOT MATERIALIZED to allow the planner to inline them.

9. What are window functions? Give an example.

Window functions compute values across a set of rows related to the current row, without collapsing them into a single output row (unlike GROUP BY).

SELECT
  name,
  department,
  salary,
  RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  LAG(salary)   OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary,
  SUM(salary)   OVER (PARTITION BY department)                      AS dept_total
FROM employees;

Common window functions:

10. What is the difference between UNION and UNION ALL?

• UNION removes duplicate rows (sorts and compares all columns) — slower.
• UNION ALL keeps all rows including duplicates — faster.

Use UNION ALL whenever duplicates are impossible or don't matter.

11. What are subqueries? When should you prefer a CTE or JOIN instead?

A subquery is a SELECT nested inside another statement.

-- Correlated subquery (executes once per row — can be slow)
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

Prefer a JOIN when the same rows need to be returned (better plans, can use indexes).
Prefer a CTE for readability or when the subquery is referenced multiple times.
Correlated subqueries are often rewritten with LATERAL joins for better performance.

Indexes

12. What index types does PostgreSQL support?

CREATE INDEX idx_products_name ON products USING GIN (to_tsvector('english', name));
CREATE INDEX idx_events_day ON events USING BRIN (created_at);

13. What is a partial index?

A partial index indexes only rows that satisfy a WHERE condition — smaller, faster:

-- Only index active users
CREATE INDEX idx_users_active_email ON users (email) WHERE is_active = TRUE;

Useful when queries always filter on a common condition and most rows don't match it.

14. What is a covering index?

A covering index includes all columns a query needs, so PostgreSQL can satisfy the query using only the index (index-only scan), without touching the heap.

CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total, created_at);

PostgreSQL checks the visibility map before performing an index-only scan — tables that aren't vacuumed frequently may still require heap access.

15. What is the difference between a unique index and a unique constraint?

In PostgreSQL they are almost identical. A UNIQUE constraint is implemented by creating a unique index. The practical differences:

• Constraints can be deferred (DEFERRABLE INITIALLY DEFERRED) — the unique check runs at COMMIT rather than immediately.
• The index created by a constraint cannot be dropped directly; you must drop the constraint.

16. How does the PostgreSQL query planner decide whether to use an index?

The planner uses cost estimation based on table statistics (pg_statistic, updated by ANALYZE). It compares:

• Sequential scan cost: seq_page_cost × pages
• Index scan cost: random_page_cost × index pages + heap pages fetched

If less than ~5–15% of rows are returned, an index scan is usually cheaper. On small tables or when most rows match, a sequential scan is preferred.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

Performance

17. How do you read an EXPLAIN ANALYZE output?

Key fields to examine:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

18. What is autovacuum and why is it important?

autovacuum is a background process that:

1. Reclaims space from dead row versions (tuples marked xmax).
2. Updates statistics with ANALYZE so the planner has accurate row counts.
3. Prevents transaction ID wraparound — PostgreSQL uses 32-bit transaction IDs; without vacuuming, the counter wraps around causing data loss.

Signs of insufficient vacuuming: table bloat, slow queries due to stale statistics, pgbench warnings about XID wraparound.

19. What is table bloat and how do you address it?

Bloat is dead tuple space that hasn't been reclaimed. Causes: many UPDATE/DELETE operations without sufficient vacuuming.

-- Check bloat (approximate)
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Fixes:

• VACUUM — reclaims space but keeps it in the table file.
• VACUUM FULL — rewrites the table (exclusive lock, reclaims disk space).
• pg_repack extension — rewrites the table online with minimal locking.

20. What is connection pooling and why is it needed?

PostgreSQL forks a new process per connection (heavyweight). Many idle connections waste memory and CPU. A connection pooler (PgBouncer, pgpool-II) sits between the application and PostgreSQL, multiplexing thousands of application connections onto a smaller pool of real database connections.

Transactions

21. How do you handle deadlocks in PostgreSQL?

A deadlock occurs when two transactions each hold a lock the other needs. PostgreSQL detects deadlocks automatically and aborts one of the transactions.

Prevention strategies:

• Always acquire locks in the same order across transactions.
• Keep transactions short.
• Use SELECT ... FOR UPDATE SKIP LOCKED for queue-like patterns.
• Use LOCK TABLE ... IN ... MODE to acquire multiple table locks upfront.

-- Safe queue processing
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 10;

22. What is SELECT FOR UPDATE?

SELECT ... FOR UPDATE acquires a row-level exclusive lock, preventing other transactions from updating or locking those rows until the current transaction commits.

Variants:

23. What is a savepoint?

A savepoint marks a point within a transaction to which you can roll back without aborting the entire transaction:

BEGIN;
INSERT INTO orders (...) VALUES (...);
SAVEPOINT after_order;

INSERT INTO order_items (...) VALUES (...);  -- might fail

ROLLBACK TO SAVEPOINT after_order;  -- undo only the items insert
-- order insert still stands

COMMIT;  -- or ROLLBACK to abort everything

Schema design

24. What types of constraints does PostgreSQL support?

CREATE TABLE bookings (
  id         BIGSERIAL PRIMARY KEY,
  room_id    INT NOT NULL REFERENCES rooms(id),
  during     TSRANGE NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)  -- no overlapping bookings
);

25. What is the difference between SERIAL and IDENTITY columns?

• SERIAL is a PostgreSQL shorthand that creates a sequence and sets a DEFAULT nextval(...). The sequence is not tightly bound to the column.
• IDENTITY (GENERATED ALWAYS AS IDENTITY) is the SQL standard way (PostgreSQL 10+). The sequence is tightly bound; GENERATED ALWAYS prevents manual inserts (unless you use OVERRIDING SYSTEM VALUE).

Prefer IDENTITY for new tables.

26. How do you implement soft deletes in PostgreSQL?

Add a deleted_at TIMESTAMPTZ column and filter it in queries. Use a partial index and a view to keep application code clean:

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;

CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

Advanced features

27. What is JSONB and how does it differ from JSON?

Use JSONB for virtually all cases where you query or index the JSON data.

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Find products with a specific attribute
SELECT * FROM products WHERE attributes @> '{"colour": "red"}';

28. What JSONB operators are most commonly used?

29. How does full-text search work in PostgreSQL?

PostgreSQL has built-in full-text search using tsvector (document) and tsquery (search expression):

-- Create a tsvector column and index
ALTER TABLE articles ADD COLUMN search_vec TSVECTOR;
UPDATE articles SET search_vec = to_tsvector('english', title || ' ' || body);
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vec);

-- Maintain it automatically
CREATE TRIGGER articles_tsvector_update BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(search_vec, 'pg_catalog.english', title, body);

-- Search
SELECT title, ts_rank(search_vec, q) AS rank
FROM articles, to_tsquery('english', 'postgresql & indexing') q
WHERE search_vec @@ q
ORDER BY rank DESC;

30. What is table partitioning?

Partitioning splits a logical table into smaller physical tables (partitions), each storing a subset of rows. PostgreSQL 10+ supports declarative partitioning:

CREATE TABLE events (
  id         BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  payload    JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE events_2026 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Benefits: faster queries (partition pruning), faster bulk deletes (drop partition), separate storage/tablespaces.

31. What are PostgreSQL arrays?

PostgreSQL supports multidimensional arrays of any type:

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  tags  TEXT[]
);

INSERT INTO products (tags) VALUES (ARRAY['electronics', 'sale']);

-- Array operators
SELECT * FROM products WHERE tags @> ARRAY['sale'];     -- contains
SELECT * FROM products WHERE tags && ARRAY['sale'];     -- overlap
SELECT * FROM products WHERE 'sale' = ANY(tags);        -- element check

CREATE INDEX idx_products_tags ON products USING GIN (tags);

32. What are generated columns?

Generated columns (PostgreSQL 12+) compute their value automatically from other columns:

CREATE TABLE orders (
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL,
  total      NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

Only STORED (persisted) generated columns are supported currently; VIRTUAL (computed on read) is not yet implemented.

Replication and high availability

33. What is streaming replication?

PostgreSQL's built-in replication ships WAL records from the primary to standby servers in near real-time:

• Synchronous: primary waits for standby to confirm WAL receipt before acknowledging commit. Zero data loss, higher latency.
• Asynchronous (default): primary does not wait. Low latency, potential data loss on failover.

-- On primary: pg_hba.conf allows replica user
-- Check replication lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS lag_bytes
FROM pg_stat_replication;

34. What is logical replication?

Logical replication replicates individual data changes (INSERT/UPDATE/DELETE) at a row level, rather than physical WAL blocks. This allows:

• Replication between different PostgreSQL major versions.
• Replicating specific tables (not the whole cluster).
• Replication to non-PostgreSQL subscribers.

-- Publisher
CREATE PUBLICATION mypub FOR TABLE orders, customers;

-- Subscriber
CREATE SUBSCRIPTION mysub
  CONNECTION 'host=primary dbname=mydb'
  PUBLICATION mypub;

35. What is pg_basebackup?

pg_basebackup takes a physical base backup of a running PostgreSQL cluster — the starting point for setting up a standby or performing point-in-time recovery (PITR):

pg_basebackup -h primary -U replicauser -D /var/lib/postgresql/standby -P -Xs -R

-R writes a standby.signal file and postgresql.auto.conf with replication settings, ready to start as a hot standby.

Security

36. How does PostgreSQL role-based access control work?

PostgreSQL uses roles for both users and groups. Roles can:

• Own database objects.
• Be granted privileges on objects.
• Be members of other roles (role inheritance).

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

CREATE USER alice WITH PASSWORD 'secret';
GRANT readonly TO alice;

37. What is Row-Level Security (RLS)?

RLS lets you define policies that restrict which rows a user can see or modify:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Each user can only see their own documents
CREATE POLICY own_docs ON documents
  USING (owner_id = current_user_id());

-- Admins see everything
CREATE POLICY admin_all ON documents
  USING (current_setting('app.role') = 'admin');

Enable FORCE ROW LEVEL SECURITY to also apply policies to the table owner.

38. How do you store passwords securely in PostgreSQL?

• Use the pgcrypto extension: crypt(password, gen_salt('bf', 12)) (bcrypt).
• Never store plain text or MD5 passwords.
• For application-level auth, consider hashing outside the database (bcrypt, Argon2) and storing only the hash.

-- Store
UPDATE users SET password_hash = crypt('mysecret', gen_salt('bf', 12)) WHERE id = 1;

-- Verify
SELECT id FROM users WHERE password_hash = crypt('mysecret', password_hash);

Administration

39. What key pg_stat_* views should you know?

-- Find long-running queries
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

40. How do you find and remove unused indexes?

SELECT schemaname, relname AS table, indexrelname AS index,
       idx_scan AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisprimary
  AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

Indexes with zero scans since last stats reset are candidates for removal. Verify by checking the query workload before dropping.

Practical SQL patterns

41. How do you do an upsert in PostgreSQL?

Use INSERT ... ON CONFLICT:

INSERT INTO user_stats (user_id, score)
VALUES (42, 100)
ON CONFLICT (user_id) DO UPDATE
  SET score = user_stats.score + EXCLUDED.score,
      updated_at = NOW();

-- Do nothing on conflict
INSERT INTO events (id, payload)
VALUES (...)
ON CONFLICT (id) DO NOTHING;

42. How do you paginate results efficiently?

Offset pagination (simple but slow for large offsets):

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 200;

Keyset (cursor) pagination (fast at any depth):

-- First page
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- Next page (use last row's values)
SELECT * FROM posts
WHERE (created_at, id) < ('2026-01-10 12:00:00', 9999)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Keyset pagination requires a compound index on (created_at DESC, id DESC) and is O(1) regardless of page number.

43. How do you calculate a running total?

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame is the default for SUM ... OVER (ORDER BY ...).

44. How do you find duplicate rows?

-- Find duplicates
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Delete duplicates, keep lowest id
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

45. How do you pivot rows into columns?

Use FILTER with aggregate functions (cleaner than CASE WHEN):

SELECT
  month,
  SUM(amount) FILTER (WHERE category = 'food')     AS food,
  SUM(amount) FILTER (WHERE category = 'transport') AS transport,
  SUM(amount) FILTER (WHERE category = 'utilities') AS utilities
FROM expenses
GROUP BY month;

For dynamic columns, use the tablefunc extension (crosstab).

Common mistakes

PostgreSQL vs alternatives

FAQ

Q: When should I use PostgreSQL instead of a NoSQL database?
A: Choose PostgreSQL when you need ACID transactions, complex joins, or enforced schema. Choose a document DB (MongoDB) when your data is document-shaped, schema is highly variable, or you need easy horizontal sharding at the database layer. PostgreSQL's JSONB support blurs this line — many workloads that once needed MongoDB work fine in PostgreSQL with a JSONB column and GIN index.

Q: What does EXPLAIN show without ANALYZE?
A: EXPLAIN alone shows the estimated plan (cost, row estimates) without executing the query. EXPLAIN ANALYZE actually runs the query and shows real execution times and row counts. Use EXPLAIN (ANALYZE, BUFFERS) for the most useful output.

Q: How do I check which queries are slowest?
A: Enable pg_stat_statements and query it:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time / calls AS avg_ms, rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Q: What is the n+1 query problem in PostgreSQL context?
A: It occurs in ORMs when you fetch a list of N rows and then execute one query per row to fetch related data — N+1 queries total. Fix with eager loading (JOIN or subquery), or PostgreSQL-specific JSON aggregation:

SELECT c.id, c.name, json_agg(o.*) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Q: Is PostgreSQL good for time-series data?
A: PostgreSQL with BRIN indexes and table partitioning by time range handles time-series data well for moderate workloads. For very high ingestion rates, consider TimescaleDB (a PostgreSQL extension) or a dedicated time-series database like InfluxDB.

Q: What is pgvector and why is it popular?
A: pgvector is an extension that adds a vector data type and approximate nearest-neighbour indexes (HNSW, IVFFlat) to PostgreSQL. It enables similarity search for AI embeddings — storing vectors from LLMs alongside relational data in the same database, eliminating the need for a separate vector database for many workloads.