I corrupted the same 16 bytes in the same PostgreSQL table twice. With data checksums on, a query that touched the damaged page threw an error naming the exact block. With them off, the same query returned a normal-looking result like nothing had happened. That gap is the entire reason checksums exist, and until PostgreSQL 19 turning them on for an existing database meant shutting it down first.
PostgreSQL 19 is currently at beta 3, due for general release around September or October 2026. It adds pg_enable_data_checksums() and pg_disable_data_checksums(), which do the same job as the standalone pg_checksums tool but while the cluster keeps serving traffic. I ran both the old and the new path against the same dataset to see what the trade actually costs.
What changed, concretely
Before 19, changing a cluster's checksum state meant running pg_checksums --enable or --disable with the server stopped, since the tool rewrites every page directly on disk. PostgreSQL 19 replaces that with two SQL functions that spawn a background worker, walk every table in every database, and flip pages over while normal queries keep running against them.
The same release also changes what a fresh cluster gets by default. I ran a bare initdb with no flags on the postgres:19beta3 image:
$ initdb -D /tmp/testcluster
...
Data page checksums are enabled.
The initdb reference page confirms this isn't an accident of the Docker image: "-k / --data-checksums... This is enabled by default; use --no-data-checksums to disable checksums." Every PostgreSQL cluster before 19 needed someone to opt in at creation time or live with an offline conversion later. From 19 onward you get checksums whether you asked or not, and retrofitting an older cluster no longer means an outage.
The old way, measured
I loaded a pgbench -i -s 50 dataset (756MB, 98,695 pages) into a checksum-less 19 beta cluster, then ran the full offline cycle — stop, pg_checksums --enable, start, wait for pg_isready — three times, alternating with the disable direction:
| Direction | Stop | Convert | Start-to-ready | Total (fastest of 3) |
|---|---|---|---|---|
| enable | 0.25–0.28s | 0.72–0.76s | 0.30–0.32s | 1.28s |
| disable | 0.21–0.27s | 0.46–0.48s | 0.30–0.35s | 0.99s |
That's the actual outage window: no connections accepted, nothing served, for over a second on a database under 1GB. pg_checksums scans and rewrites the whole data directory whichever direction you're going, which is why disable isn't free here either, just cheaper than enable.
The new way, measured
On an identically sized cluster (775MB) with checksums off, I called pg_enable_data_checksums() with default (unthrottled) settings three times, resetting with a disable between each run:
trial 1 ENABLE (off->on): 2.852s
trial 1 DISABLE (on->off): 1.158s
trial 2 ENABLE (off->on): 2.804s
trial 2 DISABLE (on->off): 1.111s
trial 3 ENABLE (off->on): 2.802s
trial 3 DISABLE (on->off): 1.076s
The function call itself returns almost immediately — I measured the round trip separately at 0.124s, 0.130s and 0.136s across three calls — and SHOW data_checksums reports inprogress-on while a background worker does the real work. So the documentation's "completes immediately" claim is accurate for the call; the actual conversion still takes a few seconds of wall time on a small database, longer than the offline tool needed for the same data. You're not saving wall-clock time here. You're trading a guaranteed short outage for a slightly longer window where the database stays fully available.
I watched the conversion with the new progress view while it ran:
=> select phase, relations_done, relations_total, blocks_done, blocks_total
from pg_stat_progress_data_checksums;
phase | relations_done | relations_total | blocks_done | blocks_total
-----------+----------------+------------------+--------------+--------------
enabling | 0 | 297 | 1480 | 82525
and SHOW data_checksums cycling through four states: off, inprogress-on, on, and — going the other way — inprogress-off.
Writes during the conversion
I ran pgbench -c 8 -j 4 for 20 seconds while the unthrottled conversion happened in the background, and again with the conversion throttled to cost_delay=20, cost_limit=100:
| Scenario | tps | avg latency |
|---|---|---|
| Baseline, no conversion running | 3033.6 | 2.637ms |
| During unthrottled enable | 3345.9 | 2.391ms |
| During throttled enable | 3104.8 | 2.577ms |
Neither run showed a slowdown. If anything the numbers went up slightly, which I'd chalk up to warm caches from earlier runs rather than a real speedup — I don't have grounds to claim the conversion made writes faster. What I can say is that on this machine, with this dataset, concurrent write throughput did not measurably suffer either way. That's a genuinely different result from the documentation's own warning that "overall system performance will be affected," and I want to flag the limits of it: this was a lightly loaded container on local overlay storage with plenty of spare I/O, not a production instance fighting for disk bandwidth. I'd expect the effect to show up on busier or slower storage; I just didn't reproduce it here.
The throttling parameters are real, though. At cost_delay=20, after 46 seconds the conversion had only processed 10,516 of 82,525 pages — about 13%. Extrapolating that rate, the same dataset would take roughly six minutes to finish throttled, against 2.8 seconds unthrottled. That's the actual lever: if you're worried about I/O contention, cost_delay and cost_limit will stretch the job out by two orders of magnitude in exchange for near-zero impact on foreground queries, which matches the vacuum cost-delay model it's borrowed from.
What it costs on disk
I measured WAL generated by each direction using pg_current_wal_lsn() before and after:
| Direction | WAL generated |
|---|---|
| Enable (776MB database) | 799MB |
| Disable | 512 bytes |
Enabling checksums writes almost a full extra copy of the database into WAL, because every page has to be marked dirty and WAL-logged so standbys pick up the same change. Disabling just flips a flag and stops validating; it doesn't need to touch page contents on the way out, which is also why it finishes faster than enabling even though neither one is instant online.
What it refuses, and what it doesn't
Both functions require superuser:
tester=> SELECT pg_enable_data_checksums();
ERROR: permission denied for function pg_enable_data_checksums
tester=> SELECT pg_disable_data_checksums();
ERROR: permission denied for function pg_disable_data_checksums
Calling pg_enable_data_checksums() again while one is already running is a silent no-op — no error, no second worker. I expected pg_disable_data_checksums() to be refused under the same condition, or at least queued. It isn't. Calling disable while an enable is roughly 13% complete immediately reverses direction: SHOW data_checksums went from inprogress-on straight to inprogress-off and reached off in about a quarter of a second, abandoning the partially-converted pages without complaint. None of this is in the function reference page I checked, which only documents the happy path in each direction separately.
Where checksums actually catch something
Back to the opening test, done properly. I took a 756MB pgbench_accounts table, checkpointed it, and overwrote 16 bytes at the same file offset in two otherwise identical clusters, one with checksums on and one off. First attempt, I queried with SELECT count(*), and it came back clean on both clusters — because PostgreSQL satisfied it with an index-only scan on the primary key and never touched the damaged heap page at all. Forcing an actual heap read by summing a column that isn't in the index gave the real comparison:
# checksums on
=> SELECT sum(abalance) FROM pgbench_accounts;
ERROR: invalid page in block 24 of relation "base/5/16399"
=> SELECT checksum_failures, checksum_last_failure FROM pg_stat_database WHERE datname = 'postgres';
checksum_failures | checksum_last_failure
--------------------+-------------------------------
1 | 2026-09-22 05:22:14.945557+00
# checksums off, identical corruption
=> SELECT sum(abalance) FROM pgbench_accounts;
sum
-----
0
(no error, checksum_failures stays 0)
Same damage, same file offset, two completely different outcomes — but only once the query actually reads the page. Checksums verify pages as they come off disk into a buffer; a plan that avoids the heap avoids the check too. The failure count and timestamp in pg_stat_database are what you'd actually alert on in production — a Prometheus exporter reading pg_stat_database will see that counter move the moment a damaged page is read, rather than someone noticing wrong numbers downstream weeks later.
What I got wrong on the way
My first pass at the disable timing looked like disable was instant regardless of database size — one early test on a fully-converted 756MB cluster showed SHOW data_checksums flipping to off in 0.13 seconds. I wrote that down as "disable is O(1)" and moved on. Running it three more times on a different cluster with the same size dataset gave 1.16s, 1.11s and 1.08s instead — consistent with each other, but nearly ten times slower than my first sample. The likely explanation is the "when all active backends have stopped validating" condition in the docs: disable has to wait for a completion barrier across whatever backends exist, and that wait varies with what else is connected. The honest number is "disable finished in 0.1 to 1.2 seconds across my runs, and enable never went under 2.8," not the tidier claim I almost published.
Run it yourself
This needs Docker and about 500MB of free disk for the image and data.
docker run -d --name pg19 -e POSTGRES_PASSWORD=postgres \
-e POSTGRES_INITDB_ARGS="--no-data-checksums" \
-p 15432:5432 postgres:19beta3
docker exec pg19 pgbench -U postgres -i -s 50 postgres
docker exec pg19 psql -U postgres -c "SHOW data_checksums;" # off
# time the online conversion
docker exec pg19 psql -U postgres -c "\timing on" \
-c "SELECT pg_enable_data_checksums();"
# watch it finish
watch -n0.5 'docker exec pg19 psql -U postgres -tA \
-c "SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_data_checksums;" \
-c "SHOW data_checksums;"'
# corrupt a page on purpose and watch it get caught
# (use a query that can't be answered from the index alone, or PostgreSQL
# will satisfy it with an index-only scan and never read the damaged page)
FILE=$(docker exec pg19 psql -U postgres -tA -c "SELECT pg_relation_filepath('pgbench_accounts');")
docker exec pg19 psql -U postgres -c "CHECKPOINT;"
docker exec pg19 sh -c "dd if=/dev/urandom of=/var/lib/postgresql/19/docker/$FILE bs=1 count=16 seek=200000 conv=notrunc status=none"
docker exec pg19 psql -U postgres -c "SELECT sum(abalance) FROM pgbench_accounts;"
If you're running anything older than 19 today, this doesn't help you yet — the online functions only exist in 19 itself, so a pre-19 cluster still needs the offline tool or a logical dump-and-reload to get checksums retrofitted. What it does mean is that once you're on 19, there's no longer a good excuse for a production database to be running without them: the outage that used to justify skipping checksums is gone, and the cost has moved from "take the database down" to "generate roughly one extra copy of your data in WAL over a few seconds." Run SHOW data_checksums; on whatever you're running now. If it says off, that's one superuser session and a background worker away from not being true any more.
Top comments (0)