Postgres Weekly

Postgres 19's built-in bloat cleanup

Wed, 4 Nov 2026 00:00:00 +0000

#672 — November 4, 2026

Postgres Weekly

Postgres 19's REPACK: Built-In, Online Bloat Cleanup — Craig's favorite new Postgres feature is the one he thinks DBAs will care about most. REPACK folds VACUUM FULL and CLUSTER into one command and adds a CONCURRENTLY mode that keeps the table readable and writable, using logical decoding (a la pg_squeeze). He digs into its benefits and how to use it here.

Craig Kerstiens (Snowflake)

⭐ Postgres 19 remains on track for November 19, with RC2 due on November 12. Jonathan Katz has posted the draft press release for review, and the draft release notes continue to expand.

Sub-Second Personalization on One Lakehouse — Live demo: See how EDB Postgres AI delivers sub-second next-best-action on one copy of data in Apache Iceberg. Postgres, WarehousePG and ClickHouse share one catalog, with no pipelines between engines. Watch the demo.

EDB sponsor

Getting Rid of a 4 Terabyte Table Efficiently — How do you get rid of a 4 TB table on a primary with ten replicas with as little impact as possible? Radim compares the obvious DROP TABLE approach against batch deletes and a copy-and-swap, with the measured pros and cons of each.

Radim Marek

💡 In a similar vein, NexHealth has written up how they moved 4 TB out of a live Postgres database into its own Aurora cluster with no downtime.

IN BRIEF:

Want to ease into contributing to Postgres? Paul Jungwirth is running a patch review workshop where small teams will get to review a patch, with committers like Robert Haas critiquing the reviews. You can sign up here.
🏴󠁧󠁢󠁳󠁣󠁴󠁿 Jimmy Angelakos recaps October's Postgres Edinburgh meetup, where Elizabeth Christensen covered what's new in Postgres 18 and 19.
Supabase has opened a public beta of OrioleDB and a private alpha of Multigres. OrioleDB's alternative Postgres storage engine needs no VACUUM, but it's not production-ready yet.
CloudNativePG is now a CNCF 'incubating' project. Gabriele Bartolini tells the project's story from its 2019 start at 2ndQuadrant to ~247 million operator downloads today.

Burnt TOAST: Why DBAs Dread Large Text Columns — A tour of how Postgres stores oversized column values, with benchmarks showing why keeping the heap small can speed up scans and updates that leave those values untouched, and why fetching them can still be expensive.

Shaun Thomas (pgEdge)

💡 Sticking to the TOAST theme, Franck Pachot ponders: do TOAST's compression and out-of-line thresholds suit typical JSONB document sizes?

'server closed the connection unexpectedly' Explained and Measured — Chris reproduces connection failures across six clients on Postgres 13, 15, 17 and 18, showing why the same failure produces different diagnostics, and why a lost connection during a COMMIT can leave the outcome uncertain.

Chris van Eijk

📄 Logical Replication Beyond One Writer – A walkthrough of building multi-region replication, testing failover and handling schema changes, with a runnable Docker lab. Dimitri Fontaine

📊 Benchmarking Vector Search in Six Database Engines – Postgres and pgvector go up against MongoDB, MariaDB, AliSQL and Valkey. Evgeniy Patlan (Percona)

📄 How Replication Slots Can Fill Your Disk Anugya Sinha (Microsoft)

📄 How Real is 'Autonomous Postgres'? Vibhor Kumar (EDB)

📰 Classifieds

📺 New 4-hour freeCodeCamp course: Watch a 1.6-second Postgres query drop to 10 ms with TimescaleDB continuous aggregates.

RELEASES AND CODE:

pgModeler 2.0 (above) – Billed as "the biggest release in the history of the project", the database modelling tool gets a multi-scene canvas and Postgres 18 support, but moves import, diff and SQL execution to a paid 'Plus' edition.
Melange – Compiles Zanzibar-style, relationship-based authorization from OpenFGA models into PL/pgSQL functions, so a permission check is just a SELECT check_permission(...) away, with no separate service or store.
🌐 PostGIS 3.7.0 – The geospatial extension (now targeting Postgres 19) adds coverage edges, Float16 rasters and faster ST_DWithin. Postgres 12/13 support is dropped, and the geocoder and address standardizer move to separate repos.
🔒 PostgreSQL Anonymizer 3.3 – Dalibo's data-masking extension fixes 12 vulnerabilities and existing users are urged to upgrade.
🔒 pgAdmin 4 v9.19 – Fixes 15 CVEs. Server-mode users should upgrade, setting two new admin allowlists first.
Postgres.js 4.0 – Node.js driver rewritten with a scheduler to stop slow queries blocking fast ones, SCRAM channel binding and two security fixes.
TimescaleDB 2.31 – Tiger Data's time-series extension gains a new integer compressor (off by default for now, but expected to be on by default in 2.32) that can make databases ~10% smaller and typical queries ~50% faster.
pg_duckdb 1.2.0 – The DuckDB-in-Postgres extension moves to DuckDB 2.0 and adds initial Postgres 19 support. It's 'not ready for production use' for now.
pg_lion 0.3 – A bitmap-based inverted index type (inspired by roaring bitmaps, hence the name!) for faster counts, facets and GROUP BY on large tables.
Reylo 0.7 – Import plain .sql files into TypeScript with types inferred from your database. From the creator of Zod.

Why a DBA said no to one column

Wed, 28 Oct 2026 00:00:00 +0000

#671 — October 28, 2026

Web Version

Postgres Weekly

'Region': A Lesson in Data Integrity — One of Lætitia's coworkers added a free-text region column and planned to validate it in app code, but Lætitia wouldn't approve it. Here she makes a case for keeping rules in the database with a look at domains, enums, CHECK constraints and lookup tables, and how to add constraints on large tables without blocking traffic.

Lætitia Avrot

CREATE INDEX on 100M Vectors in Under Two Hours — HNSW indexes on large pgvector tables use lots of memory and can take days to build. Meet pg_vectorsearch: an open-source alternative for existing vector columns. At 100M vectors: a 25 GB index and 6 ms queries at 95% recall. See the benchmarks.

Tiger Data (creators of TimescaleDB) sponsor

📖 PostgreSQL 18 Query Optimization: A Free 199-Page Book — Postgres Pro's query tuning course, reshaped into a compact book that's now in English. It covers how the planner works, access methods, join strategies, statistics, reading execution plans and tuning approaches. Here's the PDF.

Pavel Tolmachev (PostgresPro)

IN BRIEF:

Microsoft has started pitching SQL Server to Postgres developers as an 'upgrade', touting its 32,767-connection limit. SQL Server expert Brent Ozar isn't buying it, and Microsoft's author pops up in the comments to argue back.
🇺🇸 PgDay Boston 2027 takes place on April 10, 2027 at Tufts University. Registration is open, as is the CFP (until January 15).
🇺🇸 Postgres Day SF is a free meetup (registration needs approval) at PlanetScale's San Francisco office on November 4, celebrating 30 years of Postgres with Bruce Momjian and Fabrizio Mello.
📅 POSETTE 2027, Microsoft's annual virtual Postgres event, returns on March 30-31, 2027. No CFP yet.

Why I Don't Trust ON DELETE CASCADE — One big cascading DELETE plus a routine migration can freeze writes to a table. Tom explains the lock queue behind it, some replication traps, and why he prefers soft deletes.

Tom Pang (PlanetScale)

How a Read Replica Almost Took Down Its Primary Database — Google Cloud grows disks automatically so Photoroom wasn't worried about space. Then a backfill left a BI replica so far behind that the primary held 31.7TB of WAL for it. The disk grew close to a hard limit, and shrinking it meant downtime.

Marek Zaremba-Pike (Photoroom)

Moving 74GB Off RDS with a Week-Long Rollback Window — Prisma moved its console's database off RDS with logical replication and no write pause, then ran replication in reverse so it could fall back to RDS for a week.

Siket and Datta (Prisma)

📄 Your App Doesn't Need Postgres Superuser – Superuser can run commands on the server, so here's what to grant apps and agents instead. Umair Shahid (Stormatics)

