Latest Blog Posts

This is part 2 of a series about Postgres logical replication use-cases, and about how the feature set has evolved over the past ten years and ten releases, one architecture at a time. Part 1 built a hub-and-workers system for write scalability and has the table of what each release added, which this post assumes. Part 3 covers a zero-downtime major upgrade.

My tool tells you things like “this index cuts the query’s cost by 91%”. Before releasing it, I wanted to know what that number is actually worth.

The tool is PgLens, a Postgres index advisor I’m building. Like several other advisors, it checks each suggestion with HypoPG: create a hypothetical index, run EXPLAIN again, and keep the suggestion if the planner’s estimated cost drops by at least 15%.

That’s an estimate, not a timing. So I built every index for real and timed the queries.

The setup

• The Join Order Benchmark: 113 queries over the real IMDB data, 7 GB loaded. It was made to catch the planner getting row counts wrong, so it’s a hard test.
• PostgreSQL 16, hypopg 1.4.
• PgLens made 214 planner-validated recommendations for those queries. They name only 10 distinct indexes, because one foreign-key index helps many queries.
• I wrote down the metrics and what counts as a win before the first run: 15% faster is a win, 5% slower is a loss.

What happened

• 128 of 179 made their query at least 15% faster.
• 32 of 179 made it at least 5% slower. 7 made it more than twice as slow.
• 34 couldn’t be measured, because the index can’t be built.

The worst one was query 10c with an index on cast_info (movie_id). Estimated: 91% cheaper. Measured: 0.93 s before, 7.3 s after. I ran it again by hand to be sure: about 1 s became about 9 s.

With the index there, the planner picked a nested loop that probes cast_info once per row of a join. It underestimated how many rows that join returns, so the loop ran far more often than it planned for: the query read 71 times as many buffers. HypoPG can’t catch this, because it asks the same planner with the same wrong row counts. Every advisor built on hypothetical indexes has this blind spot, mine included.

The 34 are one index, PgLens’s #2 suggestion, movie_info (info):

ERROR:  index row requires 9392 bytes, maximum size is 8191

1,182 of 14.8 million values are too long for a B-tree entry. A hypoth

On 29 September, the PostgreSQL Usergroup NL met, organized by Gerard Zuidweg and Sebastiaan Mannem

Speakers:

• Peter Lengkeek
• Ellert Van Koperen
• Jan Karremans

The Postgres US Summit 2026 was held from 30 September to 2 October.

Organizers:

• Mark Wong
• Michael Alan Brewer
• Mila Zhou
• Chelsea Dole
• Pat Wright
• Joseph Koshakow

Program Committee:

• Chelsea Dole
• Jonathan Hinds
• Jonathan Katz

Code of Conduct Committee:

• Mila Zhou
• Michael Alan Brewer
• Katherine Saar

Speakers:

• Alex Anto Kizhakeyyepunnil Joy
• Alex Yarotsky
• Amit Kapila
• Anand Rao
• Andrew Atkinson
• Ankit Mittal
• Benedict Kofi Amofah
• Bertrand Drouvot
• Brian Brennglass
• Bruce Momjian
• Chris Gooch
• Claire Giordano
• Cody Fincher
• David Wheeler
• Devrim Gündüz
• Divya Bhargov
• Gabriele Bartolini
• Gabriele Fedi
• Gary Evans
• Gayathri Paderla
• Gleb Otochkin
• Greg Potter
• Hari Kiran
• Jelte Fennema-Nio
• Joaquim Oliveira
• John Sly
• Jonathan S. Katz
• Kavach Shah
• Kranthi Kiran Burada
• Makhoul Cassis
• Masahiko Sawada
• Mohamed Ali
• Nazneen Jafri
• Nikunj Doshi
• Payal Singh
• Peter Geoghegan
• Rajni Baliyan
• Raluca Constantin
• Ranjan Burman
• Richard Yen
• Robert Haas
• Robert Treat
• Ryan Booz
• Sehrope Sarkuni
• Selena Flannery
• Shane Borden
• Shayon Sanyal
• Stacey Haysler
• Stephen Brandon
• Sukhpreet Kaur Bedi
• Tatiana Krupenya
• Umair Shahid
• Xavier Fischer

Volunteers:

• Assel Sakenkyzy
• Charlie Lin
• Hengjiali Xu
• Induja Sreekanthan
• Jane Kyung
• Mehboob Alam
• Mingjun Jin
• Mukesh Agrawal
• Omololade Akinleye
• Rinisha Marar
• Selenge Tulga
• Supriya Aggarwal
• Tushar Dahiya
• Valentina Blackledge
• Vanshika Nigam
• Yehonatan Sade

The Women's Breakfast was organized by Stacey Haysler

On 30 September, the Pr

What is Direct I/O, and why would a managed Postgres care about it when taking a backup? Every day, a backup copies your whole database, hundreds of gigabytes, off the same NVMe drives that are answering your queries, and ships it to object storage. Linux treats those reads like any other file read: it keeps a copy of every byte in memory, in the page cache, in case someone asks for it again. Nobody will. To make room for those copies, the kernel throws out data Postgres had in memory and was about to use, and the next query that needs it goes to disk instead.

Direct I/O is the flag that tells the kernel to skip the cache for those reads. Turn it on and a second problem appears. The reads also lose the kernel's readahead, and on a set of four striped NVMe drives a small read keeps one drive busy while the other three sit idle.

The short version A backup reads every byte of a live database off the drives that are serving queries. By default those reads go through the kernel's page cache, pushing out data the database had in memory and costing a kernel copy per byte while queries run. Direct I/O skips the cache. On our test box it left the warm data in place, cut the latency hit queries took during the backup by about two thirds, and used about 14% less CPU. It also switches off readahead, so on striped NVMe the read size and reader count have to match the array. ClickHouse Managed Postgres sizes each direct read to span the whole RAID0 stripe and scales the reader count to the hardware. On a 48 vCPU box with four NVMe drives and a 467 GB database under a live workload, the buffered backup evicted all 40 GiB of a table that was warm in memory and idle. The Direct I/O backups evicted nothing and finished in 71 seconds.

In this post, I walk through integrating JFrog Artifactory with Fujitsu Enterprise Postgres and using Transparent Data Encryption to encrypt the Artifactory metadata stored in the database.

Last week we had our September PostgresEDI meetup, the first since postgres.scot went up as the home for Postgres events in Scotland.

Thursday, September 24th took us back to Paterson's Land at the University of Edinburgh, room 1.27 this time, for two talks that could hardly have come at the subject from further apart. Thanks to everyone who came along, to Daniel for joining us from a different corner of the software world, and to pgEdge for the pizza and refreshments. Special thanks to the friend who came all the way down from Aberdeen for the evening, which is a serious commitment for a Thursday.

The Talks

ColdFront: Transparent PostgreSQL Data Tiering to Apache Iceberg

Jimmy Angelakos (pgEdge)

First up was my own talk on ColdFront, which moves cold rows out of Postgres onto object storage as Apache Iceberg tables in columnar Parquet, and keeps them readable from Postgres as though they had never gone anywhere. The design is deliberately slim: Postgres holds your tables and your SQL, pg_duckdb does the Iceberg reading and writing in-process, Lakekeeper is the catalog, and the object store is whichever one you already have. Your application goes on querying one relation.

The hard part is what happens when the cold tier is writable and several Postgres nodes are writing to it, with a single Iceberg catalog and a single bucket between them. ColdFront takes a ticket before each cold write so that two writers never collide in the first place, and we modelled that protocol in TLA+ rather than trusting our intuition for it.

I closed with vector search over cold data, where a probe comes down to a WHERE clause with no index to build.

📊 View the slides: ColdFront: Transparent PostgreSQL Data Tiering to Apache Iceberg (PDF)

Working with Postgres in Nuxt (including live coding!)

Daniel Roe

After the break, Daniel Roe, who leads the Nuxt core team and works on it full-time at Vercel, showed us what Postgres looks like from the far end of the stack.

He started with wh

Over the past month, several major features got reverted from Postgres 19. At this point, even if everything else goes perfectly, Postgres 19 will be released about a month later than originally planned. A couple recent blog posts seem to suggest the reverts happened due to AI finding complex bugs in those patches, with fixes way too invasive this late in the development cycle. I don’t think that narrative matches reality, and I think it matters to correct that.

Query hints: You asked for it, Postgres 19 gave it to you. Now, they are just waiting to see how you use it.

