Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

pganalyze.com

Post reply on HN
Print view View original

41–50 of 159 posts

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#41

Post by nu11ptr »

lfittl wrote: post #10
nu11ptr wrote: post #2

Is this new async. I/O feature for Linux only? I know Windows has IOCP and also now an IORing implementation of its own (Less familiar with macOS capabilities other than POSIX AIO). https://learn.microsoft.com/en-us/windows/win32/api/ioringap... Update: Most of the comments below seem to be missing the fact that Windows now also has an IORing implementation, as I mentioned above. Comparison article here: https://wind…

It depends on the I/O method - as described in the article, "io_uring" is only available on Linux (and requires building with liburing, as well as io_uring to be enabled in the Kernel), but the default (as of beta1) is actually "worker", which works on any operating system. The "worker" method uses a dedicated pool of I/O worker processes that run in the background, and whilst not as performant as io_uring in our ben…

> "io_uring" is only available on Linux

Windows now also has IORing (see my comment above)

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#42

Post by nu11ptr »

stingraycharles wrote: post #4
nu11ptr wrote: post #2

Is this new async. I/O feature for Linux only? I know Windows has IOCP and also now an IORing implementation of its own (Less familiar with macOS capabilities other than POSIX AIO). https://learn.microsoft.com/en-us/windows/win32/api/ioringap... Update: Most of the comments below seem to be missing the fact that Windows now also has an IORing implementation, as I mentioned above. Comparison article here: https://wind…

Sounds like this feature is based on io_uring which is a Linux feature. I would be surprised if they implemented async io on Windows before they would on Linux given the user/deployment base being very Linux-heavy.

> this feature is based on io_uring which is a Linux feature

And now also a Windows feature, see my comment above for info

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#43

Post by greenavocado »

ARandomerDude wrote: post #30
seunosewa wrote: post #18

Earlier quoted context omitted.

Yep. It's a low hanging fruit they should've picked years ago. They will eventually figure out using b-trees for tables too.

Can you elaborate on this B-tree part of your comment? I know B-tree is the default index type in pg, but it sounds like there’s more to the story that I’m not familiar with.

PostgreSQL uses heap files for the primary table storage, not B-trees. In PostgreSQL table data is primarily stored in heap files (unordered collections of pages/blocks). Indexes (including primary key indexes) use B-trees (specifically B+ trees). When you query a table via an index, the B-tree index points to locations in the heap file

InnoDB uses a clustered index approach. The primary key index is a B-tree. The actual table data is stored in the leaf nodes of this B-tree. Secondary indexes point to the primary key.

One is not better than the other in general terms. InnoDB's clustered B-tree approach shines when:

You frequently access data in primary key order

Your workload has many range scans on the primary key

You need predictable performance for primary key lookups

Your data naturally has a meaningful ordering that matches your access patterns

PostgreSQL's heap approach excels when:

You frequently update non-key columns (less page splits/reorganization)

