Neues vom PostgreSQL Planet

Setting the tenant for PostgreSQL row-level security in Go with pgx 5.11, measured: pgxpool, PrepareConn hooks, transactions, batches and PgBouncer.

On August 25, Robert Haas opened a pgsql-hackers thread like this: I asked Claude to evaluate which v19 patches were the scariest based on the number and type of bugs fixed post-freeze. Results below, with a few particularly cutting remarks from the LLM edited out. The subject line was “scary p… (I consult on PostgreSQL through PGX Inc..)

pg_statviz can now ask Claude, a local Ollama model, OpenAI or Gemini to explain your PostgreSQL statistics on every distro we ship it for. Getting there took three days and a few dozen new Python packages

pg_statviz can now ask Claude, a local Ollama model, OpenAI or Gemini to explain your PostgreSQL statistics on every distro we ship it for. Getting there took three days and a few dozen new Python packages

On 7 October, the London PostgreSQL Meetup group met.

Organizers:

• Valeria Kaplan
• Chris Ellis
• Alastair Turner

Speakers:

• Elizabeth Christensen
• Alex Kasko
• Jimmy Angelakos

On 8 October the PostgreSQL England North Meetup Group. Joseph Bharath Reddy Allam delivered a talk.

Override PostgreSQL's prepared statement plan choice when its cost estimates go wrong on skewed data. (I consult on PostgreSQL through PGX Inc..)

For anyone with an interest in the long-term health and longevity of the broader ecosystem of Postgres extensions and tools, we’d like to hear your ideas and inspirations for its assurance.

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.

What `password_encryption` actually controls: how PostgreSQL hashes new passwords when you hand it clear text. (I consult on PostgreSQL through PGX Inc..)

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..)