Database engineer role-specific interview questions

As asked

Walk me through how a B-tree index is physically laid out on disk, how a point lookup traverses it, and what happens when a page split occurs during an insert.

Sample answer outline

A strong answer covers the balanced tree of fixed-size pages, the fan-out at each internal node holding separator keys and child pointers, and leaf pages holding key-to-rowid mappings in sorted order. The candidate should explain that a page split creates a new sibling page and propagates a separator key upward, potentially cascading to the root and increasing tree height. Bonus: mention fill factor and how it pre-allocates space to reduce split frequency.

Expect these follow-ups

• How does a clustered index differ from a secondary index in terms of page layout?
• At what point does adding an index hurt write throughput more than it helps read throughput?

As asked

Explain the conditions under which PostgreSQL's query planner prefers a hash join over a nested loop join, and describe a scenario where a nested loop is actually faster.

Sample answer outline

The planner chooses a hash join when one input is large and lacks a useful index, because building a hash table and probing it is O(N+M) versus O(N*M) for a naive nested loop. A nested loop wins when the outer side is small and the inner side has a selective index, making each inner lookup O(log N) and total cost low. The candidate should mention work_mem as the limit: if the hash table spills to disk, the hash join degrades and a merge join or nested loop might be cheaper.

Expect these follow-ups

• What does enable_hashjoin=off do and when would you set it?

As asked

You run EXPLAIN (ANALYZE, BUFFERS) on a slow query and see that the planner estimated 50 rows but the actual count was 2 million for a particular node. What does this tell you and how do you fix it?

Sample answer outline

A large discrepancy between estimated and actual rows signals stale statistics. The planner chose a plan optimized for 50 rows (possibly a nested loop with an expensive inner index scan) that performs terribly at 2 million rows. The fix starts with ANALYZE on the table to refresh statistics. If the column has a high-correlation pattern or heavy skew, increase the statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS. For complex joins, extended statistics (CREATE STATISTICS) capturing column correlations may help.

Expect these follow-ups

• What does Buffers: shared hit vs shared read tell you in the BUFFERS output?

As asked

List the four SQL standard isolation levels, identify which read anomalies each prevents, and give a concrete example of a non-repeatable read that would slip through READ COMMITTED.

Sample answer outline

READ UNCOMMITTED allows dirty reads, non-repeatable reads, and phantom reads. READ COMMITTED prevents dirty reads but allows non-repeatable reads and phantoms. REPEATABLE READ prevents dirty and non-repeatable reads but allows phantoms in the SQL standard (PostgreSQL actually prevents phantoms too due to MVCC). SERIALIZABLE prevents all anomalies. A non-repeatable read example: transaction A reads a row showing balance=100, transaction B commits an update to 50, transaction A reads again and sees 50 within the same transaction under READ COMMITTED.

Expect these follow-ups

• What is a write skew anomaly and which isolation level prevents it?

As asked

Regional sales managers should only see their own region's deals, while executives can see everything. How would you implement and test this in BI?

Sample answer outline

Model entitlements explicitly: user or group to permitted region, with executive groups mapped to all regions. Enforce row-level security in the BI semantic layer or warehouse policies, preferably close to the data if multiple tools consume it. Test with impersonation, fixture users, and negative cases so a dashboard change cannot bypass the rule. Handle edge cases like managers covering multiple regions, historical territory changes, and shared accounts. Strong answers consider privacy and auditability, not just a filter on a dashboard.

Expect these follow-ups

• Where would you enforce RLS if both Looker and ad-hoc SQL users need access?
• How do you test that a user cannot bypass the filter through drill-downs?
• What happens when a salesperson moves region mid-quarter?

As asked

You have a covering index on (user_id, created_at, amount) and your query selects only those three columns. The EXPLAIN output still shows heap fetches for some rows. Why does that happen and how do you fix it?

Sample answer outline

The candidate should identify the visibility map: PostgreSQL must fetch the heap page for any page not marked all-visible in the visibility map, because the index does not store transaction visibility information. Running VACUUM marks clean pages as all-visible. The fix is ensuring autovacuum is keeping up on high-write tables, or running VACUUM manually after bulk loads.

Expect these follow-ups

• What does VACUUM FREEZE do differently from a regular VACUUM?

As asked

How does PostgreSQL use MVCC to give each transaction a consistent snapshot, and what are the xmin and xmax fields on a tuple actually tracking?

Sample answer outline

