Design a PostgreSQL topology for a 12 TB orders database receiving 6,000 writes and 35,000 reads per second, growing 400 GB monthly, where checkout requires read-after-write consistency but reporting may be 30 seconds stale.

I would keep one write leader, route checkout reads to it, and use followers for reporting and capacity isolation. - I would place one synchronous follower in a different availability zone from the leader and two asynchronous followers for reporting, with `synchronous_standby_names = 'FIRST 1 (ha1)'`. - Checkout would use the leader endpoint through PgBouncer, while reporting would use a separate pool whose router removes a follower when replay lag exceeds 30 seconds. - At 6,000 writes per second, I would benchmark WAL generation and provision storage for at least 3 times peak WAL bandwidth rather than assume followers increase write capacity. - The 400 GB monthly growth gives about 20 months before the database reaches 20 TB, so I would set a review trigger at 70% storage or 70% sustained leader I/O utilization.

A PostgreSQL payment ledger writes 2,500 transactions per second across two availability zones with 2 ms round-trip latency, while a third region is 75 ms away; choose a synchronous and asynchronous replication policy with an RPO of zero inside the region and a remote lag target of 10 seconds.

I would acknowledge commits after one local standby flushes WAL durably and keep the remote standby asynchronous. - I would set `synchronous_commit = on` and `synchronous_standby_names = 'ANY 1 (az2a, az2b)'`, then load-test whether the extra local round trip keeps commit p99 under the application budget. - I would not put the 75 ms remote standby in the commit quorum because that would add at least one wide-area round trip and make regional network health part of write availability. - I would alert at 5 seconds of remote replay lag and remove deployment or batch pressure before the 10-second RPO target is breached. - If both local synchronous candidates disappear, I would stop writes rather than silently fall back to asynchronous commits because the stated in-region RPO is zero.

Configure Patroni for a three-node PostgreSQL 16 cluster serving 4,000 writes per second across three availability zones, with automatic failover only when the candidate is less than 64 MB behind and no split-brain writes are acceptable.

I would use a three-member etcd quorum for leader election and make Patroni reject promotion of a stale replica. - I would place one etcd voter and one PostgreSQL member per availability zone, because three voters retain quorum after one zone is lost while two do not. - I would configure Patroni with `maximum_lag_on_failover: 67108864`, `synchronous_mode: true`, and `synchronous_mode_strict: true` so writes stop when no synchronous standby can protect them. - I would set `ttl: 30`, `loop_wait: 10`, and `retry_timeout: 10`, then verify that storage and network p99 remain comfortably below those lease timings. - HAProxy would route writes only to Patroni's primary health endpoint, and fencing would revoke the old leader's client and storage access before the replacement accepts writes.

A PostgreSQL primary generates 180 MB/s of WAL and already feeds four local replicas; you must add six reporting replicas in a region 90 ms away while keeping reporting data within 45 seconds of the primary. How would you design cascading replication?

I would feed two regional relay standbys from the primary and cascade three reporting replicas from each relay. - Two independent relay streams cap the primary at six direct senders instead of ten and avoid making one remote relay the only source for all reporting capacity. - I would provision each relay for more than 180 MB/s inbound and 540 MB/s aggregate outbound throughput, plus replay I/O and 30% headroom, because it must replay WAL while serving three downstream streams. - Monitoring would measure primary-to-reporting replay delay end to end, alert at 30 seconds, and stop routing reports at the 45-second freshness limit. - I would use physical replication slots with `max_slot_wal_keep_size` sized to the reconnect window, and rehearse repointing downstream replicas when a relay changes timeline.

Choose a hash-sharding design for a 60 TB PostgreSQL event store receiving 120,000 inserts per second from 40 million devices, where the common query fetches one device's last 24 hours and capacity should double without moving all data.

I would hash a stable device ID into many logical buckets and map those buckets onto physical shards. - I would start with 4,096 logical buckets across 16 shards, so adding 16 more shards moves roughly half the buckets instead of rehashing all 60 TB. - A compound key of `(device_bucket, device_id, event_time)` keeps one device's 24-hour query on one shard while spreading unrelated devices evenly. - At 120,000 inserts per second, the initial average is 7,500 inserts per shard, but I would size each shard for at least twice that rate and track the p99 bucket rather than only the mean. - Hashing sacrifices efficient fleet-wide time-range scans, so those aggregates would flow through CDC into an analytical store instead of fanning out across all OLTP shards.

Design range sharding for a 25 TB multi-tenant billing database growing 1 TB monthly, with 18,000 writes per second, tenant-local transactions, and five tenants that each hold more than 8% of the data.

I would use explicit tenant-ID ranges but isolate oversized tenants into dedicated ranges before they dominate a shared shard. - A shard map service would route tenant ranges, and each of the five tenants above 8% would receive its own shard so its growth and write load have an independent ceiling. - Shared ranges would target 1 TB or 2,000 writes per second, with a split trigger at 70% of either storage or sustained write capacity. - Tenant-local invoices and payments remain single-shard transactions because tenant ID is present in every primary and foreign key. - Range placement supports tenant export and residency rules, but a sequential global tenant ID can create a hot growing edge, so new tenants would be allocated across pre-created ranges rather than appended to one shard.

Design a Vitess topology for a 90 TB MySQL 8 commerce catalog handling 70,000 reads and 15,000 writes per second, with 80% of requests scoped to merchant ID and a requirement to add shards online.

I would shard the Vitess keyspace by a hashed merchant ID and make merchant-scoped queries the single-shard fast path. - I would start with 32 shards, each with one primary and two replica tablets, which averages about 470 writes per second per shard before accounting for merchant skew. - VTGate would route through a VSchema using a consistent merchant-ID vindex, while unscoped catalog search would use a separate search index rather than scatter across 32 shards. - Reads that tolerate 5 seconds of staleness would use `replica` tablet types; cart validation and writes would use `primary` to preserve read-after-write behavior. - Online resharding would use VReplication to copy and stream changes, `SwitchReads` before `SwitchWrites`, and reverse replication during the rollback window.

Place 30 TB of CockroachDB data for customers in Frankfurt and Singapore at 8,000 writes and 45,000 reads per second, where EU customer data must remain in the EU, local reads should stay below 20 ms p99, and each customer's transactions require serializable consistency.

I would use separate EU and Singapore CockroachDB clusters because strict residency is stronger than the convenience of one global cluster. - The EU cluster would place at least three voters across approved EU availability zones, while the Singapore cluster would use three local zones, so either cluster can lose one zone without exporting replicas. - A regional tenant directory keeps identity metadata with the customer, while a global router sees only an opaque route token and destination region; every primary key starts with tenant ID inside the selected cluster. - CockroachDB keeps serializable transactions and leaseholders local inside each cluster, and I would load-test the 20 ms p99 target separately in Frankfurt and Singapore at the stated 8,000 writes per second. - Cross-residency SQL transactions are deliberately unavailable; only approved aggregated or tokenized CDC data reaches global analytics, trading simpler compliance for two clusters to operate.

Move 14 TB and 20,000 writes per second from eight overloaded shards to sixteen shards without downtime, while allowing at most 60 seconds of rollback exposure and requiring row-count and checksum validation.

I would treat resharding as an online ownership transfer with bulk copy, ordered change capture, validation, and versioned routing. - I would copy range snapshots at a throttled 400 MB/s per source shard and stream subsequent changes from a durable WAL or binlog position so production writes continue. - Before cutover, each destination must reach less than 2 seconds of apply lag and pass per-range row counts, chunk checksums, and sampled business-invariant queries. - A versioned shard map would switch reads first, then writes atomically; stale clients would be rejected or redirected instead of writing to the old owner. - I would retain reverse change capture for 60 seconds, cap migration I/O at 25% of shard capacity, and remove old ranges only after the rollback window and final validation pass.

