How to Understand the New PostgreSQL 18 System Views

PostgreSQL 18 introduces two powerful new system views that expose deep internal behavior that was never visible before: pg_shmem_allocations_numa and pg_aios.

Both views are designed for database administrators, performance engineers, and contributors who need to analyze memory and I/O behavior at a granular level. These additions arrive as PostgreSQL expands NUMA-aware memory reporting and introduces built-in asynchronous I/O.

Before exploring these new views, it is important to understand the existing infrastructure they extend.

Understanding the Existing View: pg_shmem_allocations

PostgreSQL 17 and PostgreSQL 18 already include the system view named pg_shmem_allocations.

This view exposes shared memory allocations inside the main shared memory segment. It shows:

postgres=# \d pg_shmem_allocations
          View "pg_catalog.pg_shmem_allocations"
     Column     |  Type  | Collation | Nullable | Default 
----------------+--------+-----------+----------+---------
 name           | text   |           |          | 
 off            | bigint |           |          | 
 size           | bigint |           |          | 
 allocated_size | bigint |           |          | 

This helps developers understand which components of PostgreSQL consume shared memory and how much.

However, the limitation of this older view is that it is not NUMA-aware.

On modern multi-socket machines, memory is allocated from multiple NUMA nodes, and understanding how much memory PostgreSQL allocates per NUMA node is essential for memory-bound workloads.

You can get the query of this view by this command

postgres=# \d+ pg_shmem_allocations
                      View "pg_catalog.pg_shmem_allocations"
     Column     |  Type  | Collation | Nullable | Default | Storage  | Description 
----------------+--------+-----------+----------+---------+----------+-------------
 name           | text   |           |          |         | extended | 
 off            | bigint |           |          |         | plain    | 
 size           | bigint |           |          |         | plain    | 
 allocated_size | bigint |           |          |         | plain    | 
View definition:
 SELECT name,
    off,
    size,
    allocated_size
   FROM pg_get_shmem_allocations() pg_get_shmem_allocations(name, off, size, allocated_size);

You can also explore the meta data of this function by this way

postgres=# \df+ pg_get_shmem_allocations()
List of functions
-[ RECORD 1 ]-------+--------------------------------------------------------------------------
Schema              | pg_catalog
Name                | pg_get_shmem_allocations
Result data type    | SETOF record
Argument data types | OUT name text, OUT off bigint, OUT size bigint, OUT allocated_size bigint
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | postgres
Security            | invoker
Leakproof?          | no
Access privileges   | postgres=X/postgres                                                      +
                    | pg_read_all_stats=X/postgres
Language            | internal
Internal name       | pg_get_shmem_allocations
Description         | allocations from the main shared memory segment

Explore the result of this view

postgres=# select * from pg_shmem_allocations limit 5;
          name          |    off     |  size   | allocated_size 
------------------------+------------+---------+----------------
 subtransaction         |   42363264 | 8553856 |        8553856
 notify                 | 8849310080 |  133760 |         133760
 Shared Memory Stats    | 8849443840 |  315552 |         315648
 pg_stat_functions hash | 9151289216 |    2896 |           2944
 serializable           | 8814577152 |  267424 |         267520

Purpose of each column:

name

• Tells you which subsystem owns this memory block
• subtransaction > subtransaction control structures
• notify > LISTEN/NOTIFY queue and slots

off (bigint)

• Byte offset from the very start of the shared memory segment
• Shows exactly where in the huge shared memory area this block begins

size (bigint)

• Exact number of bytes the subsystem originally requested
• This is the logical size the code believes it has

allocated_size (bigint)

• Actual number of bytes reserved in shared memory

Usually equal to size, but sometimes larger because PostgreSQL adds padding for alignment (64-byte, 128-byte, or even 1KB boundaries)

Purpose of the New View: pg_shmem_allocations_numa

PostgreSQL 18 introduces pg_shmem_allocations_numa, and its purpose is to extend shared memory visibility with NUMA awareness.

The view exposes:

postgres=# \d pg_shmem_allocations_numa
 
     View "pg_catalog.pg_shmem_allocations_numa"
  Column   |  Type   | Collation | Nullable | Default 
-----------+---------+-----------+----------+---------
 name      | text    |           |          | 
 numa_node | integer |           |          | 
 size      | bigint  |           |          | 

