Database Administration Concepts Flashcards

An index on a 'gender' column with only three distinct values across 10 million rows is rarely used by the optimizer. What is the most likely reason?

Answer: The column has low cardinality (poor selectivity)

Low-selectivity indexes match a large share of rows, so a full scan is often cheaper.

Which type of replication can cause data loss on failover because the primary commits before the replica confirms the change?

Answer: Asynchronous replication

Asynchronous replication does not wait for the replica to acknowledge, so recent commits can be lost if the primary fails.

A DBA splits a large sales table so that each year's rows are stored separately but can still be queried as one table. What is this technique called?

Answer: Horizontal partitioning by range

Range partitioning divides rows by a key such as date, which allows partition pruning and easier archiving.

In a database that uses MVCC, such as PostgreSQL, why is routine VACUUM maintenance needed?

Answer: To reclaim space taken by dead row versions and prevent transaction ID wraparound

MVCC leaves obsolete tuple versions behind, and VACUUM clears them out and freezes old transaction IDs.

Queries suddenly slow down after a large bulk load, even though the indexes are unchanged. What should a DBA check first?

Answer: Whether optimizer statistics are out of date

Stale statistics give the optimizer wrong row estimates, which leads to poor execution plans after big data changes.

Which RAID level gives both striping and mirroring, and is often recommended for write-heavy database transaction logs?

Answer: RAID 10

RAID 10 combines mirroring for redundancy with striping for speed, and avoids the parity write penalty of RAID 5 and 6.

What is the main advantage of taking a hot (online) backup instead of a cold backup?

Answer: The database stays available to users while the backup runs

Hot backups run while the database is open and in use, which avoids downtime.