I have only seen bitemporality being useful in two contexts. 1 is a timeseries store which as I say usually 2 tables is all you need. Secondly is an EAV (entity/attribute/value store) which is just one table. So again entirely manageable. I’ve seen it work just fine with 10s of thousands of logical timeseries and billions of ticks or entities up to the millions without much of an issue. You definitely don’t need a special database and if anything the special database would probably scale worse than normal databases of the kind I’ve mentioned.
An EAV table is usually a symptom of a wider set of issues with schema management, and in contrast, most people I've spoken to who have implemented their own EAV tables on top of a regular SQL database have ended up regretting it because the approach is too hard to scale and maintain. In your experience, was the EAV model limited to a subset of the overall schema?
I agree a special database shouldn't be necessary at all, and instead, convenient syntax for immutable DML and temporal support should be built into Postgres already. But short of a miracle it will probably take a new ('special') database in order for Postgres to evolve in response. Therefore, in the meantime, we believe there's a gap in the market for organisations who value the 'safety' (foolproof complexity reduction) that native bitemporality in a database can offer above the raw query performance offered by existing update-in-place databases: https://xtdb.com/blog/but-bitemporality-always-introduces-co...
