Demystifying PostgreSQL MultiXact Caches: Performance Tuning, Architectural Shifts, and the Impact of PostgreSQL 17 through 19

Database optimization is rarely about tinkering with settings in a vacuum; it is a delicate exercise in understanding how internal memory structures map to application behavior. For years, PostgreSQL database administrators (DBAs) managing high-concurrency workloads have faced performance bottlenecks that seemed resistant to traditional tuning methods. Among the most opaque of these bottlenecks were the Sub-Lightweight Resource User (SLRU) caches, specifically those governing multixacts.

Historically hard-wired into PostgreSQL header files, these configurations offered no levers for administrators to pull when workloads hit edge cases. However, recent releases—starting with PostgreSQL 17 and extending through the upcoming PostgreSQL 19—have radically transformed this landscape by promoting these hard-coded values into tunable parameters. Understanding how to configure multixact_offset_buffers and multixact_member_buffers is no longer optional for high-throughput environments; it is essential for maintaining predictable database performance under heavy concurrent workloads.

Main Facts: What Are MultiXacts and SLRU Caches?

To comprehend why tuning these parameters matters, one must first understand what a MultiXact is and how PostgreSQL manages row locks under concurrent pressure.

In a standard PostgreSQL transaction model, when a single transaction modifies or locks a row, the database records the transaction ID directly in the tuple’s xmax field. However, multiple transactions frequently require a concurrent interest in the exact same row—such as multiple foreign-key validation checks simultaneously holding FOR KEY SHARE locks on a single parent row. When this occurs, storing a single transaction ID becomes impossible.

PostgreSQL solves this by converting the tuple’s xmax into a MultiXact ID. The actual mapping of this ID to the underlying list of participating transactions and their respective lock modes is stored on disk within the pg_multixact directory, divided into two distinct sub-directories:

1. offsets: Maps each MultiXact ID to a physical position.
2. members: Holds the actual transaction IDs and lock modes found at that position.

To prevent continuous, expensive disk reads when resolving these locks, PostgreSQL uses dedicated RAM caches known as SLRUs (Sub-Lightweight Resource Users). Through PostgreSQL 16, the caches governing these directories were strictly defined by compile-time constants: multixact_offset_buffers defaulted to a tiny pool of 8 to 16 buffers, while multixact_member_buffers sat at 16 to 32 buffers.

PostgreSQL 17 changed the paradigm by promoting these hard-wired limits into adjustable parameters. By default, multixact_offset_buffers is initialized to 16 buffers (holding the offsets of 32,768 multixacts through PostgreSQL 18), while multixact_member_buffers defaults to 32 buffers (holding 52,352 members). Whether these default allocations are generous or dangerously restrictive depends entirely on a single metric: how many multixacts your database system creates while its longest row-locking transaction remains open.

Chronology: From Hard-Coded Header Files to Dynamic Parameters

The journey toward parameterizing SLRU caches has been a multi-year architectural evolution within the PostgreSQL community, driven by scaling challenges on modern multi-core hardware.

The Era of Hard-Coded Defaults (PostgreSQL 14–16)

For years, the size of MultiXact SLRU caches was entirely decoupled from server hardware specifications. Whether a database instance was running on a local development laptop or an enterprise-grade database server equipped with 512GB of RAM, the multixact_offset and multixact_member caches remained locked at their diminutive default sizes. These values lived statically in C header files, meaning that adjusting them required modifying source code and recompiling the entire PostgreSQL binary—an impossible feat for standard production environments.

The Breakthrough (May 2020 – September 2024)

The catalyst for change arrived in May 2020, when core contributor Andrey Borodin initiated a crucial discussion on the PostgreSQL developer mailing lists. Borodin and other community members highlighted a severe architectural bottleneck: the single-threaded nature of the SLRU banking locks.

In PostgreSQL, each SLRU cache is divided into banks of 16 buffers, with each bank protected by its own exclusive lock. Under the old defaults, the offsets cache comprised a single bank of 16 buffers. Consequently, every single MultiXact lookup and creation across the entire database server had to queue up behind one single, highly contended exclusive lock. Borodin famously noted that this bottleneck effectively "turns the MultiXact Offset subsystem into single-threaded."

After extensive core development, the fix finally shipped in September 2024 with the release of PostgreSQL 17. The hard-wired constraints were removed, and multixact_offset_buffers and multixact_member_buffers became runtime parameters modifiable via ALTER SYSTEM or postgresql.conf.

The PostgreSQL 19 Architectural Shift

Continuing this modernization trend, PostgreSQL 19 introduces a profound structural alteration: widening multixact member offsets to 64 bits, effectively retiring a long-standing wraparound threat.

However, this upgrade doubles the memory footprint of offset entries. Through PostgreSQL 18, an offset entry consumed 4 bytes, allowing a single 8kB page to hold 2,048 entries. In PostgreSQL 19, every offset entry expands to 8 bytes, cutting page density in half down to 1,024 entries per page. While member pages remain unchanged, this structural shift means that upgrade paths from PostgreSQL 18 to 19 require careful attention to buffer allocations.

Supporting Data: Analyzing Cache Contention and Workload Scenarios

To see how these parameters behave in real-world scenarios, we can analyze performance metrics captured under simulated high-concurrency workloads using pg_stat_slru.

Scenario A: Short-Lived Transactions (Healthy State)

Under normal operating conditions where transactions are short and concise, old multixacts are rarely read. PostgreSQL can easily determine via tuple headers and transaction IDs whether an old multixact contains live members; if it does not, the database skips the lookup entirely without touching either cache.

In benchmark tests running 40 concurrent clients fighting over FOR KEY SHARE locks across 2,000 rows for 40 seconds on PostgreSQL 18.6, roughly 25,000 multixacts were generated. Despite this activity, the blks_read metric for both SLRU caches in pg_stat_slru remained stubbornly at zero. For standard short-transaction workloads, the default buffer allocations are more than sufficient.

