4 ms·

This works well for the audit part - have done this myself more than once - but makes it hard to impossible to get good query performance for some kinds of quer

This works well for the audit part - have done this myself more than once - but makes it hard to impossible to get good query performance for some kinds of queries. You need to have a trigger that keeps an "up-to-date" view of only the latest version -- or alternatively, have a trigger that keeps the "insert only" table based on one you treat as usual (delete/insert/update)

specialist 5y ago
SQL Server has temporal indices. Our queries seemed fast enough. Sorry, I don't know Postgres, but would expect there's some kind of solution. We stored medical records. Datafeeds were very chatty. Updates would arrive out of order. Resolving "single best record" (aka "source of truth") was trivial via queries. The norm seems to be to adjudicate as data comes in. So my solution really upset a lot of people. To your point, I did have to change the schema for scripts and allergies. To get the indices arranged just right so that GROUP BY queries were performant. Further: UPDATE only strategy enables temporal testing; just specify time range. Saves a lot of time, effort. Versus norm of reloading the data from scratch every test run.