Databases
Relational database questions interviewers keep asking: indexes, isolation, locking, modeling, pooling, migrations, replication, sharding, and reading query plans, with PostgreSQL specifics.
Answer out loud or on paper before revealing. If your answer misses a key point, revisit the linked guide or explainer.
What is a B-tree index, and which queries can it speed up?
Reveal model answer
A B-tree index is a balanced, sorted tree of keys pointing at table rows, so the database can find a key in logarithmic time instead of scanning the whole table. Because it is sorted, it serves equality, range (<, BETWEEN), prefix LIKE 'abc%' with a suitable operator class, ORDER BY, and MIN/MAX without a sort. It is the default index type in PostgreSQL; other types (GIN for arrays, JSONB and full-text, GiST for geometric and range data, BRIN for huge naturally ordered tables, hash for equality) exist for other access patterns. The planner only uses an index when its statistics say that is cheaper than a sequential scan.
WHAT A STRONG ANSWER COVERS
- Sorted structure: equality, ranges, ordering, and prefix matches.
- Logarithmic lookup versus a full sequential scan.
- Other index types exist for other operators (GIN, GiST, BRIN, hash).
- The planner decides, based on statistics kept fresh by ANALYZE.
RED FLAGS
- "Indexes make every query faster."
- Expecting a B-tree to help with LIKE '%term%' or a function applied to the column.
- Not knowing that primary keys and unique constraints create indexes automatically.
LIKELY FOLLOW-UPS
- Why might the planner ignore an index you just created?
- How would you index WHERE lower(email) = $1?
SOURCES
How do you choose the column order in a composite index?
Reveal model answer
A multicolumn B-tree is sorted by the first column, then the second within it, and so on, so it is most effective when the query constrains the leading columns. The rule in PostgreSQL is that equality conditions on leading columns plus a range condition on the first column without equality bound the portion of the index scanned; conditions on later columns only filter. So put columns compared with equality first, then the range or sort column: for WHERE tenant_id = ? AND created_at > ? ORDER BY created_at, use (tenant_id, created_at). PostgreSQL 18 added skip scan, which can use an index even when the leading column is unconstrained, but it only pays off when that column has few distinct values.
WHAT A STRONG ANSWER COVERS
- Leftmost-prefix rule: the index sorts by column 1, then column 2.
- Equality columns first, then the range or ORDER BY column.
- One well-ordered composite index can replace several single-column ones.
- Skip scan (PostgreSQL 18) softens but does not remove the leading-column rule.
RED FLAGS
- "Put the most selective column first" as a universal rule regardless of operators.
- Creating one index per column and expecting them to act like a composite.
- Ignoring the sort direction needed by ORDER BY ... DESC on mixed columns.
LIKELY FOLLOW-UPS
- Would an index on (a, b) help WHERE b = ?
- When would you prefer two separate indexes combined with a bitmap scan?
SOURCES
What is a covering index, and what is an index-only scan in PostgreSQL?
Reveal model answer
A covering index contains every column a query needs, so the database can answer it from the index without visiting the table (heap). In PostgreSQL you can add non-key payload columns with CREATE INDEX ... (key) INCLUDE (other_cols), which keeps them out of the search key and out of uniqueness checks. PostgreSQL still has to check row visibility for MVCC: it consults the visibility map and only skips the heap for pages marked all-visible, so index-only scans work best on tables that are not heavily updated and are vacuumed regularly. The cost is a bigger index and more write overhead.
WHAT A STRONG ANSWER COVERS
- All referenced columns must be in the index.
- INCLUDE adds payload columns without changing the key.
- Visibility map: heap fetches still happen for recently modified pages.
- Watch the "Heap Fetches" count in EXPLAIN ANALYZE.
RED FLAGS
- Including wide text columns in every index "just in case".
- Assuming an index-only scan never touches the table.
LIKELY FOLLOW-UPS
- Why might an Index Only Scan still show many heap fetches?
- How does VACUUM affect index-only scans?
SOURCES
When does adding an index make things worse?
Reveal model answer
Every index has to be maintained on INSERT, UPDATE, and DELETE, so write-heavy tables pay for each extra index in latency, WAL volume, and storage. In PostgreSQL an index on a frequently updated column also prevents HOT (heap-only tuple) updates, which otherwise avoid new index entries when no indexed column changes. Indexes on low-selectivity columns (a boolean, a status that matches most rows) are often ignored by the planner because a sequential scan is cheaper, so they cost writes and memory for nothing. And building one with a plain CREATE INDEX blocks writes for the duration; use CONCURRENTLY in production. Remove indexes that pg_stat_user_indexes shows are never used.
WHAT A STRONG ANSWER COVERS
- Write amplification: every index is updated on writes.
- Indexed columns that change frequently block HOT updates.
- Low-selectivity indexes are rarely chosen; consider partial indexes instead.
- Unused and duplicate indexes still cost memory, vacuum time, and writes.
- Plain CREATE INDEX locks out writes; CONCURRENTLY does not.
RED FLAGS
- "Index every column in the WHERE clause."
- Not knowing how to find unused indexes.
LIKELY FOLLOW-UPS
- How would a partial index help a status = 'pending' queue table?
- How do you safely drop an index you think is unused?
SOURCES
Explain transaction isolation levels and the anomalies each prevents, with PostgreSQL specifics.
Reveal model answer
The SQL standard defines Read Uncommitted, Read Committed, Repeatable Read, and Serializable by which phenomena they forbid: dirty reads, non-repeatable reads, phantoms, and serialization anomalies. PostgreSQL never allows dirty reads (Read Uncommitted behaves like Read Committed) and defaults to Read Committed, where each statement sees a fresh snapshot, so two reads in one transaction can differ. Its Repeatable Read is snapshot isolation for the whole transaction and also prevents phantoms, but still allows write skew, for example two doctors both going off call because each saw the other on call. Serializable uses Serializable Snapshot Isolation to detect such dependencies and aborts one transaction with SQLSTATE 40001, so the application must retry the whole transaction.
WHAT A STRONG ANSWER COVERS
- Name the anomalies: dirty read, non-repeatable read, phantom, write skew or serialization anomaly.
- PostgreSQL default is Read Committed (per-statement snapshot).
- Repeatable Read in PostgreSQL is snapshot isolation: no phantoms, but write skew is possible.
- Serializable (SSI) needs retry loops on 40001.
- Alternatives to stronger isolation: SELECT ... FOR UPDATE, constraints, atomic UPDATE ... SET x = x - 1.
RED FLAGS
- "Transactions make concurrent code safe" without naming the isolation level.
- Using Serializable without retrying serialization failures.
- Read-modify-write in application code under Read Committed (lost update).
LIKELY FOLLOW-UPS
- How would you prevent double-booking a meeting room?
- What is a lost update, and which tools prevent it at Read Committed?
SOURCES
Optimistic versus pessimistic locking: how do they work and when do you use each?
Reveal model answer
Pessimistic locking takes a lock before working on the data, for example SELECT ... FOR UPDATE inside a transaction, so concurrent writers wait; it is right when conflicts are frequent or the critical section is short and server-side. Optimistic locking lets everyone read freely and checks at write time, usually with a version column: UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?, and if zero rows changed someone else won and you retry or report a conflict. Optimistic suits low-contention cases and long business transactions that span user think time, where holding a database lock is impossible. Pessimistic locks need consistent lock ordering and timeouts to avoid deadlocks and pile-ups.
WHAT A STRONG ANSWER COVERS
- Pessimistic: SELECT ... FOR UPDATE, blocking, good under high contention.
- Optimistic: version or timestamp check on write; zero rows updated means conflict.
- Optimistic is the only option across HTTP requests (ETag plus If-Match).
- Deadlocks: lock in a consistent order; PostgreSQL detects and aborts one transaction.
- SKIP LOCKED for job queues where workers should not wait on each other.
RED FLAGS
- Holding a database transaction open while waiting on a user or an external API.
- Implementing optimistic locking without checking the affected row count.
LIKELY FOLLOW-UPS
- How would you build a job queue on PostgreSQL?
- How does an ETag map onto optimistic locking in an API?
SOURCES
When would you normalize, and when would you denormalize?
Reveal model answer
Normalize by default: store each fact once (roughly third normal form) so updates cannot leave copies disagreeing, and let constraints and foreign keys protect integrity. Denormalize deliberately when a measured read path is too expensive, for example a counter column on posts, a JSONB snapshot of an order's line items at purchase time, or a read model for a dashboard. Every denormalized copy needs an owner and an update mechanism (the same transaction, a trigger, an event consumer, or a periodic refresh such as a materialized view) and a tolerance for staleness. Some data, like an invoice's price at the time of sale, is not really denormalization but a historical fact that should be copied.
WHAT A STRONG ANSWER COVERS
- Normalization prevents update anomalies and keeps one source of truth.
- Denormalize for measured read performance, not upfront.
- Every copy needs a sync mechanism and an accepted staleness window.
- Materialized views and read models are controlled denormalization.
RED FLAGS
- "Joins are slow, so we avoid them."
- Duplicating data without a plan for keeping it consistent.
LIKELY FOLLOW-UPS
- How would you keep a denormalized comment_count accurate under concurrency?
- How is data modeling different in a document or key-value store?
SOURCES
How do you choose between a relational database and a NoSQL store?
Reveal model answer
Start from the access patterns and consistency needs rather than the label. A relational database gives ad hoc queries, joins, constraints, and multi-row transactions, and a single well-tuned PostgreSQL instance handles far more than most products need. Key-value and wide-column stores such as DynamoDB or Cassandra scale writes horizontally and give predictable latency, but you must design the partition key and tables around known queries up front, and cross-partition transactions and ad hoc queries are limited. Document stores fit self-contained aggregates; search engines, time-series, and graph databases fit their niches. Polyglot persistence is fine, but each extra store adds operational cost and consistency work between stores.
WHAT A STRONG ANSWER COVERS
- Decide from access patterns, consistency, and scale.
- Relational: flexible queries, constraints, transactions.
- NoSQL key-value or wide-column: horizontal scale, query-first modeling, hot-partition risk.
- Each additional datastore is an operational and consistency cost.
RED FLAGS
- "NoSQL because it scales" without numbers.
- "MongoDB because the schema is flexible" while the data is highly relational.
- Ignoring the team's operational experience.
LIKELY FOLLOW-UPS
- How would you model a one-to-many relationship in DynamoDB?
- What would make you move part of a PostgreSQL workload elsewhere?
SOURCES
Why do you need connection pooling, and what can go wrong with it?
Reveal model answer
Opening a database connection costs a TCP and TLS handshake, authentication, and in PostgreSQL a whole backend process with its own memory, and max_connections (typically 100 by default) is sized against shared memory, so you cannot give every request its own connection. A pool keeps a small set of connections open and lends them out; the right size is usually close to what the database can execute in parallel, not the number of app threads. Problems: many app instances each with its own pool can exceed max_connections, so you add an external pooler such as PgBouncer; transaction pooling breaks session state (SET, LISTEN, advisory locks, SQL-level PREPARE); and connections held across slow external calls or leaked on error paths exhaust the pool, so set acquire timeouts and statement and idle-in-transaction timeouts.
WHAT A STRONG ANSWER COVERS
- Connections are expensive, and the database has a hard limit.
- Pool size is about database concurrency; bigger is not better.
- Total connections = instances x pool size; serverless makes this worse.
- PgBouncer session versus transaction pooling and what transaction mode breaks.
- Acquire timeouts and never holding a connection during network calls.
RED FLAGS
- Raising max_connections to 5000 as the fix.
- Opening a new connection per request without a pool.
- Using session-level features through a transaction-mode pooler.
LIKELY FOLLOW-UPS
- How would you size a pool for 20 app instances and a 4-vCPU database?
- How do you diagnose "too many clients already" in production?
SOURCES
How do you run schema migrations with zero downtime on a large PostgreSQL table?
Reveal model answer
Use expand and contract: add the new structure in a backward-compatible way, deploy code that writes both and reads the new one, backfill in small batches, then remove the old structure in a later deploy, so every intermediate state works with both the old and new application versions. Prefer operations that avoid long ACCESS EXCLUSIVE locks: CREATE INDEX CONCURRENTLY, adding a column with a non-volatile default (no rewrite since PostgreSQL 11), ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT, and SET NOT NULL after a validated CHECK (col IS NOT NULL) so the scan is skipped. Set a short lock_timeout on migration sessions, because even a fast DDL statement queued behind a long transaction blocks every query that queues behind it.
WHAT A STRONG ANSWER COVERS
- Expand, migrate (dual write plus batched backfill), contract.
- Old and new app versions must both work against each schema step.
- Know which DDL rewrites the table or takes ACCESS EXCLUSIVE locks.
- CONCURRENTLY and NOT VALID plus VALIDATE patterns.
- lock_timeout with retries to avoid lock-queue pile-ups.
RED FLAGS
- Renaming a column in one deploy while old pods still use the old name.
- Backfilling millions of rows in one transaction.
- Not knowing CREATE INDEX CONCURRENTLY cannot run inside a transaction and can leave an INVALID index.
LIKELY FOLLOW-UPS
- How would you change a column's type from int to bigint on a billion-row table?
- Why can a migration that takes milliseconds still cause an outage?
SOURCES
You send reads to replicas. How do you deal with replication lag?
Reveal model answer
Asynchronous replicas are eventually consistent: a user who just wrote may read from a replica that has not replayed the change yet and see stale data. Mitigations: read-your-writes routing (send a user's reads to the primary for a short window after they write, or for the rest of the request), session consistency by recording the primary's WAL position (LSN) at write time and only using a replica that has replayed past it, or keeping critical reads on the primary. Monitor lag (pg_stat_replication and replay timestamps) and take lagging replicas out of rotation. Also expect long replica queries to be cancelled by conflicts with recovery, which hot_standby_feedback trades for bloat on the primary.
WHAT A STRONG ANSWER COVERS
- Async replication means stale reads; synchronous replication costs write latency.
- Read-your-writes via primary pinning or LSN tracking.
- Monitor lag and eject lagging replicas.
- Replica query cancellations and hot_standby_feedback trade-offs.
RED FLAGS
- Treating replicas as strongly consistent copies.
- Reading back a just-created row from a replica in the same request.
LIKELY FOLLOW-UPS
- How would you route reads in an ORM to support read-your-writes?
- What happens to in-flight writes during a failover?
SOURCES
How would you choose a sharding (partition) key?
Reveal model answer
Pick a key that matches the dominant access pattern so most queries hit one shard, and that has high cardinality and evenly spread traffic so no shard becomes hot. For a multi-tenant SaaS that is usually tenant_id, which keeps a tenant's joins and transactions local; for user-generated content it might be user_id or a hash of it. Bad keys are monotonically increasing values like timestamps or auto-increment IDs under range partitioning (all writes hit the newest shard) and low-cardinality values like country. Plan for celebrity or whale tenants (dedicated shards or write sharding with a suffix), cross-shard queries (scatter-gather or a separate read model), and resharding, which is far easier with many logical shards mapped onto fewer physical nodes.
WHAT A STRONG ANSWER COVERS
- Align the key with the most common query so it is single-shard.
- High cardinality and uniform load; avoid hot partitions.
- Monotonic keys plus range partitioning create a hot tail.
- Plan for whales, cross-shard queries, and resharding.
- Shard only after vertical scaling, replicas, and partitioning run out.
RED FLAGS
- Sharding by created_at range.
- Choosing a key without checking which queries become cross-shard.
- Sharding prematurely when one bigger instance would do.
LIKELY FOLLOW-UPS
- How would you move a large tenant to its own shard with no downtime?
- How does consistent hashing help with resharding?
SOURCES
How do you read a PostgreSQL EXPLAIN plan to find why a query is slow?
Reveal model answer
Run EXPLAIN (ANALYZE) on a realistic dataset; in PostgreSQL 18 ANALYZE includes BUFFERS by default. Read the tree from the innermost nodes outward: each node shows estimated cost (startup..total), estimated rows, and with ANALYZE the actual time, actual rows, and loops, where per-loop numbers must be multiplied by loops. The usual culprits are a large gap between estimated and actual rows (stale statistics or correlated columns, so run ANALYZE or add extended statistics), a sequential scan with a filter discarding most rows (missing or unusable index), a nested loop executed thousands of times, and sorts or hashes spilling to disk. Remember that EXPLAIN ANALYZE actually executes the statement, so wrap writes in BEGIN ... ROLLBACK.
WHAT A STRONG ANSWER COVERS
- cost=startup..total, rows, width; actual time, rows, loops.
- Multiply per-loop figures by loops.
- Estimate versus actual row mismatches point to statistics problems.
- Look for Rows Removed by Filter, nested loops with high loops, and external sorts.
- ANALYZE runs the query, including side effects.
RED FLAGS
- Treating cost as milliseconds.
- Running EXPLAIN ANALYZE on a DELETE in production without a rollback.
- Tuning on an empty dev database where every plan is a sequential scan.
LIKELY FOLLOW-UPS
- The planner estimates 1 row but gets 50,000. What do you do?
- How do you find the slowest queries in production in the first place?
SOURCES