undefined | Better HN

0 pointsarter452mo ago0 comments

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?

0 comments

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.

arter45OP2mo 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.

arnsholt2mo 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.

j / k navigate · click thread line to collapse

0 pointsarter452mo ago0 comments

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?

0 comments

arnsholt2mo ago

arter45OP2mo ago

Even with indices, a table with, let's say, a billion rows can be annoying to traverse.

arnsholt2mo ago

j / k navigate · click thread line to collapse