IKM Database Administration Concepts 5 — Questions and Answers
Question 1: 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?
- The column has low cardinality (poor selectivity) (Correct answer)
- The optimizer never uses non-clustered indexes
- Indexes cannot be created on character columns
- The index is too small to be stored
Correct answer: The column has low cardinality (poor selectivity)
Low-selectivity indexes match a large share of rows, so a full scan is often cheaper.
Question 2: Which type of replication can cause data loss on failover because the primary commits before the replica confirms the change?
- Quorum-based synchronous replication
- Asynchronous replication (Correct answer)
- Two-phase commit replication
- Synchronous replication
Correct answer: Asynchronous replication
Asynchronous replication does not wait for the replica to acknowledge, so recent commits can be lost if the primary fails.
Question 3: 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?
- Materialized view refresh
- Normalization
- Vertical denormalization
- Horizontal partitioning by range (Correct answer)
Correct answer: Horizontal partitioning by range
Range partitioning divides rows by a key such as date, which allows partition pruning and easier archiving.
Question 4: In a database that uses MVCC, such as PostgreSQL, why is routine VACUUM maintenance needed?
- To remove unused user accounts
- To reclaim space taken by dead row versions and prevent transaction ID wraparound (Correct answer)
- To defragment the operating system file system
- To rebuild every index from scratch
Correct 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.
Question 5: Queries suddenly slow down after a large bulk load, even though the indexes are unchanged. What should a DBA check first?
- Whether optimizer statistics are out of date (Correct answer)
- Whether the backup schedule has changed
- Whether the database name was changed
- Whether the server time zone is correct
Correct 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.
Question 6: Which RAID level gives both striping and mirroring, and is often recommended for write-heavy database transaction logs?
- RAID 10 (Correct answer)
- RAID 6
- RAID 5
- RAID 0
Correct answer: RAID 10
RAID 10 combines mirroring for redundancy with striping for speed, and avoids the parity write penalty of RAID 5 and 6.
Question 7: What is the main advantage of taking a hot (online) backup instead of a cold backup?
- It does not require any storage space
- It is always smaller than a cold backup
- The database stays available to users while the backup runs (Correct answer)
- It never needs transaction logs to be consistent
Correct 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.
