Replication Deadlock Bug in Current Postgres Releases 14-16

The current minor releases of Postgres versions 14-16 (14.23, 15.18 and 16.14, released on May 14th) introduced a regression that can lead to a MultiXactOffsetSLRU deadlock during transaction log (WAL) replay in certain circumstances.

The bug was (to our knowledge) first reported in Bug#19490 on May 20th by Radim Marek from BoringSQL. Further reports were done on the pgsql-bugs and pgsql-admin mailing lists and we also got customer support requests through our Open-Source Support Center.

What is Currently Known About This Bug

The bug can be hit in two ways. First, during streaming replication where the standby eventually hangs due to the deadlock. The other possibility is hanging point-in-time-recovery (PITR). The following is currently known:

1. The bug is only live on Postgres versions 14-16. Version 17 and 18 (and earlier versions) are not affected.

2. The WAL needs to be generated by a leader running the Q4/2025 (November 13th) back-branch releases or earlier (14.20/15.15/16.11).

3. The standby or the instance running PITR needs to be updated to the latest minor release (14.23/15.18/16.14).

4. The startup process hangs with wait event LWLock/MultiXactOffsetSLRU in pg_stat_activity during WAL replay.

So to summarize, the leader needs to be at least a few minor versions behind and the standby needs to be updated to the latest minor version. Due to the recommended procedure of updating streaming standbys first before updating the leader, this bug is likely to be hit relatively often in the field, especially by organizations that only patch every few minor releases.

Circumventing the Issue

If one has not yet hit the issue, then not upgrading the standby or a PITR machine to the latest minor releases will avoid the problem. In the case that one is affected by the regression, there are four (well, currently three) ways to address the problem:

1. Downgrading the standby or the PITR instance to the Q1 (or earlier) point release (14.22/15.17/16.13) fixes the problem (but will potentially re-introduce problems fixed since).

2. A two-step upgrade procedure where first the standbys and then the leader are upgraded to the Q1 point releases (see previous option), followed by a second upgrade of the standbys (and then eventually the leader) to the latest point releases. This circumvents the issue as then the leader is on a safe version that will not emit the problematic WAL records that lead to the deadlock on the standbys.

3. Applying the patch “Fix self-deadlock when replaying WAL generated by older minor version” (version 16, version 15, version 14) from May 27th when self-compiling Postgres and starting the standby/PITR-instance with this also fixes the problem. Not using distribution packages of PostgreSQL is generally not advised, so this alternative should be chosen by organizations that always build PostgreSQL themselves anyway.

4. Upgrading to the upcoming minor releases (currently targeted for August 13th) will also fix the problem, once they are released.

The problem seems severe enough that one might think it should warrant an out-of-band back-branch release, which is what we have suggested on the PostgreSQL development list. So far, there have been a few other people confirming that they saw the bug (either in their own environment, or the environments of their customers), but no decision on a release has been made. As PostgreSQL 19 Beta 2 is currently scheduled for July 16th, it looks unlikely that another release could be squeezed in before. After the Beta 2 release, there is only one month left until the next scheduled minor releases. So probably no out-of-band release will be done for this issue and a downgrade of the standby (option 1 from above) or a two-step upgrade procedure (option 2 from above) should be chosen for the time being.

On March 24, I had the pleasure of attending and speaking at Nordic PGDay 2026. This year, the PostgreSQL community gathered at the Scandic Park Hotel in Helsinki, Finland, for the 11th edition of the conference. The event was fully sold out and, for the first time, was organized as a two-track conference.

There is a special kind of magic in visiting Finland, a nation repeatedly ranked as the happiest country in the world. From the moment we arrived at the Scandic Park Hotel, it was clear that this reputation extended to the PostgreSQL community as well. The overall atmosphere was exceptionally positive, welcoming, and constructive. And the magic began even before the conference doors opened. On the evening before the event, the Helsinki skies treated us to a beautiful display of the aurora borealis — a truly unforgettable welcome and a fitting prelude to this stellar conference.

My Talk: Beyond work_mem Myths

I had the privilege of representing credativ on stage with my talk, Beyond work_mem Myths: A Source-Code-Guided Tour Through PostgreSQL Memory Usage. In the talk, I explained how PostgreSQL memory management at the connection level is frequently misunderstood, and how work_mem should be viewed as a soft limit that can be exceeded multiple times even during seemingly simple operations. Using measurements based on Linux smaps kernel interface data collected during the execution of different queries, I demonstrated that the amount of memory consumed by a single backend process can be significantly higher than the configured and expected work_mem value.

