Database Indexing hard
Heap-Only Tuple (HOT) updates in PostgreSQL optimize write performance by:
- A Writing update payloads directly to the Write-Ahead Log (WAL) while skipping the Heap entirely to ensure absolute write-throughput.
- B Utilizing an in-memory buffer pool to merge multiple contiguous updates into a single physical disk write during off-peak hours.
- C Silently disabling all Foreign Key and Check Constraints during high-frequency update bursts to minimize the internal processing overhead.
- D Creating a new version of the row within the same physical disk page and using a local pointer chain to bypass the need for standard index updates.
Answer: D. Creating a new version of the row within the same physical disk page and using a local pointer chain to bypass the need for standard index updates.
Normally, in Postgres, updating a row changes its physical address (CTID), which forces every index on the table to be updated with the new pointer. This is very expensive. HEAP-Only Tuple (HOT) updates occur if the update doesn't change any indexed columns and there is enough room in the current disk page. Postgres creates a new version of the row in the same page and leaves a 'link' from the old row. The index still points to the old address, but the storage engine follows the link to the new version, saving all that index maintenance overhead.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?