Thread: Modelling versioning in Postgres

Modelling versioning in Postgres

From
Laura Smith
Date:
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
versionsget 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
"whereobjectID=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
wouldintroduce 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
thereare benefits. 

Thanks for your time.

Laura



Re: Modelling versioning in Postgres

From
Michael van der Kolff
Date:
One thing you could consider is a range type for your "versionTS" field instead of a single point in time.

So that would be:

CREATE TABLE objects (
  objectID uuid,
  versionID uuid,
  validRange tsrange,
  objectData text,
);


In particular, you can enforce the obvious business rule, that there is no objectID with overlapping validRanges (as long as you have the btree_gist extension):

CREATE EXTENSION btree_gist;
CREATE TABLE objects (
  objectID uuid,
  versionID uuid,
  validRange tsrange,
  objectData text,
  EXCLUDE USING GIST(objectID WITH =, validRange WITH &&)
);

On Fri, May 28, 2021 at 8:20 PM Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:
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


Re: Modelling versioning in Postgres

From
Laura Smith
Date:
Thanks both for the interesting idea of using tsrange, but also for introducing me to EXCLUDE USING GIST, I had never
heardof it before. 

Have a good weekend

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 28 May 2021 14:13, Michael van der Kolff <mvanderkolff@gmail.com> wrote:

> One thing you could consider is a range type for your "versionTS" field instead of a single point in time.
>
> So that would be:
>
> CREATE TABLE objects (
>   objectID uuid,
>   versionID uuid,
>   validRange tsrange,
>   objectData text,
> );
>
> See https://www.postgresql.org/docs/12.5/rangetypes.html for more information.
>
> In particular, you can enforce the obvious business rule, that there is no objectID with overlapping validRanges (as
longas you have the btree_gist extension): 
>
> CREATE EXTENSION btree_gist;
> CREATE TABLE objects (
>   objectID uuid,
>   versionID uuid,
>   validRange tsrange,
>   objectData text,
>   EXCLUDE USING GIST(objectID WITH =, validRange WITH &&)
> );
>
> On Fri, May 28, 2021 at 8:20 PM Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:
>
> > 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
versionsget 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
wouldintroduce 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
thereare benefits. 
> >
> > Thanks for your time.
> >
> > Laura