📄 Postgres 19 is Late, and It's AI's Fault Robert Haas

📄 Tuning Postgres for Analytics – Slides and transcript from PGConf.EU on running reports against your app's database without slowing it. Karen Jex

🎙️ Karen also joined Claire Giordano on the Talking Postgres podcast to talk about her path from Oracle DBA to Postgres contributor.

📰 Classifieds

⚡ Rebase turns your Postgres into an API and an admin panel your team can use. Our own CRM: 23 tables, zero hand-written admin screens.

⏱️ One 5-row lookup table turned a 0.3 ms Postgres query into 467 ms. Find yours before prod does. Now in preview.

RELEASES AND CODE:

pgVolvra: Flashback-Style 'Undo' for Bad UPDATEs and DELETEs — Trigger-based row history that lets you preview and reverse a bad statement or migration by transaction ID. Plain SQL with no superuser needed, so it works on managed Postgres too. GitHub repo.

Muhammad Aqeel (pgEdge)

pgrust 0.4 Beta: The Rust Rewrite of Postgres Opens Up — The experimental Rust reimplementation of Postgres adds a new JIT engine, transactional columnar tables and automatic memory sizing (no more work_mem tuning?).

Michael Malis

💡 ClickHouse pulled pgrust from ClickBench in September over per-query code and caching. With v0.4's source public, it's back in, and central to a benchmark-overfitting debate.

pg_pinecone: Pinecone's Vector Index for pgvector Columns — A new extension that indexes existing pgvector columns using IVF and RaBitQ quantization, applying filters during the search. Its native engine runs locally on Postgres 17 and 18, and Pinecone reports a 136MB index vs 2GB for HNSW on 1M vectors. GitHub repo.

Daddala and Mahanna (Pinecone)

pg_hdr_histogram 1.0 (above) – Store value distributions (e.g. latencies) as compact HDR histograms, then query p99-style percentiles and merge them losslessly across time windows or servers, say.
mcdview – Turn a Postgres schema dump (or a Prisma, pgModeler or Rails schema) into a searchable, shareable interactive ER diagram. You can try it online or see an example of its output here.
Citus 14.3 – The distributed Postgres extension adds sorted-merge execution for multi-shard ORDER BY queries and per-shard pruning of OR clauses.
SPQR 3.2 – Yandex's Postgres sharding router adds spqrmigrate, a schema migration tool.

Postgres 19 RC1 and what to test now

Wed, 21 Oct 2026 00:00:00 +0000

#670 — October 21, 2026

Web Version

Postgres Weekly

Postgres 19: What Changed and What to Test Now — With RC1 out and GA due in under a month, Tom Kincaid walks through what survived the reverts and what to try before upgrading, including REPACK (CONCURRENTLY), pg_plan_advice, parallel autovacuum, and logical replication sequence synchronization, each with a 'what to test' checklist.

Tom Kincaid (EDB)

⭐ Postgres 19 RC1 was released last week, with an RC2 due November 12 and the final release expected on November 19.

100+ Search Engines. One Simple API — Add live web search to your backend without managing proxies or parsers. SerpApi delivers structured results from Google and other search engines as clean JSON.

SerpApi sponsor

recovery_prefetch Considered Harmful and Causing Outages — Jeremy shows how recovery_prefetch, enabled by default since Postgres 15, can overwhelm standby storage after a write burst, causing hours of lag. With synchronous replication, a dropped connection then blocked primary writes for 35 minutes in his reproduction.

Jeremy Schneider

🩹 A patch to avoid redundant prefetches after full-page images is under review, which tackles part of the problem.

IN BRIEF:

🇺🇸 Postgres Conference 2027 takes place April 21–23, 2027 in San Jose, California. If you want to speak, its CFP is open until January 31.
Who owns an abandoned extension? PGXN's David Wheeler asks pgsql-hackers what the policy should be, after receiving takeover requests and privilege escalation reports for unmaintained extensions.
🇧🇪 FOSDEM PGDay 2027 expands to two days, January 28–29, in Brussels. The CFP is open until December 1.
🇹🇭 FOSSASIA PGDay 2027 takes place on March 9, 2027 in Bangkok. Their CFP is open until November 30.
Neon Realtime is now in public beta. You can subscribe to any deterministic SQL query and the result stays live as the data changes.
84% of surveyed Ruby on Rails developers run Postgres in production as per the results of the 2026 Rails Community Survey.

