4 ms·

The difficulty comes from the fact that VACUUM requires most of the same state that is required when processing a user transaction. For example, VACUUM needs th

The difficulty comes from the fact that VACUUM requires most of the same state that is required when processing a user transaction. For example, VACUUM needs the commit log to check whether a given transaction ID is for a transaction that committed or aborted (VACUUM also truncates the commit log to remove information about older XIDs when it is no longer required). Aurora Postgres does still have VACUUM, which seems to work in the same way -- which is the same in Neon. AWS have in the past promoted Aurora as having significantly better performance characteristics when VACUUM is run. That may well be true, but the benefits seem to come from not having to generate full page writes in WAL, which are a way of preventing a low-level problem called torn pages. Theoretically you could just turn off full-page writes in standard Postgres if you had hardware that offered atomic page writes, though I don't think that it's a widespread practice. I spend most of my time working on problems with VACUUM in Postgres itself (I'm one of the committers). An approach to organizing storage within transactional constraints seems necessary to push vacuuming down to storage, and that would more than likely need plenty of work in Postgres itself to be practical -- since it would cross a few layers of abstraction. Currently heapam doesn't specifically try to keep tuples inserted around the same time together, so it's hard to make anything that VACUUM does work implicitly or logically. Disclaimer: I work for Neon

sudhirj 4y ago
Thanks for the explanation, I’m nowhere near your experience level with this, but from what I understand Aurora is an append only log with rolling snapshots (this might be wrong). In an append only log storage system there would be no concept of page writes, right? The VACUUM process might still be necessary to find and mark dead tuples, but this seems very minor compared to filesystem operations to reclaim (sometimes gigabytes) of storage. The reclamation of storage would simply left to the background snapshotting process and would not affect DB performance in any way.
- petergeoghegan 4y ago
  The most important job of VACUUM is usually to make sure that queries remain responsive -- it's roughly comparable to a "full scan" within generational garbage collectors (for managed language runtimes). VACUUM does reclaim space in various ways, but that's often considered to be of secondary importance. In Postgres, the on-disk representation is virtually the same thing as the in-memory representation used by pages stored in the buffer cache. In a system like Aurora or Neon, the representation of pages in the buffer cache is identical to that of Postgres (or has very minimal divergence to deal with one or two isolated problems). That part doesn't really change, which makes it possible to have a very deep level of compatibility without much effort. So it's radically different in one narrow, scoped way, but otherwise very similar. While the storage knows how to materialize pages on demand, these are not transactionally consistent pages -- they often need to be interpreted by using metadata about transaction commit status, just like in Postgres.

sudhirj 4y ago

Thanks for the explanation, I’m nowhere near your experience level with this, but from what I understand Aurora is an append only log with rolling snapshots (this might be wrong). In an append only log storage system there would be no concept of page writes, right? The VACUUM process might still be necessary to find and mark dead tuples, but this seems very minor compared to filesystem operations to reclaim (sometimes gigabytes) of storage. The reclamation of storage would simply left to the background snapshotting process and would not affect DB performance in any way.

petergeoghegan 4y ago
The most important job of VACUUM is usually to make sure that queries remain responsive -- it's roughly comparable to a "full scan" within generational garbage collectors (for managed language runtimes). VACUUM does reclaim space in various ways, but that's often considered to be of secondary importance. In Postgres, the on-disk representation is virtually the same thing as the in-memory representation used by pages stored in the buffer cache. In a system like Aurora or Neon, the representation of pages in the buffer cache is identical to that of Postgres (or has very minimal divergence to deal with one or two isolated problems). That part doesn't really change, which makes it possible to have a very deep level of compatibility without much effort. So it's radically different in one narrow, scoped way, but otherwise very similar. While the storage knows how to materialize pages on demand, these are not transactionally consistent pages -- they often need to be interpreted by using metadata about transaction commit status, just like in Postgres.