Online enabling checksums in PostgreSQL 19
Enabling data checksums is strongly recommended to detect corruption originating in the storage or I/O layer, which can silently lead to incorrect query results. Although PostgreSQL performs basic sanity checks on the page header without checksums, it does not cryptographically verify page contents. As a result, many types of silent corruption could go unnoticed and produce inaccurate query…
Data corruption originating from storage or I/O layers can lead to inaccurate query results, as PostgreSQL does not cryptographically verify page contents without checksums. PostgreSQL 18 automatically includes a checksum for data pages across database clusters, which is verified each time a page is read and recalculated when written.
However, databases upgraded from earlier versions may lack these checksums. Adding checksums requires shutting down the database and can be time-consuming using the pg_checksums command. PostgreSQL 19 will enable checksums online, allowing them to operate in the background while the application remains active, with potential throttling to reduce workload impact.
The example demonstrates how corruption can go unnoticed if checksums are not enabled. To test this, a demonstration database was initialized without checksums, a table with one row containing "Hello World!" was created, and the data was modified to simulate storage corruption. When PostgreSQL read the file again, it displayed the corrupted data.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
