pgaddict

I really wish they clearly documented the parameters used by each of the databases (or are we expected to dig those out of the .rs sources somehow?), and actual versions used (saying "Postgres" is ambiguous, it could be 14 or 18 - presumably it's 18, but who knows).

For example, I see they do this for Postgres:

`let max_wal_gb = (shared_buffers_gb).clamp(2, 16);`

2-16GB of WAL is not a lot, but I have no idea how large is the data set.

Interesting paper. I only started reading / digesting it, but:

- I'm not sure how to interpret the Figure 1. It says "Flash writes (KB) per page", but it doesn't really say which page sizes were used. AFAIK MySQL has 16K by default, PostgreSQL has 8K, LeanStore has 4K, but that which makes the numbers a bit hard to compare.

- Likewise, I'm a bit unsure about the doublewrite buffering in Postgres, described as "indirect". Postgres doesn't really do doublewrite (we really should, I think), we write pages to WAL and then to data files. I assume that's what is meant by "indirect" in the paper. But this very much depends on the checkpoint frequency and write pattern, as the FPI is written only for the first page change. I wonder if the results in the paper consider this. Maybe the workload is such that it always hits the page just once between checkpoints (i.e. a worst case). Also, the WAL part is nicely sequential, which should play nice with SSDs.

So why does it happen only with hugepages? Is the extra overhead / TLB pressure enough to trigger the issue in some way? Of is it because the regular pages get swapped out (which hugepages can't be)?

True. Unfortunately it's what index scans in Postgres do right now - it's the last "major" scan type not supporting some sort of prefetch (posix_fadvise or AIO). We're working on it, hopefully it'll get into PG19.

To the best of my knowledge, yes. Unfortunately the details of how it was calculated in ~2000 seem to be lost, but the person who did that described he did it like this. It's possible we forgot some important details, of course, but the intent was to use the same formula. Which is why I carefully described and published the scripts, so that other engineers can point out thinkos and suggest changes.

Damn, I copied the wrong command. I wanted to copy this one:

fio --filename=/dev/md127 --direct=1 --rw=randread --bs=8k --ioengine=io_uring --iodepth=1 --runtime=120 --numjobs=1 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly

i.e. with iodepth=1 and numjobs=1. Because this is what "mimics" index scan (without prefetch) on cold data. More or less.

The command I posted earlier does ~10GB/s on my RAID, which matches your data.

Good idea. It's an interesting historical question - when we picked 4.0 as the default ~25 years ago, how close was is to the calculated value? I was asking that myself. Unfortunately I don't have a machine with traditional HDD in my homelab anymore, but I'll see if I can run the test somewhere.

I wouldn't be all that surprised if this was (partially) due to Postgres being less optimized back then, which might have hidden some of the random vs. sequential differences. But that's just a wild guess.

Good point, I should have included that (the linked pgsql-hackers thread have some of this information, at least).

I've observed exactly this behavior on a wide range of hardware / environments, it's not very specific to particular SSDs models (at least not for reads, which is what the blog post was measuring). That's why I showed results from three very different systems.

Some information for the two physical machines:

1) ryzen: Ryzen 9 9900X, RAID0 with 4x Samsung 990 PRO 1TB (in Asus Hyper M.2 Gen5 card)

2) xeon: E5-2699v4, WD Ultrastar DC SN640 960GB (U.3)

I don't know what exactly is backing the SSD storage on the Azure instance.

There probably is some additional inefficiency when reading pages randomly (compared to sequential reads), but most of the difference is at the storage level. That is, SSDs can handle a lot of random I/O, but it's nowhere close to sequential reads.

For example, I have a RAID0 with 4 SSDs (Samsung 990 PRO, so consumer, but quite good for reads). And this is what fio says:

# random reads, 8K, direct IO, depth=1

fio --filename=device name --direct=1 --rw=randread --bs=4k --ioengine=libaio --iodepth=256 --runtime=120 --numjobs=4 --time_based --group_reporting --name=iops-test-job --eta-newline=1 --readonly

-> read: IOPS=19.1k, BW=149MiB/s (156MB/s)(4473MiB/30001msec)

# sequential reads, 8K, direct IO, depth=1

fio --filename=/dev/md127 --direct=1 --rw=read --bs=8k --ioengine=io_uring --iodepth=1 --runtime=30 --numjobs=1 --time_based --group_reporting --name=random-1 --eta-newline=1 --readonly

-> read: IOPS=85.5k, BW=668MiB/s (700MB/s)(19.6GiB/30001msec)

With buffered I/O, random read stay at ~19k IOPS, while sequential reads get to ~1M IOPS (thanks to read-ahead, either at the OS level, or in the SSD).

So part of this is sequential reads benefiting from implicit "prefetching", which reduces the observed cost of a page. But for random I/O there's no such thing, and so it seems more expensive.

It's more complex (e.g. sequential reads allow issuing larger reads), of course.

