9 ms·

Implementing system-versioned tables in Postgres

161 points by ben_s 3y ago

deleted 3y ago
[deleted]
kiwicopple 3y ago
this approach is also available as an extension here: https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit it uses a slightly more generic implementation so that you can easily turn on/off auditing. i.e. select audit.enable_tracking('public.account'::regclass); select audit.disable_tracking('public.account'::regclass); the reason for the 'public.account'::regclass is to store the tables OID, rather than the name - this is cheaper than text and allows you to rename the table without losing the history
- refset 3y ago
  Limited to JSONB types though, by the looks of it.
  - kiwicopple 3y ago
    the data is stored as JSONB so that it can be used generically across all tables and adapt to schema changes
    - refset 3y ago
      It's definitely a reasonable trade-off given the circumstances. Do you know whether Supabase teams who use this extension simply avoid using any non-JSON Postgres types? Or do they lean on workarounds (e.g. JSON-LD encoding)?
      - kiwicopple 3y ago
        > avoid using any non-JSON Postgres types i'm not sure I follow - why would they avoid using non-JSON types? more generally however: I don't have a lot of insight into the usage/feedback for this extension, but I can ask the team
        refset 3y ago
        The issue is the complexity of figuring out appropriate JSON-compatible serializations and getting the implementation correct for every single column in use. A simple example would be round-tripping the Postgres money type using salary::numeric and later (user_data->>'salary')::money A much more complex example would be representing a numrange where you have to preserve two bounds and their respective inclusivity/exclusivity, in JSON.
  - cpursley 3y ago
    I imagine you could modify to use array type. But jsonb makes it easy to normalize back and forth to a row.
  - Rapzid 3y ago
    Yeah not really the same then. The approach in the post allows you to easily reuse existing queries by just adding global filters and pointing to the history table.
- random_kris 3y ago
  I implemented this using drizzle and PG not long ago Check the repo here https://github.com/grmkris/drizzle-pg-notify-audit-table https://github.com/grmkris/drizzle-pg-notify-audit-table
- cpursley 3y ago
  Thanks, not sure how I missed this - just added to my “Postgres Is Enough” list: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#audit-logs 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.
  - kiwicopple 3y ago
    > deep diff functions to get the changes between the old and new jsonb if anyone is interest I'd love to see this if it's public
    - cpursley 3y ago
      Sure. It's somewhat out of date but gets the general idea across: https://gist.github.com/cpursley/a2ae79423bee9274e3639839967e17ff#file-activity_feed_logic-sql-L80 https://gist.github.com/cpursley/a2ae79423bee9274e3639839967...
adamcharnock 3y ago
> Now, there are extensions out there that implement versioning – temporal_tables being the most popular I think – but none are supported for managed Postgres instances on e.g. Azure or AWS. This means that if we want system-versioned tables, we’re forced to make it ourselves. This really provokes a wild geek rage inside me. Not that it is untrue, but that this is the state of things. It's that cloud providers are now often seen as the only responsible way to operate infrastructure, and in the process we hand over so much control of our tooling. We let ourselves be convinced that simplicity was worth a 10x premium, and admitted we were not grown up enough to handle the tools we had made for ourselves. Sure, I know it is more complex than that. But I also feel sad about it.
- devnonymous 3y ago
  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 ?
  - cqqxo4zV46cp 3y ago
    A database is near the top of most people’s list for things that they don’t want to manage themselves. This rant is verging on irrelevant.
    - sroussey 3y ago
      I used to have some MySQL servers running for so many years that I was afraid to restart the machines.
    - DaiPlusPlus 3y ago
      > manage themselves. Be careful with the language there.... I assume you meant that no-one wants to have to run and administer an RDBMS like Postgres, MSSQL, Oracle, etc - especially not things like backups, restoration, disaster-recovery, figure out storage, ensure high-availability, and maybe even things like user administration and index management: i.e. a DBA's grunt-work - and yes, I agree with you entirely. ...but using the other interpretation of "manage", people might think you meant that people don't want to know how to properly design a normalized schema and apply the business-domain, knowing how to clean-up data appropriately (i.e. the other half of a DBA's job), because we can either shove it all into a cloud vendor's proprietary (and horrendously expensive) non-relational database or data-lake solution (CosmosDB, etc) - or that we can outsource database modelling and implementation of DDL to an LLM - which I'll admit, is quite capable as-is for a first-draft for DB scaffolding and getting better all the time, but ultimately isn't able to take a proactive or autonomous approach to managing an org's databases; I don't want to seem like I'm advocating economic-protectionism of DBAs, but I get the impression LLM-based DBA-bots are being aggressively pitched to smaller-sized orgs that don't know any better, at a time when the technology is still very new, with potentially serious repercussions w.r.t. data-security and compliance (and ChatGPT still gives me T-SQL that uses Oracle functions...).
Humphrey 3y ago
This extra table approach is how the django_simple_history[1] implements model history. Although it hooks into the Django ORM to save history rather than triggers. It's simple and brilliant, and I look it into any model that needs the ability to revert. [1] https://django-simple-history.readthedocs.io/en/latest/ https://django-simple-history.readthedocs.io/en/latest/
- physicsguy 3y ago
  There's also django-reversion, but I can't say I loved using it.
- emptysea 3y ago
  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)
