For everyone using MariaDB instead of PostgreSQL, a similiar feature called "System-Versioned Tables" exists since MariaDB 10.3 [0].
We're using this feature in production as part of a financial forecast application. This makes it possible to forecast spendings/revenues based on data from different points in time. For example the users can generate a forecast in december based on data as valid as in june and can compare how good their forecast model predicted the reality at the end of the year.
This works quite well but there are some pain points:
1. System-period (transaction time) data is immutable and can't be changed with queries. If "faulty" (in the sense of "technically correct" but not reflecting reality) data is imported to the database, the database will return this faulty data for this "as of" date no matter what and there is no easy way to drop this data later.
2. This is the main reason why logical backups (mysqldump) for historical data are not possible, since there is no way to write that data back with queries. This should be possible in the future but the correpondig (critical) bug ticket wasn't solved in almost 3 years.[1] You can use mariabackup (physical backups of the underlying file-system storage) instead but this requires console-access to the database server which isn't always allowed in corporate environments.
3. I'm not aware of any ORM framework which supports System-versioned tables (neither MariaDB nor PostgreSQL) so you have to write your own solution on top of an existing ORM or use raw queries.
(Disclaimer: I'm coming from MariaDBs temporal table feature but this is basically the same in PostgreSQL)
Temporal tables adding an additional "time axis" to SQL databases. A "valid from" and "valid to" field is added to each row and the SQL syntax is extended for allowing two new types of queries:
1. Query the data as of a specific point in time. E.g. "show all customers as of April 9th 2021". This not only limits WHICH customers you see, but also select the exact state of each customer as valid at this point in time. For example if the adress of a customer changed on April 10th, you will get the old address.
2. Query all versions of a specific entity. This makes tracking of changes or time series analysis possible.
This feature is transparent, so if you use "normal" SQL queries, you keep getting the current state of the data. I don't know if this is true for this PostgreSQL extension, but MariaDB even hides the "valid from" and "valid to" columns and only show them when you explicitly select them.
Additionally there a two types of "valid from"/"valid to" data, which can exist at the same time:
1. Application period: Those validity dates represent a period in the real world. If we stay at the customer address example, they can express "a customer informed me, that their addess will change on April 15th, so the current address is valid until then and the new address is valid from then".
2. System period (also called transaction time): Those validity dates are a kind of technical period. They represent when data changed in the database, e.g. the exact point in time when an UPDATE query was executed.
Looks promising, but I'm not sure about the crypto-part. Can someone give some notes about the security of NaCl Secretbox using Poly1305 as authenticator and XSalsa20 for encryption?
Is it justified to assume that this is adequate crypto as long as the nonces are choosen correctly (= as random as possible) and the keysize is bigger than 128bit (rclone uses 256bit key derived from user password)?