Each tuple stores xmin (the transaction ID that inserted it) and xmax (the transaction ID that deleted or updated it, or 0 if still live). A snapshot records the transaction ID watermarks and the list of in-progress transactions at snapshot time. A tuple is visible to a snapshot if its xmin committed before the snapshot and its xmax is either 0 or did not commit before the snapshot. This allows readers to see a consistent view without locking writers.

Expect these follow-ups

• What is transaction ID wraparound and why is it catastrophic if not managed?

As asked

Walk me through what happens when a PostgreSQL instance restarts after a hard crash: what does it read, in what order, and how does it decide what to replay versus discard?

Sample answer outline

PostgreSQL reads the WAL starting from the last checkpoint location stored in pg_control. It replays all WAL records in order (redo pass), applying each change to the data files. Because WAL is write-ahead, any committed change whose WAL was flushed is guaranteed to be replayed; dirty pages that were not flushed to disk are recovered from WAL. Uncommitted changes have no commit record, so they are effectively rolled back. The candidate should mention that PostgreSQL does not have a separate undo pass as InnoDB does.

Expect these follow-ups

• How does synchronous_commit=off change the durability guarantee?

As asked

Describe the InnoDB buffer pool eviction policy, why it uses a midpoint LRU rather than a plain LRU, and how a full-table scan can pollute the buffer pool.

Sample answer outline

InnoDB uses a midpoint insertion LRU. Newly read pages enter at a configurable midpoint (default 3/8 from the tail) rather than the head, so a large sequential scan loads pages near the cold end and they age out quickly without displacing frequently accessed hot pages. A naive LRU would evict hot working-set pages when a full scan reads millions of pages once. The candidate should mention innodb_old_blocks_time and innodb_old_blocks_pct as the tuning knobs.

Expect these follow-ups

• How would you tune the buffer pool for a workload that is 90% read on a small hot dataset?

As asked

Your monitoring shows that a heavily updated table's indexes are growing by 5 GB per week even though the data size is stable. How do you confirm index bloat is the cause and what are your remediation options?

Sample answer outline

Bloat can be confirmed by querying pgstattuple or pgstatindex, or by comparing actual table/index size against estimated live tuple size. Dead tuples accumulate when autovacuum can't keep up, or when long-running transactions hold back the vacuum horizon. Remediation options include tuning autovacuum cost delay and scale factor, running REINDEX CONCURRENTLY to rebuild the index online without locking, or using pg_repack to rewrite the table and indexes without an exclusive lock.

Expect these follow-ups

• What prevents autovacuum from running on a table with an open long-running transaction?

As asked

A PostgreSQL streaming replica is running 30 seconds behind the primary during peak hours. Walk me through how you diagnose the root cause and what levers you have to reduce the lag.

Sample answer outline

Start by checking pg_stat_replication on the primary to distinguish write lag, flush lag, and replay lag. If replay lag dominates, the replica's I/O or CPU is the bottleneck. Single-threaded WAL replay was the historical constraint; PostgreSQL 16 introduced parallel WAL apply, so verify the PostgreSQL version and, on PG 16+, check recovery_parallelism and max_parallel_workers. Also check for long-running queries on the replica that hold conflict locks, causing recovery to pause (hot_standby_feedback can help here). Network bandwidth limits show up as write lag exceeding flush lag.

Expect these follow-ups

• How does hot_standby_feedback prevent replication conflicts from long queries on the replica?

As asked

A team wants to replicate only two tables from a 500-table PostgreSQL database to a separate analytics instance. When should they use logical replication instead of physical streaming, and what are the operational downsides?

Sample answer outline

Logical replication decodes WAL into a row-level change stream and can replicate a subset of tables to a different major version or even a different database. Physical streaming replication copies the full data directory and requires identical major versions. Downsides of logical: DDL changes are not replicated automatically and must be applied manually on the subscriber; sequence values are not replicated; large transactions can cause lag if the publisher must retain WAL until the subscriber confirms receipt. Also, enabling logical WAL creates higher WAL volume.

Expect these follow-ups

• How does the publication/subscription model handle a table that gets a new column added on the publisher?

As asked

You are designing a sharded PostgreSQL setup for a multi-tenant SaaS app with 10,000 tenants, some generating 1,000x more data than others. How do you choose between range, hash, and directory-based sharding, and how do you handle hot shards?

Sample answer outline

Range sharding gives good locality for range queries but creates hot spots when new data is time-ordered (all writes hit the last shard). Hash sharding distributes writes evenly but scatters range scans across all shards. Directory-based sharding stores a mapping table per tenant and allows manual reassignment of large tenants to dedicated shards. For this multi-tenant case, tenant-ID hash sharding is a reasonable default, but large tenants should be moved to their own shards via directory overrides. The candidate should address resharding cost: rebalancing requires migrating data and updating routing.