An order placement flow spans inventory and payment data on different shards at 3,000 orders per second; overselling is forbidden, payment confirmation may take 30 seconds, and holding distributed locks during external calls is unacceptable. How would you handle the cross-shard transaction?

I would use local reservation transactions plus a durable saga rather than hold a two-phase commit open across the payment call. - The inventory shard would atomically decrement available stock and create a reservation with a 2-minute expiry, using the order ID as an idempotency key. - The same local commit would write an outbox event; a relay would deliver it at least once, and the payment shard would deduplicate by order ID before creating one charge attempt. - Payment success within 30 seconds confirms the reservation, while rejection or expiry issues an idempotent compensation that releases stock. - I would use database 2PC only for short, database-only operations whose participants support durable prepare and whose measured lock time stays below a strict threshold such as 200 ms.

A PostgreSQL audit table holds 18 TB, receives 12,000 inserts per second, grows by 1.4 TB per month, and retains 13 months; how would you design its time partitioning?

I would range-partition by event_time in monthly partitions, then verify that one month is still a practical unit for queries and retention. - PostgreSQL declarative RANGE partitioning gives 13 active monthly children plus one future child, so retention becomes DETACH PARTITION followed by archive and DROP instead of a massive DELETE. - I would create partitions ahead with exact half-open bounds, route invalid timestamps to a monitored DEFAULT partition, and move late arrivals through an explicit correction job. - Each child would have a local B-tree on (tenant_id, event_time) for tenant time scans and a BRIN on event_time only if physical ordering makes its small storage cost useful. - Daily partitions would reduce each maintenance unit but increase relation, index, statistics, and planning overhead from roughly 14 active partitions to about 400.

A 6 TB PostgreSQL SaaS database serves 8,000 QPS for 40,000 tenants, grows 500 GB per quarter, and retains seven years; how would you use list partitioning without weakening tenant isolation?

I would use LIST partitioning only for a small number of placement classes, not create one partition per tenant. - A tenant_directory would map each tenant to a stable class such as shared_eu, shared_us, regulated, or dedicated, and LIST partitions would enforce those known placement boundaries. - Every primary key and local index would start with tenant_id, while PostgreSQL RLS policies using a trusted transaction-local tenant setting would protect rows inside shared partitions. - Large tenants would move through a controlled copy and routing cutover to dedicated partitions or databases; 40,000 child tables would make DDL, autovacuum, statistics, and planning unmanageable. - A monitored DEFAULT partition would catch unmapped placements, but application traffic would fail closed when tenant_directory has no authorized route.

A PostgreSQL ingestion table is 4 TB, handles 30,000 writes per second, grows 220 GB per week, and keeps 26 weeks; would hash partitioning solve both write distribution and retention?

Hash partitioning alone would spread tenant writes but make weekly retention expensive, so I would range-partition by week and hash-subpartition only if the current week remains a measured hotspot. - The top level would use RANGE on ingested_at, allowing one 220 GB weekly partition to be detached and dropped when it ages past 26 weeks. - Eight HASH remainders on tenant_id inside each week would distribute index and vacuum work, but they create about 216 active leaf partitions and multiply schema maintenance. - Queries must include ingested_at for range pruning and tenant_id for hash pruning; tenant-only history queries would still touch all 26 weeks. - I would benchmark one weekly leaf first because PostgreSQL still writes through one primary storage stack, so hash partitions do not add disks or scale a saturated WAL path by themselves.

A 9 TB PostgreSQL metrics table has 36 monthly partitions, serves 18,000 QPS, grows 300 GB per month, and retains three years, but seven-day queries scan every partition; how do you restore pruning?

I would make the predicate directly comparable to the partition key and prove pruning at both plan and execution time. - EXPLAIN (ANALYZE, BUFFERS) should show only the current one or two children; Subplans Removed and never executed nodes distinguish execution-time pruning from a full scan. - I would replace expressions such as date_trunc('day', recorded_at) = $1 or recorded_at::date = $1 with half-open bounds recorded_at >= $1 AND recorded_at < $2 using matching timestamptz types. - PostgreSQL can prune parameterized partitions during execution, but a hidden cast, volatile function, expression around the partition key, or missing partition-key predicate can prevent the expected elimination. - Pruning only selects children, so each surviving partition still needs an index matching the remaining filters, such as (metric_id, recorded_at), and current statistics.

A 24 TB PostgreSQL history table serves 3,500 QPS, adds 4 TB per year, and retains five years; should it use daily, monthly, or yearly partitions?

I would start with monthly partitions because roughly 60 active children align with retention without making each maintenance action a multi-terabyte event. - Daily partitioning creates about 1,825 children before indexes, increasing catalog size, relcache memory, autovacuum workers' object queue, backup manifests, and DDL fan-out. - Yearly partitions approach 4 to 5 TB each, so detach validation, index rebuilds, vacuum recovery, and archival retries have an unnecessarily large blast radius. - I would replay the real query mix and compare planning time, partitions touched, lock count, and end-to-end p95 for one-month and multi-year scans before fixing the interval. - If one month exceeds the backup or maintenance window, I would shorten only that boundary and automate future child creation, index attachment, ANALYZE, and retention checks.

A 7 TB PostgreSQL orders table runs at 14,000 QPS, grows 180 GB per month, retains six years, and is range-partitioned by created_at; how would you preserve order-number uniqueness and index efficiency?

I would not claim that local indexes can enforce a global order_number constraint when the partition key is absent. - PostgreSQL requires every partition key column in a UNIQUE or PRIMARY KEY constraint on the partitioned table, so UNIQUE (order_number, created_at) does not prevent the same order number in two months. - If order_number must be globally unique, I would reserve it in a small unpartitioned order_identity table with UNIQUE (order_number), then create the order row in the same transaction. - Each child would get only workload-backed indexes, such as (tenant_id, created_at DESC) INCLUDE (status, total) and a selective index for open orders, because every extra B-tree taxes 14,000 QPS and storage. - New partitions would be built with matching CHECK bounds and indexes before ATTACH PARTITION, then validated through pg_indexes and constraint inventory automation.

A 12 TB PostgreSQL commerce database handles 22,000 OLTP QPS, grows 700 GB per quarter, retains four years, and a six-hour report saturates its read replica; how would you isolate reporting?

I would keep failover capacity separate from reporting and move the six-hour scan to a dedicated analytical copy. - One lightly loaded physical standby would remain eligible for promotion, while reporting would consume a separate logical subscriber, warehouse, or columnar store sized for scans. - Adding physical replicas does not scale writes, and each replica must replay the same WAL; long standby queries can be canceled by recovery conflicts or, with hot_standby_feedback, retain dead tuples and increase primary bloat. - Logical replication or CDC would publish required tables into a reporting schema with report-specific indexes and materialized aggregates, accepting a measured freshness target such as five minutes. - Resource groups, statement_timeout, connection limits, and scheduled refreshes would cap reporting demand so a retry storm cannot consume OLTP storage bandwidth or failover headroom.

A 10 TB PostgreSQL primary sustains 16,000 QPS, generates 18 MB of WAL per second at peak, grows 250 GB per month, and retains five years; how would you size WAL, checkpoints, and storage headroom?

