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.
In PostgreSQL MVCC, what does transaction ID (XID) wraparound cause if autovacuum fails to run?
- A. Old committed rows can appear to be in the future and become invisible, risking data loss ✓
- B. Indexes silently rebuild themselves
- C. Autovacuum permanently disables all writes
- D. Connection memory usage doubles
Correct answer: A. Unfrozen XIDs wrapping past the current counter make old rows look future-dated and unreadable, a catastrophic data-loss risk.
What happens during 'lock escalation' in SQL Server?
- A. Row locks are demoted to weaker shared locks
- B. Two transactions swap their lock queues
- C. Many fine-grained row/page locks are converted to a single coarser table lock ✓
- D. Locks are replicated to a standby server
Correct answer: C. SQL Server escalates numerous granular locks into one table lock to reduce lock-manager memory overhead.
Why can a query using LIKE '%abc' (leading wildcard) not use a B-tree index efficiently?
- A. The optimizer always ignores every index for LIKE
- B. A leading '%' prevents an ordered prefix seek, forcing a scan ✓
- C. LIKE can never use an index under any condition
- D. The wildcard corrupts the index structure
Correct answer: B. B-tree ordering is by prefix, so an unknown leading portion prevents a seek and forces a full scan.
From a DBA's perspective, what is the primary purpose of a database connection pool?
- A. It encrypts every SQL statement in transit
- B. It automatically shards the database
- C. It guarantees serializable isolation
- D. It reuses established connections to avoid connect/auth overhead and cap concurrency ✓
Correct answer: D. Pooling recycles authenticated connections, cutting handshake cost and bounding server load.
In MySQL replication, what does GTID (Global Transaction Identifier) primarily improve?
- A. It simplifies failover and consistency by uniquely identifying each transaction across servers ✓
- B. It compresses the binary logs on disk
- C. It replaces the need for a primary key
- D. It encrypts replication traffic by default
Correct answer: A. GTIDs track transactions globally so replicas and failover no longer depend on binlog file/position coordinates.
Which statement about the CAP theorem holds during a network partition in a distributed database?
- A. You can fully keep both consistency and availability
- B. Partitions never affect a distributed database
- C. During a partition you must choose between consistency and availability ✓
- D. CAP applies only to single-node databases
Correct answer: C. When a partition occurs, a system can preserve either consistency or availability, not both.
A query suddenly slows after a large data load, yet its plan looks unchanged. What is the most likely DBA cause?
- A. The table's primary key was silently dropped
- B. Stale statistics led the optimizer to a poor plan from bad cardinality estimates ✓
- C. The database switched isolation levels on its own
- D. The network MTU changed
Correct answer: B. Outdated statistics after a big load cause cardinality misestimation and suboptimal plan choices.
In PostgreSQL, which index type is best suited for full-text search and JSONB containment queries?
- A. GIN ✓
- B. B-tree
- C. Hash
- D. BRIN
Correct answer: A. GIN (Generalized Inverted Index) is designed for composite values like tsvectors and JSONB keys.
'Write amplification' and periodic compaction are characteristic performance concerns of which storage design?
- A. Traditional heap tables
- B. Classic B-tree page storage
- C. ISAM flat files
- D. LSM-tree engines like RocksDB or Cassandra ✓
Correct answer: D. Log-structured merge trees rewrite data during compaction, producing write amplification.
To add a NOT NULL column with a constant default to a huge table in PostgreSQL 11+ with minimal locking, you rely on what?
- A. A mandatory full table rewrite under an exclusive lock
- B. Disabling autovacuum during the change
- C. The optimization that stores the constant default in catalog metadata, avoiding a full rewrite ✓
- D. Converting the table to an unlogged table first
Correct answer: C. Since v11 a constant default is recorded in catalog metadata, so existing rows aren't physically rewritten.
In a database using MVCC (e.g., PostgreSQL), how are readers and writers coordinated?
- A. Readers see a snapshot and don't block writers, who create new row versions ✓
- B. Readers always acquire exclusive locks that block writers
- C. Writers overwrite rows in place and block all readers
- D. All access is serialized through a single global lock
Correct answer: A. MVCC gives readers a consistent snapshot while writers create new tuple versions, so reads and writes don't block each other.
Which index structure is optimized purely for equality lookups rather than range scans?
- A. Hash index ✓
- B. B-tree index
- C. GiST index
- D. Clustered index
Correct answer: A. Hash indexes support fast equality comparisons but cannot serve ordered range queries.
In query optimization, what does a cardinality estimate refer to?
- A. The predicted number of rows a plan step will produce ✓
- B. The number of CPU cores assigned to the query
- C. The count of indexes available on the table
- D. The total disk size of the table in bytes
Correct answer: A. Cardinality estimation predicts the row count at each plan step, which drives the optimizer's plan choice.
Per the ANSI SQL standard, which isolation level prevents phantom reads?
- A. SERIALIZABLE ✓
- B. READ COMMITTED
- C. READ UNCOMMITTED
- D. REPEATABLE READ
Correct answer: A. In the ANSI standard, only SERIALIZABLE prevents phantom reads; REPEATABLE READ still permits them.
How does horizontal sharding differ from table partitioning?
- A. Sharding distributes data across separate database servers/nodes ✓
- B. Sharding splits data only within a single server's storage
- C. Sharding never changes where data physically lives
- D. Sharding only applies to in-memory temporary tables
Correct answer: A. Sharding spreads data across independent nodes, whereas partitioning typically divides data within one server.
In a two-phase commit protocol, what does the prepare phase ensure?
- A. All participants confirm they can commit before any commits ✓
- B. The coordinator commits immediately without asking participants
- C. Each node commits independently and reconciles later
- D. Only the coordinator's local changes are durable
Correct answer: A. The prepare (voting) phase has every participant durably promise it can commit before the commit phase proceeds.
In PostgreSQL, why is VACUUM primarily needed?
- A. To reclaim space from dead tuples left by MVCC updates/deletes ✓
- B. To rebuild every index from scratch on each run
- C. To encrypt tables storing sensitive data
- D. To flush the write-ahead log to a backup server
Correct answer: A. MVCC leaves dead tuples behind; VACUUM reclaims that space and prevents transaction-ID wraparound.
How does a covering index improve a query?
- A. It contains all columns the query needs, avoiding table lookups ✓
- B. It forces a full table scan for accuracy
- C. It stores data in a compressed archive format
- D. It replaces the table's primary key constraint
Correct answer: A. A covering index includes every column the query references, so the query is answered index-only without hitting the table.
Under the CAP theorem, during a network partition a distributed database must choose between which two properties?
- A. Consistency and availability ✓
- B. Concurrency and atomicity
- C. Durability and isolation
- D. Latency and throughput
Correct answer: A. CAP states that under a partition a system must trade off between consistency and availability.
How is point-in-time recovery (PITR) achieved?
- A. Restore a base backup and replay transaction logs to a target time ✓
- B. Keep only the single latest full backup
- C. Replicate the database to a second region in real time
- D. Export all tables to CSV every hour
Correct answer: A. PITR restores a base backup and then replays archived transaction/WAL logs up to the desired moment.