This means PostgreSQL now reports how much shared memory each NUMA node contributes to a particular memory allocation.

You can get this system view’s query by this way

postgres=# \d+ pg_shmem_allocations_numa
 
                  View "pg_catalog.pg_shmem_allocations_numa"
  Column   |  Type   | Collation | Nullable | Default | Storage  | Description 
-----------+---------+-----------+----------+---------+----------+-------------
 name      | text    |           |          |         | extended | 
 numa_node | integer |           |          |         | plain    | 
 size      | bigint  |           |          |         | plain    | 
View definition:
 SELECT name,
    numa_node,
    size
   FROM pg_get_shmem_allocations_numa() pg_get_shmem_allocations_numa(name, numa_node, size);

You can also explore the meta data of this function used in this query

postgres=# \df+ pg_get_shmem_allocations_numa()
List of functions
-[ RECORD 1 ]-------+------------------------------------------------------
Schema              | pg_catalog
Name                | pg_get_shmem_allocations_numa
Result data type    | SETOF record
Argument data types | OUT name text, OUT numa_node integer, OUT size bigint
Type                | func
Volatility          | volatile
Parallel            | safe
Owner               | postgres
Security            | invoker
Leakproof?          | no
Access privileges   | postgres=X/postgres                                  +
                    | pg_read_all_stats=X/postgres
Language            | internal
Internal name       | pg_get_shmem_allocations_numa
Description         | NUMA mappings for the main shared memory segment

The view is backed by the internal function pg_get_shmem_allocations_numa, located in the PostgreSQL source code at:

src/backend/storage/ipc/shmem.c

Go to line 584 to see the definition of 

pg_get_shmem_allocations_numa(PG_FUNCTION_ARGS).

This view becomes important when:

1. PostgreSQL is running on a multi-socket NUMA server.
2. A user wants to optimize memory locality.
3. Developers need to analyze shmem allocations across NUMA nodes
4. Performance tuning requires understanding cross-node memory access penalties.

This additional NUMA information can be critical for understanding latency spikes, buffer access performance, and large shared memory region allocation behavior.

Explore the result of this view

postgres=# select * from pg_shmem_allocations_numa limit 5;
          name          | numa_node |  size   
------------------------+-----------+---------
 subtransaction         |         0 | 8556544
 notify                 |         0 |  135168
 Shared Memory Stats    |         0 |  319488
 pg_stat_functions hash |         0 |    8192
 serializable           |         0 |  270336
(5 rows)

Purpose of each columns

name Identical to pg_shmem_allocations — tells you which PostgreSQL subsystem owns this shared memory block.

numa_node (integer) The physical NUMA node (socket/memory controller) on which this entire block was allocated. 0 = first socket, 1 = second socket, etc. In your current output, every single block lives on node 0 — that is the key observation.

size (bigint) Exact number of bytes allocated for this block on that specific NUMA node. Note: this is the real allocated size including alignment padding (same as allocated_size in pg_shmem_allocations).

Asynchronous I/O in PostgreSQL 18

Before exploring the second new system view, pg_aios, it is important to understand the asynchronous I/O (AIO) framework newly added to PostgreSQL 18.

PostgreSQL 17 and earlier depended primarily on synchronous file I/O. This meant that backend processes had to wait for disk operations to complete.

PostgreSQL 18 introduces true asynchronous I/O support with multiple execution methods:

• Sync
• Worker
• io_uring

The active method depends on the value of io_method.

Key parameters supporting this feature include:

io_combine_limit

Defines the maximum size of combined read and write operations for a single I/O request.

name: io_combine_limit

setting: 16

unit: 8kB

io_max_combine_limit

A server-wide limit that clamps io_combine_limit.

setting: 16

unit: 8kB

io_max_concurrency

Maximum number of simultaneous asynchronous I/O operations a process can perform.

setting: 64

io_method

Defines which asynchronous I/O framework to use.

Values include:

• Sync
• Worker
• io_uring

Example:

io_method = worker

io_workers

Specifies the number of I/O worker processes. Relevant when io_method=worker.

Example:

io_workers = 16

These parameters are located in the category Resource Usage / I/O.

The introduction of asynchronous I/O allows PostgreSQL 18 to:

• Perform reads and writes without blocking backend processes
• Improve concurrency
• Enhance performance on systems with high-speed storage
• Support offloading I/O to dedicated workers or io_uring