Highlights from the Schedule

The schedule was packed with high-quality technical content, covering everything from operational challenges at scale to emerging AI use cases. While it was difficult to choose between the parallel tracks, here are some of the talks that stood out to me.

• Unlock AI Agents with PostgreSQL (Mats Berglin & Miguel Toscano): In the era of AI agents, data serves as the bridge between generic LLMs and unique, context-aware experiences. The talk explained vector search at scale, specifically how the ScaNN algorithm can deliver strong performance with lower resource overhead.
• Efficiently approximating/estimating percentiles and histograms (Tomas Vondra): Tomas delivered a highly technical and insightful session on approximation algorithms. It was an excellent deep dive into how statistical insights can be gathered efficiently without severely impacting performance.
• Why Postgres Won (Gianni Ciolli): PostgreSQL is today one of the most common choices for transactional workloads, for a number of reasons. This talk explored at least one of those reasons by examining MVCC and VACUUM, topics frequently discussed within the PostgreSQL community.
• A PostgreSQL Journey – What We Learned Migrating Between 3 Platforms (James McDonald & Nina Angelvik): Database migrations are rarely just about moving data. The speakers shared their experiences from two major PostgreSQL migrations within the same organization. Each migration solved real problems, but also exposed new challenges related to performance, operational practices, developer behavior, and organizational processes.

Looking Ahead

Nordic PGDay 2026 was an excellent conference. Between the stunning northern lights, the welcoming atmosphere, the flawless organization, and the high level of technical discussion, it was an event I will not soon forget. I am already looking forward to meeting the community again at Nordic PGDay 2027.

All photos (c) Nordic PGDay organizers

PostgreSQL VACUUM and ANALYZE are two important maintenance processes that ensure the optimal performance of your database. VACUUM cleans up obsolete row versions and makes their storage space reusable, while ANALYZE collects statistics that the Query Planner needs for optimal query strategies. Autovacuum automatically starts both processes in the background by default. However, they can also be executed manually to specifically improve database performance.

PostgreSQL offers various data types for the structured storage of different information in databases. The most important categories include numeric data types (INTEGER, BIGINT, DECIMAL), string data types (VARCHAR, TEXT), date and time data types (TIMESTAMP, DATE), and special data types such as JSON, BOOLEAN, and ARRAY. The correct selection significantly optimizes storage space and query performance.

What are PostgreSQL data types and why are they important?

PostgreSQL data types define what kind of data can be stored in a column and how that data is interpreted. They determine storage requirements, validation rules, and the available operations for each data value. Correctly selecting data types is crucial for database performance and data integrity.

Data types optimize storage space by precisely defining the required storage capacity. For example, an INTEGER requires 4 bytes, while a BIGINT uses 8 bytes. With millions of records, this difference has a significant impact on memory usage and query speed.

PostgreSQL organizes data types into various categories: numeric types for numbers, string types for text, date and time types for temporal data, and advanced types such as JSON, arrays, and geometric data structures. Each category offers specific functions and optimizations for different use cases.

Which numeric data types does PostgreSQL offer?

PostgreSQL provides several numeric data types: INTEGER, BIGINT, SMALLINT for integers, as well as DECIMAL, NUMERIC, REAL, and DOUBLE PRECISION for floating-point numbers. Integer data types differ in their value range and storage requirements, while floating-point types offer different levels of precision.

SMALLINT uses 2 bytes and stores values from -32,768 to 32,767. INTEGER requires 4 bytes for values between -2,147,483,648 and 2,147,483,647. BIGINT uses 8 bytes and supports extremely large numbers up to 9,223,372,036,854,775,807.

For decimal numbers, DECIMAL (synonymous with NUMERIC) offers exact precision without rounding errors, making it ideal for financial calculations. REAL uses 4 bytes for single floating-point precision, while DOUBLE PRECISION uses 8 bytes for double precision. In PostgreSQL development, the choice between exact and approximate arithmetic is crucial for data quality.

How do text and string data types work in PostgreSQL?

PostgreSQL offers four main types for text data: CHAR for fixed length, VARCHAR for variable length with a maximum, TEXT for unlimited length, and CITEXT for case-insensitive comparisons. VARCHAR and TEXT are the most commonly used options for most applications.

CHAR(n) always reserves n characters of storage space and pads shorter values with spaces. VARCHAR(n) stores only the characters actually used plus one byte for length information. TEXT has no length limit and offers maximum flexibility for large amounts of text.

