Build1 publisher2 min readPublished
PostgreSQL 19's online checksum conversion ran slower than the outage it replaces
PostgreSQL 19 changes checksum state from a SQL call. One developer's beta-3 runs show the background pass taking longer in wall time than the offline stop-start, with concurrent writes unaffected on a small container.
The Engineer · Build desk
What happened
- PostgreSQL 19 adds pg_enable_data_checksums() and pg_disable_data_checksums(), doing the same conversion as the standalone pg_checksums tool while the cluster keeps serving traffic.
- Before 19, changing a cluster's checksum state meant stopping the server, because pg_checksums rewrites every page directly on disk.
- On a 775MB test cluster, three unthrottled enable runs finished in 2.852s, 2.804s and 2.802s, with the resetting disable runs at 1.158s, 1.111s and 1.076s.
- The offline stop-start cycle on a comparable sub-1GB dataset held the outage for over a second, accepting no connections and serving nothing.
- A bare initdb with no flags on the postgres:19beta3 image reported that data page checksums are enabled, which the initdb reference page confirms as the new default.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- decision Operators now pick the shape of the window instead of the tool: a guaranteed stop of over a second, or several seconds during which every page is rewritten under live traffic.
- constraint The SQL path ships only in 19, so a checksum-less 16 or 17 cluster has to clear a major-version upgrade before it can skip the outage.
- contradiction PostgreSQL's documentation says overall system performance will be affected during conversion, and the pgbench test that saw no slowdown ran on a lightly loaded container on local overlay storage, so the warning is the safer planning input for a busy cluster.
- exposure Clusters initialised before 19 without the checksum flag have been answering queries over damaged pages with normal-looking results, and the new default protects only clusters created from 19 onward.
The new path runs in a background worker. It walks every table in every database and flips pages over while normal queries keep running against them [5]. While that happens, `SHOW data_checksums` reports `inprogress-on`, with a matching `inprogress-off` in the other direction and the settled `off` and `on` at either end [13].
The call returns long before the work finishes. Round trips for the function call itself came in at 0.124s, 0.130s and 0.136s, an average of 0.130s [12][2], against an average of 2.82s for the conversion running behind it [1]. The documentation says the call completes immediately, and the developer who ran the tests found that accurate for the call [14]. Progress lives in a new view: one mid-run sample of `pg_stat_progress_data_checksums` showed phase `enabling`, 1,480 of 82,525 blocks done, and 0 of 297 relations finished [15].
That 2.82s covers 775MB, which works out at roughly 275MB/s [4]. For the rate to transfer, storage has to feed pages as fast as it did in this test, and a 775MB dataset in a lightly loaded container on local overlay storage is largely in cache [19]. At 275MB/s, a one-terabyte cluster needs about an hour of background work [5].
The availability question was tested with pgbench: 8 clients, 4 threads, 20 seconds, run once against an unthrottled conversion and once with the conversion throttled to `cost_delay=20, cost_limit=100` [16]. Neither run showed a slowdown [16]. Throughput went up slightly, which the developer put down to warm caches from earlier runs. "I don't have grounds to claim the conversion made writes faster," they wrote [17]. The PostgreSQL documentation takes the other position, warning that "overall system performance will be affected" [18].
Undoing the feature costs a full pass too. `pg_checksums` scans and rewrites the whole data directory whichever direction you are going, so the disable case is cheaper than enable without being free [10].
What the feature buys showed up in the first test. The same 16 bytes were corrupted twice in the same table. With 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 [1].
Both functions arrive in 19, which is at beta 3 with general release due around September or October 2026 [2]. A checksum-less 17 cluster has only the offline tool until it is running 19 [8].
What to watch
- Whether the September or October 2026 general release keeps checksums enabled by default in initdb or restores the opt-in flag.
- Conversion timings from a multi-terabyte cluster on network-attached storage, where the measured 275MB/s rate would not hold.
- Whether the cost_delay and cost_limit throttle becomes the recommended setting for conversions on busy clusters.