ceving 3y ago
I am not sure why ranges are used. It seems to be redundant, because the beginning of the validity of a value is identical to the ending if the validity of its previous value. A column with an invalidation time stamp seems to be sufficient for me. The rows, in which this column is null, are currently valid.
- perlgeek 3y ago
  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.
  - javier2 3y ago
    Ah, I did not think of that!
  - zeroimpl 3y ago
    The way I deal with this is my history tables have 3 fixed columns: * OperationType: An ENUM of "INSERT", "UPDATE", "DELETE" * OperationTimestamp: TIMESTAMPTZ * OperationId: BIGSERIAL (needed to can handle cases where a row is modified multiple times in a transaction) This structure is super intuitive to understand, and very efficient since the History table is append-only. But if you are trying to use it to reconstruct the state of items from a specific time the past, it's a bit awkward and the use of a RANGE column instead would be easier to query.
- Cthulhu_ 3y ago
  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.
- Merad 3y ago
  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.
perlgeek 3y ago
The application I develop for work has its own, horrible, home-brew solution to update log/audit log, you cannot really call it system-versioned, so I read each of these posts with interest. One thing that nearly all of them gloss over that logging the "who did it" isn't as simple as they make it out to be. In most cases, there's a web application or an API gateway or so that connects to the database and does most of the modifications, and only logging the database user of that application makes this piece of information really useless. 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. The web frameworks and ORMs I've worked with in the past don't seem to have any built-in support for something like this, which somehow annoys me much more than it probably should. Sorry for the rant :-)
- silvestrov 3y ago
  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.
- doctor_eval 3y ago
  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).
  - pdimitar 3y ago
robertlagrant 3y ago
For Python at least, sqlalchemy-history might be a good option. I used its predecessor, sqlalchemy-continuum, and that was decent as well. It just uses the same definitions as the ORM, so it's very usable. Doesn't work with Django because Django ties you to their ORM, but for the rest of the Python world it might be worth a look.
- manfre 3y ago
  django-pghistory is a good option with Django. https://django-pghistory.readthedocs.io/en/3.0.0/ https://django-pghistory.readthedocs.io/en/3.0.0/
kuon 3y ago
I just implemented versioning in a CMS and I read about every article about it I could find. My conclusion is that while all articles had their point and strengths, what a version is can vary widely between applications. For example, is my app, we decided that versions would represent "committed" modifications, for example you can modify a chapter title 60 times and then publish and get only one version. For "undo" we use browser storage. We came to this solution because it is a shared collaborative editor. In the end it is very git inspired. The other big "problem" is structure. There are things that should and should not be versionned. My point is that a usefull version system is very tightly coupled with the business logic, and it is not just snapshot.
- marcosdumay 3y ago
  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.
- cpursley 3y ago
  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.
  - kuon 3y ago
    We use an undo stack on the browser side. We realized that it was better not to share this change log between users. Also we do not persist it. But your mileage may vary depending on your needs. In an older app the undo stack was zipped and serialized in db. With zip you avoid storing too much with every character added to 5 pages of text and you do not have to work with diff's.
