This is maybe one the biggest steps forward for PostgreSQL: PostgreSQL 18 will come with support for asynchronous I\/O. Traditionally PostgreSQL relies on the operating system to hide the latency of writing to disk, which is done synchronously and can lead to double buffering (PostgreSQL shared buffers and the OS file cache). This is most important for WAL writes, as PostgreSQL must make sure that changes are flushed to disk and needs to wait until it is confirmed. <\/p>\n\n\n\n
Before we do some tests let’s see what’s new from a parameter perspective. One of the new parameters is The default is “worker” and the maximum number of worker processes to perform asynchronous is controller by This can also be seen on the operating system:<\/p>\n\n\n The other possible settings for As usual: What follows are just some basic tests. Test for your own, in your environment with your specific workload to get some meaningful numbers. Especially if you test in a public cloud, be aware that the numbers might not show you the full truth.<\/p>\n\n\n\n We’ll do the tests on an AWS EC2 t3.large<\/a> instance running Debian 12. The storage volume is gp3<\/a> with ext4 (default settings):<\/p>\n\n\n PostgreSQL was initialized with the default settings:<\/p>\n\n\n The following settings have been changed:<\/p>\n\n\n The first test is data loading. How long does that take when io_method is set to worker (3 times in a row), this gives a data set of around 1536MB:<\/p>\n\n\n The same test with “sync”:<\/p>\n\n\n … and finally “io_uring”:<\/p>\n\n\n There not much difference for “sync” and “io_uring”, but “worker” clearly is slower for that type of workload.<\/p>\n\n\n\n Moving on, let’s see how that looks like for a standard pgbench benchmark. We’ll start with “io_uring” as this is the current setting:<\/p>\n\n\n Same test with “worker”:<\/p>\n\n\n … and finally “sync”:<\/p>\n\n\n As you see there is not much difference, no matter the io_method. Let’s stress the system a bit more (only putting the summaries here):<\/p>\n\n\n Some picture, there is not much difference. One last test, hammering the system even more:<\/p>\n\n\n At least for these tests, there is not much difference between the three settings for io_method (sync seems to be a bit slower), but I think this is still great. For such a massive change getting to the same performance as before is great. Things in PostgreSQL improve all the time, and I am sure there will be a lot of improvements in this area as well.<\/p>\n\n\n\n Usually I link to the commit here, but in this case that would be a whole bunch of commits. To everyone involved in this, a big thank you.<\/p>\n","protected":false},"excerpt":{"rendered":" This is maybe one the biggest steps forward for PostgreSQL: PostgreSQL 18 will come with support for asynchronous I\/O. Traditionally PostgreSQL relies on the operating system to hide the latency of writing to disk, which is done synchronously and can lead to double buffering (PostgreSQL shared buffers and the OS file cache). This is most […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-37962","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"\nio_method<\/code>:<\/p>\n\n\n\npostgres=# show io_method;\n io_method \n-----------\n worker\n(1 row)\n<\/pre><\/div>\n\n\n
io_workers<\/code>:<\/p>\n\n\n\npostgres=# show io_workers;\n io_workers \n------------\n 3\n(1 row)\n<\/pre><\/div>\n\n\n
\npostgres=# \\! ps aux | grep "io worker" | grep -v grep\npostgres 29732 0.0 0.1 224792 7052 ? Ss Apr08 0:00 postgres: pgdev: io worker 1\npostgres 29733 0.0 0.2 224792 9884 ? Ss Apr08 0:00 postgres: pgdev: io worker 0\npostgres 29734 0.0 0.1 224792 7384 ? Ss Apr08 0:00 postgres: pgdev: io worker 2\n<\/pre><\/div>\n\n\n
io_method<\/code> are:<\/p>\n\n\n\n\n
io_uring<\/code>: Asynchronous I\/O using io_uring<\/a><\/li>\n\n\n\nsync<\/code>: The behavior before PostgreSQL 18, do synchronous I\/O<\/li>\n<\/ul>\n\n\n\nio_workers<\/code> only has an effect if io_method<\/code> is set to “worker”, which is the default configuration.<\/p>\n\n\n\n\npostgres@ip-10-0-1-209:~$ grep proc \/proc\/cpuinfo \nprocessor : 0\nprocessor : 1\npostgres@ip-10-0-1-209:~$ free -g\n total used free shared buff\/cache available\nMem: 7 0 4 0 3 7\nSwap: 0 0 0\npostgres@ip-10-0-1-209:~$ mount | grep 18\n\/dev\/nvme1n1 on \/u02\/pgdata\/18 type ext4 (rw,relatime)\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ \/u01\/app\/postgres\/product\/18\/db_0\/bin\/initdb --pgdata=\/u02\/pgdata\/18\/data\/\nThe files belonging to this database system will be owned by user "postgres".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale "C.UTF-8".\nThe default database encoding has accordingly been set to "UTF8".\nThe default text search configuration will be set to "english".\n\nData page checksums are enabled.\n\nfixing permissions on existing directory \/u02\/pgdata\/18\/data ... ok\ncreating subdirectories ... ok\nselecting dynamic shared memory implementation ... posix\nselecting default "max_connections" ... 100\nselecting default "autovacuum_worker_slots" ... 16\nselecting default "shared_buffers" ... 128MB\nselecting default time zone ... Etc\/UTC\ncreating configuration files ... ok\nrunning bootstrap script ... ok\nperforming post-bootstrap initialization ... ok\nsyncing data to disk ... ok\n\ninitdb: warning: enabling "trust" authentication for local connections\ninitdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n \/u01\/app\/postgres\/product\/18\/db_0\/bin\/pg_ctl -D \/u02\/pgdata\/18\/data\/ -l logfile start\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ echo "shared_buffers='2GB'" >> \/u02\/pgdata\/18\/data\/postgresql.auto.conf \npostgres@ip-10-0-1-209:~$ echo "checkpoint_timeout='20min'" >> \/u02\/pgdata\/18\/data\/postgresql.auto.conf \npostgres@ip-10-0-1-209:~$ echo "random_page_cost=1.1" >> \/u02\/pgdata\/18\/data\/postgresql.auto.conf\npostgres@ip-10-0-1-209:~$ echo "max_wal_size='8GB'" >> \/u02\/pgdata\/18\/data\/postgresql.auto.conf \npostgres@ip-10-0-1-209:~$ \/u01\/app\/postgres\/product\/18\/db_0\/bin\/pg_ctl --pgdata=\/u02\/pgdata\/18\/data\/ -l \/dev\/null start\npostgres@ip-10-0-1-209:~$ \/u01\/app\/postgres\/product\/18\/db_0\/bin\/psql -c "select version()"\n version \n---------------------------------------------------------------------------------------\n PostgreSQL 18devel dbi services build on x86_64-linux, compiled by gcc-12.2.0, 64-bit\n(1 row)\npostgres@ip-10-0-1-209:~$ export PATH=\/u01\/app\/postgres\/product\/18\/db_0\/bin\/:$PATH\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\nNOTICE: table "pgbench_accounts" does not exist, skipping\nNOTICE: table "pgbench_branches" does not exist, skipping\nNOTICE: table "pgbench_history" does not exist, skipping\nNOTICE: table "pgbench_tellers" does not exist, skipping\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 31.85 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 24.82 s, vacuum 0.35 s, primary keys 6.68 s).\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 31.97 s (drop tables 0.24 s, create tables 0.00 s, client-side generate 25.44 s, vacuum 0.34 s, primary keys 5.93 s).\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 30.72 s (drop tables 0.26 s, create tables 0.00 s, client-side generate 23.93 s, vacuum 0.55 s, primary keys 5.98 s).\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ psql -c "alter system set io_method='sync'"\nALTER SYSTEM\npostgres@ip-10-0-1-209:~$ pg_ctl --pgdata=\/u02\/pgdata\/18\/data\/ restart -l \/dev\/null\npostgres@ip-10-0-1-209:~$ psql -c "show io_method"\n io_method \n-----------\n sync\n(1 row)\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 20.89 s (drop tables 0.29 s, create tables 0.01 s, client-side generate 14.70 s, vacuum 0.45 s, primary keys 5.44 s).\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 21.57 s (drop tables 0.20 s, create tables 0.00 s, client-side generate 16.13 s, vacuum 0.46 s, primary keys 4.77 s).\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 21.44 s (drop tables 0.20 s, create tables 0.00 s, client-side generate 16.04 s, vacuum 0.52 s, primary keys 4.67 s).\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ psql -c "alter system set io_method='io_uring'"\nALTER SYSTEM\npostgres@ip-10-0-1-209:~$ pg_ctl --pgdata=\/u02\/pgdata\/18\/data\/ restart -l \/dev\/null\nwaiting for server to shut down.... done\nserver stopped\nwaiting for server to start.... done\nserver started\npostgres@ip-10-0-1-209:~$ psql -c "show io_method"\n io_method \n-----------\n io_uring\n(1 row)\n\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 20.63 s (drop tables 0.35 s, create tables 0.01 s, client-side generate 14.92 s, vacuum 0.47 s, primary keys 4.88 s).\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 20.81 s (drop tables 0.29 s, create tables 0.00 s, client-side generate 14.43 s, vacuum 0.46 s, primary keys 5.63 s).\npostgres@ip-10-0-1-209:~$ pgbench -i -s 100\ndropping old tables...\ncreating tables...\ngenerating data (client-side)...\nvacuuming... \ncreating primary keys...\ndone in 21.11 s (drop tables 0.24 s, create tables 0.00 s, client-side generate 15.63 s, vacuum 0.53 s, primary keys 4.70 s).\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 567989\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.113 ms\ninitial connection time = 8.996 ms\ntps = 946.659673 (without initial connection time)\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 557640\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.152 ms\ninitial connection time = 6.994 ms\ntps = 929.408406 (without initial connection time)\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 563613\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.129 ms\ninitial connection time = 16.351 ms\ntps = 939.378627 (without initial connection time)\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ psql -c "alter system set io_method='worker'"\nALTER SYSTEM\npostgres@ip-10-0-1-209:~$ pg_ctl --pgdata=\/u02\/pgdata\/18\/data\/ restart -l \/dev\/null\nwaiting for server to shut down............. done\nserver stopped\nwaiting for server to start.... done\nserver started\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 549176\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.185 ms\ninitial connection time = 7.189 ms\ntps = 915.301403 (without initial connection time)\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 564898\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.124 ms\ninitial connection time = 11.332 ms\ntps = 941.511304 (without initial connection time)\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 563041\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.131 ms\ninitial connection time = 9.120 ms\ntps = 938.412979 (without initial connection time)\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ psql -c "alter system set io_method='sync'"\nALTER SYSTEM\npostgres@ip-10-0-1-209:~$ pg_ctl --pgdata=\/u02\/pgdata\/18\/data\/ restart -l \/dev\/null\nwaiting for server to shut down............ done\nserver stopped\nwaiting for server to start.... done\nserver started\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 560420\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.141 ms\ninitial connection time = 12.000 ms\ntps = 934.050237 (without initial connection time)\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 560077\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.143 ms\ninitial connection time = 7.204 ms\ntps = 933.469665 (without initial connection time)\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=2 --jobs=2\npgbench (18devel dbi services build)\nstarting vacuum...end.\ntransaction type: <builtin: TPC-B (sort of)>\nscaling factor: 100\nquery mode: simple\nnumber of clients: 2\nnumber of threads: 2\nmaximum number of tries: 1\nduration: 600 s\nnumber of transactions actually processed: 566150\nnumber of failed transactions: 0 (0.000%)\nlatency average = 2.120 ms\ninitial connection time = 7.579 ms\ntps = 943.591451 (without initial connection time)\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=10 --jobs=10\n## sync\ntps = 2552.785398 (without initial connection time)\ntps = 2505.476064 (without initial connection time)\ntps = 2542.419230 (without initial connection time)\n## io_uring\ntps = 2511.138931 (without initial connection time)\ntps = 2529.705311 (without initial connection time)\ntps = 2573.195751 (without initial connection time)\n## worker\ntps = 2531.657962 (without initial connection time)\ntps = 2523.854335 (without initial connection time)\ntps = 2515.490351 (without initial connection time)\n<\/pre><\/div>\n\n\n
\npostgres@ip-10-0-1-209:~$ pgbench --time=600 --client=20 --jobs=20\n## worker\ntps = 2930.268033 (without initial connection time)\ntps = 2799.499964 (without initial connection time)\ntps = 3033.491153 (without initial connection time)\n## io_uring\ntps = 2942.542882 (without initial connection time)\ntps = 3061.487286 (without initial connection time)\ntps = 2995.175169 (without initial connection time)\n## sync\ntps = 2997.654084 (without initial connection time)\ntps = 2924.269626 (without initial connection time)\ntps = 2753.853272 (without initial connection time)\n<\/pre><\/div>\n\n\n