Latest Blog Posts

Here is a query that shows up in every analytics workload:

SELECT count(DISTINCT user_id) FROM events;

It looks like the cheapest possible thing: count the distinct users. On a machine with cores to spare you would expect Postgres to throw a few parallel workers at it, the way it does for almost any large scan. It does not. That one keyword, DISTINCT, switches off parallel query for the entire statement, and the larger your table the more it costs you. No setting or index changes that; the reason is in how the aggregate has to execute.

The schema

Ten million events, about fifty thousand distinct users, a handful of countries. Nothing unusual.

CREATE TABLE events (
    id      bigint GENERATED ALWAYS AS IDENTITY,
    user_id int    NOT NULL,
    country text   NOT NULL,
    amount  numeric(10,2) NOT NULL
);

INSERT INTO events (user_id, country, amount)
SELECT (random()*50000)::int + 1,
       (ARRAY['US','DE','GB','FR','JP','BR','IN','CA'])[(random()*7)::int + 1],
       (random()*500)::numeric(10,2)
FROM generate_series(1, 10000000);

ANALYZE events;

max_parallel_workers_per_gather is at its default of 2 on fresh cluster. For these examples I raised it to 4 and work_mem to 64MB, so there's no resource starvation to blame for the plans below.

Two counts, two different plans

Start with a plain count(*), which has nothing to deduplicate:

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM events;

 Finalize Aggregate (actual rows=1.00 loops=1)
   ->  Gather (actual rows=5.00 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Partial Aggregate (actual rows=1.00 loops=5)
               ->  Parallel Seq Scan on events (actual rows=2000000.00 loops=5)

Four workers plus the leader (loops=5) each scan their slice and keep a running count, and the leader adds the five partial counts together at the end.

Now add one word:

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS) SELECT count(DISTINCT user_id) FROM events;

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=

[...]

Jacob Jackson at ByteofDev built something I can only describe as admirably irresponsible (and something I wish I had thought of first): claudegres, a “PostgreSQL” in which Claude is the entire backend. Not Claude tuning the database, and not Claude writing queries against the database. Claude as…

Vacuum is not a problem as we can help PostgreSQL avoid it with HOT updates. A HammerDB TPROC-C benchmark shows how tuning fillfactor for tables eliminates vacuum and reduces the bloat growth.

Idle sessions consume connection slots and backend memory—not locks or xmin. Learn when and how to reap them without sabotaging your connection pooler.

If you are a traditional database administrator (DBA) transitioning to Kubernetes, the cloud-native ecosystem can feel like a series of personal attacks on your workflow.

For years, your terminal was your home. If a query was running slow, you SSH’d into the VM, opened /var/lib/pgsql/data/postgresql.conf in vim, bumped up shared_buffers, and ran a quick pg_ctl reload. If you needed to lock down access for a new application server, you hopped into pg_hba.conf, appended a new host rule, and went about your day.

Then came Kubernetes. No SSH. No direct file access. Pods are ephemeral, and manually editing files inside a container is a capital offense in the world of GitOps.

Naturally, you’re asking: "How on earth am I supposed to tune my buffers and manage client authentication if I can't touch the configuration files?"

Enter CloudNativePG (CNPG). In this post, we will look at how this enterprise-grade Postgres operator translates declarative Kubernetes YAML into optimized, production-ready Postgres settings and why this "hands-off" approach is actually the best thing to ever happen to your database configuration.

The Mental Shift: Imperative vs. Declarative

In a traditional environment, configuration is imperative: you run commands and edit files directly to achieve a desired state.In Kubernetes, configuration is declarative: you write down what you want the final state to look like in a YAML file, and the operator (CNPG) works tirelessly in the background to make it happen.

Action	Traditional VM Way	The Cloud-Native Way (CNPG)
Edit postgresql.conf	SSH + manually edit the text file.	Update spec.postgresql.parameters in YAML.
Edit pg_hba.conf	Append host rules to the file on disk.	Update  spec.postgresql