- 3y ago
T3RMINATED 3y ago
[dead]
mgsouth 3y ago
This method uses timestamps as version IDs. That's not very reliable when tying rows together, such as primary/foreign-key tables or multiple rows in a single transaction. (There's also a "history_id" column for each history record, but it's not used to tie rows.) Suppose you've got an "order" table, with a "lineitem" detail table, and a "part" (stock item) master table. 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? Even with the three transactions being processed sequentially, it's possible for all three to wind up being handled in the same clock tick and have the same timestamp. As a result, it's not possible to accurately match the history records for the three tables. An opposite case is when a transaction straddles a clock tick and various updated rows end up with different timestamps. The author even alludes to the problem. He tackles the transaction-with-multiple-timestamps issue by using NOW() instead of CLOCK_TIMESTAMP() and punts primary/foreign-key issue, in his use-cases, with "... I don’t think it makes sense to have multiple states at a single point in time anyway." Well, experience says it will happen, surprisingly often. If your use-case can't handle the ambiguity then you'll need to do something different. A more rigorous approach is to include a "version_id" column in each table. If you make it one of the new-fangled UUID7 types then it will be sortable by timestamp. Every table updated in a single transaction should have the same version_id, and every foreign-key reference should also include the foreign version_id. So the "lineitem" table would have "id", "version_id", "order_id", "order_version_id", "part_id", "part_version_id". It is, indeed, a bit more complicated. EDIT: Normally only the history tables need the foreign-key-version columns; you can code the history-creation triggers to automatically include them. This eliminates most of the additional complexity of using them. EDIT: More issues with using timestamps: Consider this timeline: 12:34:56.000 Transaction A begins. 12:34:56.001 Transaction B begins, updates a foreign-key table that's needed by trans A, and commits with a timestamp of 12:34:56.001. 12:34:56.002 Transaction A gets around to reading those foreign-key tables, gets the version created at 56.001. Trans A then commits with a timestamp of 12:34:56.000. So if we look at trans A's history, timestamped at 56.000, we'll wind up matching against the old, stale foreign-key history rows.
- hypirion 3y ago
  > 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.