AFAIK these two joins are exactly the same once you get past the parsing. It's just a different way to write an inner join. It's translated into the same AST and so there's no difference in planning/execution.

That is part of the key idea, yes. It's more elaborate, because it can split the aggregate - it can do part of it before the join, and finalize it after the join. Similarly to what we do for parallel queries.

As for indexes, it can help, but not in this particular example - the "code" tables are tiny, and the planner adds Memoize nodes anyway, so it acts like an ad hoc index.

Indexes are more of a complementary improvement, not an alternative to this optimization (i.e. neither makes the other unnecessary). FWIW in this case the indexes won't help very much - if you use more data in the code tables, it'll use a hash join, not nested loop / merge join.

That doesn't mean we couldn't do better with indexes, there probably are smart execution strategies for certain types of queries. But indexes also come with quite a bit of overhead (even in read-only workloads).

It's not about not knowing about an optimization. The challenge is to know when to apply it, so that it does not cause regressions for cases that can't benefit from it. It may be less risky in specialized systems, like BI systems typically don't need to worry about regressing OLTP workloads. Postgres absolutely needs to be careful of that.

I believe that's one of the reasons why it took about ~8 years (the original patch was proposed in 2017).

IMHO the whole point of Qubes is that it does not do the compartmentalization at the level of individual applications, but groups of applications. Otherwise you'd need to very clearly specify how/when exactly the applications can exchange data, what data, etc. I'm not saying it's impossible, but "apps in the same qube VM can do whatever" is a much easier concept.

I'm not sure what exactly you mean by "thread" here. Postgres is not thread-based - there are people working on that, but for now it's all processes.

Some of these limitations are mostly due to Postgres design, no doubt about that.

I believe there are reasons why e.g. io_uring could be inherently slower in some cases, and I tried to point some of those out.

With io_uring everything happens in the backend process, and so consumes some of the CPU time that might otherwise be spent executing the query. All the checksum verification, memcpy into shared buffers, etc. happen in the backend. And those things can be quite expensive. With worker this happens in the other processes, spreading the overhead.

Of course, on truly I/O-bound workload (actually waiting on the I/O), this may not be a huge difference. For warmed-up cases it may be more significant.

Debian 12/13, with kernel 6.15.

Sorry, should have mentioned that in the blog post.

Right now async IO is used for sequential scans and bitmap scans, not for index scans. My initial guess would be that it mostly helps for complex queries (that use multiple indexes, so bitmap scans) and unoptimized queries (sequential scans), not so much for straightforward and/or optimized queries that use a single index. But this is really just a guess, I'm way out of my depth at this point. I'm curious how much it'll help once it is implemented for index scans as well.

Those are good guesses, IMHO.

For sequential scans, some of the "async" work could be done by kernel read-ahead, but AIO makes it explicit and loads the data into shared buffers, not just page cache. For bitmap scans we already had prefetching by fadvise, which is somewhat similar to read-ahead (also into page cache), and there were some bugs that made it ineffective in some cases, and AIO fixes that.

For index scans the difference can be an order of magnitude (say, 5-10x). Doing random I/O block by block is simply awful, prefetching data is important. I was just doing some testing on TPC-H, and on scale 50 I see Q8 going from 130s to 20s, and Q19 from 50s to 8s. And smaller improvements for a couple more queries. Of course, it depends on what else the query is doing - if it's spending 1% on the index, you won't notice a difference.

I did a lot of tests comparing the io_method choices, and I'm yet to see a realistic query where it makes a significant difference of more than a couple percent (in either direction). I'm sure it's possible to construct such queries, and it's interesting, but for real workloads it's mostly not noticeable.

At least that's how I see it right now, we'll see how that works on a much wider range of hardware and systems. The github repo linked from the pgsql-hackers post has a lot more results, some charts include results for the index prefetching patch - and there it makes more difference in some cases. But the patch is still fairly rough, it could be a bug in it too, and it changed a lot since August.

The funny thing is the local communist newspaper "Red Truth" (as if there were non-communist ones, ...) published a review of LOTR in 1977, in which they pretty much took the side of the Mordor. (It might be a made-up joke from the 90s, but the spirit of absurdity is spot on for 1977.)

The reasoning was roughly:

* Mordor is obviously meant to be USSR, as it's in the east.

* The orcs are clearly heavy industry workers, building the world of future.

* Bilbo is obviously a son from a bourgeoisie family, disgusted by hard work.

* The west is represented by elves = aristocracy, people = bourgeoisie, hobbits = landowners.

* The group of reactionaries are afraid of a made up "threat from the east", led by Gandalf.

* Gandalf = a reactionary ideologue, keeping people in state of fear of progress and knowledge.

* Saruman = protector of the oppressed, declared a traitor and destroyed by the reactionaries.

* But socialism can't be destroyed by throwing something in the fire. All the power to Mordor, surrounded by reactionary neighbors.

