Implementing system-versioned tables in Postgres

Exactly ! What's worse that we seem to have developed this all-in-or-none-at-all approach towards cloud services. Whatever happened to a hybrid approah of using the cloud only for things that you wanted ?

It's especially tragic knowing that Postgres did originally have system-time-like versioning built-in. Instead we get to enjoy being upsold on proprietary ETL-to-Redshift, AlloyDB, etc.

>>> It's that cloud providers are now often seen as the only responsible way to operate infrastructure

23 billion.

Thats the profit for AWS in 2023.

23 billon that AWS users pissed away.

AWS is NOT cheap. I have quite a few clients who, could save lots of money dumping their cloud provider and setting up their own racks, hiring SA's. I have others who, if they optimized any of their stack could cut their AWS bills by half.

Having survived the 2000 bubble the lessons are going to have to be re-learned. Everything has cost and value and managing that is going to become an engineering task in the near future.

I don't feel sad at all, I feel my competition is slacking and that is my benefit.

For a small greenfield project rolling your own Postgres is a very viable option with attractive trade offs for some. If it is now considered rarefied knowledge in your circles you should be glad as this might a competitive advantage.

Let the other startups spend x10-100 on their resume driven development and blaze through their runway. Common story actually.

But suppose you don't need it and RDS as-is covers all your needs, which it often does, why rage? This is a continuum.

If an early decision to punt and outsource it to the cloud was made it could always be reversed at an opportune moment. I don't think competent people see it as "the only way" just the most common and pragmatic option.

eh, in my experience system managed temporal tables are crap anyway, they have a specific set of circumstances built in and anytime you break out of those you basically have to design your own trigger based solution anyway.

RDS is 50 cents or less a day for postgresql on a three year term for a small instance.

I use AWS / RDS - I’ve got to believe RDS is spinning money for AWS because of all the parts I want to be reliable this is way up there.

I am sorry to say this but most developers either don’t have the ability to think for themselves or can’t be bothered.

You might disagree with this statement of course but that is truly my experience (30+ years of working professionally in software development).

Try this: Next time a developer confidently suggests that you should use tech X, ask them that the pros/cons are compared with using tech Y/Z. Ask about performance/failover/latency/team skills required etc. Most developers in my experience completely fail to give an intelligent answer. They are just loudspeakers confidently copying the opinions of somebody else. Usually somebody who benefits financially from developers using tech X.

This is actually what we're building @ Bemi https://bemi.io/

We're hoping to make it so that this becomes automatic with Postgres vs moving to specialized databases.

We recently open sourced the core tech that implements system versioned tables https://github.com/BemiHQ/bemi, check it out if interested :)

Specifically, the change log is an information that is almost entirely unrelated to the business logic versioning. They don't have to agree even on the timestamps, the only property that relation has is that the number of changes on the change log is larger or equal to the ones on the business versioning.

