PostgreSQL 19 is a good release we barely notice
PostgreSQL 19.0 came out yesterday. For most people the headline is REPACK (CONCURRENTLY): tables can finally give disk space back without locking out writers. For a waveform store that runs on edge machines, you would expect that to be the feature that matters most.
We measured instead of guessing. We ran MorletDB on 17 and on 19 side by side, on a desktop and on a Raspberry Pi, with the same data, the same settings and the same tasks. Our answer: 19 is a good release, and for MorletDB a quiet one. It changes nothing in our data, nothing in our results and almost nothing in our speed. The feature that helped most was not the headline one.
How we measured
- Two machines. An Apple M5 Max desktop, with 17.11 and 19.0 built from source with identical flags. A Raspberry Pi 3B+ with 1 GB of RAM and an SD card, running the official Debian-based
postgres:17.12andpostgres:19.0images, each with the same MorletDB build. - Real recordings. Bearing test-rig vibration and motor currents from the Paderborn dataset: 101.6 million samples on the desktop, half that on the Pi.
- Our own tasks. Loading, building search envelopes, reading, exact search by shape, the newest data per recording, and the integrity scrub.
- One server at a time. Each server ran alone, and the order alternated every round: 17 first, then 19 first. This is a development measurement, not a benchmark: a few rounds on two machines, nothing frozen in advance. The numbers describe these tasks on these machines.
Same bytes, same answers
This came first, because for MorletDB a faster wrong answer is worthless. In every round, on both machines, 17 and 19 stored byte-identical chunks and envelopes and returned identical search results, with distances compared bit for bit. The scrub found nothing. The test harness stops if any of that differs, so every time below compares identical work.
It is not automatic. PostgreSQL 19 compresses large values with lz4 by default. Since PostgreSQL 19's release candidates, MorletDB pins its sample and envelope columns to pglz, so storage stays the same across 17, 18 and 19.
Speed: one gain, otherwise the same
| Task (median of rounds) | Desktop 17 → 19 | Pi 17 → 19 |
|---|---|---|
| Read every sample | 2.29 s → 2.15 s (−7%) | 15.5 s → 14.4 s (−7%) |
| 2,000 random reads | 0.79 s → 0.77 s | 9.71 s → 9.68 s |
| Exact search, 20 queries | 0.58 s → 0.57 s | 6.04 s → 6.21 s |
| Newest data of every recording | 0.51 s → 0.52 s | 3.13 s → 3.23 s |
| Integrity scrub | 1.71 s → 1.73 s | 9.90 s → 10.06 s |
| Load (appends) | 7.94 s → 8.01 s | varied up to 2× between rounds (SD card) |
| Build envelopes | 9.68 s → 9.76 s | varied up to 2× between rounds (SD card) |
Reading every sample was 7% faster on 19 on both machines, in every round. Everything else stayed within the spread between rounds. That is what we expected: MorletDB's hot paths are chunk decoding, envelope arithmetic and distance computation in Rust, inside the extension, and PostgreSQL 19 does not change them. Where 19 does help is the small executor work around them.
Two checks behind this table. PostgreSQL 17 has JIT on by default and 19 has it off, so we added a third arm, 17 with JIT off. On the desktop, where we ran it, it matched 17 with JIT on in every task, so JIT does not explain the difference. On the Pi, writing times were dominated by the SD card. One round of the same server took twice as long as another, so those rows compare nothing. A load wrote the same amount of WAL on both versions (74.8 MB). One series of Pi loads favoured 17, and the next series of five rounds did not (median 94 s against 96 s). We cannot establish a write difference on that card. One more difference is a default: since PostgreSQL 18, a new cluster has data checksums on, so our 19 clusters had them and the 17 clusters did not. A 19 arm without checksums measured the same as 19 with them: within 2% in every task on the desktop, and in load time on the Pi, the only task we repeated there. Checksums cost nothing we could see, so keep them.
The feature that mattered: quieter notifications
MorletDB can send a NOTIFY for every committed append, so dashboards, stream processors and agents can wait for new data instead of polling. Up to PostgreSQL 18, every such notification wakes every connection in the database that listens on any channel. Edge boxes tend to have many of those: dashboards, the steward, consumers of other collections.
We measured it. One writer committed small appends with notifications on, and one consumer listened. Then we added connections that listened on unrelated channels and did nothing else:
| No idle listeners | With idle listeners | |
|---|---|---|
| Desktop, PostgreSQL 17 | 1,511 commits/s | 698 commits/s with 200, and about 8× the server CPU |
| Desktop, PostgreSQL 19 | 1,762 commits/s | 1,738 commits/s with 200, same CPU |
| Pi, PostgreSQL 17 | 64 commits/s | 54 commits/s with 50 |
| Pi, PostgreSQL 19 | 73 commits/s | 71 commits/s with 50 |
On 17, 200 idle connections halved the rate of notifying commits. On 19 they cost 1–3%, because 19 wakes only the listeners of the channel that was notified. On the Pi, each commit is dominated by the SD card's flush, so the effect is smaller there but still visible. Every run delivered exactly one notification per append. We had read this improvement in the release notes as "less wake-up work". On our workload it is the difference between paying heavily for listeners and barely paying for them.
REPACK: we kept it a manual tool
REPACK (CONCURRENTLY) rewrites a table to give its free space back to the disk while writes continue. We removed half the recordings of a collection, ran VACUUM (the files keep their size), and then rewrote the chunk table while a writer kept appending:
- It works. The chunk table went from 146 to 84 MB on the desktop and from 72 to 37 MB on the Pi. Every kept chunk was byte for byte unchanged, and every concurrent append was stored.
- Appends keep going. On the desktop they did not notice: the slowest took 2.6 ms. On the Pi's SD card they slowed about sevenfold during the 7-second rewrite, and one waited 2.1 seconds.
VACUUM FULL, the old way, stopped appends for the whole rewrite: 0.4 s on the desktop, 7 to 13 s on the Pi. - But an older snapshot sees an empty table. A
REPEATABLE READtransaction whose snapshot was taken before the repack counted 0 of 1,744 chunks afterwards. AfterVACUUM FULLit counted all 1,744. PostgreSQL documents this. For MorletDB it means a multi-statement reader, such as the agent decision tool, could report "no match" where the data exists.
That last point decides it for us, together with two facts. MorletDB collections are append-only, so space freed by retention is reused by new samples anyway. And a disk that is already full cannot run a rewrite that needs free space the size of the kept data. So REPACK stays a manual tool in our runbook, used only when no other transaction is open. The steward does not run it by itself.
Upgrading from 17: one trap
An edge box most likely meets 19 through pg_upgrade. We upgraded a 17 cluster with chunks, envelopes, time anchors, row security and commit notifications. In copy, clone and link mode alike, the new cluster held byte-identical data and gave identical search results and anchor times. Its policies and notification setup were intact, and its optimizer statistics had come across. It took about two seconds.
The trap: since PostgreSQL 18, initdb turns data checksums on by default, and pg_upgrade refuses an old cluster without them. Create the new cluster with --no-data-checksums, or turn checksums on in the old cluster first with pg_checksums --enable. That takes longer, but checksums are worth having on an edge disk. Our operations guide now says so.
How we see this release
- A quiet release is a good sign for an extension. MorletDB needed no new SQL, no migration and no format change for 19. Its storage and results are identical, and it is slightly faster where it is faster at all.
- The release image stays on PostgreSQL 17 for 0.4. Images and packages for 19 come next. The official image they need is out now (#537).
- If you use commit notifications with many listeners, 19 is worth moving to. That is the one place where we measured a large difference.
- The lesson about ourselves came from REPACK. The steward counts only free disk space, so a retention plan cannot lift its full-disk hold. Fixing that (#538) helps on every PostgreSQL version.
Limits
- A few rounds on two machines, with the order alternating and nothing frozen in advance: development measurements, not a benchmark. The desktop also ran its owner's applications throughout.
- Pi writes depend on the SD card more than on PostgreSQL, so we compare no write times there.
- The REPACK runs were one run each, on collections of 37 to 84 MB. Large tables, where others have measured writers stalled for seconds to minutes at the final swap, were not tested. Neither were probe search tables or pgvector.
pg_upgradewas tested on the desktop with source builds, not with Debian'spg_upgradeclusteror inside containers.