5 ms·

There was a very interesting presentation this year at PGConf New York with something similar [1], but where the history table has the same name as the normal t

There was a very interesting presentation this year at PGConf New York with something similar [1], but where the history table has the same name as the normal table, but lives in a separate history schema. Also there is a timetravel schema whose tables again have the same name, but are views of the history schema, with an extra WHERE filter to only show rows that were active at a given time. So by changing your schema_search_path and setting the "current_time" setting you can easily query the whole database as if it were some other time! I haven't tried this out yet, but it looks to be very compatible with an ORM like ActiveRecord. This is something I've been looking for for a long time. [1] http://www.hagander.net/talks/tardis_orm.pdf http://www.hagander.net/talks/tardis_orm.pdf (pdf)

tvon 11y ago
This seems to be the talk: https://www.youtube.com/watch?v=TRgni5q0YM8 https://www.youtube.com/watch?v=TRgni5q0YM8
aidos 11y ago
That seems like a really clean way of building the abstraction. Are the only downsides the overhead of writing to the extra schema and the space required? Anyone know how bad the hit would be for the writes? With this setup it seems like you don't lose much by trying it. No interruption to the current db, you have the extra data if you want it, you can always cull to free up space and you can remove the whole thing at any point. Or am I missing something?
- pjungwir 11y ago
  I agree it seems pretty great. One big pain point would be if the schema changes over time. What if you add a new NOT NULL column? What if you remove a column? What if you change the type of a column? What if you remove a table? What if you add a new CHECK constraint? Some of these things get mentioned in the video linked by 'tvon, but they are mostly passed over.
  - aidos 11y ago
    That's a good point. I guess in terms of the constraints you wouldn't mirror those to your history schema anyway? The changing schema is not so easily solved. Again, you could be a little looser with it. Don't remove columns from the history table (just mark as nullable). Adding columns would be a similar thing. I guess I'm thinking of the use case of wanting to get a bit of extra history of free vs the "needs to be auditable, 100% correct, user queriable" scenario.
- pjungwir 11y ago
  Oh also, re extra writes: in the video Magnus points out that this approach adds no extra transactions, because everything happens inside a trigger. Also no extra trips to the database server, etc. So it's not free, but it just means when you're doing a write already, it's a little extra work.

tvon 11y ago

This seems to be the talk: https://www.youtube.com/watch?v=TRgni5q0YM8 https://www.youtube.com/watch?v=TRgni5q0YM8

aidos 11y ago

That seems like a really clean way of building the abstraction. Are the only downsides the overhead of writing to the extra schema and the space required? Anyone know how bad the hit would be for the writes? With this setup it seems like you don't lose much by trying it. No interruption to the current db, you have the extra data if you want it, you can always cull to free up space and you can remove the whole thing at any point. Or am I missing something?

pjungwir 11y ago
I agree it seems pretty great. One big pain point would be if the schema changes over time. What if you add a new NOT NULL column? What if you remove a column? What if you change the type of a column? What if you remove a table? What if you add a new CHECK constraint? Some of these things get mentioned in the video linked by 'tvon, but they are mostly passed over.
- aidos 11y ago
  That's a good point. I guess in terms of the constraints you wouldn't mirror those to your history schema anyway? The changing schema is not so easily solved. Again, you could be a little looser with it. Don't remove columns from the history table (just mark as nullable). Adding columns would be a similar thing. I guess I'm thinking of the use case of wanting to get a bit of extra history of free vs the "needs to be auditable, 100% correct, user queriable" scenario.
pjungwir 11y ago
Oh also, re extra writes: in the video Magnus points out that this approach adds no extra transactions, because everything happens inside a trigger. Also no extra trips to the database server, etc. So it's not free, but it just means when you're doing a write already, it's a little extra work.