Postgres is such a cool project, I have so much respect for its maintainers and community! For me its the second most impactful OSS project in the business tech world behind Linux itself.
A real public good to be cherished and praised.
Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
111–120 of 159 posts
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#112Is 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…
Does anyone run postgres on windows?
Given that they still target it there must be a user base. Does anyone know the statistics of usage by platform? Anyone here use it?
Genuinely curious, windows backend dev is something I know very little about.
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#113Earlier quoted context omitted.
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)
Yes, but what's your point? It's not that we can take this new Postgres version and just compile it in a Windows machine supporting IORing, can we? It requires some effort and time -many times by volunteers- and given that most Postgres deployments are running under Linux, it's understandable that the developers focus on that platform first.
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#114Postgres is such a cool project, I have so much respect for its maintainers and community! For me its the second most impactful OSS project in the business tech world behind Linux itself. A real public good to be cherished and praised.
I recently started a ML project using text data and the choice was between MySQL and Postgres. Having looked at the respective features and pros and cons, the choice was immediately obvious. Also, with pgvector and https://postgresml.com available, the choice for Postgres was even easier.
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#115Earlier quoted context omitted.
From what I've seen a surprisingly large part of the overhead is due to SMAP when doing larger reads from the page cache - i.e. if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly. On Intel it's also not hard to simply reach the per-core memory bandwidth with modern storage HW. This matters most prominently for writes by the checkpointing…
> if I boot with clearcpuid=smap (not for prod use!), larger reads go significantly faster. On both Intel and AMD CPUs interestingly. Is there a page anywhere that collects these sorts of "turn the whole hardware security layer off" switches that can be flipped to get better throughput out of modern x86 CPUs, when your system has no real attack surface to speak of (e.g. air-gapped single-tenant HPC)?
On the kernel side there's a boot parameter for all of them: mitigations=off
Software that was compiled with additional fences may have to be recompiled to remove them.
https://www.kernel.org/doc/html/latest/admin-guide/kernel-pa...
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#116Earlier quoted context omitted.
SMAP overhead should be roughly constant, and I’d be quite surprised if it’s noticeable for large reads. Small reads are a different story.
It turns out to be the other way round, curiously. The bigger the reads (i.e. how much to read in one syscall) and the bigger the target area of the reads (how long before a target memory location is reused), the bigger the overhead of SMAP gets. If interesting I can dig up the reproducer I had at some point.
That is definitely interesting.
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#117Earlier quoted context omitted.
> "io_uring" is only available on Linux Windows now also has IORing (see my comment above)
Yes, but what's your point? It's not that we can take this new Postgres version and just compile it in a Windows machine supporting IORing, can we? It requires some effort and time -many times by volunteers- and given that most Postgres deployments are running under Linux, it's understandable that the developers focus on that platform first.
Misunderstood - thought you were saying ioring was a feature specific to linux, but you meant specific to linux on Postgres.
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#118Earlier quoted context omitted.
EX44 is 14 physical cores. Just wanted to point it out.
And no ECC memory?
EX44’s are Intel 13500’s so no, not ECC.
I have one, I use it for running an arma3 server, it’s been bulletproof for reliability though, hetzner have come a long way since the first time I used them years and years ago where it was a mess.
I’m almost at the point I’d use them for some thig that mattered (I.e. money was involved).
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#119Earlier 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.
> this feature is based on io_uring which is a Linux feature And now also a Windows feature, see my comment above for info
IoRing != io_uring. They are similar APIs but not the same thing.
Re: Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O
#120Earlier quoted context omitted.
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 ac…
Takes me back 20 years to using SQL Server! It let you choose clustered index or not IIRC.