ancieque 3y ago
I have implemented this for our tool NF Compose that allows us to build REST APIs without writing a single line of code [0]. I didn't go the route of triggers because we generate database tables automatically and we used to have a crazy versioning scheme that was inspired by data vault and anchor modelling where we stored every change on every attribute as a new record. This allowed for simple point in time queries. Sounded cool, but in practice it was really slow. The techniques that are usually employed by Data Vault to fix this issue seemed too complex. Over time we moved to an implementation that handles the historization dynamically at runtime by generating historizing SQL queries ourselves [1]. We now use transaction time to determine winners and use an autoincrementing column to determine who wins on ties. A lot of brainpower went into ensuring this design is concurrency safe. On a sidenote: Generating SQL in python sounds dangerous, but we spent a lot of time on making it secure. We even have a linter that checks that everything is escaped properly whenever we are in dev mode [2]. [0] https://github.com/neuroforgede/nfcompose/ https://github.com/neuroforgede/nfcompose/ [1] https://github.com/neuroforgede/nfcompose/blob/main/skipper/skipper/dataseries/storage/dynamic_sql/queries/modification_materialized/insert.py https://github.com/neuroforgede/nfcompose/blob/main/skipper/... [2] https://github.com/neuroforgede/nfcompose/blob/main/skipper/skipper/core/lint.py https://github.com/neuroforgede/nfcompose/blob/main/skipper/...
ttfkam 3y ago
Good write up. As I've written my own temporal table library for Postgres that also runs on managed databases like AWS Aurora, here are my observations: If you main table inherits from a history table, you can see a complete history of every entry. It also never requires triggers on insert, only update and delete. A major drawback however is that Postgres table inheritance uses the same mechanism as partitioning, so you can use one or the other but not both. Instead of inheritance, you could keep them completely separate, which is what I ended up with and created set-returning functions of the same name as the main table with a timestamp range as a parameter. The function performs a UNION ALL to merge active and historical results. So instead of FROM mytable you use FROM mytable('2023-03-01 00:00:00Z', '2023-04-01 00:00:00Z') This has made temporal queries much easier to understand and maintain. Another option put forth was to save the timestamp range and tableoid in the history but have historical column data stored as jsonb. This allows history to be shared with all tables and is more forgiving of table column changes. It's also obviously slower and less storage efficient. The temporal access functions end up calling jsonb_populate_record(...), which is extremely handy but again not as efficient as a twinned history structure. However unless you are making a lot of temporal queries with strict SLAs, the jsonb record solution is often good enough, especially with judicious use of partial indexes. Watch out for dependencies on tableoid though; the same table name created by anything other than a dump/restore will not have the same tableoid, so a surrogate id and mapping table may be preferred. One major difference between my implementation and the author's is that my history table is never updated. Ever. It is always append-only. This is a side effect of the initial insert not being recorded in the history. It also means the active table requires at least a last_modified column that is updated by trigger. As was mentioned, adding and removing columns from the active table is a difficult and complex proposition. In fairness though, neither MariaDB or MS SQL Server handle this situation either. Both just tell you to detach the history, make your table changes, and start a new history. This is where the jsonb history option when rolling your own in Postgres really shines. Not covered in the spec or in most temporal implementations in Postgres, it can be VERY useful to record the user you made the changes. Rather than record the role—though this may be all most systems have—I've tended to create application users that are passed in from JWT claims as local config variables. I got the idea from Postgraphile (https://www.graphile.org/postgraphile/jwt-guide/ https://www.graphile.org/postgraphile/jwt-guide/). At first I added this to every history table along with the start and end timestamps but later realized the user never changes mid-transaction. I switched to a model where the transaction id and user are stored in their own table. Rows in Postgres already store the transaction id. Unfortunately this suffers from the same dump/restore problem that tableoid suffers from, so you may want a transaction id surrogate instead depending on your needs. You will also need to track the delete event separately from the last state of your active table record. The user who deleted it may very well be different from the last person to insert/update it. This was handled with a start and end timestamp that were the same, aka a point in time. Finally, it's worth exploring event triggers. Managing all of this manually is a huge PITA. Better to watch for CREATE TABLE events and make the history setup automatically. Better for consistency and for dev sanity. I'm usually not a big fan of dynamic SQL, but this was an area I never regretted utilizing that particular Postgres sledgehammer. Temporal support is a massive undertaking if you want to go beyond the basics. It also imparted even more respect for the Postgres developers for such a powerful and expressive platform to work on.

161 points by ben_s 3y ago

deleted 3y ago

[deleted]

this approach is also available as an extension here: https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit it uses a slightly more generic implementation so that you can easily turn on/off auditing. i.e. select audit.enable_tracking('public.account'::regclass); select audit.disable_tracking('public.account'::regclass); the reason for the 'public.account'::regclass is to store the tables OID, rather than the name - this is cheaper than text and allows you to rename the table without losing the history

refset 3y ago
Limited to JSONB types though, by the looks of it.
- kiwicopple 3y ago
  the data is stored as JSONB so that it can be used generically across all tables and adapt to schema changes
  - refset 3y ago
    It's definitely a reasonable trade-off given the circumstances. Do you know whether Supabase teams who use this extension simply avoid using any non-JSON Postgres types? Or do they lean on workarounds (e.g. JSON-LD encoding)?
    - kiwicopple 3y ago
      > avoid using any non-JSON Postgres types i'm not sure I follow - why would they avoid using non-JSON types? more generally however: I don't have a lot of insight into the usage/feedback for this extension, but I can ask the team
      - refset 3y ago
        The issue is the complexity of figuring out appropriate JSON-compatible serializations and getting the implementation correct for every single column in use. A simple example would be round-tripping the Postgres money type using salary::numeric and later (user_data->>'salary')::money A much more complex example would be representing a numrange where you have to preserve two bounds and their respective inclusivity/exclusivity, in JSON.
- cpursley 3y ago
  I imagine you could modify to use array type. But jsonb makes it easy to normalize back and forth to a row.
- Rapzid 3y ago
  Yeah not really the same then. The approach in the post allows you to easily reuse existing queries by just adding global filters and pointing to the history table.
random_kris 3y ago
I implemented this using drizzle and PG not long ago Check the repo here https://github.com/grmkris/drizzle-pg-notify-audit-table https://github.com/grmkris/drizzle-pg-notify-audit-table
cpursley 3y ago
Thanks, not sure how I missed this - just added to my “Postgres Is Enough” list: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#audit-logs 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.
- kiwicopple 3y ago
  > deep diff functions to get the changes between the old and new jsonb if anyone is interest I'd love to see this if it's public
  - cpursley 3y ago
    Sure. It's somewhat out of date but gets the general idea across: https://gist.github.com/cpursley/a2ae79423bee9274e3639839967e17ff#file-activity_feed_logic-sql-L80 https://gist.github.com/cpursley/a2ae79423bee9274e3639839967...

adamcharnock 3y ago

> Now, there are extensions out there that implement versioning – temporal_tables being the most popular I think – but none are supported for managed Postgres instances on e.g. Azure or AWS. This means that if we want system-versioned tables, we’re forced to make it ourselves. This really provokes a wild geek rage inside me. Not that it is untrue, but that this is the state of things. It's that cloud providers are now often seen as the only responsible way to operate infrastructure, and in the process we hand over so much control of our tooling. We let ourselves be convinced that simplicity was worth a 10x premium, and admitted we were not grown up enough to handle the tools we had made for ourselves. Sure, I know it is more complex than that. But I also feel sad about it.

devnonymous 3y ago
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 ?
- cqqxo4zV46cp 3y ago
  A database is near the top of most people’s list for things that they don’t want to manage themselves. This rant is verging on irrelevant.
  - sroussey 3y ago
    I used to have some MySQL servers running for so many years that I was afraid to restart the machines.
  - DaiPlusPlus 3y ago
    > manage themselves. Be careful with the language there.... I assume you meant that no-one wants to have to run and administer an RDBMS like Postgres, MSSQL, Oracle, etc - especially not things like backups, restoration, disaster-recovery, figure out storage, ensure high-availability, and maybe even things like user administration and index management: i.e. a DBA's grunt-work - and yes, I agree with you entirely. ...but using the other interpretation of "manage", people might think you meant that people don't want to know how to properly design a normalized schema and apply the business-domain, knowing how to clean-up data appropriately (i.e. the other half of a DBA's job), because we can either shove it all into a cloud vendor's proprietary (and horrendously expensive) non-relational database or data-lake solution (CosmosDB, etc) - or that we can outsource database modelling and implementation of DDL to an LLM - which I'll admit, is quite capable as-is for a first-draft for DB scaffolding and getting better all the time, but ultimately isn't able to take a proactive or autonomous approach to managing an org's databases; I don't want to seem like I'm advocating economic-protectionism of DBAs, but I get the impression LLM-based DBA-bots are being aggressively pitched to smaller-sized orgs that don't know any better, at a time when the technology is still very new, with potentially serious repercussions w.r.t. data-security and compliance (and ChatGPT still gives me T-SQL that uses Oracle functions...).

Humphrey 3y ago

This extra table approach is how the django_simple_history[1] implements model history. Although it hooks into the Django ORM to save history rather than triggers. It's simple and brilliant, and I look it into any model that needs the ability to revert. [1] https://django-simple-history.readthedocs.io/en/latest/ https://django-simple-history.readthedocs.io/en/latest/

physicsguy 3y ago
There's also django-reversion, but I can't say I loved using it.
emptysea 3y ago
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)

ceving 3y ago

I am not sure why ranges are used. It seems to be redundant, because the beginning of the validity of a value is identical to the ending if the validity of its previous value. A column with an invalidation time stamp seems to be sufficient for me. The rows, in which this column is null, are currently valid.

perlgeek 3y ago
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.
- javier2 3y ago
  Ah, I did not think of that!
- zeroimpl 3y ago
  The way I deal with this is my history tables have 3 fixed columns: * OperationType: An ENUM of "INSERT", "UPDATE", "DELETE" * OperationTimestamp: TIMESTAMPTZ * OperationId: BIGSERIAL (needed to can handle cases where a row is modified multiple times in a transaction) This structure is super intuitive to understand, and very efficient since the History table is append-only. But if you are trying to use it to reconstruct the state of items from a specific time the past, it's a bit awkward and the use of a RANGE column instead would be easier to query.
Cthulhu_ 3y ago
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.
Merad 3y ago
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.

perlgeek 3y ago

The application I develop for work has its own, horrible, home-brew solution to update log/audit log, you cannot really call it system-versioned, so I read each of these posts with interest. One thing that nearly all of them gloss over that logging the "who did it" isn't as simple as they make it out to be. In most cases, there's a web application or an API gateway or so that connects to the database and does most of the modifications, and only logging the database user of that application makes this piece of information really useless. 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. The web frameworks and ORMs I've worked with in the past don't seem to have any built-in support for something like this, which somehow annoys me much more than it probably should. Sorry for the rant :-)

