From: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> |
---|---|
To: | postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Modelling versioning in Postgres |
Date: | 2021-05-28 10:20:30 |
Message-ID: | SnkMvVFCIfgaohLICjYwKEwe1LsI3EUX1XzYACFL8gNyBiCqUqrOKi6xu2rrDirSesUBuC45sPxWgAaVx6z_nZuBCNx_pQUcpR1dvftab8s=@protonmail.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
I was wondering what the current thinking is on ways to model versioning in Postgres.
The overall premise is that the latest version is the current version unless a rollback has occurred, in which case versions get tracked from the rollback point (forking ?).
My initial naïve starting point is something along the lines of :
create table objects (
objectID uuid,
versionID uuid,
versionTS timestamp
objectData text
);
This obviously creates a fool-proof answer to "latest version is the current version" because its a simple case of an "where objectID=x order by versionTS desc limit 1" query. However it clearly doesn't cover the rollback to prior scenarios.
I then though about adding a simple "versionActive boolean".
But the problem with that is it needs hand-holding somewhere because there can only be one active version and so it would introduce the need for a "active switch" script somewhere that activated the desired version and deactivated the others. It also perhaps is not the right way to deal with tracking of changes post-rollback.
How have others approached the problem ?
N.B. If it makes any difference, I'm dealing with a 12.5 install here, but this could easily be pushed up to 13 if there are benefits.
Thanks for your time.
Laura
From | Date | Subject | |
---|---|---|---|
Next Message | Vijaykumar Jain | 2021-05-28 11:04:28 | Re: TRUNCATE memory leak with temporary tables? |
Previous Message | Francisco Olarte | 2021-05-28 09:51:07 | Re: Size on disk of INT and BIGINT - not sure I'm getting it? |