I would size from measured peak WAL and dirty-page rates, then reserve enough space for checkpoint variance, replication retention, and recovery operations. - At 18 MB/s the cluster generates about 16 GB of WAL per 15 minutes and 1.6 TB per day, so pg_stat_wal, pg_stat_bgwriter, and checkpoint logs must validate both the steady rate and burst multiplier. - I would start testing checkpoint_timeout at 15 minutes, checkpoint_completion_target near 0.9, and max_wal_size around 64 GB, then tune from checkpoint frequency, fsync latency, recovery time, and full-page-write amplification. - WAL storage would have low fsync latency and at least several checkpoint cycles of free space, while replication slots and archive backlog would have explicit retained-byte alarms because max_wal_size is not a hard disk cap. - Data capacity would include table growth, index ratio, temporary files, vacuum rewrite space, replicas, backups, and at least one-node-loss headroom rather than only the current 10 TB heap.

A managed PostgreSQL service stores 32 TB, peaks at 45,000 QPS, grows 55 percent yearly, and must retain seven years; build a three-year capacity plan before its current 64 TB limit is reached.

I would forecast each resource from workload drivers and set migration triggers early enough to act before storage or throughput saturates. - At 55 percent annual compound growth, 32 TB becomes roughly 77 TB in two years and 119 TB in three before indexes, temporary space, WAL, backups, and replica copies, so the 64 TB ceiling is inside the second year. - I would model reads, writes, rows, WAL bytes, working set, CPU, IOPS, throughput, connections, and p99 separately because 45,000 QPS alone does not identify the limiting resource. - Quarterly production-shaped load tests would include peak traffic, maintenance, replica catch-up, and loss of one node, with a target below about 70 percent sustained saturation on the bottleneck. - A decision gate at 45 TB or 12 months of projected headroom would start vertical migration, retention-tiering, or sharding work; read replicas would be chosen only if stale-tolerant reads, not writes or storage, drive the limit.

An Oracle order platform holds 30 TB, serves 25,000 QPS, grows 3 TB per year, and retains ten years; would you spend on a second RAC node or build Data Guard in another region?

I would choose from the failure target: RAC adds local instance capacity and availability, while Data Guard provides a separate database copy for site and storage failure. - A two-node RAC design can keep services running after one instance fails, but Cache Fusion and the private interconnect do not remove shared-storage, cluster, or regional failure domains. - Active Data Guard can offload eligible reads and maintain a physical standby; synchronous Maximum Availability adds commit latency across distance, while asynchronous transport leaves a measurable RPO based on redo lag. - Capacity tests must prove that one surviving RAC node can carry the critical portion of 25,000 QPS and that the standby can receive and apply peak redo while storing the projected 60 TB ten-year footprint plus indexes and recovery headroom. - If both local continuity and regional recovery are required, RAC and Data Guard are complementary, so I would compare the combined licenses and operations against a simpler single-instance primary plus standby architecture.

Database Administrator interview questions

100 real questions with model answers and explanations for Senior candidates.

See a Database Administrator resume example →

Practice with flashcards

Spaced repetition · Hunter Pass

Questions

databasepostgresdesign

I would keep one write leader, route checkout reads to it, and use followers for reporting and capacity isolation.

I would place one synchronous follower in a different availability zone from the leader and two asynchronous followers for reporting, with `synchronous_standby_names = 'FIRST 1 (ha1)'`.
Checkout would use the leader endpoint through PgBouncer, while reporting would use a separate pool whose router removes a follower when replay lag exceeds 30 seconds.
At 6,000 writes per second, I would benchmark WAL generation and provision storage for at least 3 times peak WAL bandwidth rather than assume followers increase write capacity.
The 400 GB monthly growth gives about 20 months before the database reaches 20 TB, so I would set a review trigger at 70% storage or 70% sustained leader I/O utilization.

Why interviewers ask this: The interviewer is checking whether the candidate separates write authority, read consistency, replica use, and measurable capacity limits.

transactionspostgresreplication

I would acknowledge commits after one local standby flushes WAL durably and keep the remote standby asynchronous.

I would set `synchronous_commit = on` and `synchronous_standby_names = 'ANY 1 (az2a, az2b)'`, then load-test whether the extra local round trip keeps commit p99 under the application budget.
I would not put the 75 ms remote standby in the commit quorum because that would add at least one wide-area round trip and make regional network health part of write availability.
I would alert at 5 seconds of remote replay lag and remove deployment or batch pressure before the 10-second RPO target is breached.
If both local synchronous candidates disappear, I would stop writes rather than silently fall back to asynchronous commits because the stated in-region RPO is zero.

Why interviewers ask this: A strong answer turns durability and latency constraints into explicit PostgreSQL commit settings and degraded-mode behavior.

postgresconfigavailability

I would use a three-member etcd quorum for leader election and make Patroni reject promotion of a stale replica.

I would place one etcd voter and one PostgreSQL member per availability zone, because three voters retain quorum after one zone is lost while two do not.
I would configure Patroni with `maximum_lag_on_failover: 67108864`, `synchronous_mode: true`, and `synchronous_mode_strict: true` so writes stop when no synchronous standby can protect them.
I would set `ttl: 30`, `loop_wait: 10`, and `retry_timeout: 10`, then verify that storage and network p99 remain comfortably below those lease timings.
HAProxy would route writes only to Patroni's primary health endpoint, and fencing would revoke the old leader's client and storage access before the replacement accepts writes.

Why interviewers ask this: The interviewer is evaluating quorum placement, promotion eligibility, strict synchronous behavior, and fencing as one correctness design.

postgresreplicationdesign

I would feed two regional relay standbys from the primary and cascade three reporting replicas from each relay.

Two independent relay streams cap the primary at six direct senders instead of ten and avoid making one remote relay the only source for all reporting capacity.
I would provision each relay for more than 180 MB/s inbound and 540 MB/s aggregate outbound throughput, plus replay I/O and 30% headroom, because it must replay WAL while serving three downstream streams.
Monitoring would measure primary-to-reporting replay delay end to end, alert at 30 seconds, and stop routing reports at the 45-second freshness limit.
I would use physical replication slots with `max_slot_wal_keep_size` sized to the reconnect window, and rehearse repointing downstream replicas when a relay changes timeline.

Why interviewers ask this: The interviewer is checking whether cascading replication reduces source fan-out without hiding compounded lag and relay capacity requirements.

queriespostgressharding

I would hash a stable device ID into many logical buckets and map those buckets onto physical shards.

I would start with 4,096 logical buckets across 16 shards, so adding 16 more shards moves roughly half the buckets instead of rehashing all 60 TB.
A compound key of `(device_bucket, device_id, event_time)` keeps one device's 24-hour query on one shard while spreading unrelated devices evenly.
At 120,000 inserts per second, the initial average is 7,500 inserts per shard, but I would size each shard for at least twice that rate and track the p99 bucket rather than only the mean.
Hashing sacrifices efficient fleet-wide time-range scans, so those aggregates would flow through CDC into an analytical store instead of fanning out across all OLTP shards.

Why interviewers ask this: A strong answer balances even write distribution, query locality, spare capacity, and future shard movement.

databasetransactionssharding

I would use explicit tenant-ID ranges but isolate oversized tenants into dedicated ranges before they dominate a shared shard.

A shard map service would route tenant ranges, and each of the five tenants above 8% would receive its own shard so its growth and write load have an independent ceiling.
Shared ranges would target 1 TB or 2,000 writes per second, with a split trigger at 70% of either storage or sustained write capacity.
Tenant-local invoices and payments remain single-shard transactions because tenant ID is present in every primary and foreign key.
Range placement supports tenant export and residency rules, but a sequential global tenant ID can create a hot growing edge, so new tenants would be allocated across pre-created ranges rather than appended to one shard.