silvestrov 3y ago
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.
doctor_eval 3y ago
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).
- pdimitar 3y ago

robertlagrant 3y ago

For Python at least, sqlalchemy-history might be a good option. I used its predecessor, sqlalchemy-continuum, and that was decent as well. It just uses the same definitions as the ORM, so it's very usable. Doesn't work with Django because Django ties you to their ORM, but for the rest of the Python world it might be worth a look.

manfre 3y ago
django-pghistory is a good option with Django. https://django-pghistory.readthedocs.io/en/3.0.0/ https://django-pghistory.readthedocs.io/en/3.0.0/

kuon 3y ago

I just implemented versioning in a CMS and I read about every article about it I could find. My conclusion is that while all articles had their point and strengths, what a version is can vary widely between applications. For example, is my app, we decided that versions would represent "committed" modifications, for example you can modify a chapter title 60 times and then publish and get only one version. For "undo" we use browser storage. We came to this solution because it is a shared collaborative editor. In the end it is very git inspired. The other big "problem" is structure. There are things that should and should not be versionned. My point is that a usefull version system is very tightly coupled with the business logic, and it is not just snapshot.

marcosdumay 3y ago
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.
cpursley 3y ago
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.
- kuon 3y ago
  We use an undo stack on the browser side. We realized that it was better not to share this change log between users. Also we do not persist it. But your mileage may vary depending on your needs. In an older app the undo stack was zipped and serialized in db. With zip you avoid storing too much with every character added to 5 pages of text and you do not have to work with diff's.