(Besides, the business versioning doesn't even have to be a single one, or have a single active version at each time.)

Those extensions are often sufficient for the change log (if you add extra information on your tables). But then, they are way overkill for that.

How do you handle the 60 modifications? Do you store each in the audit log? That's what we do and use a time rollup to get the last change within a certain time range. But I feel like there's potentially better solutions.

I recently spent multiple months building out something like this blog post describes, for a notion-like app (to replace a different versioning scheme that could do draft pages but not point in time viewing).

We ended up doing it all in typescript, or with some postgraphile shenanigans.

One of the things we missed when requirements gathering was the ability to view a table of records each at their "latest published" version.

I think this can probably be done by including a pair of tztsrange columns in the history table (one for "when was this the latest valid draft?" and one for "when was this the latest valid published version", and do a bunch of date shuffling whenever you publish a version.

We've not actually done this yet, because there are some edge cases around dependency graphs and computed columns, but for a simpler app I reckon it would work just fine.

Agree. Sounds like INSERT/UPDATE/DELETE should be extended with a LOG SET webuser='abc', app=42, ... clause that provides an "UPDATE history_table SET webuser..." construct.

I.e. the history tables that temporal_tables provides, but with added columns that can include more information, and if the columns are NOT NULL then UPDATE/DELETE/INSERT should fail.

I know this probably doesn't help you in your environment, but there's not really any reason that the application needs to have only a single role that it uses to connect to the database. I implemented logging all the way down to Postgres roles - in that each user of the application had their own database user. We did a SET ROLE at the beginning of each transaction, to switch the transaction to the application user's role. I don't remember the details, but there is a way to do this very safely. I'm about to do it again on a new project I'm working on.

It's actually not that difficult to implement if you have a homogenous or flexible application environment; but it's certainly not common, although I don't know why (other than inertia).

I handle that with a “event_source” enum column [“database”, “web app”, “services_app”, etc] paired with user_id (thought fk won’t work for a lot of other folks cases). But I like the idea of doing that at database user level as another commenter posted.

Might it be easier to just add a `last_updated_by` column? Then passing the data to the db is as simple as adding it to the insert and it would be automatically stored in the history table as well without needing any special handling.

    > What we (and I imagine many others, though clearly not OP) would need is a "do-  
    > business-as" user that maps not to the database users, but to in-application   
    > users, and then a mechanism to pass this user to the DB, and log it in addition  
    > to the database user.

This is kind of what Supabase does, for use in tandem with row-level security. It stores the user's JWT in a custom configuration parameter via set_config() when the database connection is established on behalf of the user, so you can see the token by calling get_config() with the config parameter name. Supabase also provides convenience functions to destructure the token and get the things you need (like auth.uid()), so it would be easy to fetch the user ID or user name in a trigger to log it.

Yes, usually «who did it» is at least as important as the history itself

I actually "solved" this problem, though not for logging, and other restrictions made me move away from it. In our case it was for Row Level Security, but essentially we get our userId from a JWT in our API (you should already have something like that), then before each DB command, run a SET LOCAL auth.claums.userId = 'user_id'; Most ORMs let you set a variable like this automatically when opening a connection, so you don't have to think about it too much.

Then you can get as fancy as you want with RLS policies automatically attached to each table and validate the user based on a user table with capabilities. I have a feeling you could instead have the RLS rules automatically insert data instead, but I never tried it. It would have to be multiple updates though, perhaps a trigger/function.

The problem is SET applies to a session, and pgbouncer doesn't make guarantees about the session for a particular statement. Your statement might receive an existing session with another user's ID, or it might receive no ID at all.

Through AWS's RDS Proxy (beefed up pgbouncer AFAIK), the situation is slightly better, in that AWS will detect the modification to the session and 'pin' it to the current connection. Your data will be consistent, but pinned sessions can no longer be multiplexed, so you've basically removed the whole point of connection pooling to begin with.

Ultimately, its probably safer to just manually add the user into your queries. Its not that much overhead.

There's also django-reversion, but I can't say I loved using it.

The problem with Django simple history and similar is they use Django signals and aren’t transactional. So if you have any bulk updates or things that don’t trigger signals, your SOL, and your history starts missing things

A more robust solution would use triggers or an ORM with more transactional guarantees (aka not using signals)

Limited to JSONB types though, by the looks of it.

I implemented this using drizzle and PG not long ago

Check the repo here https://github.com/grmkris/drizzle-pg-notify-audit-table

Thanks, not sure how I missed this - just added to my “Postgres Is Enough” list:

https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...

I do a similar thing but add a foreign key columns for faster lookups (traversing using is slow on large sets when querying). The fk’s get set by the function itself during insert. This allows me to create per resource audit logs in the UI.

I also wrote some deep diff functions to get the changes between the old and new jsonb if anyone is interest. This makes it easy to display what exactly changed.

django-pghistory is a good option with Django.

https://django-pghistory.readthedocs.io/en/3.0.0/

There might not be a previous value.

If you insert a record and then later delete it, you need to store a range or two timestamps to know when the insertion and the deletion happened.

Foreign keys / references; you query one history table at timestamp X, then the joined history table with a "where start > X and end < X" to get the foreign key's data at timestamp X.

With a single timestamp how would you write a queries such as "what was the state of the record at this specific moment in time" or "what changes were made to the record between start_time and end_time"? TFA is also using an index on the range table to ensure that you can't enter overlapping entries in the history.

> What happens if you have, say, two rapid-fire transactions updating the order, with another transaction updating one of the parts sandwiched between the two order updates?

You have a race condition here, regardless of whether you use history tables or not, no? It'll probably still be wrong for the user if you update the part first, then do the two transactions, as you now refer to the wrong (a more recent) part version.

I mean, that's the entire reason why Stripe have `products` with multiple `prices` that are "immutable": The price, tax details and so on can't be changed, but they can be archived or set as default. A line item will refer to the price itself, not the product.

If you somehow need to do multiple transactions and your parts aren't immutable, I think you must refer to some immutable history table -- regardless of whether you use the triggers, make an append only table or do something else. If you use the triggers mentioned in the post, you could save the time you looked at the part and do an insert like so:

  INSERT INTO line_items (order, part_id, part_history_id)
  SELECT ${order_id}, ${part_id}, ph.history_id
  FROM parts_history ph
  WHERE ph.part_id = ${part_id}
    AND ph.systime @> ${as_of_time};

But to be fair, it seems unnecessarily risky. I'd just query the history as of now and use the history ID in the subsequent transaction, or make an append-only part version table and refer to that.

I think you have to really think hard about what you want out of a history tracking system, and there isn't a one-size-fits-all answer. We did something like the article but much more elaborate. At the core though, we addressed the concern you raise in two ways:

1. We forced fully serialized write transactions, because we determined that the users needed simplified semantics they could understand. They need a linear history model for the whole DB, not leaking out a weird graph of MVCC states on different rows. This obviously has some significant performance/scalability implications. Our worldview is more for small, long-tail use cases, so we can scale by having multiple independent databases for different communities and not worry so much about having one large database with optimized write concurrency.

2. We tracked the version timestamps generated by all write transactions in another system table with a uniqueness constraint. The theoretical collision here would abort the second write transaction with a conflict, requiring the application to try again (with a later timestamp). I am not sure it has happened in practical operation given the high precision timestamps used in a modern Postgres deployment.

But, we went wild in other ways: we store model information (much like is in pg_catalog) and have history tracking of that too. This allows different snapshots to add/remove schemas, tables, columns, key constraints, and foreign key constraints, as well as renaming schemas, tables, and columns. The history storage uses JSONB and the model snapshot is needed to properly project this content back into the specific table definition at a given time.

Our systems application is a relatively low-level web service that provides access to a shared database. So we're not really targeting hand-written SQL use cases (though it is feasible to do some custom SQL against the "live" database as a privileged system operator). Instead, our regular use case is that all queries are expressed in our web service API and it generates the SQL queries. Then, an extra parameter can specify a snapshot ID to have the web service API operate read-only over an earlier snapshot. It generates the different SQL necessary to perform the same kind of query over historical data.

And, since our user-visible concept is a web service rather than a raw database, we inject our own web authentication and authorization scheme. So authenticated web users can be tracked for the "who did it" metadata, and we also built our own fine-grained access control scheme that is understood by the web access layer. This policy model is written in terms of the database model and so also gets tracked as snapshots of policy appropriate to one version of the database model. Querying an older snapshot means reconstructing the older model and the older policy that is written in terms of that model.

A final kink is the possibility of needing to amend history. You might need to go back and REVISE the policies applied to older timestamps, because you realize you had a flaw in your policy or because organizational policies have changed and you want to enforce these on any subsequent queries of older data. You might also need to REDACT older data entirely for some kind of confidentiality or compliance reason. This could mean scrubbing historical tuples to make certain fields "null" or even making some historical tuples disappear entirely.