PGCon2019 - 3.4
PGCon 2019
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Paul Jungwirh |
| Schedule | |
|---|---|
| Day | Talks - Day 2 - 2019-05-31 |
| Room | DMS 1160 |
| Start time | 15:00 |
| Duration | 00:45 |
| Info | |
| ID | 1336 |
| Event type | Lecture |
| Track | Applications |
| Language used for presentation | English |
| Feedback | |
|---|---|
|
Did you attend this event? Give Feedback |
Temporal Databases: Theory and Postgres
Temporal databases let you record history: either a history of the database (what the table used to say), a history of the thing itself (what it used to be), or both at once. The theory of temporal databases goes back to the 90s, but standardization has only just begun with some modest recommendations in SQL:2011, and database products (including Postgres) are still missing major functionality.
This talk will cover how temporal tables are structured, how they are queried and updated, what SQL:2011 offers (and doesn't), what functionality Postgres has already, and what remains to be built.
Many solutions exist for tracking changes to the database, e.g. for auditing or compliance. Often they use triggers to record changes in a separate schema, and better approaches also let you easily query for what the database said at a certain date. (For example see Magnus Hagander's 2015 talk "A Tardis for your ORM".)
On the other hand, if you want to record a history of the thing itself, few solutions are available, and the problem is more complicated. The historical dimension can't be built automatically by triggers, but users need a way to edit old "versions" of the thing. They want to ask questions not only like "as of time t" but also preserving the historical changes across filtering, projecting, and joining. This kind of functionality is much harder to build without first-class support from your RDBMS.
SQL:2011 includes support for both these dimensions, respectively "system time" and "application time", or even both together ("bitemporal"). We'll touch on system time but focus more on application time:
- how to declare a temporal table,
- how to set up temporal primary and foreign key constraints,
- how to insert and update it, and how to query it.
My hope is to get the Postgres community aware of SQL:2011 temporal features so that we can start building support for them. We already have some great building blocks in place, especially ranges, exclusion constraints, and GiST indexes, so I'd like to explore how we can use those to support temporal functionality. I'd like to share my own work on a patch for temporal primary and foreign keys, and also briefly point out some work by others on writing temporal queries.