7 ms·
The way I deal with this is my history tables have 3 fixed columns: * OperationType: An ENUM of "INSERT", "UPDATE", "DELETE" * OperationTimestamp: TIME
by zeroimpl 3y ago
The 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.