Why interviewers ask this: The interviewer is testing whether range boundaries account for skew, transaction locality, growth, and operational movement.

shardingdesign

I would shard the Vitess keyspace by a hashed merchant ID and make merchant-scoped queries the single-shard fast path.

I would start with 32 shards, each with one primary and two replica tablets, which averages about 470 writes per second per shard before accounting for merchant skew.
VTGate would route through a VSchema using a consistent merchant-ID vindex, while unscoped catalog search would use a separate search index rather than scatter across 32 shards.
Reads that tolerate 5 seconds of staleness would use `replica` tablet types; cart validation and writes would use `primary` to preserve read-after-write behavior.
Online resharding would use VReplication to copy and stream changes, `SwitchReads` before `SwitchWrites`, and reverse replication during the rollback window.

Why interviewers ask this: The interviewer is evaluating concrete Vitess routing, tablet roles, workload locality, and online resharding mechanics.

consistencyserializationtransactions

I would use separate EU and Singapore CockroachDB clusters because strict residency is stronger than the convenience of one global cluster.

The EU cluster would place at least three voters across approved EU availability zones, while the Singapore cluster would use three local zones, so either cluster can lose one zone without exporting replicas.
A regional tenant directory keeps identity metadata with the customer, while a global router sees only an opaque route token and destination region; every primary key starts with tenant ID inside the selected cluster.
CockroachDB keeps serializable transactions and leaseholders local inside each cluster, and I would load-test the 20 ms p99 target separately in Frankfurt and Singapore at the stated 8,000 writes per second.
Cross-residency SQL transactions are deliberately unavailable; only approved aggregated or tokenized CDC data reaches global analytics, trading simpler compliance for two clusters to operate.

Why interviewers ask this: A strong answer makes the residency boundary explicit and weighs local transaction latency against the operational cost and lost cross-region transactions of separate clusters.

shardingvalidationrollback

I would treat resharding as an online ownership transfer with bulk copy, ordered change capture, validation, and versioned routing.

I would copy range snapshots at a throttled 400 MB/s per source shard and stream subsequent changes from a durable WAL or binlog position so production writes continue.
Before cutover, each destination must reach less than 2 seconds of apply lag and pass per-range row counts, chunk checksums, and sampled business-invariant queries.
A versioned shard map would switch reads first, then writes atomically; stale clients would be rejected or redirected instead of writing to the old owner.
I would retain reverse change capture for 60 seconds, cap migration I/O at 25% of shard capacity, and remove old ranges only after the rollback window and final validation pass.

Why interviewers ask this: The interviewer is checking whether resharding preserves write ordering and routing correctness while controlling validation, rollback, and resource contention.

transactionsshardingdistributed

I would use local reservation transactions plus a durable saga rather than hold a two-phase commit open across the payment call.

The inventory shard would atomically decrement available stock and create a reservation with a 2-minute expiry, using the order ID as an idempotency key.
The same local commit would write an outbox event; a relay would deliver it at least once, and the payment shard would deduplicate by order ID before creating one charge attempt.
Payment success within 30 seconds confirms the reservation, while rejection or expiry issues an idempotent compensation that releases stock.
I would use database 2PC only for short, database-only operations whose participants support durable prepare and whose measured lock time stays below a strict threshold such as 200 ms.

Why interviewers ask this: The interviewer is evaluating whether atomic local invariants, retries, intermediate states, and the limits of two-phase commit are handled explicitly.

postgrespartitioningdesign

I would range-partition by event_time in monthly partitions, then verify that one month is still a practical unit for queries and retention.

PostgreSQL declarative RANGE partitioning gives 13 active monthly children plus one future child, so retention becomes DETACH PARTITION followed by archive and DROP instead of a massive DELETE.
I would create partitions ahead with exact half-open bounds, route invalid timestamps to a monitored DEFAULT partition, and move late arrivals through an explicit correction job.
Each child would have a local B-tree on (tenant_id, event_time) for tenant time scans and a BRIN on event_time only if physical ordering makes its small storage cost useful.
Daily partitions would reduce each maintenance unit but increase relation, index, statistics, and planning overhead from roughly 14 active partitions to about 400.

Why interviewers ask this: The interviewer evaluates whether partition interval follows measured retention, query shape, and operational unit size.

databasepostgrespartitioning

I would use LIST partitioning only for a small number of placement classes, not create one partition per tenant.

A tenant_directory would map each tenant to a stable class such as shared_eu, shared_us, regulated, or dedicated, and LIST partitions would enforce those known placement boundaries.
Every primary key and local index would start with tenant_id, while PostgreSQL RLS policies using a trusted transaction-local tenant setting would protect rows inside shared partitions.
Large tenants would move through a controlled copy and routing cutover to dedicated partitions or databases; 40,000 child tables would make DDL, autovacuum, statistics, and planning unmanageable.
A monitored DEFAULT partition would catch unmapped placements, but application traffic would fail closed when tenant_directory has no authorized route.

Why interviewers ask this: A strong answer separates physical placement from row-level authorization and avoids partition-per-tenant metadata scale.

postgrespartitioningdistributions

Hash partitioning alone would spread tenant writes but make weekly retention expensive, so I would range-partition by week and hash-subpartition only if the current week remains a measured hotspot.

The top level would use RANGE on ingested_at, allowing one 220 GB weekly partition to be detached and dropped when it ages past 26 weeks.
Eight HASH remainders on tenant_id inside each week would distribute index and vacuum work, but they create about 216 active leaf partitions and multiply schema maintenance.
Queries must include ingested_at for range pruning and tenant_id for hash pruning; tenant-only history queries would still touch all 26 weeks.
I would benchmark one weekly leaf first because PostgreSQL still writes through one primary storage stack, so hash partitions do not add disks or scale a saturated WAL path by themselves.

Why interviewers ask this: The interviewer checks whether the candidate combines partition methods only when lifecycle and hotspot evidence justify the extra objects.

postgresqueriespartitioning

I would make the predicate directly comparable to the partition key and prove pruning at both plan and execution time.

EXPLAIN (ANALYZE, BUFFERS) should show only the current one or two children; Subplans Removed and never executed nodes distinguish execution-time pruning from a full scan.
I would replace expressions such as date_trunc('day', recorded_at) = $1 or recorded_at::date = $1 with half-open bounds recorded_at >= $1 AND recorded_at < $2 using matching timestamptz types.
PostgreSQL can prune parameterized partitions during execution, but a hidden cast, volatile function, expression around the partition key, or missing partition-key predicate can prevent the expected elimination.
Pruning only selects children, so each surviving partition still needs an index matching the remaining filters, such as (metric_id, recorded_at), and current statistics.

Why interviewers ask this: The interviewer evaluates whether the candidate can distinguish partition pruning from indexed row access and verify both in a real plan.

postgrespartitioning

I would start with monthly partitions because roughly 60 active children align with retention without making each maintenance action a multi-terabyte event.

Daily partitioning creates about 1,825 children before indexes, increasing catalog size, relcache memory, autovacuum workers' object queue, backup manifests, and DDL fan-out.
Yearly partitions approach 4 to 5 TB each, so detach validation, index rebuilds, vacuum recovery, and archival retries have an unnecessarily large blast radius.
I would replay the real query mix and compare planning time, partitions touched, lock count, and end-to-end p95 for one-month and multi-year scans before fixing the interval.
If one month exceeds the backup or maintenance window, I would shorten only that boundary and automate future child creation, index attachment, ANALYZE, and retention checks.