Scenario B: The Long-Running Batch Job (The Bottleneck)

The system breaks down when a long-running transaction enters the picture. Consider a common enterprise pattern: a batch job that inserts hundreds of thousands of child rows in a single, uncommitted transaction. Each insert acquires a FOR KEY SHARE lock on its respective parent row, and the batch holds every one of those locks open until final commitment.

While this batch is running, every other concurrent transaction inserting a child row must perform two actions:

1. Create a new multixact combining itself and the running batch.
2. Read the existing multixact located on the parent row. Because the batch is active, this lookup cannot be bypassed.

When thousands of parent rows are locked simultaneously, these lookups scatter across a wide range of historical multixacts. If the number of busy parent rows exceeds the capacity of the default 32,768-multixact offset cache, performance plummets.

Performance Metrics under Stress (20,000 Locked Parents)

Running a benchmark on PostgreSQL 18.6 with one active session holding locks on 20,000 parents while eight concurrent clients execute 800,000 inserts reveals dramatic disk read amplification at default settings:

       name       | blks_zeroed | blks_hit | blks_read | blks_written
------------------+-------------+----------+-----------+--------------
 multixact_member |         978 |   853356 |    720399 |          978
 multixact_offset |         390 |   866810 |    707404 |          390

This output reveals nearly one physical read from disk per insert operation. However, simply scaling up the parameter values—setting multixact_offset_buffers = 512 and multixact_member_buffers = 2048—yields a dramatic transformation:

 multixact_member |         978 |  1573390 |         0 |            0
 multixact_offset |         390 |  1573753 |       405 |          216

Physical disk reads for members drop cleanly to zero, and offset reads plummet to negligible levels. Consequently, overall database throughput surges from roughly 35,500 inserts per second to approximately 42,000—a performance boost of roughly 20%.

Official Responses and Technical Specifications

When configuring these parameters, database administrators must adhere to strict validation rules enforced by the PostgreSQL core engine:

• Buffer Bounds: Both multixact_offset_buffers and multixact_member_buffers represent counts of 8kB memory buffers. The allowable range spans from a minimum of 16 buffers up to a maximum of 131,072 buffers (equivalent to 1GB of shared memory per cache).
• The Multiple-of-16 Rule: Both parameters must be exact multiples of 16. This restriction is hard-wired into the storage engine because SLRU caches are partitioned into banks of 16 buffers, with each bank governed by an independent lock.
• Configuration Enforcement: Attempting to assign an invalid value via ALTER SYSTEM results in an immediate syntax rejection:

  ERROR:  invalid value for parameter "multixact_offset_buffers": 24
  DETAIL:  "multixact_offset_buffers" must be a multiple of 16.

  Manually editing postgresql.conf with an invalid multiple will cause the PostgreSQL postmaster process to refuse server startup entirely.

• Context Restrictions: The parameter context is classified as postmaster. This means every configuration change requires a full database server restart; dynamic reloading via pg_reload_conf() is unsupported.

Furthermore, PostgreSQL 19 introduces a dedicated diagnostic function—pg_get_multixact_stats()—which reports the exact number of active multixacts and members currently in existence, eliminating the need for manual mathematical estimation.

Implications and Actionable Tuning Guidelines

For database engineers managing production infrastructure, these architectural changes carry profound practical implications.

How to Diagnose SLRU Bottlenecks

Monitoring should focus heavily on the pg_stat_slru system view, specifically targeting the rows designated as multixact_offset and multixact_member (capitalized as MultiXactOffset and MultiXactMember on PostgreSQL 16 and earlier).

• Healthy Systems: Show blks_read values sitting at or near zero and remaining entirely static over time.
• Distressed Systems: Exhibit rapidly climbing blks_read counters alongside specific wait events in pg_stat_activity, namely SlruRead, MultiXactOffsetSLRU, and MultiXactMemberSLRU.

Step-by-Step Tuning Strategy

1. Examine Workload Characteristics: If blks_read is flat, leave the default parameters untouched. Artificially inflating buffer sizes yields no performance gains for short-transaction workloads.
2. Identify Long-Running Transactions: If cache reads are climbing, query pg_stat_activity ordered by xact_start to isolate runaway long-running transactions or monolithic batch jobs. Whenever possible, refactor batch scripts to commit in smaller, discrete chunks.
3. Calculate Safe Buffer Minimums: If batch jobs cannot be shortened, calculate safe upper bounds using the formula: divide the total number of multixacts created during your longest active transaction by 2,048 (or 1,024 when running PostgreSQL 19).
4. Account for Version Differences:
   • On PostgreSQL 17 and 18, set the member cache to roughly four times your calculated offset buffer requirement, as member pages fill at least two-and-a-half times faster than offset pages.
   • On PostgreSQL 19, because offset entry sizes have doubled to 64 bits, double your previous multixact_offset_buffers value upon upgrading, while leaving multixact_member_buffers at its current setting.
5. Establish Safe Defaults: In the absence of precise workload measurements, setting multixact_offset_buffers = 512 and multixact_member_buffers = 2048 provides robust coverage for up to one million concurrent multixacts on PostgreSQL 17 and 18 at a cost of roughly 20MB of shared memory. On PostgreSQL 19, safe enterprise coverage is achieved via 1024 and 2048 buffers respectively, consuming approximately 25MB.

Because altering these values demands a server restart, administrators should avoid incremental, trial-and-error adjustments. Calculate your peak concurrency requirements carefully, apply the appropriate buffer allocations during scheduled maintenance windows, and ensure your PostgreSQL infrastructure is fully primed to handle the heavy demands of modern enterprise concurrency.