Versioning data in Postgres? Testing a Git like approach

This is my cheat sheet:

  ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

  select count(*) from dual
  AS OF TIMESTAMP TO_TIMESTAMP('2010/01/01 00:00:00');

Edit: Flashback came to Oracle in 9i, when "rollback segments" were replaced by the "undo tablespace" that could present any table as it appeared in the past, limited by the amount of "undo" available. The "FLASHBACK ANY TABLE" privilege is required to see this history on tables that you do not own, and that also conveys the privilege to fully revert tables to their previous contents. You must be a DBA to exercise flashback on tables owned by SYS.

I deployed NeonDB in a large enterprise client in Q2 of this year and, yes, the initial data migration is the hard part. We added 2x100gbe network cards directly on the VMware hosts where the data was so the migration to the Kubernetes cluster running NeonDB took hours instead of weeks.

This is true in a sense, but these are commonly stored compressed and of course you can expect to have a pretty good compression ratio when changed files are mostly similar. And if they're not, you really have to store a copy anyway.

But I realized pg_largeobject (1) chunks data to around 2kB per chunk, and (2) stores each chunk on its own row, and (3) each row uses "oid" type as identifier, which is just 32 bit long. It's probably large enough for anything I would ever need, but for some reason I don't feel comfortable with only 32 bits as primary key.

This was/is a very specific solution to our very specific set of problems. So not applicable to the general problem of "versioning a database".

It is still in use and still under active development – I'm actually fixing a few bugs with it just now.

I wrote much more about it here:

https://news.ycombinator.com/item?id=25005993

I thought logically git stored every file, but implementation-wise, it does git-object compaction. So in reality, it's not actually storing every file on disk, no?

I wish PostgreSQL would allow something as Oracle Flashback feature plus allowing to control how far history do you want to keep for specific tables, as typically only part of your data needs full auditing.

create table test (id int primary key, attr int, deleted_at timestamp);

alter table test add default_filter is_active where deleted_at IS NULL;

then all selects like "select * from test where attr = 42" automatically gets added the above criteria and becomes "select * from test where attr = 42 AND deleted_at is NULL"

When you want old rows, you can override the default condition using something like:

select * from test where attr = 42 filter is_active (deleted_at IS NULL or deleted_at > '2010-01-01'::datetime)

You would then be able to have multiple criterias instead of just when row was deleted.

I run a GitHub Actions workflow every two hours which grabs the latest snapshot of the database, writes the key tables out as newline-delimited JSON and commits them to a Git repository.

https://github.com/simonw/simonwillisonblog-backup

This gives me a full revision history (1,500+ commits at this point) for all of my content and I didn't have to do anything extra in my PostgreSQL or Django app to get it.

If you need version tracking for audit purposes or to give you the ability to manually revert a mistake, and you're dealing with tens-of-thousands of rows, I think this is actually a pretty solid simple way to get that.

https://github.com/palkan/logidze

Sort of. While Git’s canonical storage is indeed not diffs, it uses delta compression to, well, store diffs as a storage and transit optimization https://git-scm.com/book/en/v2/Git-Internals-Packfiles

https://higherlogics.blogspot.com/2015/10/versioning-domain-...

With this schema, you simply add an extra clause to each of your queries and they can simultaneously return the latest version or any past version.

https://stackoverflow.com/questions/7118432/how-to-reveal-ol...

This work is great but it feels wasteful given the features Postgres already has but probably the only reasonable solution is to rebuild it in user-space.

They all revolve around known patterns, https://en.wikipedia.org/wiki/Slowly_changing_dimension

I highly doubt there's a single way to do this that solves every scenario.

Here's an alternative designed to work with an ORM, if you like that: https://www.youtube.com/watch?v=JsO551E7ySY&t=1094s.