Why interviewers ask this: A strong answer derives partition count from lifecycle and maintenance costs rather than treating smaller partitions as automatically better.

indexespostgrespartitioning

I would not claim that local indexes can enforce a global order_number constraint when the partition key is absent.

PostgreSQL requires every partition key column in a UNIQUE or PRIMARY KEY constraint on the partitioned table, so UNIQUE (order_number, created_at) does not prevent the same order number in two months.
If order_number must be globally unique, I would reserve it in a small unpartitioned order_identity table with UNIQUE (order_number), then create the order row in the same transaction.
Each child would get only workload-backed indexes, such as (tenant_id, created_at DESC) INCLUDE (status, total) and a selective index for open orders, because every extra B-tree taxes 14,000 QPS and storage.
New partitions would be built with matching CHECK bounds and indexes before ATTACH PARTITION, then validated through pg_indexes and constraint inventory automation.

Why interviewers ask this: The interviewer checks knowledge of PostgreSQL partitioned uniqueness limits and the write cost of duplicated local indexes.

databasepostgresreplication

I would keep failover capacity separate from reporting and move the six-hour scan to a dedicated analytical copy.

One lightly loaded physical standby would remain eligible for promotion, while reporting would consume a separate logical subscriber, warehouse, or columnar store sized for scans.
Adding physical replicas does not scale writes, and each replica must replay the same WAL; long standby queries can be canceled by recovery conflicts or, with hot_standby_feedback, retain dead tuples and increase primary bloat.
Logical replication or CDC would publish required tables into a reporting schema with report-specific indexes and materialized aggregates, accepting a measured freshness target such as five minutes.
Resource groups, statement_timeout, connection limits, and scheduled refreshes would cap reporting demand so a retry storm cannot consume OLTP storage bandwidth or failover headroom.

Why interviewers ask this: The interviewer evaluates whether read scaling accounts for WAL replay, consistency lag, and distinct HA and analytical roles.

postgres

I would size from measured peak WAL and dirty-page rates, then reserve enough space for checkpoint variance, replication retention, and recovery operations.

At 18 MB/s the cluster generates about 16 GB of WAL per 15 minutes and 1.6 TB per day, so pg_stat_wal, pg_stat_bgwriter, and checkpoint logs must validate both the steady rate and burst multiplier.
I would start testing checkpoint_timeout at 15 minutes, checkpoint_completion_target near 0.9, and max_wal_size around 64 GB, then tune from checkpoint frequency, fsync latency, recovery time, and full-page-write amplification.
WAL storage would have low fsync latency and at least several checkpoint cycles of free space, while replication slots and archive backlog would have explicit retained-byte alarms because max_wal_size is not a hard disk cap.
Data capacity would include table growth, index ratio, temporary files, vacuum rewrite space, replicas, backups, and at least one-node-loss headroom rather than only the current 10 TB heap.

Why interviewers ask this: A strong answer converts WAL rate into concrete sizing while recognizing that checkpoints, slots, and maintenance can exceed the nominal budget.

postgrescapacity

I would forecast each resource from workload drivers and set migration triggers early enough to act before storage or throughput saturates.

At 55 percent annual compound growth, 32 TB becomes roughly 77 TB in two years and 119 TB in three before indexes, temporary space, WAL, backups, and replica copies, so the 64 TB ceiling is inside the second year.
I would model reads, writes, rows, WAL bytes, working set, CPU, IOPS, throughput, connections, and p99 separately because 45,000 QPS alone does not identify the limiting resource.
Quarterly production-shaped load tests would include peak traffic, maintenance, replica catch-up, and loss of one node, with a target below about 70 percent sustained saturation on the bottleneck.
A decision gate at 45 TB or 12 months of projected headroom would start vertical migration, retention-tiering, or sharding work; read replicas would be chosen only if stale-tolerant reads, not writes or storage, drive the limit.

Why interviewers ask this: The interviewer evaluates compound-growth arithmetic, bottleneck-specific forecasting, failure reserve, and actionable lead-time thresholds.

I would choose from the failure target: RAC adds local instance capacity and availability, while Data Guard provides a separate database copy for site and storage failure.

A two-node RAC design can keep services running after one instance fails, but Cache Fusion and the private interconnect do not remove shared-storage, cluster, or regional failure domains.
Active Data Guard can offload eligible reads and maintain a physical standby; synchronous Maximum Availability adds commit latency across distance, while asynchronous transport leaves a measurable RPO based on redo lag.
Capacity tests must prove that one surviving RAC node can carry the critical portion of 25,000 QPS and that the standby can receive and apply peak redo while storing the projected 60 TB ten-year footprint plus indexes and recovery headroom.
If both local continuity and regional recovery are required, RAC and Data Guard are complementary, so I would compare the combined licenses and operations against a simpler single-instance primary plus standby architecture.

Why interviewers ask this: The interviewer checks whether the candidate distinguishes shared-database clustering from replicated disaster recovery and quantifies both capacity paths.

Locked questions