3y ago

T3RMINATED 3y ago

[dead]

mgsouth 3y ago

This method uses timestamps as version IDs. That's not very reliable when tying rows together, such as primary/foreign-key tables or multiple rows in a single transaction. (There's also a "history_id" column for each history record, but it's not used to tie rows.) Suppose you've got an "order" table, with a "lineitem" detail table, and a "part" (stock item) master table. 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? Even with the three transactions being processed sequentially, it's possible for all three to wind up being handled in the same clock tick and have the same timestamp. As a result, it's not possible to accurately match the history records for the three tables. An opposite case is when a transaction straddles a clock tick and various updated rows end up with different timestamps. The author even alludes to the problem. He tackles the transaction-with-multiple-timestamps issue by using NOW() instead of CLOCK_TIMESTAMP() and punts primary/foreign-key issue, in his use-cases, with "... I don’t think it makes sense to have multiple states at a single point in time anyway." Well, experience says it will happen, surprisingly often. If your use-case can't handle the ambiguity then you'll need to do something different. A more rigorous approach is to include a "version_id" column in each table. If you make it one of the new-fangled UUID7 types then it will be sortable by timestamp. Every table updated in a single transaction should have the same version_id, and every foreign-key reference should also include the foreign version_id. So the "lineitem" table would have "id", "version_id", "order_id", "order_version_id", "part_id", "part_version_id". It is, indeed, a bit more complicated. EDIT: Normally only the history tables need the foreign-key-version columns; you can code the history-creation triggers to automatically include them. This eliminates most of the additional complexity of using them. EDIT: More issues with using timestamps: Consider this timeline: 12:34:56.000 Transaction A begins. 12:34:56.001 Transaction B begins, updates a foreign-key table that's needed by trans A, and commits with a timestamp of 12:34:56.001. 12:34:56.002 Transaction A gets around to reading those foreign-key tables, gets the version created at 56.001. Trans A then commits with a timestamp of 12:34:56.000. So if we look at trans A's history, timestamped at 56.000, we'll wind up matching against the old, stale foreign-key history rows.

hypirion 3y ago
> 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.

ancieque 3y ago

