Neues vom PostgreSQL Planet

Most exploratory questions against a large table only need a rough answer, but without an index on status, even "roughly how many shipped orders" costs a full scan. On the two-million-row orders table, Postgres has to read every single 8 kB page, all 18,085 of them (141 MB), just to count matching rows.

Whether your query's leader process joins the work or just supervises depends on this boolean, and the performance question it settles is subtle. (I consult on PostgreSQL through PGX Inc..)

PostgreSQL's parallel query planner charges two fees for going parallel: a setup cost per Gather node and a per-row cost for moving data from workers to… (I consult on PostgreSQL through PGX Inc..)

When PostgreSQL 17 raises 'infinite recursion detected in policy for relation', and what each fix costs on a million rows: from 0.09 ms to 6.6 seconds.

What raised 'new row violates row-level security policy for table' in PostgreSQL 17, measured: inserts, RETURNING, upserts, MERGE and four message forms.

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.

In October 2014, a developer wrote to a PostgreSQL mailing list with a problem. His employer’s open source office required a signed contributor license agreement before he could contribute, and he could not find PostgreSQL’s. Tom Lane answered: There are no such agreements for Postgres work. The… (I am not a lawyer, and this is not legal advice.) (I consult on PostgreSQL through PGX Inc..)

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.

Key Takeaways 

• Signal 1 (SIGHUP) normally tells PostgreSQL to reload its configuration. A log line saying a process was terminated by signal 1 means that process didn’t handle the signal and died from it.
• received SIGHUP, reloading configuration files is a harmless reload.

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.

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.

Tom Lane deleted `array_nulls` in PostgreSQL 20, but `pg_dump` never set it, so restores into databases with it disabled silently corrupt text arrays. (I consult on PostgreSQL through PGX Inc..)

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.

Yay, “That time of the year”™ again soon 🎉 This time maybe with a bit more fuzz / excitement / anxiety as usual - due to various obstacles surrounding the release as detailed in a few recent posts by various people. Not going into the “Is AI to blame or...

On September 30, AWS announced that Aurora PostgreSQL can now query Apache Iceberg and Parquet data directly. That brings the number of things a vendor might mean by “Postgres reads Iceberg” to at least four, and they have about as much in common as the four things a vendor might mean by “serverl… (I consult on PostgreSQL through PGX Inc..)

The numeric type has existed in Postgres for more than 25 years. Over that time, it has undergone many optimisations and refinements.

PostgreSQL 18's new `num_os_semaphores` preset reports how many OS semaphores the server needs—a formula that drifted wrong for twelve years until the… (I consult on PostgreSQL through PGX Inc..)

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.

Dive deeper into connection pooling with five more PostgreSQL poolers: Odyssey, pgagroal, Supavisor, PgDoorman, and ProxySQL, plus where your cloud already… (I consult on PostgreSQL through PGX Inc..)

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.