21
A 400 million-row events table serves 180 QPS of SELECT id FROM events WHERE tenant_id = $1 AND status = 'open' AND created_at >= $2 ORDER BY created_at DESC; EXPLAIN ANALYZE shows a Bitmap Heap Scan reading 42,000 buffers followed by a Sort at 310 ms. What composite index would you test, and why that column order?
indexesschemadata-structures
22
An 800 million-row orders table is 98 percent archived, while 220 QPS run SELECT id, created_at FROM orders WHERE tenant_id = $1 AND archived_at IS NULL ORDER BY created_at DESC LIMIT 50; EXPLAIN ANALYZE shows an Index Scan removing 38,000 archived rows and taking 190 ms. Would you use a partial index?
indexesfundamentals
23
A 120 million-row invoices table handles 300 QPS of SELECT id, total, currency FROM invoices WHERE account_id = $1 AND issued_at >= $2; EXPLAIN ANALYZE uses an Index Scan on (account_id, issued_at) but performs 8,400 heap fetches, reads 9,100 buffers, and takes 95 ms. How would you evaluate a covering index?
decision-makingindexesdata-structures
24
Customers has 20 million rows and orders has 600 million; a 12 QPS query joins one tenant's last day of orders to customers and returns 70,000 rows, but EXPLAIN ANALYZE estimated 300 rows and chose a Nested Loop with 70,000 customer index probes, 180,000 buffers, and 1.8 seconds. How would you decide between nested loop and hash join?
indexesjoinsqueries
25
A 300 million-row events table receives 15 QPS of SELECT count(*) FROM events WHERE tenant_id = 42 AND event_type = 'purchase'; tenant 42 produces most purchases, but EXPLAIN ANALYZE estimates 500 rows, gets 8 million, and the chosen Bitmap Heap Scan spills into 620,000 buffer reads. How would you fix the cardinality estimate?
estimationdata-structures
26
A one billion-row jobs table serves 80 QPS of SELECT id FROM jobs WHERE queue_id = $1 AND state = 'ready' ORDER BY priority DESC, created_at ASC LIMIT 100; EXPLAIN ANALYZE reads 1.5 million candidates with a Parallel Bitmap Heap Scan, then runs top-N heapsort and returns in 450 ms. How would you remove the sort work?
data-structures
27
A 90 million-row users table receives 120 QPS of SELECT id FROM users WHERE tenant_id = $1 AND lower(email) = lower($2); EXPLAIN ANALYZE shows a Parallel Seq Scan removing 29 million rows per worker, reading 410,000 buffers, and taking 680 ms. What expression index would you propose?
indexes
28
A 250 million-row documents table handles 40 QPS of SELECT id FROM documents WHERE metadata @> '{"type":"invoice","region":"eu"}'; EXPLAIN ANALYZE shows a Parallel Seq Scan reading 1.2 million buffers and taking 2.4 seconds. How would you choose and validate a JSONB GIN index?
indexesvalidation
29
An append-only telemetry table has 6 billion rows physically correlated with observed_at and receives 4 QPS of seven-day range counts; EXPLAIN ANALYZE uses a Parallel Seq Scan over 14 million buffers and takes 11 seconds, while a full B-tree would be about 180 GB. Would you use BRIN?
30
A one billion-row payments table sustains 25,000 writes per second, while a 2 QPS query filters merchant_id, state, and settled_at; EXPLAIN ANALYZE shows a Parallel Seq Scan taking 900 ms, and the proposed (merchant_id, state, settled_at) B-tree is estimated at 180 GB. How would you prove whether adding it is safe and worthwhile?
estimationqueries
31
Design DB2 11.5 HADR for an 18 TB banking database handling 7,000 transactions per second, with a primary and local standby 2 ms apart plus a remote standby 60 ms away; local RTO is 90 seconds with RPO zero, while regional RPO is 30 seconds.
databasetransactionsdesign
32
How would you configure PostgreSQL 16 synchronous quorum replication for a 20 TB payment database across three availability zones, sustaining 15,000 writes per second with RTO 90 seconds and RPO zero?
databasepostgresreplication
33
Design multi-region disaster recovery for a 40 TB PostgreSQL 16 order database deployed across three availability zones in each of two regions, producing 25 MB/s of WAL, with RTO 15 minutes and RPO 2 minutes.
databasepostgresdesign
34
How would you design a MySQL 8.4 InnoDB Cluster for an 8 TB customer database across three availability zones, handling 6,000 transactions per second with RTO 2 minutes and RPO zero?
databasetransactionsdesign
35
Design Oracle 23ai availability and disaster recovery for a 60 TB settlement database generating 120 MB/s of redo, with RAC across two availability zones in the primary region, a standby region 900 km away, RTO 5 minutes, and RPO 30 seconds.
designavailabilitydatabase
36
How would you design a PostgreSQL 16 PITR chain for a 25 TB analytics database across three availability zones, generating 80 MB/s of WAL, with RTO 4 hours and RPO 5 minutes?
databasepostgresdesign
37
Design point-in-time recovery for a 12 TB MySQL 8.4 database across three availability zones, producing 40 MB/s of binlog, with restore bandwidth of 1.5 GB/s, RTO 3 hours, and RPO 1 minute.
designavailabilitydatabase
38
How would you set full and incremental backup cadence for a 100 TB Oracle 19c warehouse spanning two availability zones, with an offsite region, 2 GB/s effective restore bandwidth, RTO 8 hours, and RPO 15 minutes?
backupsavailabilitywarehouse
39
Design immutable offsite backups for a 30 TB PostgreSQL 16 healthcare database across three availability zones and a second region, generating 30 MB/s of WAL, with RTO 6 hours and RPO 15 minutes.
designbackupsavailability
40
A 48 TB MySQL 8.4 service runs across three availability zones with an offsite region, writes 50 MB/s of binlog, and requires RTO 6 hours and RPO 5 minutes. How would you calculate restore capacity and design validation drills?
designcapacityavailability
41
A PostgreSQL service has 120 application instances, each configured for 20 connections, while the database has max_connections = 500 and transactions usually finish in 30 ms. Would you use PgBouncer transaction or session pooling, and how would you configure it?
databasetransactionspostgres
42
Eighty application instances generate 16,000 PostgreSQL transactions per second, mean database time is 40 ms, max_connections is 800, and load tests show latency degrades above 480 active queries. How would you size the pools?
databasetransactionspostgres
43
A MySQL platform has 60 application instances, 12,000 reads per second, 1,500 writes per second, and three replicas that can lag by two seconds. How would you configure ProxySQL read routing without breaking checkout read-after-write consistency?
consistencyproxyreplication
44
A deployment can autoscale from 40 to 300 application instances in two minutes, each requesting 25 connections, but PostgreSQL max_connections is 1,200. How would you design protection against connection storms and runaway statements?
designscalingpostgres
45
You must define PostgreSQL privileges for 35 services across 600 tenant schemas, with separate runtime, migration, reporting, backup, and monitoring access. What role model would you implement?
postgresschemamigrations
46
Two hundred services use PostgreSQL, compliance requires application credentials to rotate within 24 hours and human access to expire after 15 minutes, and pools may keep connections for an hour. How would you implement credential issuance and rotation?
postgres
47
A shared PostgreSQL cluster stores 20,000 tenants in common tables and uses PgBouncer transaction pooling. How would you enforce tenant isolation with row-level security?
transactionspostgres
48
A regulated platform has 150 services and requires verified TLS for all PostgreSQL client and replication traffic, mutual authentication for administrative paths, and certificate rotation every 60 days. What would you configure?
postgresreplicationauth
49
You operate a 40 TB database with seven-year encrypted backup retention, annual master-key rotation, and a requirement to rotate compromised data keys within 24 hours. How would you design encryption and KMS integration?
designbackupsdatabase
50
A PostgreSQL estate executes 25,000 statements per second, must retain privileged and regulated-data access for seven years, and has a 90-day searchable audit target. How would you design audit logging without producing an unusable log stream?
postgresdesignlogging
51
A PostgreSQL 16 physical replica of an 8 TB orders database jumps from 3 seconds and 1.2 GB behind to 180 seconds and 96 GB behind while the primary generates 70 MB/s of WAL at 20,000 QPS, making reporting stale. How do you respond?
databasepostgresreplication
52
A MySQL 8.4 replica serving checkout reads falls from 2 seconds to 2,400 seconds behind after a bulk update changes 400 million rows on a 6 TB primary at 3,500 transactions per second, and `Relay_Log_Space` reaches 180 GB. What do you do?
transactionsreplication
53
An Oracle 19c Data Guard standby for a 25 TB settlement database receives 90 MB/s of redo with transport lag at 2 seconds, but apply lag grows to 18 minutes and standby reports miss a 60-second freshness target. How do you diagnose and recover it?
database
54
A DB2 11.5 HADR standby for a 12 TB banking database at 6,000 transactions per second leaves PEER state, enters REMOTE_CATCHUP, and its log gap rises from 500 MB to 85 GB in 22 minutes while regional RPO is 30 seconds. How do you handle it?
databasetransactionssoft-skills
55
A PostgreSQL 16 logical subscriber feeding billing analytics falls 75 minutes behind while the publisher handles 9,000 writes per second, a replication slot retains 420 GB of WAL, and the publisher volume reaches 88 percent. What is your incident response?
postgresreplicationincidents
56
A PostgreSQL 16 checkout database with `max_connections = 1200` becomes fully unavailable when a deploy creates 40,000 connection attempts per second, CPU reaches 100 percent, and API success drops from 99.95 percent to 0 percent. Walk through your first 20 minutes minute by minute.
databasepostgresapi
57
After a coordinator crash, a PostgreSQL 16 payments database has 37 prepared transactions totaling $12.4 million, the oldest is 46 minutes old, and their locks block 620 checkout sessions. How do you resolve them safely?
databasetransactionspostgres
58
A PostgreSQL 16 primary on AWS gp3 storage drops from 8,000 to 600 transactions per second when write latency rises from 2 ms to 85 ms, `VolumeQueueLength` reaches 140, and checkout p99 exceeds 12 seconds. How do you respond to the storage latency outage?
transactionspostgreslatency
59
A host crash restarts a 6 TB MySQL 8.4 primary that served 3,000 transactions per second; checkout has been down for 14 minutes, the error log shows InnoDB crash recovery at 62 percent, and the last checkpoint is 18 GB of redo behind. What do you do?
transactions
60
An Amazon RDS for PostgreSQL 16 Multi-AZ checkout database reaches 99 percent CPU, 4,700 of 5,000 connections, 5-second commit latency, and 70 seconds of standby replay lag at 18,000 QPS, and an operator proposes an immediate failover. What is your judgment?
databasepostgreslatency
61
At 14:05, one PostgreSQL 16 checkout query rose from 90 ms to 4.8 seconds and pushed endpoint latency from 180 ms to 2.6 seconds p99 at 3,200 QPS. Its plan shape is unchanged, but a Nested Loop now performs 1.9 million inner scans and reads 14 GB instead of serving pages from cache. How do you diagnose and recover this slow-query production incident?
queriespostgrescaching
62
After a PostgreSQL 16 `ANALYZE`, a prepared order-history query for 40,000 tenants changed from an Index Scan at 35 ms to a generic Parallel Seq Scan at 3.4 seconds; one tenant owns 48 percent of 900 million rows and API p99 reached 5 seconds. How do you diagnose the skew-driven plan regression?
indexesqueriespostgres
63
A PostgreSQL 16 hourly settlement query over 620 million rows grew from 70 seconds to 19 minutes, wrote 480 GB of temporary files, saturated a 1.5 GB/s volume, and delayed payments by 24 minutes; Sort reports `external merge Disk: 96 GB` and Hash reports 128 batches. What do you do?
queriespostgresbatch
64
Ten minutes after deployment 2026.07.16, PostgreSQL 16 checkout writes fell from 2,800 to 300 TPS, 640 sessions waited on `Lock:transactionid`, and one migration transaction had blocked an order row for 11 minutes. How do you unwind the chain without corrupting orders?
transactionspostgresmigrations
65
A PostgreSQL 16 inventory service suddenly records 840 deadlocks per minute at 6,500 transactions per second, aborts 14 percent of checkouts, and existing deadlock reports show product IDs 91 then 37 in one path and 37 then 91 in another. How do you contain and eliminate the incident?
transactionspostgreslocking
66
A MySQL 8.4 `ALTER TABLE` on a 1.2 TB orders table waited 7 minutes for a metadata lock, queued 2,300 checkout sessions, reduced throughput from 4,500 to 40 TPS, and caused a 12-minute outage. How do you recover and prevent a repeat?
sessionsthroughputdata-structures
67
On PostgreSQL 16, a connection has been idle in transaction for 31 hours with `backend_xmin` fixed, autovacuum cannot remove 420 million dead tuples from a 2.8 TB events table, storage grew 780 GB, and read p99 rose from 120 ms to 1.9 seconds. How do you recover?
transactionspostgres
68
An Oracle 19c two-node RAC payment cluster at 18,000 TPS saw commit p99 rise from 12 ms to 380 ms, global cache waits consume 62 percent of DB time, and AWR reports `gc current block busy` on one 8 KB index leaf block at 48,000 waits per second. How do you handle the incident?
soft-skillsincidentsindexes
69
After SQL Server 2022 updated statistics and changed PARAMETER_SNIFFING from OFF to ON, one invoice query rose from 22% to 78% host CPU, executions increased from 40 ms to 2.1 seconds, and API p99 reached 3.8 seconds at 900 QPS. How do you diagnose and reverse the regression safely?
sqlqueriesapi
70
On a PostgreSQL 16 hot standby, 27 of 30 nightly reports were canceled with `conflict with recovery` after running 18 minutes, replay lag reached 95 seconds against a 30-second target, and an earlier `hot_standby_feedback` trial added 260 GB of bloat to the 9 TB primary. What do you do now?
postgres
71
A PostgreSQL 16 primary has filled 96% of a 2 TB volume because an inactive logical replication slot retains 310 GB of WAL, and WAL is growing at 42 GB per hour; writes will stop in about two hours. How do you respond?
postgresreplication
72
A PostgreSQL orders tablespace is 98% full on a 4 TB mount, has 82 GB free, and grows by 55 GB per day; failed allocation will stop checkout inserts within 36 hours. What do you do?
postgres
73
After a campaign updated 1.6 billion rows in PostgreSQL 15, a 2.4 TB customer_events table has 900 GB of dead tuples, its main index grew from 480 to 910 GB, and query p99 rose from 180 ms to 2.8 s. How do you recover?
indexesqueriespostgres
74
Eighty PostgreSQL reporting sessions start hash aggregates, temporary files grow at 25 GB per minute, and a 500 GB temp volume reaches 99% in 18 minutes, causing OLTP queries to fail. How do you stabilize the database?
databasepostgresqueries
75
On PostgreSQL 14, the oldest unfrozen transaction age is 2.05 billion and rises by 140 million IDs per day, while a 19-hour transaction prevents vacuum progress; forced shutdown risk is roughly 17 hours away. What do you do?
transactionspostgres
76
A MySQL 8.0 cluster has an InnoDB history list length of 85 million, undo space is 1.3 TB and growing by 65 GB per hour, and one 14-hour transaction holds an old read view while write p99 reaches 4 seconds. How do you respond?
transactions
77
An Oracle 19c production database has an 18 TB FRA at 99%, generates 180 GB of archived redo per hour, Data Guard is 25 minutes behind, and applications are beginning to receive ORA-00257. How do you lead recovery?
database
78
A DB2 11.5 OLTP database has consumed 39 of 40 active 1 GB log files, SQL0964C errors have started, and a three-hour batch transaction has changed 1.4 TB without committing. What do you do?
databasetransactionsbatch
79
PostgreSQL 16 checksums report 14 bad 8 KB pages in a 6 TB payments cluster, read errors affect 0.3% of requests, and the replica may share the same storage fault. How do you respond?
postgresreplication
80
A PostgreSQL application credential with INSERT, UPDATE, DELETE, and limited DDL rights on 40 production tables was exposed in CI logs for 47 minutes; 12 pods still use it and audit logs show 2.3 million reads plus eight failed DDL attempts. How do you contain this without taking down all production?
postgres
81
At 02:10, the latest 18 TB pgBackRest backup fails validation during a production outage, and the service has a four-hour RTO. How do you recover under pressure?
validationbackups
82
At 14:32:18, an operator deletes 26 million PostgreSQL rows, but valid writes continue afterward; how do you recover to the precise pre-delete point without discarding those later writes?
postgres
83
A 30 TB restore is sustaining only 1.1 GB/s, so raw transfer alone will take about 7.6 hours against a four-hour RTO. What do you do during the incident?
incidents
84
During MySQL 8.4 recovery, the physical backup ends at GTID set `3e11fa47-71ca-11ef-9a44-0242ac120002:1-900000`, but transactions `3e11fa47-71ca-11ef-9a44-0242ac120002:900001-905000` are missing before the archived binlog chain resumes. How do you proceed?
transactionsbackups
85
An 80 TB Oracle database must be recovered within six hours, but RMAN reports two unavailable level 1 pieces 25 minutes into the incident. How do you lead the recovery?
incidentsdatabase
86
Oracle 19c Data Guard Fast-Start Failover switches the primary twice in 11 minutes during intermittent 8-second WAN losses, services remain unavailable for 7 minutes, and the redo transport gap reaches 18 GB. How do you stop the failover loop and restore stable authority?
87
Failover promoted a PostgreSQL candidate that was 96 MB behind, and clients wrote to it for eight minutes before 1,240 missing commits were discovered. What do you do?
postgres
88
Regional database promotion completes in 90 seconds, but after 12 minutes applications still fail because DNS is cached, a regional secret is stale, and connection pools pin the old endpoint. How do you restore service?
databasepoolingcaching
89
Ransomware is detected at 03:00, forensic evidence suggests access began nine days earlier, and the last 14 daily database backups may be tainted. How do you recover?
databasebackups
90
A quarterly restore drill finds that the newest 22 TB backup has corrupt blocks and the preceding incremental chain is missing one segment, leaving no proven recovery point within the 15-minute RPO. What do you do?
backups
91
You must upgrade a 14 TB PostgreSQL 15 orders cluster handling 9,000 writes per second to PostgreSQL 17 with less than 90 seconds of write downtime and a 30-minute rollback window; how would you execute the cutover?
postgresrollback
92
During a rolling MySQL InnoDB Cluster upgrade from 8.0.36 to 8.4.2, the first upgraded secondary stops applying at GTID 7f000000-0000-0000-0000-000000000000:184223 while the 6 TB checkout database is processing 4,500 transactions per second; what do you do before the primary cutover?
databasetransactionsconcurrency
93
Move a 20 TB Oracle 19c settlement database from AIX Power hardware to Linux x86 with a 15-minute write outage, 18,000 transactions per second, and no tolerance for an unvalidated platform cutover; what is your plan?
databasetransactions
94
You must apply a DB2 11.5.9 fix pack to a two-site HADR database of 11 TB and 6,500 transactions per second with less than 3 minutes of write interruption; how would you perform and control the rolling upgrade?
databasetransactions
95
Migrate a 9 TB MySQL 8.0 marketplace database producing 35,000 row changes per second to PostgreSQL 17 with at most 2 minutes of write downtime and a 45-minute rollback window; how would you control CDC and cutover?
databasepostgresrollback
96
A gh-ost change on a 1.2 TB MySQL 8.0 orders table reaches cutover at 02:14, but a 22-minute reporting transaction holds a metadata lock, checkout p99 rises from 80 ms to 1.8 seconds, and 600 sessions queue; how do you recover safely?
transactionssessionsdata-structures
97
A junior DBA runs DROP INDEX CONCURRENTLY on PostgreSQL 16 without checking workload usage, causing invoice-query p99 to rise from 140 ms to 9 seconds for 24 minutes and 3,200 API timeouts; how would you mentor them after service is restored?
indexesqueriespostgres
98
At 03:20 a DBA sees a three-node MySQL 8.4 InnoDB Cluster with Router reporting no writer, one member UNREACHABLE, two members ONLINE, and an application backlog growing by 8,000 requests per minute; how do you coach them through the ambiguous failover without taking the keyboard?
backlog
99
Six junior DBAs supporting 80 PostgreSQL 16 clusters generated 180 pages last week and escalated 166 of them to seniors within 3 minutes, although 118 cleared without intervention; how would you redesign on-call over the next 30 days?
escalationpostgreson-call
100
At 14:02 a PostgreSQL 16 payment cluster processing 7,000 transactions per second loses its primary storage path, Patroni shows no leader, retries drive CPU to 96%, and storage, network, and application teams propose restart, forced promotion, and rollback at the same time; how would you lead the first 20 minutes?
transactionspostgresconcurrency

