CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is performing routine maintenance on a PostgreSQL database. They notice that the physical size of several tables on disk is significantly larger than the actual data contained within them, even after deleting a large number of rows. This phenomenon is impacting storage efficiency and query performance. What is the MOST appropriate term for this condition?
- ATable bloat.
- BIndex fragmentation.
- CTable partitioning.
- DData corruption.
Show answer & explanationAnswer & explanation
Correct answer: A. Table bloat.
Table bloat refers to the accumulation of 'dead tuples' (old versions of rows) in PostgreSQL tables and indexes after UPDATE or DELETE operations. These dead tuples consume disk space and can degrade performance if not periodically cleaned up by VACUUM operations.
Why the other options are wrong
- B. Index fragmentation refers to the logical ordering of index pages not matching their physical order on disk, which can degrade performance but is distinct from excessive space usage by dead rows in the table itself.
- C. Table partitioning is a strategy to divide large tables into smaller, more manageable pieces, which can improve performance and maintenance but is not a problem condition like bloat.
- D. Data corruption implies that data has been damaged or is unreadable, which is a much more severe issue than simply inefficient space usage due to bloat.
Table Bloat (PostgreSQL)
The condition in PostgreSQL where tables and indexes consume more physical disk space than necessary due to the accumulation of 'dead tuples' from UPDATE/DELETE operations.
- Caused by PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture.
- Requires VACUUM (or autovacuum) to reclaim space and update statistics.
- Can lead to increased I/O, larger backups, and slower query performance.
Memory trick: MVCC means dead rows bloat, VACUUM cleans.