Latest Blog Posts

After pg_dump and pg_restore, by default the planner statistics and the visibility map are missing. One query: 32.6 s before VACUUM ANALYZE, 23 ms after.

PostgreSQL's `restore_command` asks for WAL segments one at a time until the command fails—and that failure marks the archive's end, whether it's real or not. (I consult on PostgreSQL through PGX Inc..)

PostgreSQL's no pg_hba.conf entry for host, measured on 12 to 18 (not 15): the 'no encryption' tail, Docker's gateway address, hostssl, replication and reload.

When a PostgreSQL backend crashes, the postmaster can restart itself automatically or exit and wait for external intervention. (I consult on PostgreSQL through PGX Inc..)

SQL vs PL/pgSQL functions in PostgreSQL 16: why a SQL function is re-planned on every loop call, how to reduce plan time.

For years, installing many packages from the PostgreSQL RPM repository on RHEL and its rebuilds started with the same step: `dnf install epel-release`. PostGIS, pgBackRest, pgcli, patroni, pg_statviz, ora2pg and lots of extensions pulled at least one dependency from EPEL.

Over the last couple of weeks I removed most of those dependencies. On RHEL 8, 9 and 10, the GIS stack, the most popular extensions and almost all of our Python tools now install from the PGDG repositories and the OS repositories (BaseOS, AppStream and CRB) alone.

Continue reading "The PostgreSQL RPM repository needs a lot less EPEL now"

As promised during the session, I'm sharing the slides from the "Fostering Inclusivity with the PostgreSQL Europe Diversity Committee" presentation at the New PostgreSQL Community Members - Summit at PostgreSQL Conference Europe (PGConf.EU) 2026.

The community day sessions weren't recorded, so there won't be a video of this one. I've made an attempt at a transcript to go with the slides!

As with most people in the Postgres community, I wear multiple hats.

• In my day job, I’m a Senior Software Engineer at Snowflake.
• I talk about databases at Postgres events and at developer events to help spread the love for Postgres outside of the Postgres community.
• I’m on the org team for PGConf.EU.
• I chair the PostgreSQL Europe (PGEU) Diversity Committee.
• And I dream of "just quietly getting on with techie stuff"!

The PGEU Diversity committee wants to encourage increased diversity of all kinds within the Postgres Europe community. Whether that's ethnic diversity, gender diversity, work background, education, socioeconomic situation...

Why do we have a diversity committee?

Because we know that diversity is good for the whole Postgres project. Diverse people bring unique skills and viewpoints, making the Postgres project better for all of us.

One of our main goals is to make sure that everyone feels welcomed, represented, and valued within our community. and I know that the vast majority of people in the Postgres community also want everyone to feel welcome.

But if you look around, and you don’t see anyone else who looks like you, or who does the same type of job as you, or who has the same type of background as you, it can sometimes be hard to believe that you really belong.

This is an anonymised but sadly pretty accurate picture of the org team and the speaker lineup for a Postgres event in Europe in 2025, so it’s easy to see why sometimes not everyone feels represented.

Fortunately, the vast majority of the folks in the Postgres community really do wan

[...]

[...]

Most database proxies that do read/write splitting get one thing wrong: after a client writes a row and immediately reads it back from a replica, the read can return stale data.

Every commit in PolarDB ends the same way: the WAL record must reach shared storage. On the way there, the bytes move from a WAL buffer, through the shared-storage daemon, to a device.

PostgreSQL 16's `reserved_connections` reserves slots for your monitoring and backup roles when the application exhausts the pool. (I consult on PostgreSQL through PGX Inc..)

In the previous two posts you could see how to manage storage for create and drop and also to work with transactions. In this post you’ll learn how to handle TRUNCATE, which replaces the arena of a table with a new one. Doing that safely means choosing which arena to release based on how the transaction ends. This post handles the normal path, the in-place path Postgres takes for tables created in the same (sub)transaction, and the rollback cases for both.

In Part 5 you could see how the arena of a table was tied to the outcome of the transaction, so that CREATE TABLE and DROP TABLE neither leak nor lose an arena when a transaction or a savepoint rolls back. When TRUNCATE is called, relation_set_new_filelocator is called for a table that already has an arena, and it should replace that arena with a new one.

The normal TRUNCATE path

Postgres assumes that you truncate a table by giving it a new relfilenode and let the old one be reclaimed. For pg_memtable it is necessary to either create a second arena and point the relfilenode at it, or truncate the arena in-place. It is not possible to blindly truncate the existing arena, and you will soon understand why.

To deal with TRUNCATE, the memtable_ProcessUtility function is extended to call the finalize function for TRUNCATE in the same way as is done for CREATE. Because the finalize function ignores relations that have no cache entry, tables that use other table access methods are unaffected and the finalize function can be called blindly without checking the table access method.

