Re: Persist MVCC forever - retain history

Hi!

On Thu, Jul 2, 2020 at 12:12 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> Even for a single table how would you go about specifying this in a user-friendly way? Then consider joins.

One general answer: you use query rewriting. But what is user-friendly
depends on the use case. For me, the main motivation for this is that
I would like to sync database and client state, including all
revisions of data. So it is pretty easy to then query based on this
row revision for which rows are newer and sync them over. And then I
can show diffs of changes through time for that particular row.

I agree that reconstructing joins at one particular moment in time in
the past requires more information. But that information also other
solutions (like copying all changes to a separate table in triggers)
require: adding timestamp column and so on. So I can just have a
timestamp column in my original (and only) table and have a BEFORE
trigger which populates it with a timestamp. Then at a later time,
when I have in one table all revisions of a row, I can also query
based on timestamp, but PostgreSQL revision column help me to address
the issue of two changes happening at the same timestamp.

I still gain that a) I do not have to copy rows to another table b) I
do not have to vacuum. The only downside is that I have to rewrite
queries for the latest state to operate only on the latest state (or
maybe PostgreSQL could continue to do this for me like now, just allow
me to also access old versions).

> If by “this” you mean leveraging MVCC you don’t; it isn’t suitable for persistent temporal data.

Why not?

> The fundamental missing piece is that there is no concept of timestamp in MVCC.

That can be added using BEFORE trigger.

> Plus, wrap-around and freezing aren’t just nice-to-have features.

Oh, I forgot about that. ctid is still just 32 bits? So then for such
table with permanent MVCC this would have to be increased, to like 64
bits or something. Then one would not have to do wrap-around
protection, no?

Mitar

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