Expect these follow-ups

• How does Citus handle tenant isolation and shard rebalancing?

As asked

Explain PostgreSQL's deadlock detection mechanism, what triggers it, and how you would diagnose and eliminate recurring deadlocks in a high-throughput OLTP application.

Sample answer outline

PostgreSQL checks for deadlocks only when a lock wait exceeds deadlock_timeout (default 1 s). The deadlock detector builds a wait-for graph and looks for cycles. When a cycle is found, it rolls back one of the transactions (the youngest by default) and logs a ERROR with the full query and lock details. To eliminate recurring deadlocks, engineers should ensure all transactions acquire locks on multiple resources in the same order, use SELECT FOR UPDATE with consistent ordering, or reduce transaction scope to hold locks for shorter durations.

Expect these follow-ups

• What is the difference between deadlock_timeout and lock_timeout?

As asked

Your application filters on status='active' 95% of the time, and the table has 200 million rows where only 2% are active. Should you use a partial index, and how does that compare to an expression index on lower(email) in terms of internals?

Sample answer outline

A partial index on WHERE status='active' indexes only the 4 million active rows, making it tiny, fast to scan, and cheap to maintain on writes for inactive rows. An expression index stores the result of the expression (lower(email)) per row so the planner can use it for WHERE lower(email) = $1 without computing the expression at scan time. The candidate should note that expression indexes require the query predicate to match the expression exactly, and that they increase write overhead because the expression is evaluated on every insert and update.

Expect these follow-ups

• How do you confirm the planner is actually using your partial index after creation?

As asked

Explain the difference between PgBouncer's session, transaction, and statement pooling modes, and describe a real situation where using transaction pooling breaks an application.

Sample answer outline

Session pooling holds the server connection for the full client session, giving the same semantics as a direct connection but with poor multiplexing. Transaction pooling returns the server connection to the pool after each transaction, allowing many more clients with few server connections. Statement pooling returns after each statement and is rarely safe. Transaction pooling breaks applications that use SET LOCAL, advisory locks, prepared statements that persist across transactions, or LISTEN/NOTIFY, because those require a stable server connection.

Expect these follow-ups

• How does Supavisor differ from PgBouncer architecturally?

As asked

A PostgreSQL server running a bulk ETL job shows high I/O spikes every few minutes and slow checkpoint warnings in the logs. How do you diagnose what is happening and what do you tune?

Sample answer outline

The 'checkpoint occurring too frequently' log message appears when WAL volume between checkpoints is high. In PostgreSQL 9.5 and later the relevant parameter is max_wal_size (replacing the old checkpoint_segments). The I/O spikes happen because dirty pages are flushed in a burst at checkpoint time. Fix: increase max_wal_size to reduce checkpoint frequency, and increase checkpoint_completion_target to 0.9 so dirty page flushing is spread over 90% of the checkpoint interval rather than happening at the end. Also, bgwriter_lru_maxpages and bgwriter_delay control proactive background page flushing to further smooth I/O.

Expect these follow-ups

• How does full_page_writes interact with checkpoints and WAL volume?

As asked

Explain the difference between record locks, gap locks, and next-key locks in InnoDB, and describe a scenario where a gap lock causes an unexpected deadlock.

Sample answer outline

A record lock locks a single index entry. A gap lock locks the space before an index entry to prevent phantom inserts in that range. A next-key lock is the combination of both. Under REPEATABLE READ, InnoDB uses next-key locks on index scans to prevent phantom reads. A deadlock can occur when two transactions each hold a gap lock in the same range and then try to insert into the other's gap, causing a cycle. Gap locks can be disabled by switching to READ COMMITTED isolation, which removes them at the cost of allowing phantom reads.

Expect these follow-ups

• What is an insert intention lock and how does it differ from a gap lock?

As asked

Your team's SLA requires a 15-minute RPO and a 1-hour RTO for a 2 TB PostgreSQL database. Walk me through the backup and recovery architecture you would build.

Sample answer outline

A base backup alone cannot meet a 15-minute RPO without continuous WAL archiving. The architecture uses pg_basebackup (or a tool like pgBackRest or Barman) for daily full and incremental base backups, combined with continuous WAL archiving to object storage (S3, GCS). WAL segments are archived as soon as they are complete (typically every 16 MB or archive_timeout seconds). Recovery replays the base backup and WAL up to the target point-in-time. To meet 1-hour RTO on 2 TB, a streaming replica with WAL applied in near-real-time allows promotion in minutes rather than replay from scratch.