[...]

Replication slots promise to keep WAL forever—until PostgreSQL 18's new `idle_replication_slot_timeout` puts an expiration date on abandoned ones before they…

“The database is locked up.” We heard some version of that sentence more than once this past week, from the same team, about what looked like the same problem. It was not the same problem. Once, Postgres had genuinely stopped responding. Every other time, Postgres was fine, and a connection sitting in the application’s pool had quietly died somewhere between the app and the database.

Both failures produce the same page at 2am: the app cannot reach the database. Only one of them means the database is actually in trouble. Mixing the two up costs you the most expensive resource in an incident, which is the first ten minutes, when you are still deciding what kind of problem you have.

A true PostgreSQL lockup means the server itself has stopped responding at the operating system level. You cannot open a new session against it, from any client, from anywhere. A stale connection problem means Postgres is healthy and reachable. The trouble is a specific connection already sitting in your application’s pool, pointed at a socket that died somewhere along the way, usually without either side getting a clean signal that it happened.

What a true lockup actually looks like

This one is rare, and when it happens, it is unambiguous. Every attempt to open a fresh session hangs or refuses, including a brand new psql connection run directly on the box. It does not matter which application, which pool, or which network path you try. Nothing gets in.

The usual causes sit below Postgres itself: the OS is out of memory and the kernel is reclaiming pages, a storage volume has stopped responding to I/O, or a runaway process has pinned every CPU core hard enough that even accepting a new connection cannot get scheduled. Whatever the trigger, the signature is consistent. Existing sessions may still be limping along, but nothing new can start.

What a stale connection actually looks like

This one is far more common, and it hides well because it looks identical from the app’s point of view. Somewhere between your

[...]

Idle transactions hold locks and block vacuums, turning one forgotten connection into a cascading outage.

ident_file tells the server where pg_ident.conf lives, but when it's wrong, the server just logs a quiet failure and keeps running.

`hot_standby_feedback` trades primary bloat to prevent query cancellations on standbys—but it only stops cleanup conflicts, not lock or drop conflicts, and…

That's 320 milliseconds vs 671 ms for exact COUNT(DISTINCT) which is about twice as fast on a single scan. Also HLL isn't just about raw speed on one query. The function names are a bit verbose but the pattern is always the same: hash the value, aggregate the hashes into an HLL, then ask for the cardinality.

Preaggregate for instant queries

The real power of HLL is that sketches are mergeable. This is the key concept that makes them different from just "a faster COUNT(DISTINCT)." You can build a separate HLL sketch for each day, store those sketches in a table, and then union them together at query time to get distinct counts across any date range.

The daily_hll table has one row per day. Each row is tiny — the HLL column in this example is about 1.3 KB per row regardless of how many users it represents. And querying this small rollup table instead of scanning millions of raw rows is what gets you from hundreds of milliseconds to sub-millisecond.

HLL sketches work as window functions too, making rolling unique counts trivial:

	TABLESAMPLE	HLL (postgresql-hll)	DataSketches
What it does	Reads a random subset of rows	Distinct counting	Distinct counting, set ops, quantiles, heavy hitters
Extension required	No (built-in since PG 9.5)	hll	datasketches
Precomputable	No (scan-time only)	Yes	Yes
Mergeable	No	Yes (hll_union_agg)	Yes (all sketch types)
Distinct counts	Poor (overcounts when extrapolated)	Yes	Yes (CPC, Theta)

[...]

 

The PostgreSQL Performance Farm is still not quite a reality, but I think there is some hope on the horizon.

After some discussions with various folks at AWS about large scale OLTP testing with PostgreSQL on EC2, I was introduced to Farrah Campbell last spring who has been helping open source software maintainers.  She hooked me up with some credits that I used to sign up for Kiro Powers.  It's been quite a while since I have done any significant performance testing and the OLTP test kits we developed back in the OSDL days could use some updating, in particular this DBT-5 kit.

