Latest Blog Posts

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

[...]

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.

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.

1. pgsql_tweaks is a bundle of functions and views for PostgreSQL
2. Changes In The pgsql_tweaks 1.1.0 Release

pgsql_tweaks is a bundle of functions and views for PostgreSQL

The source code is available on Codeberg.

The extension is also available on PGXN.

The extension is also availabe through the PostgreSQL rpm packages.

Changes In The pgsql_tweaks 1.1.0 Release

This release adds a new view to the extension, pg_index_bloat.

The planner charges `random_page_cost` per index page based on how uncorrelated the index is—but the name lies. (I consult on PostgreSQL through PGX Inc..)

Dumping across major versions? A reserved keyword can silently break constraints and policies. (I consult on PostgreSQL through PGX Inc..)

Open-source Citus delivers millions of queries per second more efficiently than Neki, the only other published data point at this scale

Last month PlanetScale published a blog post on how they achieved 118 million queries per second on Neki, their sharded Postgres. It included data points at 5, 50 and 512 shards, and showed the scalability between them.

While this was breaking news, our team at StackGres was working on a benchmark to show how we removed Citus' single-coordinator ceiling, allowing Citus to scale without limits. We introduced an architecture in StackGres 1.19 that allows to create as many Query Routers (think of Citus Coordinators, but you can scale them out) as you need. Query Routers scaled near-linearly up to two routers, allowing a three worker cluster to go from 550K queries per second with a single coordinator to 1.4M with three Query Routers.

The question then became obvious: can we compare Neki and Citus? Can Citus match, or outperform, with the new Query Routers, Neki? One observation further motivated us: Neki’s numbers show that data nodes were delivering 231K tps. A trained eye would soon recognize that figure as a low per node number for a modern ARM64 server with 64 cores. Certainly there’s overhead on a sharded system, and both Neki and Citus suffer from it. But we believed Citus could do better.

And so it did. We matched the 1M and 10M data points. At those data points Citus was able to achieve slightly better performance (with possibly lower latency too, but those numbers were not drilled down in Neki’s post) with 60% and 48% of the data nodes, respectively. The number of Query Routers was half of Neki’s at 1M and the same at 10M. Overall, Neki needed 1.5 to 1.8 times the vCPU to reach Citus performance levels.

Our benchmark was created as similar as possible to Neki’s, but they haven’t published all the details about their setup. Our benchmark setup, but also all the code needed to reproduce it anywhere, will be published soon as a follow-up blog post, so th

[...]

[...]

The 2026 version of the Ruby on Rails Community Survey results are now live.

Two things caught my eye: just how much PostgreSQL is in production for Rails apps, and the learning resources Rails developers use.

The survey has responses from nearly 1,800 developers.

Among relational SQL databases, 84% of respondents use PostgreSQL in production. That’s wild! The next one is MySQL at 23%.

84% of nearly 1,800 surveyed Ruby on Rails developers use PostgreSQL in production

This reflects a multi-year trend not only in the Ruby on Rails community but also in the broader industry among relational databases.

According to the DB-Engines Ranking Trend, Oracle, MySQL, and Microsoft SQL Server are all still more popular than PostgreSQL, but declining. In the graph below we can see the growing popularity of PostgreSQL and this trend line implies it may surpass Microsoft SQL Server. MySQL could be next.

Popularity of Oracle, MySQL, Microsoft SQL Server and PostgreSQL per DB-Engines

How do Rails developers learn?

How do developers learn? Books hold a smaller but steady percentage among learning resources. 37% of respondents in their first two years of learning felt reading whole books was most helpful, while 10% have read parts of a book and found that best.

What worked best among respondents was building and deploying their own applications. This makes sense! That’s how it’s always been for me too, nothing beats building something yourself and becoming an owner and operator. Documentation, learning videos, podcasts, books, and blog posts all help support developers looking to improve their skills.

High Performance PostgreSQL for Rails Ranked #12

I was happy to see High Performance Pos

[...]

My job at pgEdge allows me a significant amount of time to work on pgAdmin, a project I started nearly 30 years ago. The previous blog and this followup is an update on some of the recent work I’ve been doing.In my last post I wrote about moving pgAdmin's build farm from Jenkins to GitHub Actions, and I promised a follow-up about , which is the least famous repository in the pgAdmin organisation and quietly one of the most important. If you have installed pgAdmin on Windows in the last year or so, or on macOS since the move to GitHub Actions, you ran code that came from the buildfarm.

What pgbuild is for

