Latest Blog Posts

The numeric type has existed in Postgres for more than 25 years. Over that time, it has undergone many optimisations and refinements. And yet operations on this type - aggregation in particular - still look rather inefficient.Since the PostgreSQL community always fixes problems with more or less simple and obvious solutions, let's find out whether numeric has structural limitations that prevent the DBMS operators for this type from working faster.To make the investigation more visual, let's also trace how DuckDB solves the same tasks - luckily, AI agents have made source-code analysis and testing much easier.One thing to keep in mind here: DuckDB is built exclusively for OLAP queries. That means, as I noted earlier, it places weaker demands on the exactness of operations - and, as you will see below, the DuckDB developer team actively uses this to make query execution more efficient.

Contents

• The teaser

• Four decisions Postgres pays for

• How an exact decimal type is built inside

• The numeric implementation in PostgreSQL

• The DECIMAL implementation in DuckDB

• Pass by value or by reference

• Tuple deforming

• The scale of an intermediate result

• Managing the precision of intermediate results in numeric

• Managing the precision of intermediate results in decimal

• The cost of being decimal

• Where we are going

The Teaser

Last week I ported Apache Cloudberry to PostgreSQL 19 as a set of extensions and described where the port falls behind the original fork. On ClickBench, columnar engines beat it by an order of magnitude. The culprit isn’t the MPP implementation but PostgreSQL’s executor, which works on rows. Each row travels up the node tree on its own, every value goes through fmgr, and the cluster’s segments merely add more processors running the very same loop over table rows.

The logical next step is a vectorized executor. Apache Cloudberry’s open-source code doesn’t have one: only traces of the closed-source engine remain in the repository, namely the create_vectorization_plan flag, which the open-source planner always passes as false, a WindowHashAgg node with no executor behind it, and a PAX adapter under VEC_BUILD that hasn’t compiled for a long time. I would have to design it myself and, of course, as an extension once again.

That is how pg_vexec came about, a family of extensions for PostgreSQL 19:

• vexec: a vectorized planner and executor in one module;
• vexec_flight: an Arrow Flight SQL endpoint that serves and accepts Arrow while the vectorized executor is on;
• vexec_pgvector and vexec_postgis: kernel packs that let pgvector’s and PostGIS’s functions compute over batches.

The project doesn’t require modifying PostgreSQL, even with the ORCA planner. The gp_orca module from the Cloudberry port now builds on its own, without gp_core and without core patches, and vexec turns ORCA into a vectorized engine: ORCA’s search weighs its alternatives with the costs of vector nodes, and its translator builds those nodes directly. Core patches are needed only if Cloudberry itself is loaded into the server. Then vexec can also read the columnar PAX and ao_column tables column by column and write to them the same way, without turning the data into rows, and between segments the Motions carry data as Arrow IPC frames.

Once again Claude Code wrote the code for me, in parallel sessions, e

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: