6 ms·

I really hope Postgres can support temporal table out of the box. Temporal table can simplify development for the feature that need audits.

refset 6y ago
System time (aka "transaction time") is also invaluable for debugging if you annotate it with release versions. Unless an application is particularly strapped for storage costs, which is rare in this day and age, it ought to be the default choice to use built-in system time versioning wherever it exists.
mulander 6y ago
Funny historical and architecture fact about PostgreSQL. It actually can do this, for all tables without special features. Unfortunately the facility to perform a query like this is no longer exposed but it shouldn't be impossible to re-add in a more modern way. Essentially PostgreSQL has copy-on-write semantics, so historical records exist unless a vacuum marks them as no longer needed and subsequent insert/updates overwrite the values. In the past when PostgreSQL had the postquel language (before SQL was added) there was special syntax to access data at specific points in time: This is nicely outlined in "THE IMPLEMENTATION OF POSTGRES" by Michael Stonebraker, Lawrence A. Rowe and Michael Hirohama[1]. Go ahead open the PDF and search for "time travel" or read the quotes below. > The second benefit of a no-overwrite storage manager is the possibility of time travel. As noted earlier, a user can ask a historical query and POSTGRES will automatically return information from the record valid at the correct time. Quoting the paper again: > For example to find the salary of Sam at time T one would query: retrieve (EMP.salary) using EMP [T] where EMP.name = "Sam" > POSTGRES will automatically find the version of Sam’s record valid at the correct time and get the appropriate salary. [1] - https://dsf.berkeley.edu/papers/ERL-M90-34.pdf https://dsf.berkeley.edu/papers/ERL-M90-34.pdf
- jarym 6y ago
  Really nice background, thanks for sharing! I knew Postgres did CoW internally and always wondered why the SQL standard for time-travel queries was not implemented. I am using triggers and audit tables which works but my data requirements are relatively small so I won't face any challenges that way. However, re-using the old rows like this would lead to a far more efficient approach if it were supported natively.