For years the Postgres community was against adding support for hints. They felt if you had correctly analyzed tables, the Postgres query planner would make the right choice. And they're not wrong, for the most part. Yet many DBAs and developers will tell you of a time when a plan suddenly changed or something was saved by a hint. We can debate the good and bad elements of hints, but the cool thing is that you will soon have a choice in Postgres 19+.

So what are query hints? I came from the SQL Server world like many folks in the Postgres world and they were just there. Oracle, too. They are explicit instructions you give with SQL to change the default execution plan. Note: While industry standard refers to these as "hints," Postgres 19 uses the term "advice," so use both when searching.

This feature, expected later this fall, is coming to Postgres 19 via two new contrib modules (extensions packaged with Postgres that have to be explicitly turned on, like pg_stat_statements, everyone's favorite extension).

• pg_plan_advice - which lets you set query plan advice, like "join order" and "scan type." This also adds a new EXPLAIN option to get the plan advice for any query.
• pg_stash_advice - this lets you save plan advice strings per query id, so if you run the same query over and over (or a parameterized query), it will default to your advice instead of the planner's.

Here's a quick look at what it looks like to turn on plan advice and use it:

Out now on GitHub and PGXN, pg_clickhouse v0.11.0 and the chdb extension v0.1.2 continue our dogged focus on cross-database compatibility. A slew of these enhancements derive from our header-only C libraries, clickhouse-c and pg-clickhouse-c. Let's take a look at just three of the changes in these releases.

What a character {#what_a_character}

First up, character encoding. In the process of developing the benchmark for the chdb extension post, I discovered that pg-clickhouse-c wasn't validating character encodings on text columns. My colleague Philip quickly patched the library to raise an exception when any text- or json-based type contains bytes that violate the database encoding.

This fix shipped in chdb v0.1.1, but we delayed pg_clickhouse a bit to avoid errors for anyone with existing foreign tables that read invalidly-encoded data. pg_clickhouse v0.11.0 adds a new foreign server option, check_encoding, that provides encoding error handlers. The options are:

This is my personal experiment, not an official Apache Cloudberry release.

Since 2017 I have been keeping an eye on the PostgreSQL ecosystem for analytics and data warehousing. At work I had used AWS Redshift, with all the drawbacks and inconveniences a developer gets from a fork of an old PostgreSQL. To my mind, the ideal PostgreSQL-based solution should run on the latest PostgreSQL release, be built as an extension, be easy to start locally in a container for tests, and work with the latest versions of existing extensions such as PostGIS and pgvector.

Citus came closest to what I wanted, but I wouldn’t call it a full-fledged massively parallel processing (MPP) system. It is more about sharding data for OLTP workloads.

Greenplum, an MPP fork of PostgreSQL, has a lot going for it, and plenty of people run it for real work, but the PostgreSQL it is built on lags behind the current one. So I set out to turn Apache Cloudberry, the most up-to-date open-source version of Greenplum, into a set of extensions for PostgreSQL 19. For now this is my personal experiment, not an official Apache Cloudberry release. With an LLM’s help, more than 90% of Cloudberry’s database code has been ported, and 59% of the original project’s tests now run on the new version. It took me 12 days…

Greenplum has always trailed PostgreSQL by a few releases. Greenplum 6 shipped on PostgreSQL 9.4 and Greenplum 7 on 12, and Apache Cloudberry, which carries the Greenplum project on, got its main branch up to 16.9 only in May 2026. The reason is that this massively parallel database is a fork: Cloudberry has changed 882 of PostgreSQL’s source files and added 646 new ones, not counting the 1.66 million lines of its ORCA optimizer, test data included. Every new PostgreSQL major version means months of merging, and by the time the developers finish, upstream has moved on.

I decided to go the other way around and cross a hedgehog with a snake, there is a saying: instead of pouring PostgreSQL’s sources into Greenplum, fit Cl

After trying not to add any Rust packages to the PostgreSQL RPM repository, I finally bit the bullet and pushed pgrx and postgresql_anonymizer to the RPM repos, along with packaging guidelines:

https://github.com/pgdg-packaging/pgdg-rpms/wiki/Packaging-Rust-extensions-(pgrx

Some limitations exist for now:

https://github.com/pgdg-packaging/pgdg-rpms/wiki/Packaging-Rust-extensions-(pgrx)#supported-distributions

Hoping to add RHEL 10.3 and 9.9 support in November.

Enjoy!