12 ms·

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

572 points by lfittl 1y ago

nu11ptr 1y ago
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/ioringapi/ 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://windows-internals.com/ioring-vs-io_uring-a-comparison-of-windows-and-linux-implementations/ https://windows-internals.com/ioring-vs-io_uring-a-compariso...
- stingraycharles 1y ago
  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.
  - PaulHoule 1y ago
    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.
    - immibis 1y ago
      IIRC they literally just did the sync I/O on a worker thread.
      - DmitryOlshansky 1y ago
        That’s the POSIX AIO. Linux has had AIO that was only supported for direct I/O and had certain limitations making it awkward to use.
    - anarazel 1y ago
      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.
  - p_ing 1y ago
    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 advanced, ruined by PMs and the Ads division throwing crap on top. And yes, Windows' I/O Ring is a near 1:1 copy of the Linux implementation, but IOCP/OVERLAPPED I/O data structure preceded it since NT's first release. This isn't a pissing match, just we all hope that kernel devs learn from each other and take the best ideas. Sometimes we, IT, don't get to choose the OS we run -- it's dictated by the apps the business requires.
    - greenavocado 1y ago
      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?
      - dboreham 1y ago
        There are several such things. The Windows installer uses one. X-Box uses another.
      - p_ing 1y ago
        There is Windows Server Core which removes everything but a CLI, but you still have the normal login experience, you still have a "desktop" (no start menu, taskbar, etc), you can still launch normal Win32 apps... for the most part (task manager, notepad, and so on). Win32 is also responsible for core Services, which means you can't de-Windows-ify Windows and strip it down to an NT API-only. All other personalities (OS/2, POSIX, SFU) have a dependency on Win32, as well. You're still running the WindowServer of course; it's part of the Executive. That said, with a bunch of modifications, NTDEV did get Windows 11 down to it's bare minimum, and text only to boot. So I guess it's technically possible, though not useful. https://www.youtube.com/watch?v=SL6t_iuitxM https://www.youtube.com/watch?v=SL6t_iuitxM
  - nu11ptr 1y ago
    > this feature is based on io_uring which is a Linux feature And now also a Windows feature, see my comment above for info
    - zokier 1y ago
      IoRing != io_uring. They are similar APIs but not the same thing.
- deleted 1y ago
  [deleted]
- lfittl 1y ago
  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 benchmark, did clearly outperform the "sync" method (which is the same as what Postgres currently has in 17 and older).
  - nu11ptr 1y ago
    > "io_uring" is only available on Linux Windows now also has IORing (see my comment above)
    - severino 1y ago
      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.
      - nu11ptr 1y ago
        Misunderstood - thought you were saying ioring was a feature specific to linux, but you meant specific to linux on Postgres.
- spwa4 1y ago
  Yes, although Windows has had async I/O since Windows NT 3.1, their API is still not supported by Postgres.
  - nu11ptr 1y ago
    Yes, that was IOCP, however, Windows now also has IORing (see my comment above)
  - anarazel 1y ago
    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...
    - spwa4 1y ago
      I didn't mean that as a criticism on Postgres, certainly not on Postgres developers. I really look forward to evaluating this change. I'm just reminiscing a bit about admiring Windows NT ... and being incredibly disappointed by the market.