Performance considerations show that VARCHAR and TEXT offer virtually identical performance. PostgreSQL optimizes both types equally efficiently. The choice between VARCHAR with a length limit and TEXT without a limit depends on validation requirements. PostgreSQL fully supports Unicode (UTF-8) and enables international character sets without additional configuration.

Which date and time data types are available in PostgreSQL?

PostgreSQL provides five temporal data types: DATE for calendar dates, TIME for times of day, TIMESTAMP for date and time, TIMESTAMPTZ for timezone-aware timestamps, and INTERVAL for time spans. TIMESTAMPTZ is recommended for most applications as it correctly manages time zones.

DATE stores only calendar dates without time information (YYYY-MM-DD). TIME records times of day with optional microsecond precision. TIMESTAMP combines both without a time zone reference, while TIMESTAMPTZ stores timestamps in UTC and converts them to the session time zone during queries.

INTERVAL allows calculations with time spans such as “3 months 2 days 14 hours”. PostgreSQL offers extensive functions for date arithmetic, formatting, and time zone conversion. Best practices recommend the consistent use of TIMESTAMPTZ for all time-related data in international applications.

What are special PostgreSQL data types and when should they be used?

PostgreSQL offers advanced data types for modern application requirements: BOOLEAN for truth values, UUID for unique identifiers, JSON/JSONB for structured documents, ARRAY for lists, and geometric types for spatial data. JSONB and ARRAY enable flexible data structures without separate tables.

BOOLEAN efficiently stores the truth values true, false, or null. UUID generates globally unique 128-bit identifiers, ideal for distributed systems. JSON stores documents as text, while JSONB provides a binary, indexable version with better performance.

ARRAY allows the storage of multiple values of the same type in a single column, for example, INTEGER[] for lists of numbers. Geometric types such as POINT, LINE, and POLYGON support spatial applications. These advanced data types integrate seamlessly into modern application architectures and reduce the complexity of data models through native support for complex structures.

How credativ® helps with the optimal use of PostgreSQL data types

credativ® supports companies in the strategic selection and implementation of the right PostgreSQL data types for maximum performance and data integrity. Our PostgreSQL solutions analyze your specific requirements and develop customized database structures:

• Performance Analysis: Evaluation of existing data structures and identification of optimization potential
• Data Modeling: Development of efficient schemas with optimally chosen data types
• Migration and Modernization: Secure transition of legacy data structures into modern PostgreSQL implementations
• Training and Consulting: Knowledge transfer for your development teams on best practices for data types
• 24/7 Support: Continuous support and optimization of your PostgreSQL environments

Contact us for a free initial consultation and find out how the right data type strategy can revolutionize your database performance.

PostgreSQL® uses a permissive open-source license known as the PostgreSQL License. It is based on the BSD license and allows both commercial and non-commercial use without restrictions. Companies can use, modify, and redistribute PostgreSQL free of charge, and only need to retain the original copyright notice.

What is the PostgreSQL License, and why is it so popular?

The PostgreSQL License is a BSD-like open-source license that grants maximum freedom in using the database. It was specifically designed to offer both developers and companies the greatest possible flexibility, without the legal complexities of other licensing models.

This license is particularly popular with companies because it contains no copyleft provisions. This means you can integrate PostgreSQL into proprietary software without having to disclose your own application’s source code. The license dates back to 1996 and has since established itself as a stable, trusted licensing model.

The core characteristics make PostgreSQL attractive for commercial projects: no license fees, no limits on the number of users or installations, and full control over modifications. Companies particularly value the legal certainty and the ability to use PostgreSQL without long-term contractual commitments.

How does PostgreSQL differ from other database licenses?

PostgreSQL differs significantly from other database licensing models due to its permissive structure. While MySQL® is licensed under the GPL, Oracle® uses proprietary licenses, and MongoDB® introduced the restrictive SSPL, PostgreSQL remains consistently open and business-friendly.

The comparison shows clear differences: MySQL under the GPL often requires paid licenses for commercial use in proprietary software. Oracle Database generally requires license fees and offers only limited free versions. With the SSPL, MongoDB introduced a license that obliges cloud providers to disclose their entire service software.

For companies, PostgreSQL offers clear advantages: no licensing costs, no legal pitfalls when integrating into commercial products, and no obligation to disclose proprietary developments. PostgreSQL support is also available from various providers, without vendor lock-in effects.

The PostgreSQL License is compatible with almost all other open-source licenses. For example, PostgreSQL code can be incorporated into a GPL-licensed project without any issues.

Comparison of the GNU General Public License (GPL), the GNU Affero General Public License (AGPL), and the PostgreSQL License.

