Latest Blog Posts

Citus already scaled the workers horizontally. Now the coordinators too

I’ve heard more than once that Citus is not a good sharding technology/architecture because it’s limited to one coordinator. That’s not completely untrue:

• A single coordinator can scale clusters to very large workloads with multiple workers. But the coordinator is the sole entry point and, at some point, it can become the bottleneck. It’s a ceiling.

• Citus supports querying workers directly since version 11, where workers behave as coordinators from a query routing/execution perspective. However, using workers as coordinators makes query routing and data processing contend for the same resources, which is far from ideal.

This post introduces the concept of Query Routers for Citus, an innovation that shipped in StackGres 1.19. StackGres already had a deep integration with Citus, making it easier than ever to create sharded clusters with Citus. Now it also adds the capability to scale coordinators horizontally; we call this Query Routers. And they remove the scaling limit that single-coordinator Citus has. With query routers there isn’t, in principle, any limit to scaling Citus.

The single coordinator limit

So where’s the limit, after all? Let’s first analyze the architecture. For standard Citus clusters, there was a single place where clients connect: the coordinator. For each query, the coordinator accepts the connection, parses the statement, plans it, works out which shard(s) hold the data to answer the query, opens or reuses connection(s) to the worker(s) that store the involved shards, forwards the query, and aggregates/relays/post-processes the result. All of that happens on one node.

Adding workers spreads the data and the storage work over more machines; the entry path still runs through one Postgres instance on one set of vCPUs, and the workers behind it can only be as busy as that one node allows.

This is the architecture of a typical (single-coordinator) Citus c

Plenty of Postgres services end up managed by application developers, whether they like it or not. Some dabble in databases on the side, and some inherited the job when the last DBA scampered off for greener pastures. Regardless, they’re put in charge of something they barely understand. Heck, that literally happened to me twice. Sometimes there never was a DBA. Sometimes, it's just you.I recently got a timely reminder of this when chatting with a colleague. Usually when it comes to Postgres, disk emergencies get traced back to the  directory. For an amateur or novice DBA, the first reaction might be to purge its contents in any way possible. What is all this WAL junk, anyway? It's just for crash recovery or something, right? The logs say the archive command is failing, so I can just set  to  and it'll flush all that out. Easy peasy.Oh how we all wish it were that simple. WAL is the single linchpin that keeps Postgres operational at all. It's the Durability in the ACID acronym commonly associated with relational databases. In Postgres, literally every write must pass through the WAL. But why would an app dev know that? Why would anyone aside from a seasoned DBA need to know it at all?Fortunately the reality of the situation is actually incredibly easy to convey. I always like to start with something pretty much everyone has: a bank account.

Balancing the Books

The buffers article followed an 8KB page from disk into shared memory and back. What it didn't do is name the file. Every page that passes through the buffer pool is block number N of some file under the data directory, and the file has a naming scheme, a size limit, and companion files that share its name. Before the next article reads the inside of a page, this one finds it on disk with ls and od, and shows that the bytes are the same ones.

Nothing here needs more than a scratch database and shell access to its data directory. The examples were captured on PostgreSQL 18.6 with data checksums on, which is the default since 18.

Setup

CREATE EXTENSION IF NOT EXISTS pageinspect;

CREATE TABLE files_demo (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text NOT NULL,
    value numeric(10,2)
);

INSERT INTO files_demo (title, value)
SELECT 'row_' || i, i * 1.5 FROM generate_series(1, 1000) AS i;

A thousand short rows and a primary key. The text column is there so a TOAST table gets created.

From table name to file name

The function that answers "where is this table on disk" ships with the server:

SELECT pg_relation_filepath('files_demo');

 pg_relation_filepath
----------------------
 base/5/16431
(1 row)

The path is relative to the data directory (SHOW data_directory prints it). Three parts: base is the directory for the default tablespace, 5 is the OID of the database, and 16431 is the file's name. That last number is the relfilenode, and it comes from pg_class:

SELECT oid, relfilenode, relname
FROM pg_class
WHERE relname LIKE 'files_demo%';

  oid  | relfilenode |      relname
-------+-------------+-------------------
 16431 |       16431 | files_demo
 16430 |       16430 | files_demo_id_seq
 16438 |       16438 | files_demo_pkey
(3 rows)

Three relations, three files. The identity column's sequence and the primary key index each get their own, because to the storage layer a sequence is one-page relation and an index is a relation like any other. The oid and relfilen

Many readers will be aware that an unusually high number of features have been reverted from the PostgreSQL 19 branch after the beta period began. Having some reverts is not unusual, maybe one per cycle could be expected. But this time around, about 8 to 10 significant features, depending on how you count, have been reverted, and some of them quite late in the beta period. I have been on the receiving end of some of that, as the developer or committer of some of those features, and have gotten some questions about it, and so I want to take a moment to reflect on this. I’ll try to extrapolate a little bit to similarly affected work that I was not actively involved in, but these are just my opinions at this point.

So what happened?

I don’t think we, meaning all the developers affected, have had enough time to fully reflect on all this. But here are some thoughts. It’s probably a combination of these, and a different combination in the case of each reverted feature.

• Maybe we just didn’t do a good enough job. Maybe the code was just not good enough. We’ll learn and improve. It’s probably some of that, and I’m mentioning it here to not give the impression that it was only the other factors that I’m going to mention.

• It could also be a coincidence. Maybe we had been on such a good roll that we attempted to tackle several features that turned out to be too hard, or at least too hard to finish in time.

• Some of the reverted features had significant architectural defects that would have been hard to fix quickly and during beta. But there was also a long tail of relatively harmless issues involving various edge cases. In past times, many of these would not have surfaced until much later and we would have fixed them bit by bit over the subsequent five years. But with LLM-assisted code review, these kinds of things can get found much faster, and then you’re staring at a list of like forty defect reports. And even if each of them requires only a three-line

Most slow queries I have looked at are not doing anything clever. They are doing extra work that nobody asked for, because one word in the SQL told PostgreSQL to.

I took five of those words and measured each one against the version without it, on the same table. The biggest gap was UNION: 13.6 seconds, against 0.68 seconds for UNION ALL on the same two halves of the table.

Setup

One table, u, with 2,000,000 rows: a bigint identity key, an email, a status, a timestamptz and an md5 note.

PostgreSQL 18.6, default settings (work_mem 4MB, shared_buffers 128MB, up to two parallel workers per query). Machine: 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM. Every time below is the execution time from EXPLAIN (ANALYZE, TIMING OFF), warm cache, first run thrown away, median of the rest.

Results

UNION

I split the table at id = 1,000,000 and put the halves back together. With UNION ALL that is just reading the rows. Wi

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.

Last week I compared UUIDv4 and UUIDv7 as primary keys. There is a third case I left out: the key is a UUID, but the column is text. Some ORMs and JSON-first codebases end up there because a string is the easy path.

So I kept the same test and changed only the column type. 5 million rows, UUIDv7 and UUIDv4, each stored as uuid and as text. Short answer: the text index was 88% bigger, one level deeper, and every random lookup read one more page.

Setup

Same as last time. One table per run:

CREATE TABLE t (id  PRIMARY KEY, payload text NOT NULL);

The key was one of:

• uuid DEFAULT uuidv7()
• text DEFAULT uuidv7()::text
• uuid DEFAULT gen_random_uuid()
• text DEFAULT gen_random_uuid()::text

5,000,000 rows in 50 committed batches of 100,000, then VACUUM ANALYZE, a checkpoint, and two read queries. Three runs each on a fresh table, medians below. Machine: 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM, SSD. PostgreSQL 18.6 with OpenSSL (I checked this time), default settings, database collation C.

Results

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