PostgreSQL Interview Questions

Core Concepts

What makes PostgreSQL different from MySQL?

Feature	PostgreSQL	MySQL
ACID compliance	Full	InnoDB only
JSON support	JSONB (binary, indexed)	JSON (text)
Window functions	Full support	8.0+
CTEs	Yes (including writable)	8.0+
Partial indexes	Yes	No
Table inheritance	Yes	No
Custom types	Yes	Limited

PostgreSQL is more standards-compliant and feature-rich; MySQL is simpler and historically faster for simple read-heavy workloads.

---

What is MVCC?

Multi-Version Concurrency Control — PostgreSQL keeps multiple versions of each row so readers never block writers and writers never block readers.

• Each transaction sees a snapshot of the database at a consistent point in time
• Old row versions are cleaned up by VACUUM
• Avoids dirty reads without locking

---

What is the difference between TRUNCATE and DELETE?

	DELETE	TRUNCATE
Speed	Slower (row by row)	Fast (deallocates pages)
WHERE clause	Yes	No
Triggers	Fires per row	Fires once (statement-level)
Rollback	Yes	Yes (PostgreSQL only)
Resets sequences	No	Yes (with RESTART IDENTITY)

---

Indexes

What index types does PostgreSQL support?

-- B-tree (default) — equality and range queries
CREATE INDEX idx_orders_date ON orders(created_at);

-- Hash — equality only, faster than B-tree for =
CREATE INDEX idx_users_email ON users USING HASH (email);

-- GIN — full-text search, JSONB, arrays
CREATE INDEX idx_docs_body ON documents USING GIN (to_tsvector('english', body));
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- GiST — geometric types, full-text, nearest-neighbor
CREATE INDEX idx_locations ON places USING GIST (coordinates);

-- BRIN — very large, naturally ordered tables (e.g., time-series)
CREATE INDEX idx_logs_time ON logs USING BRIN (created_at);

---

What is a partial index?

An index on a subset of rows — smaller, faster, only covers the rows you actually query:

-- Index only active users (not the millions of inactive ones)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Query uses the index automatically when WHERE matches
SELECT * FROM users WHERE email = 'user@example.com' AND status = 'active';

---

What is an index on expression?

-- Case-insensitive email lookups
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

---

CTEs and Window Functions

What is a CTE and when would you use one?

A Common Table Expression (WITH clause) is a named temporary result set:

-- Basic CTE
WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', created_at) AS month,
           SUM(amount) AS total
    FROM orders
    GROUP BY 1
)
SELECT month, total,
       total - LAG(total) OVER (ORDER BY month) AS change
FROM monthly_sales
ORDER BY month;

-- Recursive CTE — for hierarchical data
WITH RECURSIVE category_tree AS (
    -- Base case: root categories
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive step
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

---

What are window functions?

Window functions compute values across a set of rows related to the current row, without collapsing them into groups:

SELECT
    employee_id,
    department,
    salary,

    -- Rank within department
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,

    -- Running total
    SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total,

    -- Compare to department average
    salary - AVG(salary) OVER (PARTITION BY department) AS vs_avg,

    -- Previous row's value
    LAG(salary) OVER (ORDER BY salary) AS prev_salary,

    -- Row number (no gaps in sequence)
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank

FROM employees;

---

Transactions and Locking

What isolation levels does PostgreSQL support?

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Not possible*	Yes	Yes
Read Committed (default)	No	Yes	Yes
Repeatable Read	No	No	No**
Serializable	No	No	No

*PostgreSQL never allows dirty reads even at READ UNCOMMITTED
**PostgreSQL’s MVCC prevents phantom reads at REPEATABLE READ

---

What is SELECT FOR UPDATE?

Locks selected rows for the duration of the transaction, preventing other transactions from modifying them:

BEGIN;
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;
-- row is now locked
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;

-- SKIP LOCKED — skip rows that are already locked (queue processing pattern)
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

---

Performance

How do you analyze a slow query?

-- See execution plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

-- See actual execution time and row counts
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

-- Full verbose output
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Key things to check:

• Seq Scan on large tables — suggests missing index
• Rows estimate vs actual — large gaps indicate stale statistics; run ANALYZE
• Nested Loop with large tables — might need a Hash Join
• Sort step — consider an index matching the ORDER BY

---

What is VACUUM and why does it matter?

PostgreSQL’s MVCC leaves dead row versions after updates/deletes. VACUUM reclaims that space:

-- Manual vacuum
VACUUM orders;

-- Vacuum and update statistics
VACUUM ANALYZE orders;

-- Full vacuum — rewrites table, reclaims disk space (locks table)
VACUUM FULL orders;

-- Check table bloat
SELECT relname, n_dead_tup, n_live_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

autovacuum handles this automatically. Tune it for high-traffic tables:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

---

Quick Reference

-- Check running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Kill a slow query
SELECT pg_terminate_backend(pid);

-- Table and index sizes
SELECT relname AS table,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Missing indexes (high sequential scans)
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_scan DESC;