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