Expect these follow-ups

• How does pgBackRest delta restore reduce RTO versus a full restore?

As asked

A team is storing semi-structured product attributes as JSON in PostgreSQL. They ask whether to use json or jsonb, and whether a GIN index will help their queries. What do you tell them?

Sample answer outline

jsonb decomposes and stores JSON in a binary format that is faster to query and supports indexing; json stores the raw text and re-parses on every access. The write cost for jsonb is slightly higher due to parsing at insert time. A GIN index on a jsonb column with the default jsonb_ops operator class supports containment queries (@>) and key existence queries (?). For queries on specific known keys, a functional index on jsonb_extract_path_text is often smaller and faster than a full GIN index.

Expect these follow-ups

• What is the difference between the jsonb_ops and jsonb_path_ops GIN operator classes?

As asked

You partition a PostgreSQL table by range on created_at and a query that filters on created_at still scans all partitions according to EXPLAIN. What could cause that and how do you fix it?

Sample answer outline

Partition pruning fails when the predicate uses a function call on the partition key (e.g., date_trunc('month', created_at) = '2024-01-01') because the planner cannot infer which partition to prune. It also fails if the bind parameter type does not match the partition key type, causing an implicit cast that the planner cannot evaluate at plan time. Enable_partition_pruning must be on. The fix is to write the predicate as a range directly on the raw column: created_at >= '2024-01-01' AND created_at < '2024-02-01'.

Expect these follow-ups

• What is the difference between partition pruning at plan time versus runtime pruning?

As asked

A production PostgreSQL database is throwing 'sorry, too many clients already' errors during a traffic spike. Walk me through the immediate mitigation and the longer-term architecture fix.

Sample answer outline

Immediate mitigation: find idle connections with pg_stat_activity, kill idle ones if safe, and temporarily raise max_connections if RAM allows (each connection costs 5-10 MB). Longer-term: introduce PgBouncer in transaction pooling mode in front of PostgreSQL to multiplex hundreds of application connections onto a small pool of actual server connections. Also review application connection pool settings (pool size, connection lifetime, idle timeout) to ensure the pool is not growing unbounded.

Expect these follow-ups

• What is the shared memory cost of increasing max_connections and why does it matter?

As asked

You rely on Redis to cache a database query result that is expensive to compute. During peak traffic, the cached key expires and thousands of requests simultaneously query the database. How do you prevent this?

Sample answer outline

The standard approach is probabilistic early expiration (the XFetch algorithm): each requester computes a probability of refreshing the cache before the expiry time, weighted by how expensive the computation is. Simpler approaches include a mutex pattern, using SET NX with a short TTL as a distributed lock so only one process refreshes the cache while others serve the stale value, and a background refresh where a scheduled job proactively refreshes keys before they expire. The candidate should also discuss stale-while-revalidate semantics: always return the cached value immediately and refresh asynchronously, so no request ever waits on a cold cache.

Expect these follow-ups

• How does the XFetch algorithm implement probabilistic early expiration?

As asked

Explain why a columnar storage format like Parquet or the one used in Redshift or BigQuery is faster than a row-oriented PostgreSQL table for a query that aggregates two columns over 1 billion rows.

Sample answer outline

Columnar storage places all values for a single column contiguously on disk, so a query that touches only two columns reads only those columns' bytes, drastically reducing I/O. Row storage reads every column in every row even if only two are needed. Columnar storage also achieves much higher compression ratios because values in a column are of the same type and often have low cardinality, allowing run-length encoding and dictionary encoding. The vectorized execution model that most columnar engines use processes batches of column values with SIMD instructions, improving CPU efficiency.

Expect these follow-ups

• What is the trade-off of columnar storage for single-row point lookups or frequent updates?

As asked

A company says it has nightly backups but has never restored them. What backup and restore strategy would you put in place?

Sample answer outline

Define RPO and RTO first because backup frequency and restore architecture follow from business tolerance. Use automated full backups plus continuous WAL or binlog archiving for point-in-time recovery, encrypted and stored in a separate failure domain. Test restores regularly into an isolated environment and measure how long the restore takes, whether credentials and extensions work, and whether applications can connect. Include corruption, accidental delete, regional failure, and operator error scenarios. The trap is treating backup creation as success; recovery is the product.

Expect these follow-ups

• How do you test restore without exposing production data broadly?
• What is your plan for a bad migration that corrupts data but continues for six hours?
• How do backups interact with GDPR deletion requirements?