[arnsholt]

The core system at my previous employer (an insurance company) worked along the lines of the solution you outline at the end: each table is an append only log of point in time information about some object. So the current state is in the row with the highest timestamp, and all previous stars can be observed with appropriate filters. It’s a really powerful approach.

[arter45]

So basically something like this?

(timestamp, accountNumber, value, state)

And then you just

SELECT state FROM Table WHERE accountNumber = ... ORDER BY timestamp DESC LIMIT 1

right?

[arnsholt]

Yeah, basically. The full system actually has more date stuff going on, to support some other more advanced stuff than just tracking objects themselves, but that's the overall idea. When you need to join stuff it can be annoying to get the SQL right in order to join the correct records from a different table onto your table of interest (thank Bob for JOIN LATERAL), but once you get the hang of it it's fairly straightforward. And it gives you the full history, which is great.

[ndr]

This is also a recurring pattern when using bigtable.