The toaster mention reminded me of this: https://www.youtube.com/watch?v=LRq_SAuQDec

This is how "talking to AI" feels like for anything mildly complex.

I've been involved in a couple of those cases, where a large company ran into an issue, and chose to solve it by migrating to something else. And while the issues certainly exist (and are being addressed), the technical reasons often turned out to be a rather tiny part of the story. And in the end it was really about internal politics and incentives.

In several such cases, the company was repeatedly warned about how they implemented some functionalities, and that it will cause severe issues with bloat/vacuuming, etc. Along with suggestions how to modify the application to not hit those issues. Their 10x engineers chose to completely ignore that advice, because in their minds they constructed an "ideal database" and concluded that anything that behaves differently is "wrong" and it's not their application that should change. Add a dose of politics where a new CTO wants to rebuild everything from scratch, engineers with NIH syndrome, etc. It's about incentives - if you migrate to a new system, you can write flashy blog posts how the new system is great and saved everything.

You can always argue the original system would be worse, because everyone saw it had issues - you just leave out the details about choosing not to address the issues. The engineering team is unlikely to argue against that, because that'd be against their interests too.

I'm absolutely not claiming the problems do not exist. They certainly do. Nor am I claiming Postgres is the ideal database for every possible workload. It certainly is not. But the worst examples that I've seen were due to deliberate choices, driven by politics. But that's not described anywhere. In public everyone pretends it's just about the tech.

I only noticed the jerky scrolling on pages with a lot of images, particularly hires + CSS effects (blur etc.). Everything else feels OK to me (I'm sure it could be smoother, but it's not too bad so I haven't noticed).

For backups, I don't them the qubes way, I do "regular" backups within VM using rsync/duplicity/... When moving to a new machine I prefer to setup everything from scratch (and then restore the data). And it gives me all the features like incremental backups etc.

Thanks for the clarification.

I occasionally see stutters too, even with Full HD video. Or more precisely, mplayer complained about slowness and having to drop frames.

It often helped to actually give the VM more cores (not just the default 2), but sometimes it was due to some weirdo codec/quality setting, and recoding the video just solved it. Sometimes switching to vlc (from mplayer) helped. Other times it was simply due to the sys-usb vm being overloaded.

Not sure what "mpv" means in this context, but this reminds me the one actual pet peeve I have with Qubes - video/audio calls just don't work for me. It either doesn't work or the audio quality is really poor. I've tried all kinds of stuff, without much success. I'm using phone/tablet as a fallback, but it's not very convenient.

Weird. Multiple people submitted HCL for X260, and not a single one mentions issues with sleep.

https://www.qubes-os.org/hcl/

When I had similar issues in the past, I posted a question either to the mailing list or forum, and people were helpful.

I'm using Qubes OS as my primary for years - I think I started with the 2.0 release in 2014 (I might have tried/used the 1.0 release, I don't recall.) and I was immediately hooked.

I understand the usual story is that the goal is security benefits, and the compartmentalization (or rather the implied inconvenience) is the price for that. But for me the compartmentalization turned out to be a benefit on it's own, and actually convenient.

I find it extremely convenient to have multiple isolated / virtual workspaces for different stuff, even if you assume attackers / malice do not exist. Having separate VMs is not the same as having separate folders. I also love the VM templates, which allow me to do all kinds of experiments (e.g. install packages in the app VM, which disappear after restart). Or run VMs with a mix of distros/versions/... Yes, I could do some of that with plain VMs, but Qubes integrates that in a way that I find very convenient. The commands for copying stuff between VMs are muscle memory at this point.

Yes, there are limitations, like the lack of GPU acceleration. But movies in 1080p play just fine without it, and I'm not a gamer, so I don't mind much. I can't play with CUDA etc. on these QubesOS machines, and scrolling web pages with large images is laggy, but I find this to be an acceptable price.

I went through multiple laptops / workstations over the years, and the situation improved a lot I think. Initially I had to solve quite a few issues with installer, some hardware not working (or requiring setting something special), or poor battery life on the laptops. But after a while that mostly either went away, especially once I switched to laptops with official Linux support (Dell Precision were good, I'm on Thinkpad P1 G7 now). The battery life is pretty decent too (especially once I disabled HT in BIOS).

Is it perfect for everyone? No, certainly not. But it sure is great for me, and I hope they keep working on it.

I guess it's this MySQL bug: https://bugs.mysql.com/bug.php?id=101369 which seems to have stalled after request ti sign an OCA.

Anyway, I have no idea what would it take to do something like that in Postgres, I'm not familiar with this sfuff. But if someone submits a patch with some measurements, I'm sure we'll take a look.

Interesting question. I think most optimizations described in the BOLT paper are fairly hardware agnostic - branch prediction does not depend the architecture, etc. But I'm not an expert on microarchitectures.

I believe some JIT systems already do PGO / might be extended to do what BOLT does.