This table highlights, in particular, the differences in the obligation to disclose source code, which is crucial for commercial use and proprietary software development:

The key business advantage of the PostgreSQL License

As the table shows, the biggest difference lies in how derivative works are handled and in integration into proprietary software.

The GPL and AGPL are strong copyleft licenses. This means: If you integrate software (such as a GPL-licensed database) into your own software and distribute this combined solution, the “viral effect” applies. Your own software is legally considered an extended work of the GPL software and must also be released under the GPL (including disclosed source code). The AGPL goes even further and requires source code disclosure even if you only offer the modified database as a service (SaaS/cloud) over a network.

The PostgreSQL License, by contrast, is a very permissive license (similar to MIT or BSD). It explicitly grants permission to use, copy, modify, and distribute the software and its documentation for any purpose, without fees or a written contract. The only condition is that the copyright notice and the two paragraphs of the disclaimer remain included in all copies. For a company, this represents a major business advantage: you can easily integrate a PostgreSQL database into a commercial, closed (proprietary) software product and sell it without ever being forced to disclose your own product’s source code.

Please note that this information reflects only the current state of our research and does not constitute legal advice. For a specific assessment of a particular use case, we recommend seeking individual legal advice for the respective case and the relevant jurisdiction.

What should you consider when using PostgreSQL commercially?

When using PostgreSQL commercially, you should follow basic compliance rules to ensure legal certainty. Document PostgreSQL usage in your project, retain copyright notices, and establish internal policies for handling open-source components.

Practical guidelines for legally compliant use include: maintain a software inventory list of all open-source components used, ensure development teams are informed about licensing terms, and store all relevant license texts and copyright notices in a central repository.

To minimize risk, professional support is recommended, especially for business-critical applications. Regular updates and security patches should be planned. Professional PostgreSQL support can help you manage both technical and legal aspects optimally and fully leverage the benefits of the permissive license. For a legal assessment of the licensing situation, please consult your attorney.

Liability

The PostgreSQL License also includes a warranty disclaimer. The software is provided “AS IS”. In our view, recourse against the authors or the project in the event of an error is therefore largely excluded. For this reason, in a business environment you should always safeguard PostgreSQL operations through a qualified service provider, such as the PostgreSQL Competence Center of credativ GmbH. Depending on compliance requirements, this may even be mandatory.

How credativ® supports PostgreSQL implementation

As an ISO 9001 and ISO 27001 certified service provider, credativ® offers comprehensive consulting and support for the optimal use of PostgreSQL. Our expert team helps you fully leverage the benefits of the PostgreSQL License while meeting all compliance requirements.

Our services include:

• Compliance consulting: Integrating PostgreSQL into your commercial software
• Technical implementation: Professional PostgreSQL installation and configuration
• 24/7 support: Continuous support for your PostgreSQL environment
• Migration and updates: Secure data migration and regular updates
• Training: Upskilling your teams on PostgreSQL best practices

Contact us today for a free consultation and learn how you can use PostgreSQL optimally and securely in your company.

Transparency notice:

Oracle® and MySQL® are trademarks of Oracle Corp. MongoDB® is a trademark of MongoDB Inc. PostgreSQL® is a trademark of the PostgreSQL Association, Canada. The mention of these trademarks is solely for the factual description of migration scenarios and services provided by credativ GmbH. There is no business relationship with the trademark owners mentioned with regard to the products referenced.

PostgreSQL is an open-source object-relational database that has been under development since 1986. It is characterized by ACID compliance, high extensibility, and strong SQL support. Companies value PostgreSQL for its reliability, performance, and cost-free nature. This guide answers the most important questions about the installation, configuration, and use of PostgreSQL in a corporate environment.

What is PostgreSQL and why do so many companies use it?

PostgreSQL is an object-relational database management system that is available as free open-source software. The database originated from the POSTGRES project at the University of California and has been continuously developed since 1986. PostgreSQL supports both relational and non-relational data structures and is considered one of the most advanced open-source databases worldwide.

The main features of PostgreSQL include ACID compliance for transaction security, Multi-Version Concurrency Control (MVCC) for concurrent access, and comprehensive SQL support. The database software also offers advanced data types such as JSON, XML, and arrays, as well as the ability to define custom functions and data types.

Companies of various sizes rely on PostgreSQL because it offers a stable and scalable solution for complex applications. The active developer community ensures regular updates and security patches. In addition, the free license allows for use without license costs, which is particularly attractive for growing companies.

How does PostgreSQL differ from other databases like MySQL?

