Re: Persist MVCC forever - retain history

Hi!

On Thu, Jul 2, 2020 at 12:16 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> This was a research topic in ancient times (somewhere I read that in
> some ancient version, VACUUM didn't originally remove tuples, it moved
> them to permanent write-only storage). Even after the open source
> project began, there was a "time travel" feature, but it was removed
> in 6.2:

Very interesting. Thanks for sharing.

> There aren't indexes on those things.

Oh. My information is based on what I read in [1]. This is where I
realized that if PostgreSQL maintains those extra columns and indices,
then there is no point in replicating that by copying all that to
another table. So this is not true? Or not true anymore?

> If you want to keep track of all changes in a way that lets you query
> things as of historical times, including joins, and possibly including
> multiple time dimensions ("on the 2nd of Feb, what address did we
> think Fred lived at on the 1st of Jan?") you might want to read
> "Developing Time-Oriented Database Applications in SQL" about this,

Interesting. I checked it out a bit. I think this is not exactly what
I am searching for. My main motivation is reactive web applications,
where I can push changes of (sub)state of the database to the web app,
when that (sub)state changes. And if the web app is offline for some
time, that it can come and resync also all missed changes. Moreover,
changes themselves are important (not just the last state) because it
allows one to merge with a potentially changed local state in the web
app while it was offline. So in a way it is logical replication and
replay, but just at database - client level.

[1] https://eng.uber.com/postgres-to-mysql-migration/

Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m