6 ms·

Does this storage layer still require VACUUMing? The Aurora system that AWS has uses a log based storage system, so along with point in time restore there’s no

Does this storage layer still require VACUUMing? The Aurora system that AWS has uses a log based storage system, so along with point in time restore there’s no longer a need to go free up old garbage rows. This system says it’s copy on write, wondering if vacuuming is still necessary.

petergeoghegan 4y ago
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.
hlinnaka 4y ago
Yes, it still requires VACUUM. I believe Aurora does too. It would be cool to push down at least parts of the VACUUM down to the storage layer, but it would require more invasive changes Postgres code, which we try to avoid. Maybe in the future. Ideally though, I'd like to improve PostgreSQL itself, to reduce the need for VACUUM in the first place. Disclaimer: Neon co-founder
- canadiantim 4y ago
  What are your thoughts on orioleDB? Which is a slightly modified Postgres that does away with the need for vacuum, amongst other changes. Ostensibly done by a dev who is “solving Postgres’ wicked problems”. Eventually the team behind orioleDB plan to upstream their minor changes to vanilla Postgres such that orioleDB becomes simply just an extension of Postgres itself. Would you ever consider supporting the orioleDB extension in the future?
  - nikita 4y ago
    If it becomes more widespread and mature yes. We are in contact with Alexander Korotkov - Postgres committer and the auther of OrielDB and compare notes from time to time. Right now it makes a lot more sense to us to build our storage at the page level and have minimal changes in Postgres itself.

petergeoghegan 4y ago

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.

hlinnaka 4y ago

Yes, it still requires VACUUM. I believe Aurora does too. It would be cool to push down at least parts of the VACUUM down to the storage layer, but it would require more invasive changes Postgres code, which we try to avoid. Maybe in the future. Ideally though, I'd like to improve PostgreSQL itself, to reduce the need for VACUUM in the first place. Disclaimer: Neon co-founder

canadiantim 4y ago
What are your thoughts on orioleDB? Which is a slightly modified Postgres that does away with the need for vacuum, amongst other changes. Ostensibly done by a dev who is “solving Postgres’ wicked problems”. Eventually the team behind orioleDB plan to upstream their minor changes to vanilla Postgres such that orioleDB becomes simply just an extension of Postgres itself. Would you ever consider supporting the orioleDB extension in the future?
- nikita 4y ago
  If it becomes more widespread and mature yes. We are in contact with Alexander Korotkov - Postgres committer and the auther of OrielDB and compare notes from time to time. Right now it makes a lot more sense to us to build our storage at the page level and have minimal changes in Postgres itself.