Bitemporality in PostgreSQL: Update Your Data Without Losing Information — Miroslav Šedivý

Discover how to update your PostgreSQL data without losing information with bitemporality, a method for storing data records to represent both the history of reality and the database.

Key takeaways

Bitemporality is a method of storing data records to represent both the history of the reality and the history of the database.
In PostgreSQL, ranges can be used to represent time intervals, including intervals that include or exclude the edge points.
Time zones can be taken into account when storing and querying date and time values.
The valid column in a table can be used to store the valid timestamp range for each record.
The revision table can be used to store the history of changes to each record, including the timestamp and ID of the revision.
The rev_id column in the revision table can be used to track the sequence of revisions for each record.
The timestamp column in the revision table can be used to store the timestamp of each revision.
The valid column in the revision table can be used to store the valid timestamp range for each revision.
The overlapping column in the revision table can be used to track whether each revision overlaps with other revisions.
The end column in the revision table can be used to store the end timestamp of each revision.
The start column in the revision table can be used to store the start timestamp of each revision.
The database table can be used to store the current state of the database, including the revision ID and timestamp of each record.
The customer table can be used to store customer information, including the customer ID and name.
The fee table can be used to store fee information, including the fee ID and amount.
The invoice table can be used to store invoice information, including the invoice ID and amount.
The product table can be used to store product information, including the product ID and name.
The timestamp column in each table can be used to store the timestamp of each record.
The valid column in each table can be used to store the valid timestamp range for each record.
The revision table can be used to store the history of changes to each record, including the timestamp and ID of the revision.
The overlapping column in the revision table can be used to track whether each revision overlaps with other revisions.
The end column in the revision table can be used to store the end timestamp of each revision.
The start column in the revision table can be used to store the start timestamp of each revision.
The database table can be used to store the current state of the database, including the revision ID and timestamp of each record.
The customer table can be used to store customer information, including the customer ID and name.
The fee table can be used to store fee information, including the fee ID and amount.
The invoice table can be used to store invoice information, including the invoice ID and amount.
The product table can be used to store product information, including the product ID and name.

Bitemporality in PostgreSQL: Update Your Data Without Losing Information — Miroslav Šedivý

More talks