Is PostgreSQL 18 Faster? A 20M-Row Benchmark vs 17

"Upgrade to 18 and everything gets faster" did not hold up

Before upgrading this blog itself from PostgreSQL 17.9 to 18, we measured what actually changes. We spun up two containers, 17.9 and 18, loaded exactly the same 20 million rows into both, and ran five kinds of tables and queries you see in real systems.

The result was not what we expected. The big aggregation query, where PostgreSQL 18's headline "async I/O" should shine, was actually slower than on 17. Only one thing got clearly faster (skip scan). The rest either did not change or depended on configuration. Here are the unembellished numbers first.

The "join 2.3s → 1.1s" is the difference when you change 18's async I/O setting (io_method) away from its default. That is the single most important point of this article, so we cover it in detail below.

All the benchmark code is on GitHub. With docker compose and a few commands, anyone can reproduce the comparison (link at the end). These are measurements from one environment. We are publishing the code precisely so you do not take them at face value and can check on your own.

Why we measured it

PostgreSQL 18, released in September 2025, is the latest major version of the database. Its headline features include asynchronous I/O, skip scan, and statistics carry-over on upgrade, and the release notes say it is faster.

But "faster" in the release notes comes with conditions. For which tables, which queries, and by how much? Without that, you cannot justify taking production down to upgrade. We had a plan to move this blog's database off 17.9, so we measured it first, in a shape close to our own use case.

We used PostgreSQL 18 once before, in our article benchmarking UUID vs bigint primary keys with 10 million rows. This is the follow-up: what changes from the act of upgrading itself.

How we measured

We modeled a business SaaS (project management + activity-log analytics + an internal article store) with seven tables you commonly see in practice: organizations, users, projects, an activity-log table, articles, and so on. The goal is that you can map your own tables onto these.

Realistic data at scale via "seed-pool amplification"

Empty dummy data cannot measure how heavy real searches are. But generating 20 million rows one by one with Faker (a library that produces realistic names, company names, and text) would take forever.

So we generated a realistic "seed pool" once with Faker (tens of thousands of names, company names, and sentences), then combined and amplified it into the large tables via COPY. This keeps the data realistic while reaching real volume. The random seed is fixed, so 17.9 and 18 get exactly the same data, isolating the version difference.

# Generate the seed pool once with Faker, then amplify via COPY
names = [fake.name() for _ in range(50000)]        # realistic names
sentences = [fake.text() for _ in range(5000)]      # material for article bodies
# 20M activity-log rows are streamed in with COPY by sampling this pool

We capped shared_buffers (the memory PostgreSQL reserves for itself) at 256MB so reads actually hit disk. The activity-log table is about 2.9GB at 20 million rows. Each query was run five times with EXPLAIN (ANALYZE, BUFFERS) and we took the median. The environment is PostgreSQL 17.9 and 18 in Docker, on an 8-core machine with 7.6GB of RAM. We look at the seconds a human actually waits, not a "40% faster" percentage.

The biggest surprise: large aggregation got slower on 18

This is a dashboard-style query that aggregates sales or access logs by month and region. It reads the whole table without an index, the very place where PostgreSQL 18's headline async I/O was expected to help most.

Roughly, async I/O means "ask the disk to read many blocks at once and do other work while waiting." In 18 a setting called io_method was added, defaulting to worker (dedicated processes prefetch in batches).

But the result was the opposite. The same aggregation took 4.3s on 17.9 and 7.1s on 18 (default) — 1.6x slower on 18. It was consistently slower across five runs, not noise.

Suspecting the async I/O method, we re-measured with different io_method values.

Switching io_method to io_uring shrank it from 7.1s to 6.1s, but still did not reach 17.9's 4.3s. For this aggregation query, in this environment, 18 is slower.

An important caveat: the async I/O effect depends heavily on the storage. We measured on an ordinary cloud disk. On fast local NVMe SSDs or a well-tuned setup the result may differ. Still, the lesson from real measurement is clear: do not assume "18 means faster I/O so aggregations get faster." If you rely on large aggregations, measure on your own environment before upgrading.

Where it clearly got faster: filtering a business table

Of the five, this is where 18 genuinely got faster. It is a common multi-tenant SaaS query (many companies sharing one system): filtering an activity-log table by a time range.

The table has a composite index on "type (8 values like view/click) plus timestamp", a very common setup. But when the query filters by "timestamp only," PostgreSQL 17 cannot use the index well, because the leading column "type" is not specified. It falls back to a sequential scan (reading the whole table).

PostgreSQL 18's skip scan can now internally skip over the 8 values of "type" and still use the timestamp part of the index. Same SQL, same data, same index, yet the plan switches to something completely different. We measured 847ms → 585ms, and the disk blocks read dropped from about 370k on 17 to about 150k on 18.

-- PostgreSQL 17.9 (no leading column, so it falls back to a full scan)
Parallel Seq Scan on events    -- reads ~370k blocks

-- PostgreSQL 18 (uses the index via skip scan)
Bitmap Index Scan on idx_events_type_time   -- ~150k blocks
  Index Searches: 17           -- probes the index in several passes (skip scan)

What matters here is that this works without rebuilding any index. Your application code, table definitions, and indexes stay the same. Just upgrading lets queries that used to do full scans start using the index. The "composite index slapped onto a business table" that most companies have is exactly what benefits most.

The win is bigger the fewer rows your filter returns. With a narrower range over 10 million rows, the same query went 34ms → 5.6ms (about 6x). At 20 million rows here, the filter matched about 190k rows, so the gap is smaller (still faster, thanks to the index). It depends on how many rows your query returns and how many distinct values the leading column has. Millions of distinct values in the leading column mean more skips and less benefit.

