Latest Blog Posts

PostgreSQL 15's `recursive_worktable_factor` helps you fix slow recursive CTEs by tuning a single estimate. (I consult on PostgreSQL through PGX Inc..)

Yesterday, I spoke about pg_acm at PGConf.EU. Speaking at the biggest Postgres conference in the world is an honor in itself, and it’s not that I have a chance to present there every year, so I am incredibly thankful to the CfP committee for choosing this talk.

This talk has a long history of rejection and denial of the fact that something could be wrong with Postgres, which made me appreciate this opportunity even more.

My presentation earlier this year at Tallinn PUG definitely helped me shape it better, and I think it went as well as it could have.

Not only do I have a dream, but I also have a request: please try pg_acm and give me feedback! From my perspective, the tool is complete, but I am sure other people will find issues. If you find them, please let me know! Please open an issue on GitHub!

https://github.com/hettie-d/pg_acm

Every major PostgreSQL upgrade I have done on a single primary came with the same conversation: how long can the application be down?  is fast, but it is still an outage, and you still have to rebuild or resync the standbys afterwards.A multi-master cluster gives you a different option. Spock is pgEdge's open-source PostgreSQL extension for multi-master logical replication, and with it every node takes writes, so you never have to upgrade a node in place. You add a brand new node on the new major version, let it join the cluster, move traffic to it, and retire the old one. Repeat for every node and the cluster ends up on the new version without the application ever losing a writable endpoint.Starting with PostgreSQL 19, community Postgres offers a blue-green style upgrade built on ,  and the new sequence sync. This is not really ZERO downtime upgrade because the writes need to be paused for a certain period of time. At cutover, writes have to stop while the last changes drain, sequences are synced and the application is switched over. For a critical application, that pause is still an outage.I tested this end to end on my two node pgEdge test cluster running PostgreSQL 17.10 with Spock 5.0.12, adding a PostgreSQL 18.6 node next to them. This post walks through exactly what I ran, including the things that tripped me up, so you can do the same thing on your own cluster.One note before we start. pgEdge Control Plane is pgEdge's declarative orchestration tool for managing Postgres clusters. If your cluster runs under it, use its built-in major version upgrade instead. It drives the same zero downtime add node process through its API.

The Starting Setup

Before I touched anything, the cluster was a plain two node Spock setup on PostgreSQL 17, running on two Rocky Linux ARM64 VMs. Both nodes take writes.The database is , with the usual  tables plus a couple of test tables, all in the  replication set. I put n3 and its standby on the same VM as n1 to keep the lab small. In production each node would be its own se[...]

Please enjoy the slides and transcript from Tuning Postgres for Analytics at PostgreSQL Conference Europe (PGConf.EU) in Valencia, Spain on 20 Oct 2026.

I'll add a link to the recording once it's available.

I've included just very brief alt-text for each slide image, but I've tried to add any links or other essential information into the body of the blog post.

Obviously, I don't know the specifics of your particular Postgres environment. But, as always, there are some generalisations that apply to most systems. So, just for today, we're going to imagine that you're running a small chain of popular coffee shops in Valencia. If you've seen any of my previous talks, you might start to notice a coffee and cake theme in lots of my talks, demos and examples!

You've implemented a state of the art system to manage your sales, stock, customer loyalty program etc. and you've carefully configured your production database for the needs of your day-to-day application workload.

Let's talk about what happens if you then need to run analytics queries against your application data. How can you do that without compromising the performance of your application?

First, a quick "about me":

I’ve spent my whole career working with databases; for the first 20 years or so as a database administrator. I’m currently a Senior Software Engineer on the Postgres extensions team at Snowflake, working mainly on the pg_lake set of extensions.

I’m actively involved in PostgreSQL Europe; amongst other things, leading the PGEU Diversity Committee and helping to organise pgconf.eu. I also write and present talks (about databases) at Postgres and Developer conferences.

