Comment by arter45
5 hours 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?
5 hours 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?
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.
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.
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.