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

101–110 of 159 posts

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

#101

Post by deedubaya »

trollied wrote: post #8
niux wrote: post #3

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 s…

That’s great, but you need a solid HA/backup and recovery strategy if you even remotely care about your data.

He literally mentioned backups and not everyone needs HA?

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

#102

Post by macdice »

kev009 wrote: post #75

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.

BTW I have patches for PostgreSQL AIO on FreeBSD, which I will propose for v19. It works pretty well! I was trying to keep out of Andres's way for the core architectural stuff and basic features ie didn't want to overload the pipes with confusing new topics for v18 :-)

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

#103

Post by TacticalCoder »

binary132 wrote: post #15
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…

Meanwhile people are running things on raspberry pi home clusters thinking they’re winning

[dead]

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

#105

Post by macdice »

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…

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.

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

#106

Post by natmaka »

niux wrote: post #3

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 s…

> Automated VACUUM ANALYZE operations scheduled via pgcron targeting write-heavy tables

Why don't you set (per table) the autovacuum_analyze_scale_factor parameter (or autovacuum_analyze_threshold) then let AUTOVACUUM handle this?

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

#107

Post by gmokki »

senderista wrote: post #77
the8472 wrote: post #71

Earlier quoted context omitted.

That doesn't speed up uerspace kernel memcopy, it just reduces cache churn. Despite its name it still goes through the page cache, it just triggers writeback and drops the pages once that's done. For example when copying to a tmpfs it makes zero difference since that lives entirely in memory.

So you're less dependent on the page replacement algorithm being scan-resistant, since you can use this flag for scan/loop workloads, right?

I would initially add it for WAL writes and reads. There should never be another read in normal operation.

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

#108

Post by olavgg »

dpacmittal wrote: post #95
niux wrote: post #3

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 s…

EX44 is 14 physical cores. Just wanted to point it out.

And no ECC memory?

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

#109

Post by rdtsc »

znpy wrote: post #81
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.

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.…

Oh sure, if you run your own server or sell your cloud as a service, you can enable it. But if you want to run on someone else’s setup, you have to play by their rules.

> Disabling io_uring because “guy on the internet said so” or “$faang_company says so” is beyond dumb.

I think it’s more like “$faang_company already disabled it in their hosts, so I am out of luck for my containers running on their cloud”

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

#110

Post by perching_aix »

slashdev wrote: post #79
the8472 wrote: post #51

Earlier quoted context omitted.

stop or hibernate kills it. https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/instance...

Yeah, so restart does not. Which means you can count on it about as much as a server of your own, if you could not repair the server. I know a database company that uses instance storage as the primary storage. It’s common.

Wasn't aware, interesting. I did consider it in the past as well, but the reliability aspect made me consider this as a moonshot rather than anything practical. Kind of weirdly validating to know there are (supposedly) database providers using it.

That said, a good bit of our environments are scheduled, so it still wouldn't be an option there without hacks (e.g. doing a compressed blockwise dump before shutting down and then a blockwise flash on startup).

Post reply on HN