You have many secondary indexes (they're smaller without primary keys)

Your access patterns vary widely and don't follow one particular field

You need faster table scans when indexes aren't applicable

I personally find PostgreSQL's approach more flexible for complex analytical workloads with unpredictable access patterns, while InnoDB's clustered approach feels more optimized for OLTP workloads with predictable key-based access patterns. The "better" system depends entirely on your specific workload, data characteristics, and access patterns.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#44

Post by the8472 »

codegeek wrote: post #29
martinald wrote: post #13

I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important. We're effectively optimising c…

You probably already know this but I will say it anyway. These cloud services like AWS are not succeeding in enterprise because they have outdated hardware. They succeed because in enterprise, CIOs and CTOs want something that is known, has a brand and everyone else uses it. It's like the old adage of "No one got fired for using IBM". Now it is "No one gets fired for hosting with AWS no matter how ridiculous the cost…

> No one gets fired for hosting with AWS

But consider the counterfactual: Non-realized customers because AWS certified solutions architect(tm) software couldn't deliver the price/perf they would have needed.

At $work this is a very real problem because a software system was built on api gateway, lambdas, sqs and a whole bunch of other moving pieces (serverless! scalable! easy compliance!) that combined resulted in way too much latency to meet a client's goal.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#45

Post by coder543 »

maherbeg wrote: post #26
martinald wrote: post #13

I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important. We're effectively optimising c…

instance store on aws can give up to 3.3mil iops https://aws.amazon.com/blogs/aws/now-available-i3-instances-... - the main problem is just using networked storage.

That quoted IOPS number is only with an 8-disk stripe (requiring the full instance), even if you don't need 488GB of RAM or a $3600/mo instance, I believe.

The per-disk performance is still nothing to write home about, and 8 actually fast disks would blow this instance type out of the water.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#46

Post by anarazel »

PaulHoule wrote: post #7
stingraycharles wrote: post #4

Earlier quoted context omitted.

Sounds like this feature is based on io_uring which is a Linux feature. I would be surprised if they implemented async io on Windows before they would on Linux given the user/deployment base being very Linux-heavy.

For a long time ago there have been APIs to do asynchronous file I/O on the books for Linux but they weren't worth using because they didn't really speed anything up.

They sped up things for a long time - but only when using unbuffered IO. The new thing with io_uring is that it also accelerates buffered IO. In the initial version it was all through kernel worker threads, but these days several filesystems have better paths for common cases.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#47

Post by anarazel »

spwa4 wrote: post #23
nu11ptr wrote: post #2

Is this new async. I/O feature for Linux only? I know Windows has IOCP and also now an IORing implementation of its own (Less familiar with macOS capabilities other than POSIX AIO). https://learn.microsoft.com/en-us/windows/win32/api/ioringap... Update: Most of the comments below seem to be missing the fact that Windows now also has an IORing implementation, as I mentioned above. Comparison article here: https://wind…

Yes, although Windows has had async I/O since Windows NT 3.1, their API is still not supported by Postgres.

FWIW, there are prototype patches for an IOCP based io_method. We just couldn't get them into an acceptable state for PG 18. I barely survived getting in what we did...

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#48

Post by greenavocado »

p_ing wrote: post #33
stingraycharles wrote: post #4

Earlier quoted context omitted.

Sounds like this feature is based on io_uring which is a Linux feature. I would be surprised if they implemented async io on Windows before they would on Linux given the user/deployment base being very Linux-heavy.

Yeah, surprise Linux had to play catch up to a Windows 1994 release! Same with the scheduler, I'd argue Windows does OOM better than Linux today... Windows even had the concept of io_uring before, but network only with Registered I/O back in the Windows 8 (8.1?) days. Linux still lacks the "all I/O is async" NT has. The underlying kernel and executive of Windows aren't primitive pieces of trash. They're quite advance…

How difficult would it be to completely tear out the Windows desktop experience and just use the system and display drivers without the rest? Has anybody attempted such a feat?

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#49

Post by p_ing »

hansvm wrote: post #38
p_ing wrote: post #35

Is io_uring still plagued by security issues enabled by it's use? Or have those largely been fixed? My understanding was many Linux admins (or even distros by default?) were disabling io_uring.

https://github.com/axboe/liburing/discussions/1047

Thanks. It looks like it is still going through growing pains.

https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=io_uring

https://www.theregister.com/2025/04/29/linux_io_uring_securi...

But most of the 'off by default' are from ~2023 and not a current concern.

Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

#50

Post by the8472 »

maherbeg wrote: post #26
martinald wrote: post #13

I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important. We're effectively optimising c…

instance store on aws can give up to 3.3mil iops https://aws.amazon.com/blogs/aws/now-available-i3-instances-... - the main problem is just using networked storage.

The NVMe on other instance types is quite throttled. E.g. on a G5.4xlarge instance EBS is limited to 593MB/s and 20000IOPS while instance-attached NVMe is limited to 512MB/s (read) at 125000IOPS, a fraction of IO what a workstation or gaming PC with similar GPU and RAM would have. And stopping the instance wipes it, which means you can't do instance warmup with those, everything must be populated at boot.
Post reply on HN