static void memtable_ProcessUtility(PlannedStmt* pstmt, const char* queryString,
                                    bool readOnlyTree,
                                    ProcessUtilityContext context,
                                    ParamListInfo params,
                                    QueryEnvironment* queryEnv,
                                    DestReceiver* dest, QueryCompletion* qc) {
  Node* parsetree = pstmt->utilityStmt;

  if (prev_ProcessUtility)
 

[...]

AwideLabs Tech Column: How we took PolarDB from a promising open-source project to production-ready Awide Polar.

Every durable database change in PolarDB passes through one place before it is safe: the write-ahead log. A replica is just a machine that stores that log and replays it. But how if a cluster need a pure copy of the WAL?

Every time PostgreSQL decides whether a row is visible to your query, it asks the same question: was the transaction that wrote this row already committed when my snapshot was taken?

Protect your disk from orphaned temporary files after a backend crash. (I consult on PostgreSQL through PGX Inc..)

A first look at what CloudNativePG 1.31 will bring to PostgreSQL credentials: a DatabaseRole can now generate its own password, keep it in a Secret the operator owns, shape it with criteria and rotate it on a schedule, with PostgreSQL enforcing the lifetime through VALID UNTIL. The feature is merged but not yet released, so this recipe anticipates 1.31 and shows how to try it today on the latest development snapshot.

On 12 October, the PostgreSQL User Group Vienna met, organized by * Cornelia Biacsics * Ranjeet Kumar

Speakers:

• Mayur B.
• Radim Marek

On 13 October, the San Francisco Bay Area PostgreSQL Meetup Group met online. Christophe Pettus delivered a talk. The meetup was organized by:

• Katharine Saar
• Stacey Haysler
• Christophe Pettus

On 13 October, Devrim Gündüz and Pavlo Golub staffed the PostgreSQL booth at DATA STACK CONF 2026.

On 14 October, the PostgreSQL Istanbul User Group met

Organizers:

• Devrim Gündüz,
• Gülçin Yıldırım Jelínek
• Bilge Korkmaz Erdim

Speakers:

• Melih Mutlu (Tiger Data)
• Mehmet İnce (Prodaft)

On 14 October, Claire Giordano and Aaron Wislang hosted and published a new podcast episode “From Oracle DBA to Postgres contributor with Karen Jex" from the Talking Postgres series.

On 15 October, the PostgreSQL User Group Edinburgh met, organized by Jimmy Angelakos. Elizabeth Garrett Christensen delivered a talk.

On 16 October the PostgreSQL Ghana User Group met, organized by Ezra Yendau and Julius Boakye. Ezekiel Sebastine delivered a talk.

From 20-23 October, PostgreSQL Conference Europe 2026 took place.

Organized by:

• Andreas Scherbaum
• Chris Ellis
• Dave Page
• Ilya Kosmodemiansky
• Karen Jex
• Magnus Hagander
• Marc Linster
• Martin Marques
• Samed Yildirim
• Stefan Fercot
• Valeria Kaplan

Talk Selection Committee:

• Samed Yildirim
• Floor Drees
• Gülçin Yıldırım Jelinek
• Jan Wieremjewicz
• Mayuresh Bagayatkar
• Andrei Lepikhov
• Jan Karremans
• Priyanka Chatterjee
• Stefanie Janine Stölting

Code of Conduct Committee:

• Stacey Haysler
• Ellyne Phneah
• Pavlo Golub
• Samed Yildirim

Speakers:

• Andres Freund
• Alexander Korotkov
• Alexander Kukushkin
• Alicja Kucharczyk
• Andreas Karlsson
• Andrei Lepikhov
• Ants Aasma
• Ashutosh Bapat
• Bertrand Drouvot
• Boriss Mejias
• Chris Ellis

[...]

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

PolarDB separates compute from storage. Many machines can reach the same block device. Exactly one of them may write to it. A second read-write mount corrupts data, and there is no recovery from it.

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

[...]

Every Postgres team eventually types CREATE INDEX in a hurry, and most of them type the same one. B-tree is the right default, but it is not always the right answer. On a table with 10 million rows I found an index that was 3,000 times smaller and only 3 times slower, an index that did nothing at all, and a “covering” index that made queries faster until a routine UPDATE took the advantage away.

This is Part 1 of Postgres in Production, a series about the decisions that matter once a database leaves the tutorial: indexes, VACUUM and bloat, connection pooling, partitioning and JSONB. Each part follows one rule: measure first, then explain. The numbers below come from PostgreSQL 18.6 and an open-source benchmark that you can run yourself.

Which PostgreSQL index type to use: the short answer

A PostgreSQL index is a separate data structure that lets the database find rows without reading the whole table. The type decides which questions it can answer and how much space and write time it costs.

[...]

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 [...]

[...]