https://postgr.es/p/7hp

In PostgreSQL 18, the statistics & monitoring subsystem receives a
significant overhaul - extended cumulative statistics, new per-backend I/O
visibility, the ability for extensions to export / import / adjust statistics,
and improvements to GUC controls and snapshot / caching behavior. These changes
open new doors for performance analysis, cross‑environment simulation, and
tighter integration with extensions. In this article I explore what’s new, what
to watch out for, Grand Unified Configuration (GUC) knobs, and how extension
authors can leverage the new C API surface.

Introduction & motivation

Statistics (in the broad sense: monitoring counters, I/O metrics, and planner /
optimizer estimates) lie at the heart of both performance tuning and internal
decision making in PostgreSQL. Transparent, reliable, and manipulable
statistics, among other things, allow DBAs to address the efficiency of
PostgreSQL directly, as well as enable “extensions” to improve the user
experience.

That said, the historic statistics system of PostgreSQL has not been without
points of friction. These include limited ability to clear (relations)
statistics, metrics with units that don’t always align with user goals, and no C
API for using the PostgreSQL Cumulative Stats engine. PostgreSQL 18 addresses
these concerns head on.

Below is a summary of the key enhancements.

A warning on stats

While statistics offer incredible value, their collection can take up
significant time and resources. PostgreSQL 18 introduces an important
consideration: with the expanded range of collectible metrics, the hash table
maximum size has been increased. Do keep in mind, especially if you’re designing
large-scale systems with table-per-customer architectures, that 1GB ceilings
have been shown to be hit with some millions of tables.

What’s new with PostgreSQL 18 and “stats”

Here are the major new or improved features relating to statistics and
monitoring. Each item links to the relevant

https://postgr.es/p/7hp

https://postgr.es/p/7hs

If you are reading this hoping that PostgreSQL finally got UPDATE ... LIMIT like MySQL, I have to disappoint you. The LIMIT clause is not yet supported for DML statements in PostgreSQL. If you want to UPDATE only a limited number of rows, you have to use workarounds. This article will describe how to do this and how to avoid the pitfalls and race condition you may encounter. Note that most of the following also applies to DELETE ... LIMIT!

An example for how we would like to do UPDATE ... LIMIT

The following table contains 100 categories of 100 entries each. Since we don't discuss persistence, an UNLOGGED table will do:

CREATE UNLOGGED TABLE upd (
   id bigint GENERATED ALWAYS AS IDENTITY,
   category integer NOT NULL,
   flag boolean DEFAULT FALSE NOT NULL
);

INSERT INTO upd (category)
SELECT (i - 1) / 100
FROM generate_series(1, 100000) AS i;

/* set hint bits, gather optimizer statistics */
VACUUM (ANALYZE) upd;

/* created after INSERT for performance */
ALTER TABLE upd ADD PRIMARY KEY (id);

CREATE INDEX ON upd (category);

Now we would like to set the flag on a single row with category 42:

UPDATE upd
SET flag = TRUE
WHERE category = 42
LIMIT 1;

But alas, we cannot do that. We'll have to look for other ways to achieve our goal.

Attempt to solve UPDATE ... LIMIT with a simple subquery

Since there is a LIMIT clause for SELECT, we can use a subquery as follows:

UPDATE upd AS u
SET flag = TRUE
WHERE u.category = 42
  AND u.id IN (SELECT id FROM upd AS u2
               WHERE u2.category = u.category
               LIMIT 1);

This query works as intended if nobody else is using the table. But it can fail to work as expected in the face of concurrent data modifications:

BEGIN;

DELETE FROM upd WHERE id = 4201;

Now run our proposed update statement in a concurrent session, and it will hang. Now, if you COMMIT the DELETE, our UPDATE statement will delete nothing:

UPDATE 0

How did this happen? It is true that the entire query sees the same state

https://postgr.es/p/7hs

https://postgr.es/p/7hq

PostgreSQL 18 was released on schedule this week!

Development continues apace, but no user-visible features were committed this week. See the Commits of Interest list below for more details on changes.

more...

https://postgr.es/p/7hq

https://postgr.es/p/7ho

This is a follow‑up to the last article: Run Jepsen against CloudNativePG to see sync replication prevent data loss. In that post, we set up a Jepsen lab to make data loss visible when synchronous replication was disabled — and to show that enabling synchronous replication prevents it under crash‑induced failovers.

Since then, I’ve been trying to make data loss happen more reliably in the “async” configuration so students can observe it on their own hardware and in the cloud. Along the way, I learned that losing data on purpose is trickier than I expected.

Methodology and a Kubernetes caveat

To simulate an abrupt primary crash, the lab uses a forced pod deletion, which is effectively a kill -9 for Postgres:

kubectl delete pod -l role=primary --grace-period=0 --force --wait=false

This mirrors the very first sanity check I used to run on Oracle RAC clusters about 15 years ago: “unplug the server.” It isn’t a perfect simulation, but it’s a simple, repeatable crash model that’s easy to reason about.

I should note that the label role is deprecated by CNPG and will be removed. I originally used it for brevity, but I will update the labs and scripts to use the label cnpg.io/instanceRole instead.

After publishing my original blog post, someone pointed out an important Kubernetes caveat with forced deletions:

Irrespective of whether a force deletion is successful in killing a Pod, it will immediately free up the name from the apiserver. This would let the StatefulSet controller create a replacement Pod with that same identity; this can lead to the duplication of a still-running Pod

https://kubernetes.io/docs/tasks/run-application/force-delete-stateful-set-pod/

This caveat would apply to the CNPG controller just like a StatefulSet controller. In practice, for my tests, this caveat did not undermine the goal of demonstrating that synchronous replication prevents data loss. The lab includes an automation script (Exercise 3) to run the 5‑minute Jepsen test in a loop for ma

https://postgr.es/p/7ho

https://postgr.es/p/7hm

more...

https://postgr.es/p/7hm

https://postgr.es/p/7hl

 The PostgreSQL 18 Release Notes are like a great noir novel, full of surprises and intrigue. But we know who did it - the amazing PostgreSQL community.

If you have not perused the Notes, I advise you to do so now. They contain many of what I call 'Wow!' level items, such as the redesigned I/O subsystem, skip scan lookups, virtual generated columns, and more.  The new Uuidv7 function and temporal constraints would be a significant leap forward. The OLD and NEW support for RETURNING will help lessen the learning curve for many who are new to SQL.

But go down to the seemingly more mundane items. Removing redundant self-joins, merge-joins can now use incremental sorts, and internally reordering the keys of SELECT DISTINCT to avoid sorting are the types of improvements that are not as attention-getting as the items in the preceding paragraph, but will make life much nicer for us. 

Each release of PostgreSQL is faster than the previous one, and early reports report that this has happened again. This is an astounding delivery for those of us with backgrounds in other databases. All too often, new features came with a trade-off in performance that relegated upgrades to the back burner.

Please read the Acknowledgments. This is a list of the good folks who have worked very hard on our behalf to deliver an improved version of PostgreSQL. And they are already working on 19! If you run into any of them, please give them a big 'thank you'.  

https://postgr.es/p/7hl