PostgreSQL · Intermediate interview

B-tree for equality/range and ordering (the default). GIN for 'contains' queries over composite values — JSONB, arrays, and full-text search. GiST for geometric/spatial and nearest-neighbour (PostGIS, ranges). BRIN for very large, naturally-ordered tables (like time-series) where it stores tiny summaries per block. Hash for equality only. You match the index type to the data and operators your queries use.

A partial index covers only rows matching a WHERE condition (e.g. WHERE active), making it smaller and faster when queries always filter on that condition — great for indexing a hot subset. An expression (functional) index indexes the result of an expression (e.g. LOWER(email)) so queries using that same expression can be index-served. Both target specific query patterns to keep indexes lean and usable.

CREATE INDEX ON users (LOWER(email));
CREATE INDEX ON orders (created_at) WHERE status = 'open';

A covering index stores extra non-key columns via INCLUDE so a query can be answered entirely from the index (an index-only scan) without visiting the table heap, eliminating extra I/O. The included columns aren't part of the search key but are available in the index. It's a targeted optimization for read-heavy queries that select a few columns by an indexed predicate.

Multi-Version Concurrency Control means each write creates a new row version rather than overwriting, and each transaction sees a consistent snapshot based on transaction ids (xmin/xmax). Readers don't block writers and writers don't block readers, which gives high concurrency. The cost is that old, no-longer-visible row versions (dead tuples) accumulate and must be cleaned up.

Because MVCC leaves dead tuples behind, VACUUM reclaims that space and updates visibility info; it also prevents transaction-id wraparound, a critical correctness issue. autovacuum runs VACUUM and ANALYZE automatically based on table activity. If it can't keep up, tables bloat (wasted space, slower scans) and statistics go stale — so tuning autovacuum on busy tables is a common production task.

Postgres implements READ COMMITTED (the default — each statement sees a fresh snapshot), REPEATABLE READ (snapshot fixed at transaction start, preventing non-repeatable and phantom reads, and detecting some serialization issues), and SERIALIZABLE (full serializable via SSI, which may abort transactions that would violate serializability). Note Postgres's REPEATABLE READ already prevents phantoms, unlike the SQL standard's minimum. You choose higher levels for read-modify-write correctness, handling serialization failures with retries.

CTEs (WITH) name subqueries for readability and support recursion for hierarchies; note that since PG12 they're inlined by default (no longer an optimization fence unless MATERIALIZED). Window functions compute values across a set of rows (OVER(PARTITION BY ... ORDER BY ...)) without collapsing them — for rankings, running totals, and per-group calculations alongside each row. Both are standard SQL features Postgres implements fully.

EXPLAIN shows the planner's chosen execution plan; adding ANALYZE actually runs the query and reports real timing and row counts per node. You compare estimated vs actual rows (big gaps mean stale statistics), spot sequential scans on large tables, expensive sorts, and nested loops over many rows. It's the primary tool for diagnosing why a query is slow and whether your index is used.

DISTINCT ON (Postgres-specific) returns the first row per group given an ORDER BY — a concise way to get the latest record per key without window-function boilerplate. A LATERAL join lets a subquery in the FROM clause reference columns from preceding tables, so you can run a correlated subquery per row (e.g. top 3 orders per customer). Both solve common 'per-group' problems elegantly.

You navigate JSONB with operators: -> returns a JSON object/element, ->> returns text, #> for paths, and @> tests containment. To make containment and key queries fast you create a GIN index on the column (or a specific expression). For querying a known scalar path you can also use a B-tree expression index on (data->>'field'). Indexing is what keeps JSONB queries from doing full scans.

CREATE INDEX ON docs USING GIN (data);
SELECT * FROM docs WHERE data @> '{"status":"active"}';

Postgres has built-in full-text search using tsvector (preprocessed document tokens) and tsquery (search terms), matched with the @@ operator and ranked with ts_rank. You typically store or compute a tsvector, index it with GIN, and query with to_tsquery/plainto_tsquery. It handles stemming and stop words, giving decent search without a separate engine for many apps.

pg_stat_statements aggregates query statistics to find your slowest/most-frequent queries — essential for tuning. PostGIS adds geospatial types and indexing. pgcrypto provides cryptographic functions, uuid-ossp/pgcrypto generate UUIDs, and others like pg_trgm enable fuzzy/trigram search. Extensions are a core Postgres strength — you enable them with CREATE EXTENSION to add capabilities without external systems.