postgres io - ghdrako/doc_snipets GitHub Wiki

-- Checking your AIO settings and description
SELECT name, setting, short_desc
FROM pg_settings
WHERE name IN (
               'io_method',                -- Selects the method for executing asynchronous I/O.
               'io_workers',               -- Number of IO worker processes, for io_method=worker.
               'io_max_concurrency',       -- Max number of IOs that one process can execute simultaneously.
               'effective_io_concurrency'  -- Number of simultaneous requests that can be handled efficiently by the disk subsystem.
              )
ORDER BY name;

I/O Method	Use case	Explaination
worker	Asynchronous I/O (default)	A background worker processes I/O requests, allowing the main process to continue execution. This is the default setting in PostgreSQL 18.
sync	Synchronous I/O	If you want to keep the same behavior as PostgreSQL 17 with blocking reads, you can set it to sync instead.
io_uring	Specific to Linux	Creates a shared ring buffer that minimizes system call overhead. While this usually gives the best performance, it's important to keep in mind that you might be opening yourself up to security risks by using io_uring. To use it, you would also need your PostgreSQL to be built with the --with-liburing flag.

Monitoring asynchronous activity

PostgreSQL 18 introduces the pg_aios system view to provide insights into asynchronous I/O operations and requests currently in progress:

-- Show me the AIO
SELECT * FROM pg_aios;

You can also use pg_stat_activity to view information about background worker processes by searching for the io worker backend type.

SELECT *
FROM pg_stat_activity
WHERE backend_type = 'io worker';

io_uring in docker

docker run -d --name=pg18io_uring  --security-opt seccomp=unconfined  -e POSTGRES_PASSWORD=secret  postgres:18 -c io_method=io_uring

docker exec -it  pg18io_uring psql -U postgres -c  "show  io_method;"

 io_method 
-----------
 io_uring
(1 row)

$ docker pull postgres:18.0-alpine3.22
18.0-alpine3.22: Pulling from library/postgres
Digest: sha256:fadc2b63607986d054ccb7302ca6fedc7bb9b6586d03f2110ae3fbc1ce806693
Status: Downloaded newer image for postgres:18.0-alpine3.22
docker.io/library/postgres:18.0-alpine3.22

$ docker run -d --name=pg18alpine-io_uring \
>    --security-opt seccomp=unconfined \
>   -e POSTGRES_PASSWORD=secret \
>   postgres:18.0-alpine3.22 -c io_method=io_uring
4e4b7bf49f02bc95067bb959ccd2bb7b8ea5c97ef2e4cce7037c74fc201f7f38

$ docker exec -it pg18alpine-io_uring psql -U postgres -c  "show  io_method; select version();"
 io_method 
-----------
 io_uring
(1 row)