PostgreSQL and MySQL differ in several important areas, with both having their specific strengths. PostgreSQL offers advanced data types such as JSON, JSONB, arrays, and geometric types, while MySQL focuses on basic SQL data types. PostgreSQL often shows better performance with complex queries and joins, while MySQL is often faster with simple read operations.

Licensing also differs: PostgreSQL is under the PostgreSQL license, which is very permissive, similar to the BSD license. MySQL uses dual licensing with GPL for open-source projects and commercial licenses for proprietary software. This can be relevant when deciding on commercial applications.

• PostgreSQL is particularly suitable for applications with complex data structures, analytical workloads, and when strict ACID compliance is required. For example, PostgreSQL natively supports geo-data and operations on geometric structures with the PostGIS extension.
• MySQL is often chosen for web applications, content management systems, and situations where simple configuration and high read performance are paramount.

Both databases have strong communities and professional support available.

What advantages does PostgreSQL offer for companies?

PostgreSQL offers companies significant cost advantages through the open-source license, as there are no license fees. This also enables small and medium-sized enterprises to build a professional database infrastructure. The saved license costs can be invested in hardware, development, or professional support.

PostgreSQL’s security standards meet enterprise requirements with features such as Row-Level-Security, SSL encryption, and comprehensive authentication options. The database supports various compliance standards and offers detailed audit functions for regulated industries.

PostgreSQL scales both vertically and horizontally and grows with the demands of your company. Independence from individual manufacturers prevents vendor lock-in and gives you the flexibility to obtain support and services from various providers. The large community and open development model ensure continuous innovation and long-term availability.

How to install and configure PostgreSQL correctly? Simple example:

The PostgreSQL installation is done in different ways depending on the operating system. Under Ubuntu/Debian, for example, use the command sudo apt-get install postgresql postgresql-contrib. For Windows, download the official installer from postgresql.org. macOS users can install PostgreSQL via Homebrew with brew install postgresql.

After the installation, you must start the PostgreSQL service and create a database user. Under Linux, this is done with sudo systemctl start postgresql and sudo -u postgres createuser --interactive. The basic configuration is done via the files postgresql.conf for general settings and pg_hba.conf for authentication.

This means that you are ready for a test on most Linux distributions. For more demanding applications, you should of course make further considerations, such as whether you want to rely on the packages and versions supplied with the respective distribution or whether you want to concentrate on the packages from PostgreSQL.org. A performant database server also requires considerations regarding the underlying file system and its optimization.

For productive use, you should also make important security settings:

• Change the default password of the postgres user, if one has been assigned
• Configure SSL encryption for network connections
• Restrict access via pg_hba.conf to necessary IP ranges
• Enable logging for audit purposes
• Set up regular backups

How credativ® supports PostgreSQL projects

credativ® offers comprehensive PostgreSQL support for companies that need professional support for their database infrastructure. Our service includes 24/7 support from experienced PostgreSQL specialists who are immediately available for critical problems. You get direct access to our German support team without detours via international call centers. We are happy to help you and support you in the selection of open-source tools.

Our PostgreSQL services in detail:

• Migration from other database systems to PostgreSQL
• Installation consulting and audit of an existing installation
• Performance optimization and tuning of existing installations
• High availability setups with replication and clustering
• Backup and recovery strategies for maximum data security
• Monitoring and proactive maintenance of your PostgreSQL environment
• Long Term Support, which gives you time to complete the upgrades.

As PostgreSQL experts with over 20 years of experience, we help you to exploit the full potential of your database. Contact us for a non-binding consultation on your PostgreSQL project and find out how we can optimize your database infrastructure.

-- invalid page in a table:
pg_dump: error: query failed: ERROR: invalid page in block 0 of relation base/16384/66427
pg_dump: error: query was: SELECT last_value, is_called FROM public.test_table_bytea_id_seq

-- damaged system columns in a tuple:
pg_dump: error: Dumping the contents of table "test_table_bytea" failed: PQgetResult() failed.
pg_dump: error: Error message from server: ERROR: could not access status of transaction 3353862211
DETAIL: Could not open file "pg_xact/0C7E": No such file or directory.
pg_dump: error: The command was: COPY public.test_table_bytea (id, id2, id3, description, data) TO stdout;

-- damaged sequence:
pg_dump: error: query to get data of sequence "test_table_bytea_id2_seq" returned 0 rows (expected 1)

-- memory segmentation fault during pg_dump:
pg_dump: error: Dumping the contents of table "test_table_bytea" failed: PQgetCopyData() failed.
pg_dump: error: Error message from server: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: error: The command was: COPY public.test_table_bytea (id, id2, id3, description, data) TO stdout;