If you want ideas for non-database-related conversation topics when you meet me in the hallway: I love being creative; crocheting cute things, sewing clothes and accessories, and 3D printing all sorts of random things. I also enjoy getting out and about on one of my bikes in the mountains where I live.

Back to the presentation.

Let's

[...]

Restore to the moment before the disaster using eight parameters that answer where replay stops, which timeline to follow, and what to do when you get there. (I consult on PostgreSQL through PGX Inc..)

We’re wrapping up our review of the March CommitFest for PostgreSQL 19. This fourth and final article in the series covers the changes related to SQL and to our DEV1 and DEV2 courses.

As a reminder, here’s the plan for our coverage of the final PostgreSQL 19 CommitFest:

Part 5.1 (QPT)

Part 5.2 (DBA1, DBA2)

Part 5.3 (DBA3, DBS)

Part 5.4 (SQL, DEV1, DEV2) -- you are here 🙂

For the earlier CommitFests, see: 2025-07, 2025-09, 2025-11, 2026-01.

Here’s what’s covered:

• INSERT … ON CONFLICT DO SELECT
• COPY FROM: replacing invalid values with NULL
• COPY TO: exporting data in JSON format
• jsonpath: new string methods
• IS JSON predicate with domain types
• postgres_fdw: remote transactions inherit READ ONLY and DEFERRABLE modes
• Integrity constraints: altering the enforceability of CHECK constraints
• Integrity constraints: changing the generation expression of virtual columns with CHECK constraints
• Casting between uuid and bytea, and base32hex format support
• PL/pgSQL: SELECT … INTO optimization
• tid_block and tid_offset functions
• TOAST: lz4 as the default compression method

...

In my last post, a friend of mine kept four databases in production. Ask a team like that for a knowledge graph, and you can guess the fifth: a triple store. Search over company data, an ontology of products or skills, facts for an LLM application: a knowledge base often means RDF and SPARQL, and usually a separate server next to the relational database.

That server has a cost. The facts it needs already live in PostgreSQL tables, so an ETL job copies them as triples, and the copy goes stale as soon as the job ends. Two databases mean two security models, two backup schedules, two failover procedures, and no transaction that spans a row and a triple.

So I built pg_knowledge_base, a PostgreSQL 19 extension that runs Oxigraph’s SPARQL engine inside the server, without core patches. Triples live in ordinary tables, existing tables are read as RDF through R2RML mappings, and one SPARQL query can use both. Oxigraph’s HTTP endpoints remain for any SPARQL client, while transactions, replication and backups come from PostgreSQL. As with my previous extensions, Claude Code wrote the code, while I made the architectural decisions and checked the results.

Why Oxigraph

Oxigraph is a graph database and RDF toolkit written in Rust, and its stated goal is “a compliant, safe, and fast graph database”. What matters for embedding is that its query stack is a set of small crates: spargebra parses SPARQL, sparopt optimizes it, spareval evaluates it, and sparesults and oxrdfio handle result and RDF formats. spareval evaluates over anything that implements its QueryableDataset trait, so pg_knowledge_base reuses Oxigraph’s parser, optimizer and evaluator and replaces only the storage: PostgreSQL tables instead of RocksDB.

Keeping the engine keeps its answers. Of the W3C SPARQL 1.1 query and update tests, 990 pass and none fails. Another 11 differ from the expected results exactly as Oxigraph’s own store does, mostly because it keeps typed literals as values ("01"^^xsd:integer comes back as 1), and 6 are skipped,

[...]

The PostgreSQL RPM repository has supported Amazon Linux 2023 for a while, but until this week it had no GIS packages there: a few weeks ago I removed the AL2023 build of the GIS stack, as the distribution does not ship several libraries our GDAL was built against (hdf5, netcdf, xerces-c, Qt and friends).

Taking a second look, all of those turned out to be optional GDAL drivers, so I put the whole stack back, and this time PostGIS also comes with SFCGAL. Everything is available for PostgreSQL 14 to 18, on both x86_64 and aarch64.


What is in the repository?