Questions

1Design a PostgreSQL topology for a 12 TB orders database receiving 6,000 writes and 35,000 reads per second, growing 400 GB monthly, where checkout requires read-after-write consistency but reporting may be 30 seconds stale.

2A PostgreSQL payment ledger writes 2,500 transactions per second across two availability zones with 2 ms round-trip latency, while a third region is 75 ms away; choose a synchronous and asynchronous replication policy with an RPO of zero inside the region and a remote lag target of 10 seconds.

3Configure Patroni for a three-node PostgreSQL 16 cluster serving 4,000 writes per second across three availability zones, with automatic failover only when the candidate is less than 64 MB behind and no split-brain writes are acceptable.

4A PostgreSQL primary generates 180 MB/s of WAL and already feeds four local replicas; you must add six reporting replicas in a region 90 ms away while keeping reporting data within 45 seconds of the primary. How would you design cascading replication?

5Choose a hash-sharding design for a 60 TB PostgreSQL event store receiving 120,000 inserts per second from 40 million devices, where the common query fetches one device's last 24 hours and capacity should double without moving all data.

6Design range sharding for a 25 TB multi-tenant billing database growing 1 TB monthly, with 18,000 writes per second, tenant-local transactions, and five tenants that each hold more than 8% of the data.

7Design a Vitess topology for a 90 TB MySQL 8 commerce catalog handling 70,000 reads and 15,000 writes per second, with 80% of requests scoped to merchant ID and a requirement to add shards online.