Shortly after that, I caught up with Mila Zhou about the AWS Open Source Credits Program because doing any large scale OLTP testing in the cloud is going to take a serious amount of credits.  As luck would have it, I was granted enough credits to start working on sizing up a system.

I'm currently thinking a TPC-E-like workload might be a good stress test for PostgreSQL on EC2 with its more balance i/o to processing requirements compared to a TPC-C, so I've started sizing up a r5b.4xlarge instance type with as many block devices as it can attach to

Keep an eye out here and on Blue Sky as I start posting updates over the coming weeks.

On 16th of March 2026, Peter Eisentraut committed patch: SQL Property Graph Queries (SQL/PGQ)   Implementation of SQL property graph queries, according to SQL/PGQ standard (ISO/IEC 9075-16:2023).   This adds:   - GRAPH_TABLE table function for graph pattern matching - DDL commands CREATE/ALTER/DROP PROPERTY GRAPH - several new system catalogs and information schema views - … Continue reading "Waiting for PostgreSQL 19 – SQL Property Graph Queries (SQL/PGQ)"

I'm pleased to announce that David Rowley will be joining us in September to discuss his talk Optimizing code in the hot path; with examples from tuple deformation. If you're interesting in joining us, please sign up using this form and I will send you an invite to one of the sessions. As always, thanks to David for agreeing to join the sessions.

Read more »

PostgreSQL 18 adds a Grand Unified Configuration (GUC), , that lets an extension's control and SQL files live outside the server's own directories. Together with Kubernetes's ImageVolume and Docker's , that makes it practical to package an extension as its own small OCI container image and mount it into the PostgreSQL pod at runtime, without rebuilding the server image.The idea is compelling. A fleet of clusters sharing one lean, unmodified PostgreSQL image. Extensions versioned and upgraded independently. pgvector 0.8 today, 0.9 tomorrow, without touching the server layer - sounds clean.It is clean - for the right extensions. For others, the container boundary doesn't actually decouple anything meaningful. Whether the extension deserves its own container depends almost entirely on how deeply it is wired into the server's startup sequence, process tree, and storage subsystem. This post maps out that landscape.

What PostgreSQL 18 Actually Changed

Before PostgreSQL 18,  already let you place shared libraries (. files) outside the compiled-in . While  took care of the binary side of an extension, the gap was the control side: extension control files (.) and SQL scripts had to live in  with no override mechanism. Any extension that wasn't installed in the server's own share directory simply could not be found by .PostgreSQL 18 closes that gap by adding  - a GUC that works exactly like  but for control and SQL files. Set both GUCs together, and an extension can live entirely outside the PostgreSQL installation:One subtlety that tripped us up during testing:  takes sharedir-level paths, not the extension subdirectory directly. PostgreSQL appends  automatically when scanning for .control files. Specifying the full path to the extension subdirectory silently finds nothing, not even built-in extensions like plpgsql.The  placeholder resolves to the compiled in sharedir (the path returned by ). It must appear in extension_control_path or the server loses access to all its own built-in extensions. The same logic appli[...]

The official logs are still processing, but the raw data is ready.

Welcome to the newest #UNLOGGED drop from WAWTech+Summer 2026 in Warsaw. We traded the classic conference center for an open-air tech festival at Tor Służewiec, and the energy was incredible.

This uncompressed cut captures the pure hallway track reality—setting up the Postgres booth with the international team, taking the stage for my talk on “From Hops to Vectors: Building AI-Powered Beer Recommendations in Postgres,” and the inevitable post-event fast food run.

Skip the WAL. Enjoy the raw feed. Link to the full video on YouTube.