With this foundation in place, PostgreSQL adds a new system view to expose the internal state of running asynchronous I/O operations.

Purpose of pg_aios

The new system view pg_aios provides detailed information about all in-progress asynchronous I/O operations in the server.

postgres=# \d+ pg_aios 
                              View "pg_catalog.pg_aios"
     Column      |   Type   | Collation | Nullable | Default | Storage  | Description 
-----------------+----------+-----------+----------+---------+----------+-------------
 pid             | integer  |           |          |         | plain    | 
 io_id           | integer  |           |          |         | plain    | 
 io_generation   | bigint   |           |          |         | plain    | 
 state           | text     |           |          |         | extended | 
 operation       | text     |           |          |         | extended | 
 off             | bigint   |           |          |         | plain    | 
 length          | bigint   |           |          |         | plain    | 
 target          | text     |           |          |         | extended | 
 handle_data_len | smallint |           |          |         | plain    | 
 raw_result      | integer  |           |          |         | plain    | 
 result          | text     |           |          |         | extended | 
 target_desc     | text     |           |          |         | extended | 
 f_sync          | boolean  |           |          |         | plain    | 
 f_localmem      | boolean  |           |          |         | plain    | 
 f_buffered      | boolean  |           |          |         | plain    | 
View definition:
 SELECT pid,
    io_id,
    io_generation,
    state,
    operation,
    off,
    length,
    target,
    handle_data_len,
    raw_result,
    result,
    target_desc,
    f_sync,
    f_localmem,
    f_buffered
   FROM pg_get_aios() pg_get_aios(pid, io_id, io_generation, state, operation, off, length, target, handle_data_len, raw_result, result, target_desc, f_sync, f_localmem, f_buffered);

This is a low-level diagnostic tool for understanding:

• Which backend initiated an I/O
• What type of operation it is performing
• The offset and length of the I/O
• The state of the asynchronous request
• Whether the operation is synchronous, buffered, or uses local memory
• How the lower-level I/O layer responded

This level of detail was previously not available, making it difficult to analyze complex I/O performance problems.

The view is backed by the internal function pg_get_aios, located in PostgreSQL source code at

src/backend/storage/aio/aio_funcs.c

Go to line 48 to explore the definition and structure of asynchronous I/O tracking.

Explore the result of this view

postgres=# select * from pg_aios limit 1;
-[ RECORD 1 ]---+---------------------------------------------
pid             | 730095
io_id           | 331
io_generation   | 12
state           | COMPLETED_SHARED
operation       | readv
off             | 434176
length          | 131072
target          | smgr
handle_data_len | 16
raw_result      | 131072
result          | OK
target_desc     | blocks 53..68 in file "base/5335543/5468066"
f_sync          | f
f_localmem      | f
f_buffered      | t

Purpose of each columns

This system view is especially useful for:

• Performance debugging
• Understanding worker behavior under high load
• Tracking io_uring vs worker vs sync operations
• Monitoring slow I/O requests
• Observing how PostgreSQL schedules and completes I/O under concurrency

Exploring the Source Code

Developers can dive deeper into these features inside the PostgreSQL source tree.

System view definitions reside in:

src/backend/catalog/system_views.sql

Important internal functions:

pg_get_shmem_allocations_numa

Path:

src/backend/storage/ipc/shmem.c

Search for the function starting around line 584.

pg_get_aios

Path:

src/backend/storage/aio/aio_funcs.c

Look around line 48 for the implementation.

Studying these functions helps contributors and performance engineers understand how PostgreSQL collects and exposes internal system metrics.

Conclusion

PostgreSQL 18 significantly expands its introspection capabilities with two new system views: pg_shmem_allocations_numa and pg_aios.

The first provides NUMA-aware shared memory visibility, essential for tuning modern servers.

The second exposes asynchronous I/O operations in real time, helping developers and administrators diagnose performance issues and understand I/O behavior at a deep level.

Combined with new asynchronous I/O infrastructure and configurable parameters such as io_method, io_workers, io_combine_limit, and others, PostgreSQL 18 delivers a major leap forward in transparency and performance monitoring.

These tools make PostgreSQL more predictable, more observable, and more adaptable to modern hardware, especially large NUMA servers and high-performance storage systems.