Interesting! MVCC mechanics aside, it's also worth remembering that work_mem is only 4MB by default [0], so large intermediate results will likely spill to disk (e.g. external sorts for ORDER BY operations).
Did not see your comment until after I posted mine, but exactly this. The amount of disk io from these sort operations can be massive and very surprising.
Trying to reason about postgres is somewhat of an enigma when you are forced to do it; generally the only reason as a programmer you have to is because something went wrong, and then the mindset is a mix of nervousness and panic; then incredulity at some of the seemingly unintuitive behaviors. I suspect this might be true of any large, complex system at the edges.
With postgres I think it's also the problem of weak observability mechanisms. By default all you get is cumulative statistics. Then with extensions you get pg_stat_statements and a few more things, but you really shouldn't need to use something like pgAnalyze to get basics, like history of autovacuums, cumulative wait events and other stuff like that.
Things get even weirder when you use extensions. I remember being profoundly confused using Timescale 1 and doing a lot of concurrent writes on a hypertable with a foreign key (while also inserting into the other table) when I would get transaction deadlocks even in scenarios where it wouldn't normally be possible. This is how I found out doing DML on a "hypertable" actually does DDL under the hood, with all of the associated problems that brings.
Likely creating child tables for the various chunks that kick in periodically (e.g., depending on your hypertable chunking policy). Used to hit these all the time, quite annoying.
Greate article! I have learned about block/page long time ago when I needed to debug performance issue but not as deep as this article. Will share it with my teammate and its funny to see their emotional face :D
Similar things can also happen with file systems: ext4 mounted -o ro will let the driver do filesystem recovery even if userspace writes are prevented.
At the same time, you want to be able to read files in normal use-case. Being able to read them (after recovery) only if mounted read-write seems counterintuitive. This is the kind of times where right or wrong depends on the use.
Hmmm yes and no. If I set / to mount read-only in some embedded Linux system context, my intention is just that the contents of disk shouldn't change just because some program decided to write something somewhere; I would be quite surprised if some recoverable metadata bit flip or something caused the system to irrecoverably fail to boot just because the readonly flag also prevented fsck from fixing errors.
However if I have a faulty drive that I connect to my system to recover data from it and I don't want it to experience any more writes because I'm worried further writes may break it further, I would be quite surprised if 'mount -o ro' caused the driver to write to it.
The authors of this article obviously know infinitely more about postgres than I do, but you can trigger writes using reads much easier. If you’re selecting something that does not fit into working memory and try to sort it (or use a mechanism that needs sorting), the sort is performed on disk.
This almost rendered our SAN nonfunctional a few years back.
Readit News
https://www.databasejournal.com/oracle/delayed-block-cleanou...
[0] https://www.postgresql.org/docs/current/runtime-config-resou...
However if I have a faulty drive that I connect to my system to recover data from it and I don't want it to experience any more writes because I'm worried further writes may break it further, I would be quite surprised if 'mount -o ro' caused the driver to write to it.
Deleted Comment
This almost rendered our SAN nonfunctional a few years back.