- macdice 1y ago
  I am not a Windows guy but I (with help) managed to get IOCP working for this in a basic prototype. Will share publicly soon. I also sketched out an IoRing version (if you are interested in helping debug and flesh that out let me know!). Main learnings: the IOCP version can't do asynchronous flush! Which we want. The IoRing version can! But it can't do scatter/gather AKA vector I/O yet! Which is an essential feature for buffer pool implementation. So actually I am basically waiting for IoRing to add support for that before taking it too seriously (I can see they are working on it because the ops are present in an enum, it's just that the build functions are missing). So my guess is that in a year or so we should be able to run all PostgreSQL disk I/O through IoRing on Windows. Maybe? Another complications is that it really wants to be multithreaded (consuming completions for IOs started in another process requires a lot of hoop jumping, I made it work but...) This will resolve itself naturally with ongoing work to make PostgreSQL multithreaded. The next problem is that IoRing doesn't support sockets! So in future work on async networking (early prototypes exist) we will likely also need IOCP for that part.
  - p_ing 1y ago
    Look into Registered I/O for sockets.
- mijoharas 1y ago
  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.
niux 1y ago
I recently deployed Postgres on a dedicated Hetzner EX-44 server (20 cores, 64GB RAM, 2x 512GB NVMe SSDs in RAID 1) for €39/month. The price-to-performance ratio is exceptional, providing enterprise-level capacity at a fraction of typical cloud costs. For security, I implemented TailScale which adds only ~5ms of latency while completely eliminating public network exposure - a worthwhile tradeoff for the significant security benefits. My optimization approach includes: - Workload-specific configuration generated via PGTune (https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/) - Real-time performance monitoring with PgHero for identifying bottlenecks - Automated VACUUM ANALYZE operations scheduled via pgcron targeting write-heavy tables, which prevents performance degradation and helps me sleep soundly - A custom CLI utility I built for ZSTD-compressed backups that achieves impressive compression ratios while maintaining high throughput, with automatic S3 uploading: https://github.com/overflowy/pgbackup https://github.com/overflowy/pgbackup This setup has been remarkably stable and performant, handling our workloads with substantial headroom for growth.
- trollied 1y ago
  That’s great, but you need a solid HA/backup and recovery strategy if you even remotely care about your data.
  - deedubaya 1y ago
    He literally mentioned backups and not everyone needs HA?
- Tostino 1y ago
  I would absolutely use another backup utility (additionally if you want) if I were you (barman, pgbackrest, etc). You are just wrapping pgdump, which is not a full featured backup solution. Great for a snapshot... Use some of the existing tools and you get point-in-time recovery, easy restores to hot standbys for replication, a good failover story, backup rotations, etc.
  - niux 1y ago
    The reason I wrote my own tool is because I couldn't find anything for Pg17 at the time and pgbackrest seemed overkill for my needs. Also, the CLI handles backup rotations as well. Barman looks interesting though, I'll definitely have a look, thanks!
the8472 1y ago
On linux there also is preadv2(..., RWF_NOWAIT) which can be used to do optimistic non-blocking read from the page cache. That might be useful for io_method = worker to shave off a bit of latency. Try reading on the main thread with NOWAIT and only offload to a worker thread when that fails.
- anarazel 1y ago
  FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease. Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost. It turns out, as some of the results in Lukas' post show, that the offload to the worker is often actually beneficial particularly when the data is in the kernel page cache - it parallelizes the memory copy from kernel to userspace and postgres' checksum computation. Particularly on Intel server CPUs, which have had pretty mediocre per-core memory bandwidth in the last ~ decade, memory bandwidth turns out to be a bottleneck for page cache access and checksum computations. Edit: Fix negation
  - gavinray 1y ago
    Do you think there's a possibility of Direct IO being adopted at some point in the future now that AIO is available?
    - anarazel 1y ago
      > Do you think there's a possibility of Direct IO being adopted at some point in the future now that AIO is available? Explicitly a goal. You can turn it on today, with a bunch of caveats (via debug_io_direct=data). If you have the right workload - e.g. read only and lots of seqscans, bitmap index scans etc you can see rather substantial perf gains. But it'll suck in any cases in 18. We need at least: - AIO writes in checkpointer, bgwriter and backend buffer replacement (think bulk loading data with COPY) - readahead support in a few more places, most crucially index range scan (works out ok today if the heap is correlated with the index, sucks badly otherwise) EDIT: Formatting
skeptrune 1y ago
How close is this to the way MySQL does it with InnoDB? It appears to be about the same.
- seunosewa 1y ago
  Yep. It's a low hanging fruit they should've picked years ago. They will eventually figure out using b-trees for tables too.
  - ARandomerDude 1y ago
    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.
    - greenavocado 1y ago
      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.
pseudopersonal 1y ago
Does anyone know when the update allowing more concurrent connections is dropping, so we can stop using pgbouncer?
- __s 1y ago
  That'll likely need conversion from process per connection, so not any time soon
  - anarazel 1y ago
    FWIW, there actually are some ongoing efforts towards that - including several preparatory changes in PG 18. Still lots more work, but we are working towards it. https://wiki.postgresql.org/wiki/Multithreading https://wiki.postgresql.org/wiki/Multithreading
- hoherd 1y ago
  I recently read a great article exploring what would change if they were to switch from processes to threads for each connection. Running a connection pooler didn't seem so bad to me after reading it. https://medium.com/@tusharmalhotra_81114/why-postgresql-chooses-processes-over-threads-and-why-it-matters-9011a98e1d84 https://medium.com/@tusharmalhotra_81114/why-postgresql-choo...
- CoolCold 1y ago
  Do you know, how well pgbouncer works with prepared statement nowadays? We slowly migrating our clusters to pg16/new bouncer and feel unsure on stability and reliability of prepared statements support
shayonj 1y ago
Very nicely written post! I'd love to start running these in production on NVMe and hope its something major cloud providers start to offer ASAP. The performance gains are _extremely_ attractive
- CoolCold 1y ago
  My understanding is that local storage like NVMe is not much affected as latency is very low compared to network based block devices like ebs
martinald 1y ago
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 critical infrastructure tech for ~2010 hardware because that's when big cloud got going and there has been so few price reductions on things since then vs the underlying hardware costs. Obviously a consumer NVMe is not "enterprise" but my point is we are 3+ orders of magnitude off performance on cheap consumer hardware vs very expensive 'enterprise' AWS/big cloud costs.
- binary132 1y ago
  Meanwhile people are running things on raspberry pi home clusters thinking they’re winning
  - brulard 1y ago
    Maybe they are. With NVMe hat, you get decent IO performance
    - adgjlsfhk1 1y ago
      It's still moderately bad. Raspberry pi is limited to 2 gen3 pcie lanes which is ~4-8x slower than the drive (and you will likely be further limited by cpu speed)
      - ttfkam 1y ago
        If the engineering demand is lower than the engineering supply, you're still winning. If your transactions per second only amounts to 60% of the Pi's capacity for a given use case, why complain?
  - TacticalCoder 1y ago
    [dead]
Tostino 1y ago
Thank you for the effort that went into getting this committed. I remember seeing the first discussions about async I/O (and using io_uring) like 6 or 7 years ago. Amazing amount of work to get the design right. Looking forward to the other places that async I/O can be used in future Postgres releases now that the groundwork is done.
p_ing 1y ago
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.
- hansvm 1y ago
  https://github.com/axboe/liburing/discussions/1047 https://github.com/axboe/liburing/discussions/1047
  - p_ing 1y ago
    Thanks. It looks like it is still going through growing pains. https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=io_uring https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=io_uring https://www.theregister.com/2025/04/29/linux_io_uring_security_flaw/?td=readmore 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.
- znpy 1y ago
  Disabling io_uring because “guy on the internet said so” or “$faang_company says so” is beyond dumb. One should evaluate the risk according to their specific use case. It can be a good idea to disable it of you run untrusted workloads (eg: other people’s containers, sharing the same kernel) but if you have a kernel on a machine (virtual or real) dedicated to your own workload you can pretty much keep using io_uring. There are other technologies to enforce security (eg: selinux emand similar).
  - wtallis 1y ago
    I think at this point, those "other technologies to enforce security" are the main area of concern for io_uring users: if those other security layers don't know about io_uring they won't apply any restrictions to it.
  - p_ing 1y ago
    Yes, every security mechanism should be reviewed to validate it is applicable, i.e. I had a vulnerable version of node running but I wasn't using the particular aspect of node so it was a non-issue (and amazingly, IT sec agreed!). But in the case of io_uring, it was outright bypassing other security layers. And while we all like to think we're running trusted services/code, we have to think about supply-chain attacks that may surprise us, or zero days, etc.
song 1y ago
Are there good performance comparisons between postgres, mariadb and percona? I'm really curious at this point in which case each of those database shine.
- KronisLV 1y ago
  Probably depends on the particular workload, but there are at least some attempts at benchmarking vaguely typical workloads: https://datasystemreviews.com/postgresql-vs-mariadb-performance-comparison.html https://datasystemreviews.com/postgresql-vs-mariadb-performa...
- ttfkam 1y ago
  Apples to apples, Postgres might lose, but that'd be tying both hands behind its back first. Remember that Postgres's feature set is far larger than those alternatives. If you can use a range with an exclusion constraint, an unnest with an array, or the like, you'll be seeing Postgres leave the alternatives in the dust. Imagine writing a benchmark comparing programming languages, but the benchmark only includes idioms that all tested languages shared in common. Wouldn't be a fair comparison, would it?
  - song 1y ago
    Yes, but I wonder how many apps using ORMs actually use all features from postgres. So, in the practical use case of typical Rails, Laravel, Django or expressJS, what would the performance look like?
    - ttfkam 1y ago
      That's an indictment of ORMs more than an evaluation of database options.
kev009 1y ago
A lot of work has gone into FreeBSD's aio(4) so it will be interesting to see how that works, because it doesn't have the drawbacks of Linux/glibc aio.
- tiffanyh 1y ago
  Would you mind expanding more on this topic. Is FreeBSD doing anything significantly different and/or better?
  - kev009 1y ago
    Sure two separate things really. Linux aio (the POSIX one, not the broader concept that now includes io_uring) has been fundamentally bad. Part of it is some of the implementation is delegated to user space in glibc as worker threads. https://lwn.net/Articles/671649/ https://lwn.net/Articles/671649/ surveys the issues. I have not done a deep dive into this topic in a long time but as far as I know the situation never greatly improved. io_uring does not suffer from the problems, although it is a new and non-standard API with associated pros and cons. Thomas Munro has gone into some of the benefits and also gaps of FreeBSD's aio(4) vs io_uring here https://wiki.postgresql.org/wiki/FreeBSD/AIO https://wiki.postgresql.org/wiki/FreeBSD/AIO. Notably, because the implementation is in kernel and has received continuous improvement it is gradually removing downsides and there are several users that need it to work well. This document undersells the problems of ZFS though: the ARC is a necromanced buffer cache which Sun ironically worked very hard to remove (http://mcvoy.com/lm/papers/SunOS.vm_arch.pdf http://mcvoy.com/lm/papers/SunOS.vm_arch.pdf) and nobody has fixed this in 20 years. But for UFS or raw block devs or vendor file systems that doesn't matter. FreeBSD being a complete "src" tree yields some advantages. In concrete, there are some in tree consumers like ctld (a CAM/iSCSI server) that have been used as vehicles to provide end to end implementation of things like NIC offload of complex protocols that play well with backend block devices such that you can make an offloaded data path that is pumped via asynchronous completion events on both sides (network and block). A related whitepaper https://www.chelsio.com/wp-content/uploads/resources/T6-100G-DDP-FreeBSD.pdf https://www.chelsio.com/wp-content/uploads/resources/T6-100G... but this concept can be extended to iSCSI, NVMeOF etc. It seems NVMeOF work sponsored by Chelsio is yielding a lot of the finishing touches https://papers.freebsd.org/2023/eurobsdcon/baldwin-implementing_nvme_over_fabrics_in_freebsd/ https://papers.freebsd.org/2023/eurobsdcon/baldwin-implement.... I believe my colleagues are also improving aio to further optimize the Netflix case of KTLS NIC with data on disk but I am not the right person to extrapolate on that.
WhyNotHugo 1y ago
It's pretty disappointing that simply using O_NONBLOCK doesn't work as expected on regular files. It would be such a simple and portable mechanism to do async I/O using the same interfaces that we already use for networking.
dbbk 1y ago
This looks promising! Wonder if it's coming to Neon
- clarkbw 1y ago
  It is! And we plan to have 18 out as quickly as we did 17; on the day of release.
gitroom 1y ago
insane how long it took postgres to get async i/o right - feels like all the big changes spark a million little tradeoffs, right? you think stuff like io_uring is finally gonna push postgres to catch up with the clouds
cryptonector 1y ago
I've been following the `AIO 2.5` thread on the PG mailing lists, and I've been salivating at the thought of this being released.
curtisszmania 1y ago
[dead]
drdrek 1y ago
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.
- freilanzer 1y ago
  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 https://postgresml.com available, the choice for Postgres was even easier.
  - cpursley 1y ago
    Postgres is Enough: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
  - powerbook5300CS 1y ago
    Why not MongoDB?
    - belter 1y ago
      Because it's web scale...
    - IgorPartola 1y ago
      I guess they didn’t need anything web scale.
    - ricw 1y ago
      The question should be the other way around: why mongodb? It’s not ACID compliant so has major down sides…
      - okeuro49 1y ago
rks404 1y ago
I remember back in the day when mysql vs postgres was a real debate with mysql being the popular fave. Genuinely interesting to see that postgres eventually won.

572 points by lfittl 1y ago

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/ioringapi/ 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://windows-internals.com/ioring-vs-io_uring-a-comparison-of-windows-and-linux-implementations/ https://windows-internals.com/ioring-vs-io_uring-a-compariso...

stingraycharles 1y ago
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.
- PaulHoule 1y ago
  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.
  - immibis 1y ago
    IIRC they literally just did the sync I/O on a worker thread.
    - DmitryOlshansky 1y ago
      That’s the POSIX AIO. Linux has had AIO that was only supported for direct I/O and had certain limitations making it awkward to use.
  - anarazel 1y ago
    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.
- p_ing 1y ago
  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 advanced, ruined by PMs and the Ads division throwing crap on top. And yes, Windows' I/O Ring is a near 1:1 copy of the Linux implementation, but IOCP/OVERLAPPED I/O data structure preceded it since NT's first release. This isn't a pissing match, just we all hope that kernel devs learn from each other and take the best ideas. Sometimes we, IT, don't get to choose the OS we run -- it's dictated by the apps the business requires.
  - greenavocado 1y ago
    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?
    - dboreham 1y ago
      There are several such things. The Windows installer uses one. X-Box uses another.
    - p_ing 1y ago
      There is Windows Server Core which removes everything but a CLI, but you still have the normal login experience, you still have a "desktop" (no start menu, taskbar, etc), you can still launch normal Win32 apps... for the most part (task manager, notepad, and so on). Win32 is also responsible for core Services, which means you can't de-Windows-ify Windows and strip it down to an NT API-only. All other personalities (OS/2, POSIX, SFU) have a dependency on Win32, as well. You're still running the WindowServer of course; it's part of the Executive. That said, with a bunch of modifications, NTDEV did get Windows 11 down to it's bare minimum, and text only to boot. So I guess it's technically possible, though not useful. https://www.youtube.com/watch?v=SL6t_iuitxM https://www.youtube.com/watch?v=SL6t_iuitxM
- nu11ptr 1y ago
  > this feature is based on io_uring which is a Linux feature And now also a Windows feature, see my comment above for info
  - zokier 1y ago
    IoRing != io_uring. They are similar APIs but not the same thing.
deleted 1y ago
[deleted]
lfittl 1y ago
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 benchmark, did clearly outperform the "sync" method (which is the same as what Postgres currently has in 17 and older).
- nu11ptr 1y ago
  > "io_uring" is only available on Linux Windows now also has IORing (see my comment above)
  - severino 1y ago
    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.
    - nu11ptr 1y ago
      Misunderstood - thought you were saying ioring was a feature specific to linux, but you meant specific to linux on Postgres.
spwa4 1y ago
Yes, although Windows has had async I/O since Windows NT 3.1, their API is still not supported by Postgres.
- nu11ptr 1y ago
  Yes, that was IOCP, however, Windows now also has IORing (see my comment above)
- anarazel 1y ago
  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...
  - spwa4 1y ago
    I didn't mean that as a criticism on Postgres, certainly not on Postgres developers. I really look forward to evaluating this change. I'm just reminiscing a bit about admiring Windows NT ... and being incredibly disappointed by the market.
macdice 1y ago
I am not a Windows guy but I (with help) managed to get IOCP working for this in a basic prototype. Will share publicly soon. I also sketched out an IoRing version (if you are interested in helping debug and flesh that out let me know!). Main learnings: the IOCP version can't do asynchronous flush! Which we want. The IoRing version can! But it can't do scatter/gather AKA vector I/O yet! Which is an essential feature for buffer pool implementation. So actually I am basically waiting for IoRing to add support for that before taking it too seriously (I can see they are working on it because the ops are present in an enum, it's just that the build functions are missing). So my guess is that in a year or so we should be able to run all PostgreSQL disk I/O through IoRing on Windows. Maybe? Another complications is that it really wants to be multithreaded (consuming completions for IOs started in another process requires a lot of hoop jumping, I made it work but...) This will resolve itself naturally with ongoing work to make PostgreSQL multithreaded. The next problem is that IoRing doesn't support sockets! So in future work on async networking (early prototypes exist) we will likely also need IOCP for that part.
- p_ing 1y ago
  Look into Registered I/O for sockets.
mijoharas 1y ago
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.

niux 1y ago

I recently deployed Postgres on a dedicated Hetzner EX-44 server (20 cores, 64GB RAM, 2x 512GB NVMe SSDs in RAID 1) for €39/month. The price-to-performance ratio is exceptional, providing enterprise-level capacity at a fraction of typical cloud costs. For security, I implemented TailScale which adds only ~5ms of latency while completely eliminating public network exposure - a worthwhile tradeoff for the significant security benefits. My optimization approach includes: - Workload-specific configuration generated via PGTune (https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/) - Real-time performance monitoring with PgHero for identifying bottlenecks - Automated VACUUM ANALYZE operations scheduled via pgcron targeting write-heavy tables, which prevents performance degradation and helps me sleep soundly - A custom CLI utility I built for ZSTD-compressed backups that achieves impressive compression ratios while maintaining high throughput, with automatic S3 uploading: https://github.com/overflowy/pgbackup https://github.com/overflowy/pgbackup This setup has been remarkably stable and performant, handling our workloads with substantial headroom for growth.

trollied 1y ago
That’s great, but you need a solid HA/backup and recovery strategy if you even remotely care about your data.
- deedubaya 1y ago
  He literally mentioned backups and not everyone needs HA?
Tostino 1y ago
I would absolutely use another backup utility (additionally if you want) if I were you (barman, pgbackrest, etc). You are just wrapping pgdump, which is not a full featured backup solution. Great for a snapshot... Use some of the existing tools and you get point-in-time recovery, easy restores to hot standbys for replication, a good failover story, backup rotations, etc.
- niux 1y ago
  The reason I wrote my own tool is because I couldn't find anything for Pg17 at the time and pgbackrest seemed overkill for my needs. Also, the CLI handles backup rotations as well. Barman looks interesting though, I'll definitely have a look, thanks!

the8472 1y ago

On linux there also is preadv2(..., RWF_NOWAIT) which can be used to do optimistic non-blocking read from the page cache. That might be useful for io_method = worker to shave off a bit of latency. Try reading on the main thread with NOWAIT and only offload to a worker thread when that fails.

anarazel 1y ago
FWIW, I played with that - unfortunately it seems that the the overhead of doing twice the page cache lookups is a cure worse than the disease. Note that we do not offload IO to workers when doing I/O that the caller will synchronously wait for, just when the caller actually can do IO asynchronously. That reduces the need to avoid the offload cost. It turns out, as some of the results in Lukas' post show, that the offload to the worker is often actually beneficial particularly when the data is in the kernel page cache - it parallelizes the memory copy from kernel to userspace and postgres' checksum computation. Particularly on Intel server CPUs, which have had pretty mediocre per-core memory bandwidth in the last ~ decade, memory bandwidth turns out to be a bottleneck for page cache access and checksum computations. Edit: Fix negation
- gavinray 1y ago
  Do you think there's a possibility of Direct IO being adopted at some point in the future now that AIO is available?
  - anarazel 1y ago
    > Do you think there's a possibility of Direct IO being adopted at some point in the future now that AIO is available? Explicitly a goal. You can turn it on today, with a bunch of caveats (via debug_io_direct=data). If you have the right workload - e.g. read only and lots of seqscans, bitmap index scans etc you can see rather substantial perf gains. But it'll suck in any cases in 18. We need at least: - AIO writes in checkpointer, bgwriter and backend buffer replacement (think bulk loading data with COPY) - readahead support in a few more places, most crucially index range scan (works out ok today if the heap is correlated with the index, sucks badly otherwise) EDIT: Formatting

skeptrune 1y ago

How close is this to the way MySQL does it with InnoDB? It appears to be about the same.

seunosewa 1y ago
Yep. It's a low hanging fruit they should've picked years ago. They will eventually figure out using b-trees for tables too.
- ARandomerDude 1y ago
  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.
  - greenavocado 1y ago
    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.

pseudopersonal 1y ago

Does anyone know when the update allowing more concurrent connections is dropping, so we can stop using pgbouncer?

__s 1y ago
That'll likely need conversion from process per connection, so not any time soon
- anarazel 1y ago
  FWIW, there actually are some ongoing efforts towards that - including several preparatory changes in PG 18. Still lots more work, but we are working towards it. https://wiki.postgresql.org/wiki/Multithreading https://wiki.postgresql.org/wiki/Multithreading
hoherd 1y ago
I recently read a great article exploring what would change if they were to switch from processes to threads for each connection. Running a connection pooler didn't seem so bad to me after reading it. https://medium.com/@tusharmalhotra_81114/why-postgresql-chooses-processes-over-threads-and-why-it-matters-9011a98e1d84 https://medium.com/@tusharmalhotra_81114/why-postgresql-choo...
CoolCold 1y ago
Do you know, how well pgbouncer works with prepared statement nowadays? We slowly migrating our clusters to pg16/new bouncer and feel unsure on stability and reliability of prepared statements support

shayonj 1y ago

Very nicely written post! I'd love to start running these in production on NVMe and hope its something major cloud providers start to offer ASAP. The performance gains are _extremely_ attractive

CoolCold 1y ago
My understanding is that local storage like NVMe is not much affected as latency is very low compared to network based block devices like ebs

martinald 1y ago

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 critical infrastructure tech for ~2010 hardware because that's when big cloud got going and there has been so few price reductions on things since then vs the underlying hardware costs. Obviously a consumer NVMe is not "enterprise" but my point is we are 3+ orders of magnitude off performance on cheap consumer hardware vs very expensive 'enterprise' AWS/big cloud costs.

binary132 1y ago
Meanwhile people are running things on raspberry pi home clusters thinking they’re winning
- brulard 1y ago
  Maybe they are. With NVMe hat, you get decent IO performance
  - adgjlsfhk1 1y ago
    It's still moderately bad. Raspberry pi is limited to 2 gen3 pcie lanes which is ~4-8x slower than the drive (and you will likely be further limited by cpu speed)
    - ttfkam 1y ago
      If the engineering demand is lower than the engineering supply, you're still winning. If your transactions per second only amounts to 60% of the Pi's capacity for a given use case, why complain?
- TacticalCoder 1y ago
  [dead]

Tostino 1y ago

Thank you for the effort that went into getting this committed. I remember seeing the first discussions about async I/O (and using io_uring) like 6 or 7 years ago. Amazing amount of work to get the design right. Looking forward to the other places that async I/O can be used in future Postgres releases now that the groundwork is done.

p_ing 1y ago

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.

hansvm 1y ago
https://github.com/axboe/liburing/discussions/1047 https://github.com/axboe/liburing/discussions/1047
- p_ing 1y ago
  Thanks. It looks like it is still going through growing pains. https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=io_uring https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=io_uring https://www.theregister.com/2025/04/29/linux_io_uring_security_flaw/?td=readmore 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.
znpy 1y ago
Disabling io_uring because “guy on the internet said so” or “$faang_company says so” is beyond dumb. One should evaluate the risk according to their specific use case. It can be a good idea to disable it of you run untrusted workloads (eg: other people’s containers, sharing the same kernel) but if you have a kernel on a machine (virtual or real) dedicated to your own workload you can pretty much keep using io_uring. There are other technologies to enforce security (eg: selinux emand similar).
- wtallis 1y ago
  I think at this point, those "other technologies to enforce security" are the main area of concern for io_uring users: if those other security layers don't know about io_uring they won't apply any restrictions to it.
- p_ing 1y ago
  Yes, every security mechanism should be reviewed to validate it is applicable, i.e. I had a vulnerable version of node running but I wasn't using the particular aspect of node so it was a non-issue (and amazingly, IT sec agreed!). But in the case of io_uring, it was outright bypassing other security layers. And while we all like to think we're running trusted services/code, we have to think about supply-chain attacks that may surprise us, or zero days, etc.

song 1y ago

Are there good performance comparisons between postgres, mariadb and percona? I'm really curious at this point in which case each of those database shine.

KronisLV 1y ago
Probably depends on the particular workload, but there are at least some attempts at benchmarking vaguely typical workloads: https://datasystemreviews.com/postgresql-vs-mariadb-performance-comparison.html https://datasystemreviews.com/postgresql-vs-mariadb-performa...
ttfkam 1y ago
Apples to apples, Postgres might lose, but that'd be tying both hands behind its back first. Remember that Postgres's feature set is far larger than those alternatives. If you can use a range with an exclusion constraint, an unnest with an array, or the like, you'll be seeing Postgres leave the alternatives in the dust. Imagine writing a benchmark comparing programming languages, but the benchmark only includes idioms that all tested languages shared in common. Wouldn't be a fair comparison, would it?
- song 1y ago
  Yes, but I wonder how many apps using ORMs actually use all features from postgres. So, in the practical use case of typical Rails, Laravel, Django or expressJS, what would the performance look like?
  - ttfkam 1y ago
    That's an indictment of ORMs more than an evaluation of database options.

kev009 1y ago

A lot of work has gone into FreeBSD's aio(4) so it will be interesting to see how that works, because it doesn't have the drawbacks of Linux/glibc aio.

tiffanyh 1y ago
Would you mind expanding more on this topic. Is FreeBSD doing anything significantly different and/or better?
- kev009 1y ago
  Sure two separate things really. Linux aio (the POSIX one, not the broader concept that now includes io_uring) has been fundamentally bad. Part of it is some of the implementation is delegated to user space in glibc as worker threads. https://lwn.net/Articles/671649/ https://lwn.net/Articles/671649/ surveys the issues. I have not done a deep dive into this topic in a long time but as far as I know the situation never greatly improved. io_uring does not suffer from the problems, although it is a new and non-standard API with associated pros and cons. Thomas Munro has gone into some of the benefits and also gaps of FreeBSD's aio(4) vs io_uring here https://wiki.postgresql.org/wiki/FreeBSD/AIO https://wiki.postgresql.org/wiki/FreeBSD/AIO. Notably, because the implementation is in kernel and has received continuous improvement it is gradually removing downsides and there are several users that need it to work well. This document undersells the problems of ZFS though: the ARC is a necromanced buffer cache which Sun ironically worked very hard to remove (http://mcvoy.com/lm/papers/SunOS.vm_arch.pdf http://mcvoy.com/lm/papers/SunOS.vm_arch.pdf) and nobody has fixed this in 20 years. But for UFS or raw block devs or vendor file systems that doesn't matter. FreeBSD being a complete "src" tree yields some advantages. In concrete, there are some in tree consumers like ctld (a CAM/iSCSI server) that have been used as vehicles to provide end to end implementation of things like NIC offload of complex protocols that play well with backend block devices such that you can make an offloaded data path that is pumped via asynchronous completion events on both sides (network and block). A related whitepaper https://www.chelsio.com/wp-content/uploads/resources/T6-100G-DDP-FreeBSD.pdf https://www.chelsio.com/wp-content/uploads/resources/T6-100G... but this concept can be extended to iSCSI, NVMeOF etc. It seems NVMeOF work sponsored by Chelsio is yielding a lot of the finishing touches https://papers.freebsd.org/2023/eurobsdcon/baldwin-implementing_nvme_over_fabrics_in_freebsd/ https://papers.freebsd.org/2023/eurobsdcon/baldwin-implement.... I believe my colleagues are also improving aio to further optimize the Netflix case of KTLS NIC with data on disk but I am not the right person to extrapolate on that.

WhyNotHugo 1y ago

It's pretty disappointing that simply using O_NONBLOCK doesn't work as expected on regular files. It would be such a simple and portable mechanism to do async I/O using the same interfaces that we already use for networking.

dbbk 1y ago

This looks promising! Wonder if it's coming to Neon

clarkbw 1y ago
It is! And we plan to have 18 out as quickly as we did 17; on the day of release.

gitroom 1y ago

insane how long it took postgres to get async i/o right - feels like all the big changes spark a million little tradeoffs, right? you think stuff like io_uring is finally gonna push postgres to catch up with the clouds

cryptonector 1y ago

I've been following the `AIO 2.5` thread on the PG mailing lists, and I've been salivating at the thought of this being released.

curtisszmania 1y ago

[dead]

drdrek 1y ago

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.

freilanzer 1y ago
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 https://postgresml.com available, the choice for Postgres was even easier.
- cpursley 1y ago
  Postgres is Enough: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
- powerbook5300CS 1y ago
  Why not MongoDB?
  - belter 1y ago
    Because it's web scale...
  - IgorPartola 1y ago
    I guess they didn’t need anything web scale.
  - ricw 1y ago
    The question should be the other way around: why mongodb? It’s not ACID compliant so has major down sides…
    - okeuro49 1y ago

rks404 1y ago

I remember back in the day when mysql vs postgres was a real debate with mysql being the popular fave. Genuinely interesting to see that postgres eventually won.