Top PostgreSQL Interview Questions & Answers for 2025

PostgreSQL is one of the most advanced open-source relational databases, known for its robustness, extensibility, and compliance with SQL standards. In interviews, you'll be tested on both fundamental concepts and practical problem-solving skills. This guide covers the most common PostgreSQL interview questions, from basic SQL queries to advanced topics like indexing strategies, transaction isolation, and performance tuning. Each question includes a detailed answer with code examples, complexity analysis, and tips on how to present your solution in an interview. Whether you're a backend developer, data engineer, or DBA, mastering these questions will help you stand out. We'll also explore real-world scenarios like debugging slow queries and handling concurrent updates. By the end, you'll be ready to tackle any PostgreSQL interview question with confidence.

What is PostgreSQL and How Does It Differ from Other Databases?

PostgreSQL is an object-relational database management system (ORDBMS) that emphasizes extensibility and SQL compliance. Unlike MySQL, which is a relational DBMS, PostgreSQL supports advanced data types like arrays, JSON, and custom types. It also offers features like table inheritance, function overloading, and a sophisticated rule system. In interviews, highlight PostgreSQL's MVCC (Multiversion Concurrency Control) which allows readers to see a consistent snapshot without blocking writers. Compare it to MySQL's InnoDB which uses a similar approach but with different locking granularity. PostgreSQL's support for full-text search, geospatial data via PostGIS, and procedural languages like PL/pgSQL makes it a versatile choice for complex applications.

-- PostgreSQL: Create a table with JSONB and array types
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],
    attributes JSONB
);

-- MySQL equivalent would require separate tables or JSON type (MySQL 5.7+)

Explain MVCC and How It Handles Concurrency

MVCC (Multiversion Concurrency Control) allows multiple transactions to see a consistent snapshot of the database at a point in time. Each row has hidden columns like xmin (transaction that created it) and xmax (transaction that deleted/updated it). When a transaction reads, it sees only rows that were committed before its snapshot. Writers create new row versions without blocking readers. This avoids read-write conflicts. However, old row versions accumulate and need to be cleaned by VACUUM. In interviews, explain that MVCC provides high concurrency but requires careful tuning of autovacuum to prevent bloat.

-- Transaction 1: Update a row
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Transaction 2 (concurrent): Read the same row
-- Transaction 2 sees the old balance until Transaction 1 commits
COMMIT;

What Are the Different Index Types and When to Use Them?

PostgreSQL supports B-tree, Hash, GiST, GIN, SP-GiST, BRIN, and Bloom indexes. B-tree is the default and works for equality and range queries. Hash indexes are only for equality comparisons and are less common. GiST (Generalized Search Tree) is for geometric or full-text search. GIN (Generalized Inverted Index) is for array or JSONB containment. BRIN (Block Range Index) is for large tables with naturally ordered data. In interviews, explain that choosing the right index can drastically improve query performance. For example, use GIN for JSONB queries like WHERE data @> '{"key": "value"}' and B-tree for WHERE id = 5.

-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);

-- GIN index for JSONB
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- BRIN index for large time-series data
CREATE INDEX idx_orders_created ON orders USING BRIN (created_at);

How Do You Optimize a Slow Query?

Start by running EXPLAIN (ANALYZE, BUFFERS) to see the execution plan. Look for sequential scans on large tables, high buffer usage, or nested loops. Common fixes include adding indexes, rewriting queries to use JOINs instead of subqueries, or using materialized views for complex aggregations. Also check work_mem for sort operations and shared_buffers for caching. In interviews, walk through a real example: a query that joins two large tables without indexes. Show how adding an index on the join column reduces the cost from thousands to single digits.

-- Slow query: full table scan on orders
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123;
-- Output shows Seq Scan on orders (cost=0.00..1000.00 rows=1)

-- Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Re-run EXPLAIN: now shows Index Scan (cost=0.28..8.29 rows=1)

Explain Transaction Isolation Levels in PostgreSQL

PostgreSQL implements four isolation levels as per SQL standard: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. However, Read Uncommitted behaves like Read Committed. Read Committed (default) sees only committed changes. Repeatable Read ensures that the same query returns the same results within a transaction. Serializable prevents all anomalies but may abort transactions due to serialization failures. In interviews, explain the trade-offs: lower isolation levels offer higher concurrency but risk dirty reads, non-repeatable reads, or phantom reads. Use Serializable only when absolute consistency is required.

-- Set isolation level for a transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Another transaction updates balance and commits
SELECT balance FROM accounts WHERE id = 1; -- Same result as first read
COMMIT;

What Are Window Functions and How Do You Use Them?

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output. Common functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM() with OVER. They are useful for ranking, running totals, and comparing values between rows. In interviews, demonstrate a query that finds the top 3 products per category using ROW_NUMBER() and PARTITION BY.

-- Find top 3 products by sales in each category
SELECT category, product_name, sales, rank
FROM (
    SELECT category, product_name, sales,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
    FROM products
) ranked
WHERE rank <= 3;

How Do You Handle JSON Data in PostgreSQL?

PostgreSQL offers two JSON types: JSON (stores exact copy) and JSONB (stores decomposed binary format). JSONB supports indexing via GIN, making it faster for queries that check containment or existence. Use JSONB when you need to query inside the JSON document. Common operators include -> (get JSON field), ->> (get as text), @> (contains), ? (key exists). In interviews, show how to create a GIN index on a JSONB column and query it efficiently.

-- Create table with JSONB
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Insert sample data
INSERT INTO events (data) VALUES ('{"user": "Alice", "action": "login", "ip": "10.0.0.1"}');

-- Create GIN index
CREATE INDEX idx_events_data ON events USING GIN (data);

-- Query: find events where user is Alice
SELECT * FROM events WHERE data @> '{"user": "Alice"}';

What Are Common Table Expressions (CTEs) and Recursive Queries?

CTEs (WITH clauses) define temporary result sets that can be referenced within a query. They improve readability and allow recursion. Recursive CTEs are used for hierarchical data like organizational charts or category trees. A recursive CTE has a non-recursive term (base case) and a recursive term (joins back to the CTE). In interviews, show how to traverse a tree structure.

-- Find all subordinates of a manager
WITH RECURSIVE subordinates AS (
    -- Base case: direct reports
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id = 1
    UNION ALL
    -- Recursive step: reports of reports
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

How Do You Backup and Restore a PostgreSQL Database?

PostgreSQL provides pg_dump for logical backups and pg_basebackup for physical backups. pg_dump creates a SQL script or archive file that can be restored with psql or pg_restore. For large databases, use directory format with parallel jobs. Also consider continuous archiving with WAL files for point-in-time recovery. In interviews, explain the difference between logical and physical backups and when to use each.

# Logical backup of a single database
pg_dump -U postgres -d mydb -F c -f mydb.backup

# Restore from backup
pg_restore -U postgres -d mydb -c mydb.backup

# Physical backup with pg_basebackup (for replication)
pg_basebackup -U postgres -D /backup/dir -X stream -P

What Is the Role of VACUUM and Autovacuum?

VACUUM reclaims storage occupied by dead tuples (old row versions) and updates statistics for the query planner. Autovacuum automatically runs VACUUM and ANALYZE based on thresholds. Without vacuuming, table bloat increases, and query performance degrades. In interviews, explain that you can monitor dead tuples with pg_stat_user_tables and tune autovacuum parameters like autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor.

-- Manual vacuum and analyze
VACUUM (VERBOSE, ANALYZE) my_table;

-- Check dead tuples
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'my_table';