As is tradition around this time of year, the global PostgreSQL community has surprised us with a new major version, and this time is no exception. On September 25th, the PostgreSQL Global Development Group officially released PostgreSQL 18. I'll tell you right now: it's one of the most exciting updates in recent years, especially if you're obsessed with performance and operational efficiency.
In this article, we'll break down the official announcement to uncover the new features that will genuinely change the way we work—from developing custom software to deploying high-availability architectures. Forget minor tweaks; these are game-changing improvements. Let's dive in!
The Key New Features in PostgreSQL 18
After analyzing the announcement, I've identified four areas that I consider revolutionary.
The Headliner: Asynchronous I/O (AIO) for a Massive Performance Boost
This is the crown jewel of PostgreSQL 18. Until now, Postgres relied on the operating system's readahead mechanisms to speed up data retrieval, but this isn't always efficient.
PostgreSQL 18 introduces a new Asynchronous I/O (AIO) subsystem. Instead of requesting a block of data and waiting, it can now issue multiple I/O requests in parallel.
- What does this mean? For operations that read a lot of data from disk (like sequential scans,
VACUUM, etc.), the impact is huge. Community benchmarks have shown performance improvements of up to 3x in certain scenarios.
- How do you use it? It's controlled by the new
io_method parameter, which lets you choose between different implementations like io_uring (on Linux) or stick with the classic synchronous behavior.
Bottom line: If you have a read-intensive (I/O-bound) workload, like analytics systems or applications with large datasets, you're going to see a night-and-day difference.
Upgrades Are Finally Pain-Free
Anyone who has managed a production database knows the post-upgrade dread: queries run slow until ANALYZE rebuilds the planner statistics. PostgreSQL 18 tackles this problem head-on.
- Statistics Preservation: Finally! It's now possible to keep planner statistics during a major version upgrade. This means your database will perform at its optimal level almost immediately after upgrading, eliminating that painful performance degradation phase.
- Faster
pg_upgrade: The upgrade utility itself has been optimized. It can now run checks in parallel (--jobs) and has a new --swap option that exchanges data directories instead of copying them, dramatically speeding up the process.
Bottom line: The process of upgrading to a new major version will be much faster and less disruptive to your applications.
Developer Gems You'll Love
This release is packed with features that make our lives easier and allow us to write more modern and efficient code.
- Say Hello to
uuidv7()!: A native function to generate v7 UUIDs has been added. Unlike random v4 UUIDs, v7 UUIDs are time-ordered. Using them as primary keys drastically improves B-Tree index performance by reducing fragmentation and improving data locality. This is a fundamental change for high-insertion tables!
- Virtual Generated Columns: Generated columns (
GENERATED COLUMNS) are now virtual by default. This means the value is computed at query time instead of being stored on disk, saving space for values that aren't needed constantly.
RETURNING with OLD and NEW: DML statements (INSERT, UPDATE, DELETE, MERGE) can now access both the old (OLD) and new (NEW) values directly in the RETURNING clause. This is fantastic for creating audit logs or implementing change data capture logic in a single query.
Bottom line: Native tools for modern design patterns that previously required more complex workarounds.
Authentication, Security, and Important Changes to Note
- Native OAuth 2.0 Support: This is one of the hidden gems of the release, especially for professional environments. In practice, it means you can now authenticate database users through external identity providers like Auth0, Okta, Microsoft Entra ID (Azure AD), or Google. Technically, this is enabled directly in the
pg_hba.conf file with a new oauth authentication method. You configure key parameters like the issuer (your identity provider's URL) and the scope (the permissions the access token must have). The token validation itself is modular and delegated to specialized libraries, making the system highly flexible. The result is that instead of managing local passwords, you can delegate authentication to your central Single Sign-On (SSO) system. This not only radically simplifies user management but also elevates security by enabling centralized policies like multi-factor authentication (MFA).
- The End of an Era: MD5 Deprecation: Although secure authentication with SCRAM has been available for several versions, PostgreSQL 18 takes the final step: it officially deprecates the use of MD5 for passwords. Support will be completely removed in a future release, so there are no more excuses. The community's message is clear: if you're still using MD5, migrating to SCRAM is an urgent and high-priority task.
- Default Checksums: New clusters created with
initdb will now have page checksums enabled by default. This improves data integrity but is a crucial detail to consider when planning an upgrade from a cluster without them.
My Critical Perspective: What This Means for Your Projects
- Is Asynchronous I/O a silver bullet? No. It's a powerful tool for I/O-bound workloads. If your bottleneck is the CPU, you won't see a magical improvement. But for deploying analytics applications or AI services that process large volumes of data, the impact will be enormous.
- The Real Operational Game-Changer: While AIO gets the headlines, I believe preserving statistics on upgrades is the single most important feature for production systems. It eliminates one of the biggest headaches and risks associated with an upgrade, reducing uncertainty and making it easier to keep your tech stack up to date.
- For Custom Software Development: My recommendation is to start using
uuidv7() as the default primary key on all new tables where it makes sense. It's an easy and free performance win. The improvements to RETURNING will also greatly simplify audit logic in the backends I build.
- The Ace Up Your Sleeve for Professional Projects (OAuth 2.0): Don't underestimate this feature. If you build custom software for businesses, as I do, offering integration with their SSO system (like Microsoft 365 or Google Workspace) is no longer a nightmare. It's an enterprise-grade feature that now comes standard, allowing you to sell more secure and better-integrated projects into your clients' ecosystems. It's a powerful differentiator.
- Immediate Call to Action: Audit your systems. If you're still using MD5 authentication, plan your migration to SCRAM now. This isn't a suggestion; it's an imminent security requirement.
Hands-On: Test PostgreSQL 18 with Docker and Examples
To understand why the deprecation of MD5 is such a big deal, there's nothing like seeing it in action. We'll use Docker to set up a lab with two parallel instances of PostgreSQL 18: one configured to use the old and now-obsolete MD5 method, and another using the modern and secure SCRAM, which has been the recommended method for years. This way, you can see the security difference for yourself.
Requirements
To execute these examples you will need to install Docker.
Step 1: Set Up the Comparison Environment
Create a file named docker-compose.yml. Notice that we're defining the two databases (db_md5 and db_scram), the adminer web interface, and an extra container named pg_client that will act as our toolbox with psql pre-installed.
docker-compose.yml (MD5 vs. SCRAM Comparison)
services:
db_md5:
image: postgres:18
container_name: postgres18_md5
restart: always
command: postgres -c password_encryption=md5 -c io_method=sync
environment:
POSTGRES_USER: user
POSTGRES_PASSWORD: password
POSTGRES_DB: testdb
ports:
- "5432:5432"
volumes:
- postgres_data_md5:/var/lib/postgresql/data
db_scram:
image: postgres:18
container_name: postgres18_scram
restart: always
environment:
POSTGRES_USER: user
POSTGRES_PASSWORD: password
POSTGRES_DB: testdb
ports:
- "5433:5432"
volumes:
- postgres_data_scram:/var/lib/postgresql/data
pg_client:
image: postgres:18
container_name: pg_client
# This command keeps the container running so we can exec into it
command: tail -f /dev/null
adminer:
image: adminer
container_name: postgres18_adminer
restart: always
ports:
- "8080:8080"
volumes:
postgres_data_md5:
postgres_data_scram:
In a terminal, run docker-compose up -d or docker compose up -d. Your entire environment is now ready, with no need to install anything else on your machine.
Note: The password for the user in all connections that require it (psql, Adminer) is password, as defined in the docker-compose.yml file.
Step 2: Run the Examples and Compare
For the terminal examples, we'll use our pg_client container.
How to use the terminal:
Open a shell session inside the client container with this command:
docker exec -it pg_client bash
Once inside (you'll see a prompt like root@...:/#), you can run psql commands against the databases using their service names (db_scram or db_md5).
Connect to the SCRAM (secure) instance:
Terminal: Inside the pg_client container, run:
psql -h db_scram -U user -d testdb
Adminer (http://localhost:8080): Server: db_scram
![Login Adminer - db_scram][1]
Example 1: The new uuidv7()
-- Compare the results of the two UUID types
SELECT uuidv4(), uuidv7();
Run the query a few times. You'll notice that uuidv4() values are completely random, while uuidv7() values start with a similar sequence that changes slightly. That initial part is the timestamp, which allows the index to sort them efficiently!
testdb=# SELECT uuidv4(), uuidv7();
uuidv4 | uuidv7
--------------------------------------+--------------------------------------
a3c361d2-4aa2-49fe-84fd-247d249484e0 | 019999e7-70ce-7e41-ad73-97cd6e42f2a8
(1 row)
testdb=# SELECT uuidv4(), uuidv7();
uuidv4 | uuidv7
--------------------------------------+--------------------------------------
3c8115a7-8667-47e2-971c-b7ce61504572 | 019999e7-7713-75db-a7ff-e7c2ebbfb47f
(1 row)
testdb=# SELECT uuidv4(), uuidv7();
uuidv4 | uuidv7
--------------------------------------+--------------------------------------
1fc5216d-a874-418b-abcb-0fd389823abc | 019999e7-7c0f-7a0a-a364-6a3a536a0b6c
(1 row)
Example 2: Virtual Generated Columns
CREATE TABLE products (
name TEXT,
price_eur NUMERIC,
price_usd NUMERIC GENERATED ALWAYS AS (price_eur * 1.07) VIRTUAL
);
INSERT INTO products (name, price_eur) VALUES ('Mechanical Keyboard', 80);
SELECT * FROM products;
The price_usd column doesn't take up disk space; it's calculated every time you query it.
testdb=# CREATE TABLE products (
name TEXT,
price_eur NUMERIC,
price_usd NUMERIC GENERATED ALWAYS AS (price_eur * 1.07) VIRTUAL
);
CREATE TABLE
testdb=# INSERT INTO products (name, price_eur) VALUES ('Mechanical Keyboard', 80);
INSERT 0 1
testdb=# SELECT * FROM products;
name | price_eur | price_usd
---------------------+-----------+-----------
Mechanical Keyboard | 80 | 85.60
(1 row)
Example 3: RETURNING with OLD and NEW Values
CREATE TABLE price_audit (
product_name TEXT,
old_price NUMERIC,
new_price NUMERIC,
changed_at TIMESTAMPTZ DEFAULT now()
);
WITH updated AS (
UPDATE products
SET price_eur = 85
WHERE name = 'Mechanical Keyboard'
RETURNING OLD.name, OLD.price_eur AS old_price, NEW.price_eur AS new_price
)
INSERT INTO price_audit (product_name, old_price, new_price)
SELECT name, old_price, new_price FROM updated;
SELECT * FROM price_audit;
It's like magic! No complex triggers or double queries needed.
testdb=# CREATE TABLE price_audit (
product_name TEXT,
old_price NUMERIC,
new_price NUMERIC,
changed_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE
testdb=# WITH updated AS (
UPDATE products
SET price_eur = 85
WHERE name = 'Mechanical Keyboard'
RETURNING OLD.name, OLD.price_eur AS old_price, NEW.price_eur AS new_price
)
INSERT INTO price_audit (product_name, old_price, new_price)
SELECT name, old_price, new_price FROM updated;
INSERT 0 1
testdb=# SELECT * FROM price_audit;
product_name | old_price | new_price | changed_at
---------------------+-----------+-----------+-------------------------------
Mechanical Keyboard | 80 | 85 | 2025-09-30 09:16:50.260192+00
(1 row)
Step 3: Experimenting with Asynchronous I/O (AIO)
Demonstrating a performance improvement like AIO in a simple example is tricky, as it heavily depends on your hardware (especially your disk speed). However, we can set up a small lab for you to experiment and measure the impact on your own system.
With our Docker setup, we have the perfect lab: the db_md5 service starts with synchronous I/O (io_method=sync), and the db_scram service uses the PostgreSQL 18 default, which is with AIO enabled.
1. Prepare a Large Dataset
We'll use pgbench, a tool included with PostgreSQL, to create test data in both instances.
From your terminal, run these two commands. Each will take a couple of minutes to populate its respective database with \~1.5 GB of data:
# Populate the database WITHOUT AIO (db_md5)
docker exec pg_client bash -c "PGPASSWORD=password pgbench -i -s 100 -h db_md5 -U user testdb"
# Populate the database WITH AIO (db_scram)
docker exec pg_client bash -c "PGPASSWORD=password pgbench -i -s 100 -h db_scram -U user testdb"
2. Run the Tests and Compare
For a fair comparison, we'll first restart both containers to clear memory caches.
From your terminal, run:
docker restart postgres18_md5 postgres18_scram
Wait a few seconds for the databases to start up.
Now, enter the client container to run the tests:
docker exec -it pg_client bash
Test on the server WITHOUT AIO:
# Connect to db_md5 and measure the time
PGPASSWORD=password psql -h db_md5 -U user -d testdb -c "\timing on" -c "SELECT count(*) FROM pgbench_accounts;"
Take note of the time it returns.
root@5dfdfaef5e1a:/# PGPASSWORD=password psql -h db_md5 -U user -d testdb -c "\timing on" -c "SELECT count(*) FROM pgbench_accounts;"
Timing is on.
count
----------
10000000
(1 row)
Time: 828.820 ms
Test on the server WITH AIO:
# Connect to db_scram and measure the time
PGPASSWORD=password psql -h db_scram -U user -d testdb -c "\timing on" -c "SELECT count(*) FROM pgbench_accounts;"
Take note of this second time.
root@5dfdfaef5e1a:/# PGPASSWORD=password psql -h db_scram -U user -d testdb -c "\timing on" -c "SELECT count(*) FROM pgbench_accounts;"
Timing is on.
count
----------
10000000
(1 row)
Time: 539.800 ms
3. Analyze the Results
Compare the time from the second test (WITH AIO) with the first (WITHOUT AIO). On a system with fast disks (SSD/NVMe), you should see a noticeable reduction in execution time. In my case, the time dropped from 828.820 ms to 539.800 ms, an improvement of 34.87%.
Important: The improvement you see will depend heavily on your hardware, operating system, and Docker configuration. The goal of this experiment is to give you the tools to measure it in your own environment.
Step 4: The Definitive Authentication Comparison
Now, let's create a user on each database to see the crucial difference.
1. On the MD5 (insecure) instance:
Connect to it:
And run the following SQL:
CREATE USER test_md5_user WITH PASSWORD 'a_simple_password';
SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'test_md5_user';
The result will show the easily identifiable MD5 hash:
testdb=# CREATE USER test_md5_user WITH PASSWORD 'a_simple_password';
WARNING: setting an MD5-encrypted password
DETAIL: MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
HINT: Refer to the PostgreSQL documentation for details about migrating to another password type.
CREATE ROLE
testdb=# SELECT rolname, rolpassword FROM pg
[1]: https://coderlegion.com/?qa=blob&qa_blobid=3116745045302959377
