A PostgreSQL query that ran in 20ms yesterday now takes 8 seconds. EXPLAIN ANALYZE shows the planner chose a nested-loop join with an inner sequential scan, and the plan node reports `rows=1` estimated vs `rows=480000` actual on the driving table after a large bulk load. Which action most directly addresses the root cause?
- A. Add a covering index on the join column so the nested loop can seek instead of scan
- B. Run ANALYZE on the affected table so the planner has fresh cardinality statistics ✓
- C. Increase work_mem so the hash join spills less to disk
- D. Set enable_nestloop = off for the session to force a hash join
Correct answer: B. A 1-vs-480000 estimate error after a bulk load is a stale-statistics symptom, so running ANALYZE restores accurate cardinality and lets the optimizer pick the correct join method.
On SQL Server you observe a stored procedure that is fast for most inputs but occasionally times out after a plan recompiles. The cached plan was compiled for a highly selective parameter value but is being reused for a value that matches millions of rows, causing an index seek + key lookup instead of a scan. Which technique specifically neutralizes this parameter-sniffing regression without disabling reuse entirely?
- A. Add OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN to the statement ✓
- B. Update statistics with FULLSCAN on the underlying table
- C. Rebuild the nonclustered index to remove fragmentation
- D. Wrap the query in a transaction at SERIALIZABLE isolation
Correct answer: A. OPTION (RECOMPILE) recompiles per execution using the actual parameter, and OPTIMIZE FOR UNKNOWN uses average density, both directly countering a skewed sniffed plan.
Two application transactions repeatedly deadlock in production. Session A updates rows in table `orders` then `inventory`; session B updates `inventory` then `orders`. Retrying reduces symptoms but the deadlocks recur under load. What is the most robust permanent fix?
- A. Lower the isolation level of both transactions to READ COMMITTED
- B. Enforce a consistent lock-acquisition order (always orders then inventory) in both code paths ✓
- C. Add NOLOCK hints to the SELECT statements inside both transactions
- D. Increase the deadlock_timeout so the detector waits longer before aborting
Correct answer: B. Deadlocks from a lock cycle are eliminated by imposing a global ordering on resource acquisition so a circular wait can never form; retry logic only masks the recurrence.
In PostgreSQL, `pg_stat_activity` shows an idle-in-transaction session open for 6 hours. Autovacuum is running but dead tuples on a hot table keep climbing and the table is bloating. Why can't autovacuum reclaim the space, and what is the correct immediate action?
- A. Autovacuum is blocked by a lock; run VACUUM FULL to force reclaiming
- B. The long transaction holds back the xmin horizon so dead tuples remain visible; terminate that transaction ✓
- C. Autovacuum cost limits are too low; raise autovacuum_vacuum_cost_limit to catch up
- D. The table needs a REINDEX because index bloat prevents tuple removal
Correct answer: B. An old open transaction pins the global xmin horizon, so VACUUM cannot remove tuples still potentially visible to it; ending that transaction lets cleanup proceed.
You must choose an isolation level for a financial ledger that debits one account and credits another, where the invariant is that total balance across a set of accounts never goes negative even under concurrent transfers. Under PostgreSQL, which level prevents the write-skew anomaly that lets two concurrent transactions each pass the check and jointly violate the invariant?
- A. READ COMMITTED with SELECT ... FOR UPDATE on both rows
- B. REPEATABLE READ (snapshot isolation)
- C. SERIALIZABLE ✓
- D. READ COMMITTED with an advisory lock on the account pair
Correct answer: C. Write skew is a snapshot-isolation anomaly that REPEATABLE READ permits; only SERIALIZABLE (SSI) detects the dangerous read/write dependency and aborts one transaction, though explicit row locking can also work it is not the isolation-level answer asked for.
After an automated standby promotion, application reads begin failing with errors like 'cannot execute UPDATE in a read-only transaction' intermittently, while other reads succeed. The connection string points to a DNS name fronting both nodes. What is the most likely root cause?
- A. Replication lag on the new primary is serving stale reads
- B. The old primary was not fully demoted and connections are load-balanced across a still-read-only node ✓
- C. The new primary's statistics are stale after promotion
- D. Synchronous commit is waiting on a standby that no longer exists
Correct answer: B. The 'read-only transaction' error means some connections still land on a node in recovery/standby mode, indicating split routing where the old node wasn't demoted or removed from the pool.
An overnight alert fires: the primary's data volume hit 100% and writes are failing. You have a synchronous standby that is healthy. Ordering the response, which sequence best balances availability and data safety?
- A. Immediately run VACUUM FULL on the largest table to reclaim space on the primary
- B. Delete old WAL segments manually with rm to free space, then continue
- C. Fail over to the healthy standby to restore writes, then root-cause the primary's disk (WAL accumulation, unrotated logs, bloat) offline ✓
- D. Extend the volume online and take no further action since space is restored
Correct answer: C. Failing over restores service safely without risky in-place operations on a full disk, and manually deleting WAL can corrupt recovery/replication; root-cause and retention fixes follow.
A gp3 EBS volume backs an RDS PostgreSQL instance. Monitoring shows disk queue depth climbing and read latency spiking during a nightly batch, yet CPU is at 30% and the buffer cache hit ratio is 99%. You provisioned 12000 IOPS but throughput sits pinned at 125 MiB/s. What is the actual bottleneck?
- A. The instance is CPU-starved and needs a larger instance class
- B. The IOPS ceiling is being hit and more provisioned IOPS is required
- C. gp3's default throughput (125 MiB/s) is saturated; provision higher throughput separately from IOPS ✓
- D. The buffer cache is too small, forcing physical reads
Correct answer: C. gp3 decouples IOPS from throughput, and the default 125 MiB/s cap is a common hidden ceiling for large sequential batch I/O even when IOPS headroom remains.
You are designing HA/DR for a system with RPO = 0 and RTO = 60 seconds. Which architecture satisfies both constraints?
- A. Asynchronous cross-region replica with automated failover
- B. Synchronous replication to a standby in another AZ with automatic failover and split-brain fencing ✓
- C. Nightly full backups plus WAL archiving to object storage for PITR
- D. Semi-synchronous replication to a cross-region replica
Correct answer: B. RPO=0 mandates synchronous commit (no acknowledged-but-lost writes), and a same-region cross-AZ synchronous standby with fast fenced failover meets the 60-second RTO, whereas async/PITR permit data loss.
In an Oracle RAC cluster, AWR shows high 'gc buffer busy acquire' and 'gc cr block busy' waits concentrated on one hot index's right-most leaf block during heavy concurrent inserts of sequential keys. Which change most directly reduces the interconnect contention?
- A. Increase the interconnect MTU (jumbo frames)
- B. Convert the index to a reverse-key or hash-partitioned global index to spread inserts across blocks ✓
- C. Raise the buffer cache size on all instances
- D. Increase the ASM rebalance power limit
Correct answer: B. Sequential-key inserts create a right-hand-index hot block pinging across the interconnect; a reverse-key or hash-partitioned index distributes inserts across many blocks, eliminating the single hot leaf.