How hstore Can Beat jsonb for Audit Logs — depesz saw a post claiming that logging only changed columns from an audit trigger shrank the log 5x but slowed updates. But his own test, using hstore instead of jsonb, made diff logging the fastest variant.

Hubert 'depesz' Lubaczewski

Why is Postgres's numeric Type So Slow? — A deep dive into numeric's internals explaining why it's slow compared to double precision or DuckDB's integer-backed DECIMAL, and why "just use int128" doesn't fix it.

Andrei Lepikhov (pgEdge)

Citus Hits 10M QPS on Half Neki's Data Nodes — A month after PlanetScale's 118M QPS Neki post, Álvaro ran the same shape of workload on Citus with StackGres's new query routers and hit 10M QPS on 24 workers rather than Neki's 50 shards.

Álvaro Hernández (StackGres)

🎧 Postgres FM had Álvaro on the same day for an hour on Citus, StackGres and the thinking behind the benchmark.

📄 Why Materialize Dropped SERIALIZABLE for 3x Write Throughput – Hundreds of thousands of predicate locks sank their Postgres-backed metadata store, so they rebuilt it on READ COMMITTED and proved it correct in Lean. Petros Angelatos

📄 From Alert to Online Repack: A Playbook for Postgres Table Bloat – Build a bloat 'watchdog' with pg_cron and pgstattuple, then use pg_repack to take a 13GB table down to 3.4GB without taking it offline. Haider Z (Microsoft)

📄 Reducing Storage Initialization Impact in Amazon RDS After Snapshot Restore – Why queries on a freshly restored RDS Postgres can run 10–15x slower at first, and how to prewarm it. Modappady, Pattabiraman and Thakur (AWS)

📄 UUIDs Stored as text: What It Costs in Postgres 18 Hollis Varden

📰 Classifieds

Today: Let a coding agent fork, break, and fix a real Postgres database. Free live workshop, recording included.

RELEASES AND CODE:

DocumentDB 1.0: A MongoDB-Compatible Document Database on Postgres — First open sourced by Microsoft last year but now a Linux Foundation project, DocumentDB is a set of extensions (plus a gateway) that speak the MongoDB wire protocol and store BSON natively. This first stable release comes with packages for Postgres 17 and 18.

DocumentDB

PGX: Run Thousands of Throwaway Databases in One Process — From the team behind pgbot, and built upon a Rust reimplementation of Postgres, PGX hosts large numbers of isolated databases in one process, each cloned from a template in milliseconds, for agent sandboxes and per-PR tests. Beta, AGPL-licensed, with fsync off, so for dev use only. (GitHub repo.)

PGRun

pgBackRest 2.60: Restores Now Stop on Invalid Page Checksums — The popular backup and restore tool now stops a restore with an error if any restored file has an invalid page checksum. Worth knowing before your next restore! (checksum-page-error=n can change it to a warning only.)

pgBackRest Community

pg_hint_plan 1.9.0 – The long-standing query hinting extension, now built (only) for Postgres 19 using its new path generation strategies.
PostgREST 16.5 – Popular REST API server layer for Postgres. Adds a client-side mitigation for a recent Postgres CVE.
⚠️ pgmq 1.13.1 – The Postgres-native message queue. Fixes two privilege issues, so upgrade if you use it.
pg_ivm 1.17 – Incrementally maintained materialized views, which can now survive pg_upgrade if you use pg_ivm_dump_metadata --pg-upgrade.
🌐 PostGIS 3.7.0 RC3 – The geospatial data extension targets Postgres 19 RC1 – I'd expect a final 3.7 around the same time as Postgres 19 itself.

Postgres 19 now due November 19

Wed, 14 Oct 2026 00:00:00 +0000

#669 — October 14, 2026

Web Version

Postgres Weekly

