5 ms·

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 o

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 8mo ago
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 8mo ago
  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.
  - arter45 8mo ago
    Sounds cool! Do you keep all data forever in the same table? I assume you need long retention, so do you keep everything in the same table for years or do you keep a master table for, let's say, the current year and then "rotate" (like logrotate) previous stuff to other tables? Even with indices, a table with, let's say, a billion rows can be annoying to traverse.
    - arnsholt 8mo ago
      I wasn’t involved in the day to day operations of the system, but it had records going back to the 90s at least I think. I think data related to non accepted offers were deleted fairly quickly (since they didn’t end up being actual customers), but outside of that I think everything was kept more or less indefinitely.
ndr 8mo ago
This is also a recurring pattern when using bigtable.
deleted 8mo ago
[deleted]

arter45 8mo ago

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 8mo ago
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.
- arter45 8mo ago
  Sounds cool! Do you keep all data forever in the same table? I assume you need long retention, so do you keep everything in the same table for years or do you keep a master table for, let's say, the current year and then "rotate" (like logrotate) previous stuff to other tables? Even with indices, a table with, let's say, a billion rows can be annoying to traverse.
  - arnsholt 8mo ago
    I wasn’t involved in the day to day operations of the system, but it had records going back to the 90s at least I think. I think data related to non accepted offers were deleted fairly quickly (since they didn’t end up being actual customers), but outside of that I think everything was kept more or less indefinitely.

ndr 8mo ago

This is also a recurring pattern when using bigtable.

deleted 8mo ago

[deleted]