What does HackerNews think of temporal_tables?
Postgresql temporal_tables extension in PL/pgSQL, without the need for external c extension.
For the "capture changes in an audit table" section, I've had good experiences at a previous company with the Temporal Tables pattern. Unlike other major RDBMS vendors, it's not built into Postgres itself, but there's a simple pattern [1] you can leverage with a SQL function.
This allows you to see a table's state as of a specific point in time. Some sample use cases:
- "What was this user's configuration on Aug 12?"
- "How many records were unprocessed at 11:55pm last night?"
- "Show me the diff on feature flags between now and a week ago"
- https://github.com/nearform/temporal_tables
- https://github.com/xocolatl/periods
- https://github.com/scalegenius/pg_bitemporal
- https://github.com/arkhipov/temporal_tables
I haven't used any of them but I work on https://xtdb.com which is also implementing SQL:2011's temporal features :)
When we switched, we found that although there were minor bugs, we didn't have any noticeable loss of performance and we have used it ever since for many projects.
https://github.com/nearform/temporal_tables
If you're also limited by cloud services and the extensions limitations, this is a great solution.