geos315: 3.15.0
proj98: 9.8.1
libgeotiff17: 1.7.4
librttopo: 1.1.0
libspatialite50: 5.1.0
gdal313 (with the Python 3.13 and Java bindings): 3.13.3
SFCGAL: 2.3.0
PostGIS (postgis33 ... postgis37): 3.3.10, 3.4.6, 3.5.7, 3.6.4, 3.7.0 RC
pgRouting: 4.0.2
ogr_fdw: 1.1.9
h3-pg: 4.5.0
postgis_tiger_geocoder: 2025.2

Amazon Linux 2023 ships its own GDAL 3.10, GEOS 3.13 and PROJ 9.6. Our packages do not replace them: as on RHEL, they are installed under their own prefixes (/usr/gdal313, /usr/geos315, /usr/proj98, ...), and PostGIS is linked against them through the rpath.

SFCGAL

SFCGAL (the library behind the `postgis_sfcgal` extension and the `CG_*` functions) needs CGAL and nlohmann's JSON library to build, and Amazon Linux 2023 has neither. So the repository now has two new build-time packages for
AL2023:

- **CGAL 6.1.2** (`CGAL-devel`, header-only, built without the Qt demos)
- **nlohmann_json 3.12.0** (`nlohmann_json-devel`)

SFCGAL 2.3.0 is built against them, and PostGIS 3.3 to 3.7 are built with SFCGAL support. You only need these two packages if you build software against CGAL yourself: installing PostGIS just pulls in `SFCGAL-libs`.

Installing

```
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/AL-2023-x86_64/pgdg-amazonlinux-repo-latest.noarch.rpm
dnf install postgresql18-server postgis36_18
```

(use `AL-2023-aarch64` on Graviton). Then, in your database:

```
CREATE EXTENSION postgis;
CREATE EXTENSION [...]

Postgres gives you no way to take back a single statement. When someone runs an UPDATE without its WHERE clause, recovery is a procedure rather than a command. You restore a copy of the database to the moment before the mistake, pull out the rows that were damaged, and reconcile them by hand against a live table that has kept moving since.This kind of recovery works, and teams do it. It also takes hours, needs your most senior person, and every step is a chance to make things worse while the table is still serving traffic. One second of bad SQL turns into an afternoon of careful work by the people you can least afford to tie up.Oracle users have Flashback for exactly this case, and it is really two features: one to read the past, one to put it back. Postgres has neither. Point in time recovery is what makes that procedure possible, but it recovers an entire cluster rather than a statement. You restore it alongside production and compare, because rewinding production itself would discard everything that happened after the mistake. I wanted something smaller and more precise, so I built pgVolvra.

What pgVolvra Does

pgVolvra records every row change on the tables you choose. For each change, it keeps the row as it was, and the row as it became, along with who made the change, when, and in which transaction. When something goes wrong, it uses that record to put the affected rows back, and it touches nothing else.The whole engine is plain SQL and PL/pgSQL. There is no compiled extension, no shared_preload_libraries, no superuser, and no access to the server's filesystem. That was a design constraint from the first day, and it is the reason the tool runs on managed Postgres at all.

Setting It Up

Installation is running one file against your database. It works from psql, from any SQL client, or from a provider's web console.Then you pick the tables you want covered. pgVolvra records nothing until you do this, and it cannot recover anything that happened before, so this step is the one that matters.That is the [...]

[...]

Create replicas with a single command, but master the procedure for replacing a failed primary—that's where things get tricky and installations break. (I consult on PostgreSQL through PGX Inc..)

Standby lagging on network storage? `recovery_prefetch` lets WAL replay ask the kernel to read pages before it needs them, turning seventeen minutes of… (I consult on PostgreSQL through PGX Inc..)

Yesterday, a colleague of mine opened a simple PR. The business needed a region on an existing table, so he added a text column to hold it. Clean, small, ready to review and merge. Database internals? No problem. This was going to be a five-minute approval. I did not approve it. Not because the code was wrong. The code was fine. I challenged the content: why a free-text column? Why not a domain, an enum, a text column with a check constraint, or a reference table?

Read replicas are one tool, but tuning a replica for one job and sending it another is a recipe for conflict. (I consult on PostgreSQL through PGX Inc..)

Another story of something I saw in the field which surprised me. Turns out recovery_prefetch can cause outages.

Something which has been happening quietly in the background all the time, but we didn’t notice until we looked close enough to see it.

After a lot of work I discovered this was partly a case where someone had dropped an email to the postgres hackers list years ago “hey what about this” and everyone thought “nah it can’t be that bad” … and now here we are seeing production outages!

Exhibit A: Feb 2021, while the feature was being developed, a year and a half before it shipped, Stephen Frost

• Issuing unnecessary prefetches for blocks we’ve already sent a prefetch for is arguably a concern even if FPWs are off but the benefit of doing the prefetching almost certainly will outweight that and mean that finding a way to address it is something we could certainly do later as a future improvement. I wouldn’t have any issue with that. Just doesn’t seem as clear-cut to me when thinking about the FPW-enabled case. Ultimately, if you, Andres and Munro are all not concerned about it and no one else speaks up then I’m not going to pitch a fuss over it being committed, but, as you said above, it seemed like a good point to raise for everyone to consider.

https://www.postgresql.org/message-id/20210214231815.GO27507@tamriel.snowman.net

Exhibit B: Mar 2025, clear report from the field, Michael Zhilin (Postgres Pro)

• One customer complained that physical standby has significant replay lag (few hours) on modern powerful bare metal servers. According to output of “top”, startup process and few kernel workers utilized CPU, mostly in kernel space. The incident has been resolved by disabling recovery_prefetch after quick analysis of perf and strace outputs. Actual XLog prefetching code doesn’t consider that shared buffer may be loaded from WAL FPI rather than from disk and makes fadvise calls in such cases.

https://www.postgresql.org/message-id/9bdb428a-fc8e-4801-8fff-1ad0bc6f

[...]

This is the second post in a series exploring graph databases and PostgreSQL. The first post covered what graphs are, what can be modeled as a graph, and the landscape of graph database systems. This post moves from the general to the specific: how we query graphs, why SQL can be an awkward fit, and what SQL/PGQ changes. It ends with the PostgreSQL implementation, why it was reverted, and a few notes on the main features still to come.

Query languages for graphs

In the previous post, we ended on the observation that graph databases come in many forms, and that they differ in how they store graphs and what data models they support. They also differ in how you question them.

Unlike the relational world, which converged on SQL early and kept it as the dominant standard for decades, the graph ecosystem produced a proliferation of query languages:

• Cypher<sup>[1]</sup>, created at Neo4j, represents patterns as ASCII art:
  (a:Person)-[:KNOWS]->(b:Person);
• Gremlin<sup>[2]</sup>, from Apache TinkerPop, is imperative and step-based:
  g.V().hasLabel('Person').out('KNOWS');
• PGQL<sup>[3]</sup>, from Oracle, embedded graph patterns inside a SQL-like shell;
• And in 2024, GQL<sup>[4]</sup> was ratified as ISO/IEC 39075, the first new database query language to reach ISO standardization since SQL itself.

For this series, the relevant one is SQL/PGQ<sup>[5]</sup>, standardized as part of SQL:2023. It is an extension of SQL that lets you define property graphs over existing tables and query them with GQL- and PGQL-like pattern syntax inside a standard SELECT.

How good is SQL for graphs ?

Consider a social network stored in two tables: person and knows.
A Cypher query asking for friends of friends looks like this:

 MATCH (a:Person)-[:KNOWS]->(b)-[:KNOWS]->(c)
 WHERE a.name = 'Alice'
RETURN c.name

The equivalent in plain SQL is a two-way self-join:

SELECT DISTINCT c.name
  FROM person a
  JOIN knows k1 ON k1.src = a.id
  JOIN person b ON k1.dst = b.id
  JOIN knows k2 ON k2.src = b.id
  JOIN person c ON k2.dst = c.id
 WHERE a.na

[...]

A friend of mine is an excellent architect but a complete snob. He has four different databases in production: PostgreSQL for OLTP, ClickHouse for OpenTelemetry data and web analytics, Elasticsearch for full-text search in the application, and MongoDB, “for historical reasons.”

In between, there are ETL and CDC pipelines, plus on-call developers hunting for where exactly the results don’t match. To him, it’s all a well-thought-out architecture; to me, it’s needless complexity in maintenance and development.

The other day I told him what I’ve been working on lately.

“I’ve managed to plug ClickHouse’s MergeTree table engine into PostgreSQL as a table access method, on a vanilla database core, without a fork, and with a vectorized query executor reading from it.”

“What for? Just use ClickHouse and spare yourself the pain.”

“Because I need JOINs across lots of tables, consistent data throughout the system, and one platform that’s familiar to develop and maintain.”

“That’s not how it works. It’s either reliable or fast!”

“Well, I’ve managed to combine the best of both worlds.”

Vectorization hit a wall in heap

“Wait a minute. Didn’t you just port Greenplum into a Postgres extension and then write a vectorized executor on top of it? What do you need MergeTree for?”

“Vectorization grinds through columns fast, but it chokes the moment it has to read the data.”

The backstory began when I ported Apache Cloudberry to PostgreSQL 19 as a set of extensions, which got me ORCA, Greenplum’s cost-based planner, on PostgreSQL 19. On top of that query optimizer I developed pg_vexec, a vectorized executor without a single core patch.

Its nodes, VecScan, VecHashJoin, VecAgg and VecSort, get into a query plan through the database’s standard extension mechanisms and compete with the ordinary nodes on cost. ORCA prices the vector nodes with its own cost model. Inside, the executor processes batches of 1,024 rows in columnar form with kernels bound to function OIDs. Where there is no dedic

[...]

The PostGIS Team is pleased to release PostGIS 3.7.0rc3! Best Served with PostgreSQL 19 RC 1 , GEOS 3.15.0 , postgis_tiger_geocoder 2025.2 , and address_standardizer.

This version requires GEOS 3.10+, PostgreSQL 14-19beta3, Proj 6.1+, and libgmp. To take advantage of all postgis extension features, GEOS 3.15+ is needed. To take advantage of all postgis_sfcgal extension features SFCGAL 2.3+ is needed. Python 3 is a new requirement to build the documentation. Visual examples in the docs are off by default. To build docs with them, configure with --with-visual-examples and make sure you have a running PostgreSQL instance; they are then generated automatically when building the docs (or explicitly with make -C regress visual-examples).

This release contains fixes since 3.7.0rc3 release.

• source download md5

• NEWS

• HTML Online en ja zh_Hans fr

• PDF docs: en ja, zh_Hans, fr

• Cheat Sheets:

  • postgis: en ja zh_Hans fr
  • postgis_raster: en ja zh_Hans fr
  • postgis_topology: en ja zh_Hans fr
  • postgis_sfcgal: en ja zh_Hans fr

This release is a release candidate of a major release, it includes bug fixes since PostGIS 3.6.4 and new features.

A read replica is a PostgreSQL server in permanent recovery that accepts queries—and nearly every surprise it causes follows from that fact. (I consult on PostgreSQL through PGX Inc..)

recovery_min_apply_delay makes a standby late on purpose. For the length of the delay there is a running, queryable copy of your database on which the DELETE without a WHERE clause has not happened yet. That is the whole feature, and it is a good one. The problem is that almost everyone who sets … (I consult on PostgreSQL through PGX Inc..)

After a crash, PostgreSQL syncs every file in your data directory before replaying any WAL—and on large clusters, this can consume minutes. (I consult on PostgreSQL through PGX Inc..)

A hook that fires exactly once at promotion or restore completion—but the server is neither ready nor recovering while your command runs. (I consult on PostgreSQL through PGX Inc..)

We’re continuing our review of the March CommitFest for PostgreSQL 19. This third article in the series covers the changes related to our DBA3 and DBS courses.

As a reminder, here’s the plan for our coverage of the final PostgreSQL 19 CommitFest:

Part 5.1 (QPT)

Part 5.2 (DBA1, DBA2)

Part 5.3 (DBA3, DBS) -- you are here 🙂

Part 5.4 (SQL, DEV1, DEV2)

For the earlier CommitFests, see: 2025-07, 2025-09, 2025-11, 2026-01.

Here’s what’s covered:

• pg_verifybackup, pg_waldump: reading WAL from tar archives
• pg_stat_recovery: monitoring replication and recovery
• psql: replica indicator in the prompt
• pg_createsubscriber --logdir: writing diagnostics to files
• Publications: excluding specific tables
• Logical replication subscriptions: connecting via postgres_fdw
• wal_sender_shutdown_timeout: limiting the wait for WAL replication at shutdown
• wal_receiver_timeout: per-user and per-subscription settings
• SSL: server-side SNI
• GRANT/REVOKE … GRANTED BY
• Password expiration warning
• MD5 passwords: more end-of-support preparations
• pg_[read|write]_all_data: now for large objects too
• psql: comments for publications, subscriptions, and extended statistics

...

For years, https://yum.postgresql.org and https://zypp.postgresql.org have had documentation pages for the extensions we package. But the PGDG repositories hold much more than PostgreSQL and its extensions: backup tools, high availability managers, connection poolers, monitoring tools, drivers, migration tools, and the libraries all of these depend on. Unless you already knew a package name, most of that software was hard to find.

As of today, both sites have a new Software section, linked from the navigation bar:

- https://yum.postgresql.org/software/ (RHEL / Rocky Linux / AlmaLinux, Fedora and Amazon Linux)
- https://zypp.postgresql.org/software/ (SLES 15 / 16 and openSUSE Leap 16.0)

It lists more than 70 packages by category:
- backup and recovery: pgBackRest, Barman, PGHoard, pgmoneta
- high availability: Patroni, Pgpool-II, and etcd, HAProxy, keepalived and vip-manager from the extras repository
- connection poolers: PgBouncer, pgagroal
- monitoring and log analysis: check_postgres, pg_activity, pgwatch, pgBadger
- migration: Ora2Pg, pgloader, pg_chameleon, pgcopydb
- client tools, drivers, and development and testing tools

Each page has a short description, the package names, and the exact dnf or zypper commands to install it, including how to enable the extras or non-free repository when it's needed. It also has a table of the operating systems, architectures and PostgreSQL versions where the package is available. That table comes from the current repository metadata, so it shows what you can actually install today.

Tools built separately for each PostgreSQL version, such as pg_catcheck, pg_checksums, pg_filedump and repmgr, now have pages on the extensions page, next to the extensions they ship alongside.

There's also a page for the support packages. These are the newer GIS libraries, the Python modules behind tools like pg_statviz and bigquery_fdw, cargo-pgrx, and the other build dependencies. You normally never install them by hand, but now you can see what each one is and which packages use it.

[...]

With the first release candidate available, PostgreSQL 19 brings practical advances in database maintenance, query planning, and replication.

Four Supabase row-level security policy sets, tested with Supabase's own roles: own rows, public read, teams without recursion, and admins from the JWT.

Why a Supabase row-level security policy returns nothing, fails or leaks, measured on PostgreSQL 17 with Supabase's own roles and auth.uid(): ten causes.

auth.uid() against (select auth.uid()) in a Supabase-style RLS policy on PostgreSQL 17, measured: 923 against 42 ms without an index, no difference with one.

hstore against jsonb on a million flat documents in PostgreSQL 17 and 18, measured: storage, reads, updates, GIN indexes, and what hstore does to types.

Awide Labs releases Awide Polar, its PostgreSQL fork with compute-storage disaggregation and HTAP capabilities, alongside enhanced PolarFS storage and Polar Proxy for consistent replica reads.