8Place 30 TB of CockroachDB data for customers in Frankfurt and Singapore at 8,000 writes and 45,000 reads per second, where EU customer data must remain in the EU, local reads should stay below 20 ms p99, and each customer's transactions require serializable consistency.

9Move 14 TB and 20,000 writes per second from eight overloaded shards to sixteen shards without downtime, while allowing at most 60 seconds of rollback exposure and requiring row-count and checksum validation.

10An order placement flow spans inventory and payment data on different shards at 3,000 orders per second; overselling is forbidden, payment confirmation may take 30 seconds, and holding distributed locks during external calls is unacceptable. How would you handle the cross-shard transaction?

11A PostgreSQL audit table holds 18 TB, receives 12,000 inserts per second, grows by 1.4 TB per month, and retains 13 months; how would you design its time partitioning?

12A 6 TB PostgreSQL SaaS database serves 8,000 QPS for 40,000 tenants, grows 500 GB per quarter, and retains seven years; how would you use list partitioning without weakening tenant isolation?

13A PostgreSQL ingestion table is 4 TB, handles 30,000 writes per second, grows 220 GB per week, and keeps 26 weeks; would hash partitioning solve both write distribution and retention?

14A 9 TB PostgreSQL metrics table has 36 monthly partitions, serves 18,000 QPS, grows 300 GB per month, and retains three years, but seven-day queries scan every partition; how do you restore pruning?

15A 24 TB PostgreSQL history table serves 3,500 QPS, adds 4 TB per year, and retains five years; should it use daily, monthly, or yearly partitions?

16A 7 TB PostgreSQL orders table runs at 14,000 QPS, grows 180 GB per month, retains six years, and is range-partitioned by created_at; how would you preserve order-number uniqueness and index efficiency?

17A 12 TB PostgreSQL commerce database handles 22,000 OLTP QPS, grows 700 GB per quarter, retains four years, and a six-hour report saturates its read replica; how would you isolate reporting?

18A 10 TB PostgreSQL primary sustains 16,000 QPS, generates 18 MB of WAL per second at peak, grows 250 GB per month, and retains five years; how would you size WAL, checkpoints, and storage headroom?

19A managed PostgreSQL service stores 32 TB, peaks at 45,000 QPS, grows 55 percent yearly, and must retain seven years; build a three-year capacity plan before its current 64 TB limit is reached.

20An Oracle order platform holds 30 TB, serves 25,000 QPS, grows 3 TB per year, and retains ten years; would you spend on a second RAC node or build Data Guard in another region?

Locked questions