Deep joins: slower by default, faster once you change a setting

We also measured a deep join aggregation across four tables (activity log → project → organization → user). This is where "depends on config" showed up most clearly.

With the default worker it was slower than 17 (1.7s → 2.3s), but switching to io_method=io_uring made it faster than 17 (1.1s). On the same 18, a single I/O setting flips "slower" into "faster."

The takeaway is one thing: after upgrading to 18, compare io_method on your own workload first. The default worker is not always best. On Linux with a recent kernel, io_uring is worth trying.

Logins and fuzzy search did not change at all

The "find one user by email" point lookup at login is instant on both 17 and 18, under a millisecond. It is already instant via the index, so the upgrade changes nothing you can feel. Fuzzy article search (partial match) was 8.2ms → 8.1ms, essentially the same.

This is not bad news. Knowing that "systems dominated by light queries like auth and master-data lookups will not feel different on 18" is itself useful for the upgrade decision. The faster-or-slower swings only appear in heavy queries that read large tables at once.

The upgrade-day trap: statistics carry-over

Aside from raw speed, one quietly important operational change is "statistics carry-over." If you do not know about it, it bites you on upgrade day.

PostgreSQL plans optimal execution based on statistics about how much data exists and how it is distributed. But through PostgreSQL 17, pg_upgrade (the cross-version migration tool) did not carry these statistics over. Right after migration the database "knows nothing about its contents," and until you manually run ANALYZE to recollect them, it picks bad plans and runs slowly.

That ANALYZE takes longer on bigger tables. At our 20 million rows it finished in about 1 second, but at tens of millions to billions of rows it can take minutes, during which production is not back to full speed. PostgreSQL 18's pg_upgrade now carries statistics over, so the first query runs with the right plan the moment migration finishes. That "why is everything slow right after the upgrade" window drops to zero. It is unglamorous, but for production where you want minimal downtime, it is a big win (caveats: extended statistics are not carried over, and you can disable it with --no-statistics).

Security, and what was deprecated or removed

Beyond speed, there are changes worth knowing before you upgrade. We confirmed the behavior on the actual 17.9 and 18 containers.

Data corruption detection is on by default

In PostgreSQL 18, newly initialized databases enable data checksums (a mechanism to detect corruption of stored data) by default. We confirmed it: 17.9 reports data_checksums = off, 18 reports on. A good move toward safety, but note: pg_upgrade requires the checksum setting to match between source and target, so upgrading from an old cluster without checksums needs adjustment, such as initializing the target with --no-data-checksums.

The old password scheme (MD5) is deprecated

The old MD5 password authentication is now officially deprecated. Creating an MD5-password user on 18 produces a clear warning (17 says nothing).

-- Setting an MD5 password on PostgreSQL 18...
WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed
         in a future release of PostgreSQL.

It will be removed entirely in a future version. If you use MD5, start migrating to the safer scram-sha-256 scheme now.

What was removed (check before upgrading)

The main things fully removed in PostgreSQL 18 are below. If you are upgrading from an old environment, check that you do not depend on them first.

• •Some columns of pg_stat_wal (wal_write / wal_sync, etc.). Monitoring tools that read them may break
• •Support for OpenSSL versions older than 1.1.1
• •Build options --disable-spinlocks / --disable-atomics, and old CPU architectures (HP-PA)

Also, uuidv7() (a function generating time-ordered IDs), which we covered in the primary-key article, is an official feature in 18. And the default for generated columns (columns whose value is computed) changed to "not stored, computed on read" (VIRTUAL), which is a point to check when migrating table definitions.

Limitations of this test

To be fair, here is what we did not measure.

• ?Per-storage differences. The async I/O effect strongly depends on disk speed and type. Our "18 is slower for aggregation" may flip on different storage
• ?Larger scale. We capped at 20 million rows for this machine (the repository code can run 50M or 100M+ by changing SCALE)
• ?Write-heavy (INSERT/UPDATE) workloads. We focused on read queries here

More than the exact numbers, read this as a map of "which kinds of tables and queries change, which do not, and where a setting can flip the result." You can run your own variations with the repository below.

Conclusion: is it worth upgrading?

It does not make everything faster. In fact, the large aggregation where we expected async I/O to shine got slower in this environment. Even so, the conclusion is "yes, it is worth upgrading," for three reasons.

1. If you have a business table with a composite index, skip scan alone makes it worth it. With no changes to code or indexes, full-scan queries start using the index. 2. Operational and security improvements ship together — statistics carry-over, checksums on by default, MD5 deprecation — which is reason enough to upgrade on a plan.

3. But always measure on your own environment after upgrading. Especially if you rely on large aggregations or joins, compare io_method across worker / sync / io_uring. The default is not always optimal, and changing it can turn "slower" into "faster."

We decided to move this blog's database to 18 as well, based on these results (we will pick io_method after comparing in the real environment). Next we want to measure write-heavy workloads.

References and benchmark code

• •Full benchmark code (GitHub) — docker-compose, data generation, benchmark, charts. Reproducible with a few make commands
• •PostgreSQL 18 release notes (official)
• •UUIDv4 vs v7 vs bigint, benchmarked with 10M rows in PostgreSQL (related)
• •Method: median of 5 runs with EXPLAIN (ANALYZE, BUFFERS), PostgreSQL 17.9 / 18, identical data (fixed seed), shared_buffers=256MB, 7.6GB RAM, 8 cores