4 ms·
I am not sure why ranges are used. It seems to be redundant, because the beginning of the validity of a value is identical to the ending if the validity of its
by ceving 3y ago
I am not sure why ranges are used. It seems to be redundant, because the beginning of the validity of a value is identical to the ending if the validity of its previous value. A column with an invalidation time stamp seems to be sufficient for me. The rows, in which this column is null, are currently valid.
- perlgeek 3y agoThere might not be a previous value. If you insert a record and then later delete it, you need to store a range or two timestamps to know when the insertion and the deletion happened.
- javier2 3y agoAh, I did not think of that!
- zeroimpl 3y agoThe way I deal with this is my history tables have 3 fixed columns: * OperationType: An ENUM of "INSERT", "UPDATE", "DELETE" * OperationTimestamp: TIMESTAMPTZ * OperationId: BIGSERIAL (needed to can handle cases where a row is modified multiple times in a transaction) This structure is super intuitive to understand, and very efficient since the History table is append-only. But if you are trying to use it to reconstruct the state of items from a specific time the past, it's a bit awkward and the use of a RANGE column instead would be easier to query.
- Cthulhu_ 3y agoForeign keys / references; you query one history table at timestamp X, then the joined history table with a "where start > X and end < X" to get the foreign key's data at timestamp X.
- Merad 3y agoWith a single timestamp how would you write a queries such as "what was the state of the record at this specific moment in time" or "what changes were made to the record between start_time and end_time"? TFA is also using an index on the range table to ensure that you can't enter overlapping entries in the history.