mildbyte 6y ago
Shameless plug (I'm a co-founder) but this is basically what we've built with Splitgraph[0]: we can add change tracking to tables using PostgreSQL's audit triggers and let the user switch between different versions of the table / query past versions. [0] https://www.splitgraph.com/product/data-lifecycle/research https://www.splitgraph.com/product/data-lifecycle/research
- refset 6y ago
  That sounds neat. What does the performance of querying past versions look like? For instance, is lookup time linear with the amount of history or do you maintain special temporal indexes?
  - mildbyte 6y ago
    It varies depending on how the user chooses to structure storage (we're flexible with that) and what mode of querying they use. We have a more in-depth explanation and some benchmarks in an IPython notebook at [1]. We store Splitgraph "image" (schema snapshot) metadata in PostgreSQL itself and each image has a timestamp, so you could create a PG index on that to quickly get to an image valid at a certain time. Each image consists of tables and each table is a set of possibly overlapping objects, or "chunks". If two chunks have a row with the same PK, the row from the latter will take precedence. Within these constraints, you can store table versions however you want -- e.g. as a big "base" chunk and multiple deltas (least storage, slowest querying) or as a multiple big chunks (faster querying, more storage). You can query tables in two ways. Firstly, you can perform a "checkout". Like Git, this replays changes to a table in the staging area and turns it into a normal PostgreSQL table with audit triggers. You get same read performance (and can create whatever PG indexes you want to speed it up). Write performance is 2x slower than normal PostgreSQL since every change has to be mirrored by the audit trigger. When you "commit" the table (a Splitgraph commit, not the Postgres commit), we grab those changes and package them into a new chunk. In this case, you have to pay the initial checkout cost. You can also query tables without checking them out (we call this "layered querying" [2]). We implemented this through a read-only foreign data wrapper, so all PG clients still support it. In layered querying, we find the chunks that the query requires (using bloom filters and other metadata), direct the query to those and assemble the result. The cool thing about this is you don't have to have the whole table history local to your machine: you can store some chunks on S3 and Splitgraph will download them behind the scenes as required, without interrupting the client. Especially for large tables, this can be faster than PostgreSQL itself, since we are backed by a columnar store [3]. [1] https://www.splitgraph.com/docs/getting-started/frequently-asked-questions#whats-the-performance-like-do-you-have-any-benchmarks https://www.splitgraph.com/docs/getting-started/frequently-a... [2] https://www.splitgraph.com/docs/large-datasets/layered-querying https://www.splitgraph.com/docs/large-datasets/layered-query... [3] https://www.splitgraph.com/docs/concepts/objects https://www.splitgraph.com/docs/concepts/objects
gen220 6y ago
I work at a company where (many years ago) we built an extension to Postgres (and some helper libs in SQLAlchemy, Go) for implementing decently-performant bitemporal tables (biggest history tables have hundreds of millions of rows). Pretty much our entire company runs on it today. We implemented the “minimum viable” features (i.e. automatic expiring, non-destructive updates, generated indexes and generated table declarations), but left some of the “harder” ideas up to the application designer (adding semantic versioning on top of temporal versioning, schema migrations). It’s worked really well for us. I can’t think of anything we’ve done that’s had a higher ROI than this. I’ll really miss it when I leave!
- eyelidlessness 6y ago
  This is the kind of thing I always design with the possibility of open sourcing in mind, even if I don’t have buy in or dedicated time to make the open source effort at that moment. Even if you miss it when you’re gone, you’ll have the benefit of hindsight of where the boundaries are between your own business needs and the more general use case, and can take that with you and apply the same lessons (often with improvements) the next time you face a similar problem.

refset 6y ago

System time (aka "transaction time") is also invaluable for debugging if you annotate it with release versions. Unless an application is particularly strapped for storage costs, which is rare in this day and age, it ought to be the default choice to use built-in system time versioning wherever it exists.

mulander 6y ago

Funny historical and architecture fact about PostgreSQL. It actually can do this, for all tables without special features. Unfortunately the facility to perform a query like this is no longer exposed but it shouldn't be impossible to re-add in a more modern way. Essentially PostgreSQL has copy-on-write semantics, so historical records exist unless a vacuum marks them as no longer needed and subsequent insert/updates overwrite the values. In the past when PostgreSQL had the postquel language (before SQL was added) there was special syntax to access data at specific points in time: This is nicely outlined in "THE IMPLEMENTATION OF POSTGRES" by Michael Stonebraker, Lawrence A. Rowe and Michael Hirohama[1]. Go ahead open the PDF and search for "time travel" or read the quotes below. > The second benefit of a no-overwrite storage manager is the possibility of time travel. As noted earlier, a user can ask a historical query and POSTGRES will automatically return information from the record valid at the correct time. Quoting the paper again: > For example to find the salary of Sam at time T one would query: retrieve (EMP.salary) using EMP [T] where EMP.name = "Sam" > POSTGRES will automatically find the version of Sam’s record valid at the correct time and get the appropriate salary. [1] - https://dsf.berkeley.edu/papers/ERL-M90-34.pdf https://dsf.berkeley.edu/papers/ERL-M90-34.pdf

jarym 6y ago
Really nice background, thanks for sharing! I knew Postgres did CoW internally and always wondered why the SQL standard for time-travel queries was not implemented. I am using triggers and audit tables which works but my data requirements are relatively small so I won't face any challenges that way. However, re-using the old rows like this would lead to a far more efficient approach if it were supported natively.

mildbyte 6y ago

Shameless plug (I'm a co-founder) but this is basically what we've built with Splitgraph[0]: we can add change tracking to tables using PostgreSQL's audit triggers and let the user switch between different versions of the table / query past versions. [0] https://www.splitgraph.com/product/data-lifecycle/research https://www.splitgraph.com/product/data-lifecycle/research

refset 6y ago
That sounds neat. What does the performance of querying past versions look like? For instance, is lookup time linear with the amount of history or do you maintain special temporal indexes?
- mildbyte 6y ago
  It varies depending on how the user chooses to structure storage (we're flexible with that) and what mode of querying they use. We have a more in-depth explanation and some benchmarks in an IPython notebook at [1]. We store Splitgraph "image" (schema snapshot) metadata in PostgreSQL itself and each image has a timestamp, so you could create a PG index on that to quickly get to an image valid at a certain time. Each image consists of tables and each table is a set of possibly overlapping objects, or "chunks". If two chunks have a row with the same PK, the row from the latter will take precedence. Within these constraints, you can store table versions however you want -- e.g. as a big "base" chunk and multiple deltas (least storage, slowest querying) or as a multiple big chunks (faster querying, more storage). You can query tables in two ways. Firstly, you can perform a "checkout". Like Git, this replays changes to a table in the staging area and turns it into a normal PostgreSQL table with audit triggers. You get same read performance (and can create whatever PG indexes you want to speed it up). Write performance is 2x slower than normal PostgreSQL since every change has to be mirrored by the audit trigger. When you "commit" the table (a Splitgraph commit, not the Postgres commit), we grab those changes and package them into a new chunk. In this case, you have to pay the initial checkout cost. You can also query tables without checking them out (we call this "layered querying" [2]). We implemented this through a read-only foreign data wrapper, so all PG clients still support it. In layered querying, we find the chunks that the query requires (using bloom filters and other metadata), direct the query to those and assemble the result. The cool thing about this is you don't have to have the whole table history local to your machine: you can store some chunks on S3 and Splitgraph will download them behind the scenes as required, without interrupting the client. Especially for large tables, this can be faster than PostgreSQL itself, since we are backed by a columnar store [3]. [1] https://www.splitgraph.com/docs/getting-started/frequently-asked-questions#whats-the-performance-like-do-you-have-any-benchmarks https://www.splitgraph.com/docs/getting-started/frequently-a... [2] https://www.splitgraph.com/docs/large-datasets/layered-querying https://www.splitgraph.com/docs/large-datasets/layered-query... [3] https://www.splitgraph.com/docs/concepts/objects https://www.splitgraph.com/docs/concepts/objects

gen220 6y ago

I work at a company where (many years ago) we built an extension to Postgres (and some helper libs in SQLAlchemy, Go) for implementing decently-performant bitemporal tables (biggest history tables have hundreds of millions of rows). Pretty much our entire company runs on it today. We implemented the “minimum viable” features (i.e. automatic expiring, non-destructive updates, generated indexes and generated table declarations), but left some of the “harder” ideas up to the application designer (adding semantic versioning on top of temporal versioning, schema migrations). It’s worked really well for us. I can’t think of anything we’ve done that’s had a higher ROI than this. I’ll really miss it when I leave!

eyelidlessness 6y ago
This is the kind of thing I always design with the possibility of open sourcing in mind, even if I don’t have buy in or dedicated time to make the open source effort at that moment. Even if you miss it when you’re gone, you’ll have the benefit of hindsight of where the boundaries are between your own business needs and the more general use case, and can take that with you and apply the same lessons (often with improvements) the next time you face a similar problem.