Postgres ships with a genuinely bountiful catalog of built-in functions. There are literally thousands of them, covering everything from trigonometry to JSON path queries to full-text ranking. It's a kitchen sink of staggering proportions. Somehow despite all of that, there are actually still a few gaps to fill.Need a random date for some test data? That's going to be an entire expression, I'm afraid. How about the exact definition of a role, tablespace, or database? You better have a GUI database tool like pgAdmin or access to the  command with a little assistance from .It's little annoyances like this that Postgres 19 aims to alleviate. Let's explore these new quality of life enhancements!

In the Near Future

Suppose we want a random date sometime in 2026. The canonical approach for this has been to begin with January 1st and add a random amount of days:It's kind of a hack, but it works. Aside from not accounting for leap years, there's also something else to take note of:We asked for a date and got a , because adding an interval to a date promotes the whole expression. If we actually wanted a , we have to wrap the result in another cast. If we wanted a , that's a different cast. That's not exactly critical, but type sensitivity varies across platforms.Either way, that's a lot of ceremony for "give me a random date."So Postgres 19 adds a family of  overloads that take an explicit lower and upper bound. The  we all know and love is still around in the mathematical functions, but now there are three new temporal definitions in datetime functions.There's one for each of the valid Postgres date and time types:A random date is now just , and the result is a real . No interval multiplication, trailing cast, or surprise timestamp conversion. Ditto for  and . The inclusive bounds also address the issue we had with leap years, where adding 364 days could mean never reaching December 31st.You may have also noticed the matching significant figures in the  output. If you haven't ever used that before, it works univer[...]

PostgreSQL's hot_standby switch transforms a spare server into a readable replica, but the real tuning work happens elsewhere.

Most production vector queries are not simple nearest-neighbor searches. Rarely is the query to return "the 10 most similar documents in the entire table." Typically, it's something closer to: find the 10 most similar documents in the legal category, published in the last 30 days. That mix of similarity ranking plus scalar filters is hybrid search.

We have written before about HNSW indexes with pgvector and scaling vector data. Those posts go over indexes used to accelerate Nearest Neighbor queries with Approximate Nearest Neighbor indexes (ANN indexes). The next problem shows up the moment a WHERE clause is added. pgvector's iterative index scans help with filtered search, but they come with some tuning and tradeoffs.

When nearest neighbor meets a WHERE clause

Here is the query almost everyone writes first:

SELECT id, content
FROM docs
WHERE category = 'legal'
ORDER BY emb <=> '[0.031, ...]'
LIMIT 10;

It looks innocent. With a typical B-tree index, a WHERE plus ORDER BY is a solved problem: the planner picks an index, applies the filter, sorts what is left, and you move on.

A vector index finds nearby embeddings; a WHERE clause filters rows. Combining them forces Postgres to sacrifice either recall or performance.

The natural follow-up question is: why not just intersect the indexes? Postgres already knows how to combine two B-trees. Scan each index, build bitmaps of matching row IDs, BitmapAnd them together, and done. If you have an index on category and an index on emb, why can't the planner find the rows that are both legal and near the query vector the same way?

Because the two indexes are not answering the same kind of question.

A B-tree on category returns a set. The predicate category = 'legal' is a yes-or-no membership test. Every qualifying row ID goes into the set. That shape is perfect for intersection: set of legal rows, set of active rows, or tenant = 42, or (you get the point).

An HNSW (and IVFFlat) index returns an approximate ordered top-k, not a set. ORDER

[...]

High availability setups are never “set and forget”. Every new release of your tooling can change how your cluster behaves at 03:00 when something breaks.

vip-manager v5 is one of those releases you really want to read about before just hitting apt upgrade. In this post I’ll walk through the important breaking changes, what they mean in practice, and what you should do before rolling this out on production.

---

Quick reminder: what vip-manager does

vip-manager is the small helper that manages a Virtual IP (VIP) in front of your PostgreSQL primary:

• It watches the Distributed Configuration Store (DCS) / leader info (patroni, etcd, Consul, ZooKeeper, etc.).
• When a node becomes leader, vip-manager attaches the VIP to it.
• When it loses leadership, vip-manager removes the VIP.

Clients connect to the VIP, not to the individual node. If the VIP is wrong or stale, your applications hit the wrong PostgreSQL instance. That’s why these behavior changes matter.

---

Breaking change #1: configuration refactor

The configuration handling in vip-manager v5 has been refactored and deprecated parameters have been removed.

In other words: if you still rely on old, deprecated keys in vip-manager.yml, v5 may fail to start or behave differently than you expect.

What you should do

Before upgrading:

1. Open your config
   Check your current vip-manager.yml on all nodes where vip-manager runs.

2. Compare against current documentation
   Use the v5 docs or sample config and line them up with your file.

3. Remove deprecated parameters
   Any parameter that is no longer recognized must go. Do not assume “it will just ignore it” — future refactors often get stricter.

4. Rename to current keys
   Where keys have been renamed, update to the new names instead of keeping legacy aliases.

If you keep your configs in Git (you should), this is a good moment to commit a clean, documented vip-manager.yml so that

[...]

hba_file points to your authentication rules, not the rules themselves. Reload changes to pg_hba.conf instantly; restart when moving the file.

What Is Open Alliance for PostgreSQL Education (OAPE)?

The OAPE is the recently founded organistion that will offer indipendent community PostgreSQL certifications.

Why Becoming A Supporter

For years there have only been companies offering PostgreSQL certifications.

I always believed, that the PostgreSQL community could do better with community based certifications.
A community based certification has the advantage, that even the process itself becomes public.

The program needs people doing the work and to create the exams and the certifications.

First Chance To Become A Certified PostgreSQL DBA

The PostgreSQL Conference Europe 2026 has an addition Community Day. On this day the OAPE offers the PostgreSQL Open Certification Level: DBA-1.

You have to register yourself for the Community Day to get certified on that day. For details please see OAPE Meetup and the Exam.

 DBeaver recently introduced interactive chat capabilities into the free, open-source Community Edition. What does that mean? 

It means some of your database tasks can be accomplished significantly faster! 

First, you can ask for information without coding SQL. I love Structured Query Language, but it is much faster to write this prompt:

What are the top ten most popular rentals and how much revenue did they generate?

[...]

Background

I've been working on pgjdbc, the PostgreSQL JDBC driver, for many years. It's a great driver, gets millions of monthly downloads, and it's not going anywhere.

It's also a driver whose shape was decided a very long time ago. JDBC came first and PostgreSQL came second. The JVM it was designed for had no virtual threads, no records, and no sealed types. Most of what you'd want to change about that today can't be changed in place. Doing so would break the applications that depend on the current behavior, and that's most of the Java world talking to PostgreSQL.

So I started a new one: pg-java.

It's a modern, PostgreSQL-specific driver for the JVM, and it is pre-release. But the core driver works, there's a JDBC layer on top of it, and it's been tested far more thoroughly than "pre-release" usually implies.

Why a new driver

Four things drove the design.

PostgreSQL-first. The native API is designed around PostgreSQL's wire protocol and feature set. It is not designed around the lowest common denominator that JDBC has to support across every database on earth. If PostgreSQL can do it, the API should be able to say it directly.

JDBC as a layer, not a foundation. Full JDBC compliance is a long-term goal. There's already a java.sql.* layer that registers a Driver and gives you Connection, PreparedStatement, ResultSet, DatabaseMetaData,

[...]

Hash and sort operations have wildly different relationships with memory, and `hash_mem_multiplier` lets you feed them separately.

Seven major versions of PostgreSQL shipped between 2018 and 2025, one per year without exception, and each with a changelog of 150 to 200 user-visible changes. Each release covers a broad canvas — performance, replication, administration, and security — but every one of them also advanced the SQL layer, filling gaps in the standard, adding missing functionality, or cleaning up long-standing rough edges. Working through the new edition of The Art of PostgreSQL forced me to catalogue them all; this is my selection of the features I kept reaching for while rewriting the examples. I hope it’s useful beyond that context — organized by theme, with the version each feature landed in.