/*
* The following checks don't prove the header is correct, only that
* it looks sane enough to allow into the buffer pool. Later usage of
* the block can still reveal problems, which is why we offer the
* checksum option.
*/

if ((p->pd_flags & ~PD_VALID_FLAG_BITS) == 0 &&
    p->pd_lower <= p->pd_upper &&
    p->pd_upper <= p->pd_special &&
    p->pd_special <= BLCKSZ &&
    p->pd_special == MAXALIGN(p->pd_special))
    header_sane = true;

if (header_sane && !checksum_failure)
    return true;

SELECT * FROM page_header(get_raw_page('pg_toast.pg_toast_32840', 100));

     lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/2B2FCD68 |        0 |     4 |    40 |    64 |    8192 |     8192 |       4 |         0

Here we can nicely see the Item IDs array – offsets and lengths. The first tuple is stored at the very end of the data block, therefore it has the biggest offset. Each subsequent tuple is stored closer and closer to the beginning of the page, so offsets are getting smaller. We can also see lengths of tuples, they are all different, because they contain a variable-length text value. We can also see tuples and their system columns, but we will look at them later.

Now, when we damage the Item IDs array and diagnose how it looks like – output is shortened because all other columns are empty as well. Due to the damaged Item IDs array, we cannot properly read tuples. Here we can immediately see the problem – offsets and lengths contain random values, the majority of them exceeding 8192, i.e. pointing well beyond data page boundaries:

 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax 
----+--------+----------+--------+--------+--------
  1 |  19543 |        1 |  16226 |        | 
  2 |   5585 |        2 |   3798 |        | 
  3 |  25664 |        3 |  15332 |        | 
  4 |  10285 |        2 |  17420 |        |

SELECT * FROM verify_heapam('test_table', FALSE, FALSE, 'none', 7, 7);

 blkno | offnum | attnum | msg
-------+--------+--------+---------------------------------------------------------------------------
     7 |      1 |        | line pointer to page offset 19543 is not maximally aligned
     7 |      2 |        | line pointer redirection to item at offset 5585 exceeds maximum offset 4
     7 |      4 |        | line pointer redirection to item at offset 10285 exceeds maximum offset 4

 lp | lp_off | lp_flags | lp_len |   t_xmin   |   t_xmax   |  t_field3  |       t_ctid       | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid
----+--------+----------+--------+------------+------------+------------+--------------------+-------------+------------+--------+--------+-------
  1 |   6160 |        1 |   2032 | 1491852297 |  287039843 |  491133876 | (3637106980,61186) |       50867 |      46441 |    124 |        |
  2 |   4128 |        1 |   2032 | 3846288155 | 3344221045 | 2002219688 | (2496224126,65391) |       34913 |      32266 |     82 |        |
  3 |   2096 |        1 |   2032 | 1209990178 | 1861759146 | 2010821376 | (426538995,32644)  |       23049 |       2764 |    215 |        |

Summary

• Heap sequential scans, like plain SELECT and COPY operations that stream lots of data
• VACUUM on big tables and indexes
• ANALYZE sampling
• Bitmap heap scans

Autovacuum benefits from this change too, since its workers share the same VACUUM/ANALYZE code paths. Other operations still remain synchronous for now:

• B‑tree index scans / index‑only scans
• Recovery & replication
• All write operations INSERT, UPDATE, DELETE, WAL writes
• Small OLTP lookups that touch a single heap page

io_method = sync

io_method = io_uring (Linux only)

SELECT pg_config FROM pg_config() where pg_config::text ilike ’%liburing%’;

1. Backends write requests via API into a submission ring in shared memory
2. The kernel performs I/O asynchronously and writes results into a completion ring
3. Completion ring content is consumed by the backend with fewer context switches

io_method = worker

