Question 1

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.

Accepted Answer

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.

Question 2

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.

Accepted Answer

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.

Question 3

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?

Accepted Answer

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.

Question 4

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.

Accepted Answer

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.

Question 5

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?

Accepted Answer

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.

Question 6

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?

Accepted Answer

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.

Question 7

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?

Accepted Answer

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.

Question 8

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?

Accepted Answer

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.

Question 9

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.

Accepted Answer

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.

Question 10

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?

Accepted Answer

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.

Question 11

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.

Accepted Answer

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.

Question 12

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?

Accepted Answer

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.

Question 13

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?

Accepted Answer

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.

Question 14

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

Accepted Answer

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.

Question 15

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?

Accepted Answer

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.

Question 16

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

Accepted Answer

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.

Question 17

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?

Accepted Answer

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.

Question 18

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.

Accepted Answer

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.

Question 19

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.

Accepted Answer

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.

Question 20

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?

Accepted Answer

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.

Question 21

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?

Accepted Answer

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'.

Question 22

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.

Accepted Answer

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.

Question 23

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?

Accepted Answer

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.

Question 24

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.

Accepted Answer

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.

Question 25

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

Accepted Answer

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.

Question 26

Describe how you would use PostgreSQL tablespaces to place a hot OLTP table on NVMe and an archive table on cheaper spinning disk, and what the operational risks are.

Accepted Answer

Create two tablespaces pointing to mount points on the respective devices. Create or ALTER the hot table to use the NVMe tablespace and the archive table to use the HDD tablespace. Indexes can also be placed in a different tablespace from their table. Operational risks include: if the tablespace mount is unavailable, the database cannot start; backup tools must include all tablespace directories; and moving a table between tablespaces requires an ACCESS EXCLUSIVE lock (consider pg_repack for zero-downtime).

Question 27

A high-throughput insert workload on a child table is experiencing lock contention. You trace it to a foreign key referencing the parent table. Explain the locking mechanism and your remediation options.

Accepted Answer

When a row is inserted into the child table, PostgreSQL acquires a ROW SHARE lock on the referenced parent row to ensure it is not deleted concurrently. If the parent row is being updated or deleted in parallel, the insert must wait. At high concurrency this causes queue-ups. Options: index the foreign key column on the child table (PostgreSQL does not auto-create it) to make referential integrity checks fast; use NOT VALID to skip historic checks and VALIDATE CONSTRAINT separately during off-peak; or re-evaluate whether the FK is operationally necessary given application-level enforcement.

Question 28

What additional information does EXPLAIN FORMAT=JSON give you compared to the standard tabular EXPLAIN in MySQL, and when is that extra detail useful?

Accepted Answer

FORMAT=JSON exposes the actual cost estimates per node (read_cost, eval_cost, prefix_cost), the number of rows per loop, the materialization strategy for subqueries, and the attached_condition showing exactly what filter was pushed down. This is useful for diagnosing why the optimizer chose a full scan over an index (cost estimate mismatch) and for comparing optimizer decisions across MySQL versions or after statistics updates.

Question 29

Your team needs to upgrade a 20 TB production PostgreSQL 13 instance to PostgreSQL 16 with less than 5 minutes of downtime. What strategy do you use?

Accepted Answer

pg_upgrade requires shutting down the old cluster and running the upgrade in place (or with hard links for speed), but downtime is proportional to catalog size, not data size, making it feasible for large databases. For near-zero downtime, use logical replication: set up the new PG16 cluster as a logical subscriber, let it sync, then cut over by redirecting the application after a brief write pause to drain in-flight transactions. pglogical or pg_logical_replication_slot can be used, though DDL changes during the sync period require careful coordination.

Question 30

Explain the two phases of 2PC, identify the failure modes it does not protect against, and describe when you would accept the limitations versus choosing an alternative pattern.

Accepted Answer

Phase 1 (prepare): the coordinator asks all participants to prepare and log a durable commit record; participants reply vote-commit or vote-abort. Phase 2 (commit): if all voted commit, the coordinator sends commit to all participants; otherwise it sends abort. The unresolved failure mode is coordinator failure after phase 1: participants are blocked holding locks until the coordinator recovers. This is the in-doubt transaction problem. 2PC also adds latency proportional to the number of round trips. Alternatives include saga pattern with compensating transactions, or choosing an architecture that keeps a single atomic unit within one database.

Questions

How does a B-tree index store and retrieve rows?Role-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

When does the planner choose a hash join over a nested loop?Role-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Reading EXPLAIN ANALYZE output to find the real bottleneckRole-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Which anomalies does each isolation level prevent?Role-specificmediumVery common

As asked

Sample answer outline

Expect these follow-ups

Design row-level security for regional reportingRole-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

When does an index-only scan fail to avoid a heap fetch?Role-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

Explain how MVCC implements snapshot isolation in PostgreSQLRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

How does PostgreSQL use WAL to recover after a crash?Role-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

How does InnoDB decide which pages to evict from the buffer pool?Role-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

How do you detect and fix index bloat in PostgreSQL?Role-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

Diagnosing and reducing streaming replication lagRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

Logical replication vs physical streaming replication tradeoffsRole-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

Range sharding vs hash sharding: choosing and migratingRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

How does PostgreSQL detect and resolve deadlocks?Role-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

When to use partial indexes versus expression indexesRole-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

PgBouncer pooling modes and when each is appropriateRole-specificmediumCommon

As asked

Sample answer outline

Expect these follow-ups

Tuning PostgreSQL checkpoints for write-heavy workloadsRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

InnoDB row lock types and gap locks explainedRole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

Designing a backup strategy to meet a 15-minute RPORole-specifichardCommon

As asked

Sample answer outline

Expect these follow-ups

JSONB vs JSON storage and GIN index usage in PostgreSQLRole-specificmediumCommon

As asked

Sample answer outline