Conversation

Replying to

and

Issues in index-only scans in InnoDB secondary indexes also demonstrate my point, I think. Whole pages (not rows) are what a given InnoDB MVCC snapshot sees. It's an indivisible thing (mostly), which is sometimes for the better, other times (e.g., with bug #74919) for the worse.

Replying to

and

I am not sure upstream InnoDB has a counter to show when secondary index page requires fetching base row to determine visibility. So it isn't a well known problem. Does PG have a counter for the equivalent case (when visibility map bit isn't set)?

Replying to

and

PG doesn't really have a counter (only shows it in EXPLAIN ANALYZE), but it should. Each set of index-only scan problems is very similar - but *also* very different. My guess is that the worst case for InnoDB IoS is much worse than Postgres IoS, while InnoDB is better on average.

Replying to

and

Back in the day when I was running the insert benchmark I really needed such counters to explain IO/query. Queries used covering secondary indexes, but the insert rate made it hard for purge/vacuum to keep up.

Replying to

and

Maybe secondary index scans needed to materialize index pages the hard way all too often. Their MVCC snapshots never become old (in "human scale" wall clock time), but *were* old relative to the high rate of change in pages. Maybe it was just this competition - not purge per se?

Replying to

and

Unfortunately, I never explained what was going on and probably might not return to it. My focus is RocksDB now.

Replying to

and

In case that wasn't enough wild speculation for one day: seems as if trends in hardware increasingly disfavor "logical database first" designs. Coordination and latency are the bottleneck now -- not I/O. See also:

pathelland.substack.com

I Am SO Glad I’m Uncoordinated!

Technology trends have evolved to provide an abundance of CPU, Memory, Storage, and Networking. Coordination, formerly dirt cheap, has become the precious commodity.

Replying to

and

Would be great if that were motivation for getting an LSM storage engine into Postgres

Replying to

and

That does seem like a good reason to write a new table AM. It's just not obvious what that means for WAL, checkpoints, etc. Postgres is traditionally highly extensible, and so in theory new tables AM should be additive. This is very much above my pay grade.