1. At server start, the postmaster creates a pool of I/O worker processes. Number is controlled by io_workers parameter with a default of 3. However, benchmarks suggest this number should be higher on many‑core machines, typically between ¼ and ½ of available CPU threads. Best value depends on workload and storage latency.
2. Backends submit read requests into a shared memory submission queue. This submission queue is generally a ring buffer that multiple backends can write into concurrently. It contains only metadata about the request – handle indices, not full request record. There is only one submission queue for the entire cluster, not per database or per backend. The actual details of the request are stored in separate memory structure.
3. Request is checked if it must be executed synchronously or can be handled asynchronously. Synchronous execution can also be chosen if the submission queue is full. This avoids problems with shared memory usage under extreme load. In case of synchronous execution, code uses path for “sync” method described above.
4. Request submission in shared memory wakes up one I/O worker, which pops request and executes traditional blocking read() / pread() calls. If queue is still not empty, woken worker can wake up 2 additional workers to process it in parallel. Note in code mentions that this can be in the future extended to configurable N workers. This limit helps to avoid so called “thundering herd problem”, when single submitter would wake up too many workers causing havoc and locks for other backends.
5. One limitation for asynchronous I/O is the fact, that workers cannot simply reuse file descriptors opened by backends, they must reopen files in their own context. If this is not possible for some types of operations, synchronous I/O path is used for that specific request.
6. When workers finish a request without an error, they write data blocks into share buffers, put result into a completion queue and signal the backend.
7. From the perspective of the backend, I/O becomes “asynchronous”, because the “waiting” happens in worker processes, not in the query process itself.

• Works on all supported OSes
• Simple error handling: if a worker crashes, requests are marked as failed, worker exits and a new worker is spawned by postmaster
• Avoids the security concerns around Linux io_uring interface
• The downside is extra context switches and possible shared‑memory queue contention, but for many workloads the ability to overlap reads easily pays for that
• This method improves performance even in the case when all blocks are just copied from local Linux memory cache, because it is now done in parallel

Tuning the New I/O Parameters

How to Monitor Asynchronous I/O

pg_stat_activity

SELECT pid, backend_start, wait_event_type, wait_event, backend_type
FROM pg_stat_activity
WHERE backend_type = 'io worker';


  pid |        backend_start          | wait_event_type |  wait_event  | backend_type
------+-------------------------------+-----------------+--------------+--------------
   34 | 2025-12-09 11:44:23.852461+00 | Activity        | IoWorkerMain | io worker
   35 | 2025-12-09 11:44:23.852832+00 | Activity        | IoWorkerMain | io worker
   36 | 2025-12-09 11:44:23.853119+00 | IO              | DataFileRead | io worker
   37 | 2025-12-09 11:44:23.8534+00   | IO              | DataFileRead | io worker

SELECT a.pid, a.usename, a.application_name, a.backend_type, a.state, a.query,
ai.operation, ai.state AS aio_state, ai.length AS aio_bytes, ai.target_desc
FROM pg_aios ai
JOIN pg_stat_activity a ON a.pid = ai.pid
ORDER BY a.backend_type, a.pid, ai.io_id;


-[ RECORD 1 ]----+------------------------------------------------------------------------
             pid | 58
         usename | postgres
application_name | psql
    backend_type | client backend
           state | active
           query | explain analyze SELECT ........
       operation | readv
       aio_state | SUBMITTED
       aio_bytes | 704512
     target_desc | blocks 539820..539905 in file "pg_tblspc/16647/PG_18_202506291/5/16716"
-[ RECORD 2 ]----+------------------------------------------------------------------------
             pid | 159
         usename | postgres
application_name | psql
    backend_type | parallel worker
           state | active
           query | explain analyze SELECT ........
       operation | readv
       aio_state | SUBMITTED
       aio_bytes | 704512
     target_desc | blocks 536326..536411 in file "pg_tblspc/16647/PG_18_202506291/5/16716"

pg_aios: Current AIO handles

• pid: backend issuing the I/O
• io_id, io_generation: identify a handle across reuse
• state: HANDED_OUT, DEFINED, STAGED, SUBMITTED, COMPLETED_IO, COMPLETED_SHARED, COMPLETED_LOCAL
• operation: invalid, readv (vectored read) or writev (vectored write)
• off, length: offset and size of I/O operation
• target, target_desc: what we’re reading/writing (typically relations)
• result: UNKNOWN, OK, PARTIAL, WARNING, ERROR

-- Summary of current AIO handles by state and result
SELECT state, result, count(*) AS cnt, pg_size_pretty(sum(length)) AS total_size
FROM pg_aios GROUP BY state, result ORDER BY state, result;

       state      |  result | cnt | total_size
------------------+---------+-----+------------
 COMPLETED_SHARED | OK      |   1 | 688 kB
 SUBMITTED        | UNKNOWN |   6 | 728 kB

-- In-flight async I/O handles
SELECT COUNT(*) AS aio_handles, SUM(length) AS aio_bytes FROM pg_aios;

 aio_handles | aio_bytes
-------------+-----------
           7 |     57344

-- Sessions currently waiting on I/O
SELECT COUNT(*) AS sessions_waiting_on_io FROM pg_stat_activity WHERE wait_event_type = 'IO';

 sessions_waiting_on_io
------------------------
                      9