pg_vectorsearch: A Partition-Based Alternative to HNSW — A vector search extension that uses PRISM, a vector index that scans compressed k-means partitions rather than walking a graph like HNSW, and works on existing pgvector columns. It's alpha and requires Postgres 18+, while pgvectorscale moves to maintenance mode.

Erik Nordström et al. (Tiger Data)

Agent Memory: Raw Retrieval Vs. Extracted Facts, Tested — Redis reran agent memory systems on LongMemEval. Published scores missed reproduced results by up to 28 points, and pairing raw conversation retrieval with extracted facts beat either approach alone. See the data, costs and methodology.

Redis sponsor

How Savepoints Throttled Our Postgres Queue — A technical look, complete with some rather nifty visualizations, into how adding more workers to a Postgres-based queue made it slower because of per-job SAVEPOINTs (and how claiming jobs with a lease fixed it).

Rory Malcolm (incident.io)

IN BRIEF:

⭐ Postgres 19's release has been pushed back to November 19, with an extra RC2 on November 12 to include many more recent fixes. The first release candidate still lands tomorrow (October 15).
⚠️ PgBouncer 1.26.1 fixes three CVEs, the worst letting a client who knows one user's password log in as a different user (even a superuser) when SCRAM auth is in use.

'The Reverts Will Continue Until Morale Improves' — A Postgres committer, whose own work was among the casualties, shares his take on why so many Postgres 19 features were reverted during beta.

Peter Eisentraut

📊 Meanwhile, Kaarel Moppel ran 800 hours of benchmarks on the Postgres 19 betas and found them a few percent faster than v18 on OLTP workloads.

Don't Bet on SELECT Order: Shuffle Your Rows and See What Folds — If your app or tests implicitly rely on rows returning in insertion order, that can cause many an annoying bug (I've been bitten myself!). The pg_disorder extension shuffles SELECT results lacking an ORDER BY to catch such bugs in testing.

Yaroslav Kurbatov (Evil Martians)

What Deleting Rows Does to a Table — A tidy explainer and demo covering a common misunderstanding. Deleting half a table's rows, then running VACUUM, left it at 137 MB on disk, with only VACUUM FULL reclaiming it.

Hollis Varden

📄 Why Setting archive_command to /bin/true Can Lose Data – The 'fix' for a full WAL disk that breaks recovery without warning. Shaun Thomas

📄 PgBouncer Pooling Modes in Practice – What transaction pooling does to your advisory locks. Oskar Dudycz

📄 PgBouncer vs. Amazon RDS Proxy: How the Two Poolers Differ Wood and Chiba (AWS)

📄 How TABLESAMPLE Picks Rows Radim Marek

📰 Classifieds

RDST's always-on agent watches every PG query, automatically sends you explained fixes, and verifies they worked once applied. Put an agent to work on your Postgres database. It's free.

⚡ Rebase: An open-source backend for Postgres. REST, a typed SDK, auth, realtime, storage, and an admin panel you don't have to build.

Try ClickHouse Managed Postgres: Up to 10x faster for I/O-heavy workloads on local NVMe, with native ClickHouse integration.

RELEASES AND CODE:

pg-delta: Diff Postgres Schemas Into Ordered Migrations — Supabase's new open source schema diff engine. Point it at two Postgres 14–18 databases and get an ordered migration covering RLS policies, grants and domains. It replaces the deprecated migra, but is still alpha.

Valleteau and Xie (Supabase)

asyncpg 0.32.0: Fast Postgres Client Library for Python/asyncio — The long-standing Python client library for use with the asyncio framework now supports Postgres 19, as well as the newly released Python 3.15.

MagicStack

Orafce: Oracle Compatibility Functions for Postgres — Implements some of the functions from Oracle that might be useful for users migrating to Postgres. We haven’t linked to this in a few years but it continues to get frequent releases.

Orafce Project

wal2json 2.7 – A logical decoding plugin that emits row changes as JSON, used by many change data capture setups. This first release in over two years adds Postgres 18 and 19 support.
PGQueuer 1.6 – A Python job queue library built on Postgres.
Squawk 2.68 – A linter for Postgres schema migrations.