PostgreSQL - storing data that never changes

user_270663 0
Viewed 203

Is there a way in PostgreSQL to mark a table as storing data that will never change after insertion, thus improving performance? One example where this could help is Index-Only Scans and Covering Indexes:

However, for seldom-changing data there is a way around this problem. PostgreSQL tracks, for each page in a table's heap, whether all rows stored in that page are old enough to be visible to all current and future transactions. This information is stored in a bit in the table's visibility map. An index-only scan, after finding a candidate index entry, checks the visibility map bit for the corresponding heap page. If it's set, the row is known visible and so the data can be returned with no further work. If it's not set, the heap entry must be visited to find out whether it's visible, so no performance advantage is gained over a standard index scan

If PostgreSQL knew that the data in a table never changes the heap entries would never have to be visited in index-only scans.

postgresql sql
2 Answers

This is a little hard to follow in the documentation, but basically you need to vacuum the table after it has been created. Then if there are no changes (and no locks), no worries.

The documentation does explain that vacuuming updates the visibility map:

PostgreSQL's VACUUM command has to process each table on a regular basis for several reasons:

. . .

  1. To update the visibility map, which speeds up index-only scans.
user_11440350

If you have data that truly never change, run VACUUM (FREEZE) on the table. If there is no concurrent long-running transaction, that will mark all blocks in the visibility map as “all-frozen” and “all-visible”. Then you will get index-only scans and anti-wraparound autovacuum won't have to do any work on the table.

user_64643080
Related