SELECT pid, state, operation, pg_size_pretty(length) AS io_size, target_desc, result
FROM pg_aios ORDER BY pid, io_id;

 pid |   state   | operation |   io_size  |                             target_desc                                 | result
-----+-----------+-----------+------------+-------------------------------------------------------------------------+---------
  51 | SUBMITTED | readv     | 688 kB     | blocks 670470..670555 in file "pg_tblspc/16647/PG_18_202506291/5/16716" | UNKNOWN
  63 | SUBMITTED | readv     | 8192 bytes | block 1347556 in file "pg_tblspc/16647/PG_18_202506291/5/16719"         | UNKNOWN
  65 | SUBMITTED | readv     | 688 kB     | blocks 671236..671321 in file "pg_tblspc/16647/PG_18_202506291/5/16716" | UNKNOWN
  66 | SUBMITTED | readv     | 8192 bytes | block 1344674 in file "pg_tblspc/16647/PG_18_202506291/5/16719"         | UNKNOWN
  67 | SUBMITTED | readv     | 8192 bytes | block 1337819 in file "pg_tblspc/16647/PG_18_202506291/5/16719"         | UNKNOWN
  68 | SUBMITTED | readv     | 688 kB     | blocks 672002..672087 in file "pg_tblspc/16647/PG_18_202506291/5/16716" | UNKNOWN
  69 | SUBMITTED | readv     | 688 kB     | blocks 673964..674049 in file "pg_tblspc/16647/PG_18_202506291/5/16716" | UNKNOWN

pg_stat_io: Cumulative I/O stats

SELECT backend_type, context, sum(reads) AS reads, 
pg_size_pretty(sum(read_bytes)) AS read_bytes, 
round(sum(read_time)::numeric, 2) AS read_ms, sum(writes) AS writes, 
pg_size_pretty(sum(write_bytes)) AS write_bytes,
round(sum(write_time)::numeric, 2) AS write_ms, sum(extends) AS extends,
pg_size_pretty(sum(extend_bytes)) AS extend_bytes
FROM pg_stat_io
WHERE object = 'relation' AND backend_type IN ('client backend')
GROUP BY backend_type, context
ORDER BY backend_type, context;

   backend_type |  context  |  reads  | read_bytes |  read_ms  | writes | write_bytes | write_ms | extends | extend_bytes
----------------+-----------+---------+------------+-----------+--------+-------------+----------+---------+--------------
 client backend | bulkread  |   13833 | 9062 MB    | 124773.28 |      0 | 0 bytes     |     0.00 |         |
 client backend | bulkwrite |       0 | 0 bytes    |      0.00 |      0 | 0 bytes     |     0.00 |       0 | 0 bytes
 client backend | init      |       0 | 0 bytes    |      0.00 |      0 | 0 bytes     |     0.00 |       0 | 0 bytes
 client backend | normal    | 2265214 | 17 GB      | 553940.57 |      0 | 0 bytes     |     0.00 |       0 | 0 bytes
 client backend | vacuum    |       0 | 0 bytes    |      0.00 |      0 | 0 bytes     |     0.00 |       0 | 0 bytes


-- Top tables by heap blocks read and cache hit ratio
SELECT relid::regclass AS table_name, heap_blks_read, heap_blks_hit,
ROUND( CASE WHEN heap_blks_read + heap_blks_hit = 0 THEN 0
ELSE heap_blks_hit::numeric / (heap_blks_read + heap_blks_hit) * 100 END, 2) AS cache_hit_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC LIMIT 20;

      table_name      | heap_blks_read | heap_blks_hit | cache_hit_pct
----------------------+----------------+---------------+---------------
 table1               |       18551282 |       3676632 |         16.54
 table2               |        1513673 |     102222970 |         98.54
 table3               |          19713 |       1034435 |         98.13
...


-- Top indexes by index blocks read and cache hit ratio
SELECT relid::regclass AS table_name, indexrelid::regclass AS index_name,
idx_blks_read, idx_blks_hit 
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC LIMIT 20;

 table_name |    index_name   | idx_blks_read | idx_blks_hit
------------+-----------------+---------------+--------------
 table1     | idx_table1_date |        209289 |          141
 table2     | table2_pkey     |         37221 |      1223747
 table3     | table3_pkey     |          9825 |      3143947
...

SELECT pg_stat_reset_shared('io');

Then run our workload and query pg_stat_io again to see how many bytes were read/written and how much time was spent waiting on I/O.

Conclusion

PostgreSQL is a registered trademark oftThe PostgreSQL Community Association of Canada.