Indexing

2 articles

Database 15 Sep 2026 5 min read

PostgreSQL Visibility Map Tracks Heap Page State

PostgreSQL keeps tuple visibility metadata in heap rows, but checking every heap tuple is unnecessary when an entire page is already known to satisfy stronger conditions. A visibility map stores that page-level state in a compact relation fork. Each heap page has two corresponding bits. The all-visible bit records that every tuple on the page is visible to every current and future transaction. The all-frozen bit records that every tuple on the page is frozen. Those facts let PostgreSQL avoid work in index-only scans and vacuum operations without moving MVCC visibility data into indexes.

PostgreSQL Database

Database 04 Sep 2026 10 min read

Use Partial Indexes to Index the Rows You Actually Query

A normal database index contains an entry for every table row that qualifies for the indexed columns. That is often appropriate, but some applications repeatedly query only a small, stable subset of a table. Consider a task table where most tasks eventually become completed or archived, while the application dashboard mainly reads current open tasks. A full index on project_id keeps index entries for historical rows even though those rows are rarely part of the hot query path.

Database SQL