3 ms·
Creates lots of complexity and Performance issues. I agree using PostgreSQL Extension for Temporal tables is a better idea. I wish PostgreSQL would allow somet
by PeterZaitsev 3y ago
Creates lots of complexity and Performance issues. I agree using PostgreSQL Extension for Temporal tables is a better idea.
I wish PostgreSQL would allow something as Oracle Flashback feature plus allowing to control how far history do you want to keep for specific tables, as typically only part of your data needs full auditing.
- pphysch 3y agoI am curious what success stories people have with versioned databases, and whether those success stories could be replicated by a combination of bitemporal schema design and infrequent snapshots/backups. That is, to avoid the complexity and performance issues of adding a temporal dimensions to 100% of your data instead of the X% that actually needs it.
- gen220 3y agoJust to pile on, I think that adopting bitemporal schemas is the solution. There are various schemes you can employ to keep "archived" data within postgres (e.g. offloading it to another instance accessible behind a FDW). I've thought about this problem a lot, and I think every alternative solution I've seen is just an approximation of bitemporal schemas with some limitations that are typically not worth the cost:benefit trade-off. The main difficulty is there isn't a standardized framework/FOSS solution for bitemporal data. I feel like it'll have to get into the SQL standard before we see widespread adoption.