PostgreSQL & SQL

Your 40% latency win came from PostgreSQL optimization — interviewers will dig into how you did it. Master query plans, indexes, and transaction isolation.

ACID Properties & MVCC

PostgreSQL is a relational database strictly adhering to ACID principles:

• Atomicity: All or nothing. Transactions either commit fully or rollback entirely.
• Consistency: Data constraints (foreign keys, unique, checks) are always enforced.
• Isolation: Concurrent transactions don't interfere with each other (see Isolation Levels).
• Durability: Committed data is saved to disk and survives crashes (via WAL - Write Ahead Log).

MVCC (Multi-Version Concurrency Control)

Postgres uses MVCC for isolation. Instead of locking a row when reading/writing, it creates a new version of the row. Readers don't block writers, and writers don't block readers. Old rows are marked as dead tuples and cleaned up later by the VACUUM process. If autovacuum fails to keep up, the table bloats.

Indexes Deep Dive

An index is a separate data structure that maps column values to row locations (TID - Tuple Identifier). Trade-off: faster reads, slower writes, more disk space.

Composite Index Order (The Leftmost Prefix Rule)

If you create an index on (user_id, created_at, status):

• ✅ WHERE user_id = 1 (Uses index)
• ✅ WHERE user_id = 1 AND created_at > '2024-01-01' (Uses index)
• ❌ WHERE created_at > '2024-01-01' (Full table scan, because user_id is missing)

Rule of thumb for ordering: Equality first, then ranges/sorting.

Query Optimization & EXPLAIN ANALYZE

EXPLAIN shows the planner's estimate. EXPLAIN ANALYZE actually runs the query and shows real timings.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE user_id = 'u123' AND created_at > NOW() - INTERVAL '7 days';

How to read the output:

• Seq Scan (Sequential Scan): Reads every row in the table. Bad for large tables. Means missing index.
• Index Scan: Traverses the index to find the row, then reads the row from the table heap. Good.
• Index Only Scan: Traverses the index, and the index already contains all requested columns. Never hits the table heap. Very fast.
• Bitmap Heap Scan: Used when returning many rows. Reads the index, builds a bitmap of row locations in memory, sorts them by disk block, then reads blocks efficiently.
• Nested Loop Join: For every row in A, loop through B. Good for small result sets.
• Hash Join: Hashes table A into memory, then scans table B probing the hash table. Good for large tables (if memory permits).
• Buffers: shared hit=X read=Y. Hits are from RAM. Reads are from Disk. If you see high reads, tune shared_buffers.

The N+1 Problem

The most common ORM/SQL performance bug. You load a list of N items, then make a query for each item.

-- BAD: 1 + N queries (e.g. loops in code)
users := db.Query("SELECT id FROM users")
for u := range users {
    orders := db.Query("SELECT * FROM orders WHERE user_id=$1", u.ID)
}

-- GOOD: 1 query with JOIN
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- GOOD: 2 queries with IN clause (often preferred in microservices/GraphQL)
orders := db.Query("SELECT * FROM orders WHERE user_id = ANY($1::uuid[])", userIDs) 

Transactions & Isolation Levels

Isolation levels prevent anomalies when multiple transactions run concurrently.

Anomalies explained:

• Dirty Read: Reading uncommitted data from another transaction. (PG prevents this natively).
• Non-repeatable Read: Reading the same row twice in a transaction yields different results (because another txn updated it).
• Phantom Read: A query for a range of rows yields different results (because another txn inserted a row in that range).

Pessimistic vs Optimistic Locking

For high-stakes data like money transfers, you must prevent race conditions:

-- Pessimistic Locking (Row-level lock)
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- Blocks other txns from modifying this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- Optimistic Locking (Requires a 'version' column)
UPDATE accounts 
SET balance = balance - 100, version = version + 1 
WHERE id = 1 AND version = 5; 
-- If affected_rows == 0, another txn updated it first. Retry.

Connection Pooling (PgBouncer)

PostgreSQL forks a new OS process for every connection. This takes ~10MB of RAM and takes time. You cannot have 10,000 direct connections to Postgres without crashing it.

PgBouncer sits between the app and the DB. The app opens 10,000 connections to PgBouncer. PgBouncer multiplexes them onto a small pool (e.g. 100) of real DB connections. Transaction pooling mode is the most common.

Advanced SQL Features

JSONB

Postgres handles JSON natively. JSONB stores it in a parsed binary format, allowing fast indexing and querying. Perfect for schemaless data, metadata, or webhooks.

-- Querying inside JSON
SELECT * FROM events WHERE payload->>'event_type' = 'click';
-- Creating a GIN index on JSONB
CREATE INDEX idx_payload ON events USING GIN (payload);

Window Functions

Perform calculations across a set of rows related to the current row, without collapsing them (unlike GROUP BY).

-- Rank events per user by date
SELECT user_id, event_id,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM events;

-- Running total
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

CTEs (Common Table Expressions)

Makes complex queries readable. The WITH clause.

WITH recent_users AS (
    SELECT id FROM users WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM recent_users);

Your 40% Latency Win — How to Tell It

Interview Quick Reference