pgAdmin's desktop packages are self-contained, because that is what people expect on Windows and macOS: you download an installer or a disk image, you run it, and pgAdmin works, without you first having to install PostgreSQL's client libraries, a Kerberos implementation and an SSL library, and without pgAdmin quietly binding itself to whatever versions happen to be on your machine. That convenience has to come from somewhere, and where it comes from is , which compiles PostgreSQL itself along with OpenSSL, Kerberos, , , ICU, , , ,  and a handful of build tools, for Windows on x86_64 and for macOS on both Intel and Apple silicon, and publishes each as an archive that the pgAdmin build then downloads and links against.It grew out of a repository called , which as the name suggests did this only for Windows, and which itself came out of conversations at PGConf.dev 2025 in Montreal, with Dave Cramer and Andrew Dunstan amongst others, about how useful it would be to have a standardised reference build of PostgreSQL and its dependencies for Windows: something that anybody shipping a PostgreSQL client on that platform could build against, rather than every project solving the same problem privately and slightly differently. The macOS dependencies, meanwhile, were built on the Jenkins farm right up until the migration, by a separate set of jobs with their own conventions, so bringing them into the same repository [...]

Some time ago I read this blogpost. They wrote about logging changes to table using a trigger, but one thing that caught my attention was: Storing only the changed columns made the log five times smaller and the trigger slower. Figured I can try it myself… Unfortunately they didn't provide schema of table, nor test … Continue reading "Old tech is sometimes better – a case study based on blogpost from Now-Next…"

Prairie Postgres just turned two! We say “community building” often, maybe not always thinking about what it means, but looking back on the past two years, I can confidently say the most important achievement, the one I’m most proud of, is reshaping the Chicago Postgres Community.

Gradually, our gatherings transformed from a group of experienced DBAs plus a dozen newcomers who sat quietly and didn’t ask any questions because they didn’t know where to start, to a gathering of curious developers, data scientists, and yes, both junior and experienced DBAs are here – how could we keep going without them? Each talk sparks a lot of questions; people stay engaged in conversations long after it ends. It’s not just about “Do you have job openings in your organization?” It’s also (and mostly) about knowledge sharing, talking about passion projects, and building connections.

I am not saying it happened during these last two years; things started to change a while ago, but the changes were empowered by the sense of ownership: it’s our organization, our meetups, and it’s up to us to make the most of them.

Another exciting change is the growth of our connections with other technical meetups. We have strong ties with Chicago Code&Coffee and the Chicago Ruby and Django communities, and we are planning our first joint meetup with ChiPy. We are also exploring ways to strengthen connections with the Chicago AWS meetup and the Databricks meetup.

Working together. Building connections. Building community. All I was dreaming about

What am I looking forward to? What would I like us to achieve in our third year?

First, the geography. There is more Postgres in the greater Midwest than just Chicago, and Prairie Postgres is happy to help give it a jump start. Reach out to us – you know where we are!

Second, building connections with educational institutions. So far, there is nothing major to report, except that we have some students coming to our meetups, but we hope we can do more.

Finally, we hope to b

[...]

Configure a replication slot name on your standby and the upstream keeps WAL forever—until the slot itself fills your disk or the standby dies holding an old… (I consult on PostgreSQL through PGX Inc..)

A PostgreSQL session idle in transaction, measured on 17: what it blocks (locks, ALTER TABLE, vacuum) and how idle_in_transaction_session_timeout ends it.

PostgreSQL error 40001 'could not serialize access due to concurrent update', measured on 17: when it fires, why SKIP LOCKED does not avoid it, and the retry.

Restoring a pg_dump 17 file into PostgreSQL 16 fails with unrecognized configuration parameter transaction_timeout. Measured fixes, plus four other causes.

'remaining connection slots are reserved for roles with the SUPERUSER attribute': why PostgreSQL refuses you, measured on 15–17, and how to free slots.

What raised 'invalid input syntax for type uuid' in PostgreSQL 17, measured: which strings cast, 'undefined' from a driver, '' from a setting, and fixes.

PostgreSQL has no CREATE POLICY IF NOT EXISTS. Three ways to write a repeatable policy migration, measured on PostgreSQL 17 and 18: gaps, locks and races.

Setting the tenant for PostgreSQL row-level security in Rails 8.1, measured: before_action, a transaction per request, threads in a request, failed writes.

What raised 'stack depth limit exceeded' in PostgreSQL 17, measured: triggers, recursion, a policy reading its table via a function, long expressions.

We’re continuing our review of the March CommitFest for PostgreSQL 19. This second article in the series covers the changes related to our DBA1 and DBA2 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) -- you are here 🙂

Part 5.3 (DBA3, DBS)

Part 5.4 (SQL, DEV1, DEV2)

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

Here’s what’s covered:

• REPACK: rewriting a table to reclaim disk space
• Autovacuum: table processing order
• Autovacuum: parallel index vacuuming
• Enhanced transaction ID wraparound warnings
• Checkpoint reason in the completion log message
• Less WAL and on-access visibility map updates
• More code paths use read streams
• pg_stat_lock: statistics on long lock waits
• pg_upgrade: faster large object transfer, part 2
• oid2name: file paths relative to PGDATA
• psql: comments on internal queries
• log_min_messages per backend type
• Stats reset time for pg_stat_database_conflicts and pg_statio_all_sequences

...