4 ms·
If you're tempted to use audit tables, you might also consider making the jump to temporal tables. I rolled my own light-weight version using inheritance-based
by sa46 5y ago
If you're tempted to use audit tables, you might also consider making the jump to temporal tables. I rolled my own light-weight version using inheritance-based partitioning in Postgres. The basic idea is:
- Create a parent table like items with a valid time range column as a tstzrange type. This table won't store data.
- Create two child tables using Postgres inheritance, item_past and item_current that will store data.
- Use check constraints to enforce that all active rows are in the current table (by checking that the upper bound of the tstzrange is infinite). Postgres can use check constraints as part of query planning to prune to either the past or current table.
- Use triggers to copy from the current table into the past table on change and set the time range appropriately.
The benefits of this kind of uni-temporal table over audit tables are:
- The schema for the current and past is the same and will remain the same since DDL updates on the parent table propagate to children. I view this as the most substantial benefit since it avoids information loss with hstore or jsonb.
- You can query across all versions of data by querying the parent item table instead of item_current or item_past.
The downsides of temporal tables:
- Foreign keys are much harder on the past table since a row might overlap with multiple rows on the foreign table with different times. I limit my use of foreign keys to only the current table.
- sparsely 5y agoMSSQL comes with (very nice) temporal table support out of the box, if you can stomach the license fees.
- 5e92cb50239222b 5y agoMariaDB supports temporal tables, if you can stomach MySQL. Works fine in my experience, although the largest database I've used it with is no more than 30 GBs. https://mariadb.com/kb/en/system-versioned-tables/ https://mariadb.com/kb/en/system-versioned-tables/
- tluyben2 5y agoYes, I wondered why open source dbs don't have that out of the box (although it seems mysql has it in the other answer to you, so will check) as I use them extensively: both audit tables and temporal tables really make my work in banking far easier (and audit tables are often mandatory anyway). We use open source (both mysql and postgres) but I keep missing mssql features and have to replace them with adhoc stuff that often does not work on AWS Aurora as-is.
- BatteryMountain 5y agoIf anyone feel interested after reading this comment, checkout the book called "Developing Time-Oriented Database Applications in SQL". edit: it contains many database-patterns that are useful even you aren't building time-oriented applications. It will make you a better developer. edit 2: basically, once you know some things from this book, every time you encounter a new database engine or programming language, the first thing you will want to look at is how the language handles dates & times - suddenly this kind of field become way more interesting. So the book might change how you view dates/times permanently, just be careful if you tend to over-engineer or are perfectionist, because it makes it easy to do so. Apply the patterns when it makes pragmatic sense.
- sa46 5y agoGood recommendation; I also got a lot of mileage out of: Managing Time in Relational Databases: How to Design, Update and Query Temporal Data How to Design, Update and Query Temporal Data > be careful if you tend to over-engineer or are perfectionist One of the downsides of these books is that I see bitemporal data everywhere now but its quite difficult to write bitemporal queries in Postgres.
- BatteryMountain 5y agoThat's exactly what I mean. It's new lens and feels like it should be applied to everything, when it really should not. But then you start overthinking, that ~maybe~ your will regret not doing it etc. Silly I know.