I have implemented this for our tool NF Compose that allows us to build REST APIs without writing a single line of code [0]. I didn't go the route of triggers because we generate database tables automatically and we used to have a crazy versioning scheme that was inspired by data vault and anchor modelling where we stored every change on every attribute as a new record. This allowed for simple point in time queries. Sounded cool, but in practice it was really slow. The techniques that are usually employed by Data Vault to fix this issue seemed too complex. Over time we moved to an implementation that handles the historization dynamically at runtime by generating historizing SQL queries ourselves [1]. We now use transaction time to determine winners and use an autoincrementing column to determine who wins on ties. A lot of brainpower went into ensuring this design is concurrency safe. On a sidenote: Generating SQL in python sounds dangerous, but we spent a lot of time on making it secure. We even have a linter that checks that everything is escaped properly whenever we are in dev mode [2]. [0] https://github.com/neuroforgede/nfcompose/ https://github.com/neuroforgede/nfcompose/ [1] https://github.com/neuroforgede/nfcompose/blob/main/skipper/skipper/dataseries/storage/dynamic_sql/queries/modification_materialized/insert.py https://github.com/neuroforgede/nfcompose/blob/main/skipper/... [2] https://github.com/neuroforgede/nfcompose/blob/main/skipper/skipper/core/lint.py https://github.com/neuroforgede/nfcompose/blob/main/skipper/...

ttfkam 3y ago

Good write up. As I've written my own temporal table library for Postgres that also runs on managed databases like AWS Aurora, here are my observations: If you main table inherits from a history table, you can see a complete history of every entry. It also never requires triggers on insert, only update and delete. A major drawback however is that Postgres table inheritance uses the same mechanism as partitioning, so you can use one or the other but not both. Instead of inheritance, you could keep them completely separate, which is what I ended up with and created set-returning functions of the same name as the main table with a timestamp range as a parameter. The function performs a UNION ALL to merge active and historical results. So instead of FROM mytable you use FROM mytable('2023-03-01 00:00:00Z', '2023-04-01 00:00:00Z') This has made temporal queries much easier to understand and maintain. Another option put forth was to save the timestamp range and tableoid in the history but have historical column data stored as jsonb. This allows history to be shared with all tables and is more forgiving of table column changes. It's also obviously slower and less storage efficient. The temporal access functions end up calling jsonb_populate_record(...), which is extremely handy but again not as efficient as a twinned history structure. However unless you are making a lot of temporal queries with strict SLAs, the jsonb record solution is often good enough, especially with judicious use of partial indexes. Watch out for dependencies on tableoid though; the same table name created by anything other than a dump/restore will not have the same tableoid, so a surrogate id and mapping table may be preferred. One major difference between my implementation and the author's is that my history table is never updated. Ever. It is always append-only. This is a side effect of the initial insert not being recorded in the history. It also means the active table requires at least a last_modified column that is updated by trigger. As was mentioned, adding and removing columns from the active table is a difficult and complex proposition. In fairness though, neither MariaDB or MS SQL Server handle this situation either. Both just tell you to detach the history, make your table changes, and start a new history. This is where the jsonb history option when rolling your own in Postgres really shines. Not covered in the spec or in most temporal implementations in Postgres, it can be VERY useful to record the user you made the changes. Rather than record the role—though this may be all most systems have—I've tended to create application users that are passed in from JWT claims as local config variables. I got the idea from Postgraphile (https://www.graphile.org/postgraphile/jwt-guide/ https://www.graphile.org/postgraphile/jwt-guide/). At first I added this to every history table along with the start and end timestamps but later realized the user never changes mid-transaction. I switched to a model where the transaction id and user are stored in their own table. Rows in Postgres already store the transaction id. Unfortunately this suffers from the same dump/restore problem that tableoid suffers from, so you may want a transaction id surrogate instead depending on your needs. You will also need to track the delete event separately from the last state of your active table record. The user who deleted it may very well be different from the last person to insert/update it. This was handled with a start and end timestamp that were the same, aka a point in time. Finally, it's worth exploring event triggers. Managing all of this manually is a huge PITA. Better to watch for CREATE TABLE events and make the history setup automatically. Better for consistency and for dev sanity. I'm usually not a big fan of dynamic SQL, but this was an area I never regretted utilizing that particular Postgres sledgehammer. Temporal support is a massive undertaking if you want to go beyond the basics. It also imparted even more respect for the Postgres developers for such a powerful and expressive platform to work on.