Back to the Future! Time Travel with Bitemporal Databases
summary
A logistics app records bills of lading and must reproduce exactly what data was shown to authorities, so a handcrafted Postgres solution uses a vessel_name table with valid_from / valid_to plus a separate audit table filled by triggers + audit tables to capture transaction time the result is many views, triggers and maintenance headaches with surprising complexity and storage cost MariaDB and IBM DB2 implement SQL2011 temporal features like system versioning and PERIOD columns so queries using SYSTEM_TIME AS OF return past states without custom audit plumbing
AI-written summary · about
chapters (9)
- 0:00Introduction to talk
- 2:55Problem with vessel renaming
- 7:58Concepts of valid and transaction time
- 17:59Bitemporal databases explained
- 23:22SQL 2011 temporal features
- 29:02MariaDB system versioning
- 33:39Use cases for bitemporal data
- 37:13Retrofitting temporality
- 38:40Resources and conclusion
related talks
01h 03mTemporal Database Demo
23mImplementing Temporal Features in PostgreSQL: SQL Standard and Beyond
39mPrinciples of Reporting Systems: It's About Time we Talked About Bitemporality
46mIntro to Postgres Hacking via Temporal Tables
40mLet's SQL Like It's 1992!
46mEnhancements to the bitemporal model support: integrity constraints
39mTimescaleDB: Re-engineering PostgreSQL as a time-series database
30mCatching Waves with Time-Series Data