Just 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.

Just 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.