To get a sense of where the community actually puts its effort, Noriyoshi Shinoda of Hewlett-Packard Enterprise Japan has been publishing a meticulous “PostgreSQL New Features with Examples” series for every major release since version 9.4 — each edition catalogues every user-visible change with a working code example. Counting his categories across PG 11–18 gives a clear picture of contributor priorities:

Release	SQL	Performance	Admin & Ops	Replication	Security	Other
PG 11	5	6	5	3

[...]

Picture this: it's Monday morning, your coffee is still warm, and someone from finance slides into your DMs asking why the Q4 revenue number doesn't match what they expected. You know the data flows through about five transformation steps, but the pipeline was built by someone who left two years ago, and the documentation is... let's say aspirational.

So you do what any engineer does: you start digging. You grep through ETL scripts. You trace foreign keys. You find a view that references another view that references a table that might have been renamed at some point. An hour later, you're not sure if you've found the answer or just found more questions.

This is the data lineage problem. And if you've been around data systems long enough, you've lived it.

What even is data lineage?

Let me give you the textbook definition first, then I'll translate it into something that doesn't make your eyes glaze over.

Data lineage is basically a paper trail for your data. It answers:

• Where did this come from? (Provenance - the what and where of your data's origins)

• What else depends on this? (Dependency - the blast radius of a change)

• What happened to it along the way? (Transformation history - the mutations it went through)

• If I change this upstream, what breaks? (Impact analysis - the "oh no" question)

Think of it like a family tree, but for your data. Every value has parents (the data that created it), grandparents (the data that created the parents), and so on back to the original source. And like family trees, it gets complicated fast once you go back more than a couple generations.

Why should you care? (Besides "it's the right thing to do")

Here's where it gets real. Regulations actually require this stuff:

• GDPR Article 5(2) - remember that one? It says people have the right to understand "the logic involved" in processing their data. You can't explain the logic if you can't trace where the data went.

• SOX - if you're in

[...]

PostgreSQL 16 lets servers accept delegated Kerberos credentials to act as users against other services, but it defaults to off because the trade-off is real…

Why Enterprise Platforms Often Fail Long Before They Break

The examples in this article are anonymized, and certain details have been adjusted to protect the organizations involved.

---

The Crossroads Nobody Schedules

Every enterprise platform eventually reaches a crossroads.

Not because it stops working. Because it slowly becomes harder to change.

The warning signs almost never arrive as outages. They arrive as hesitation.

Upgrades get postponed to the next quarter, and then the quarter after that. Deployment windows stretch from two hours to six. Recovery exercises get scheduled, then quietly moved. Architects begin designing around the platform instead of with it — a new service here, a side database there, an integration layer that was only ever meant to be temporary.

And then someone in a design review says the six words that should concern every technology leader:

“Let’s not touch that.”

Nothing appears broken. Availability is excellent. The dashboards are green. Leadership believes the modernization succeeded, and by every metric on the executive dashboard, it did.

Yet confidence has quietly begun to disappear.

Across banking, insurance, telecom, and healthcare, this is how most enterprise platforms actually accumulate risk. Not through catastrophic failure. Through the gradual erosion of trust. The platform doesn’t collapse — it calcifies. And calcification is more expensive than an outage, because an outage ends and calcification compounds.

That observation is what led me to develop the CALM Platform Test.

Not another maturity model. Not another operational checklist. A way of answering a question most organizations never think to ask:

Does this platform become easier to trust as it grows?

That question has become urgent in a way it wasn’t five years ago. We are placing AI workloads — retrieval, embeddings, agents, workflows that take action on behalf of the business — on top of platforms that were never evaluated for their capacity

[...]