undefined | Better HN

0 pointsryanianian3y ago0 comments

This works fine until the table gets big.

Then most of your statuses are Complete and finding an ever decreasing percentage of Incomplete rows will result in essentially a table-scan.

Then that table scan will start to take longer than your cron interval or will biff through your IO or CPU and you've got problems.

Then you might try adding indexes or beefing up your queries and then you'll realize you've just traded 6 for 12/2 because now your index updates on writes are hurting you more than your scans on reads.

Then you'll have the brilliant idea to cull your data regularly or have different tables for Complete vs Incomplete.

And then you'll want to do this culling or row-move in a transaction because you've never bothered making the job idempotent because it was implicitly so in the database all along, and now your transaction locks will compete with your cron scanning or index updates and now you'll maybe put the status table(s) in a different database and work on idempotency.

And then maybe you'll take a step back, have a nap, and wake up to realize you've just implemented a queue but not very well and have tied its design to your specific business-flow and good luck adding retry logic or new status fields.

If you're gonna use a database as a queue, make it a different database server (or schema!) from your business data. Don't tie business transactions (completing the job) to database transactions (since ultimately there will almost always be more than just one data/processing system involved), and maybe really just adding a simple Redis or SQS thing from the beginning isn't going to cost you that much so might as well squeak it in while the costs are still low.

0 comments

hbrn3y ago

> This works fine until the table gets big.

> Then most of your statuses are Complete and finding an ever decreasing percentage of Incomplete rows will result in essentially a table-scan

This is absolutely not true. Performance of your lookups will depend on the total number of "pending" items and never on the table size.

Generally indexes on boolean columns are useless due to low cardinality, but in this specific example they'll work just fine. If you have a 100 "pending" items in a table with billions of rows, search on "pending" status will never trigger a fullscan, only a 100 lookups. You can even make your index partial, so essentially your index will be your mini-queue.

Idempotency is a valid point, but it is as hard with queues as it is with databases. Exactly-once delivery is generally not possible in distributed systems (in the real world at least, where you don't own every node). Queues won't magically solve it for you, they need to implement the same locking mechanisms as the database.

Partially this is why AWS SQS boasts to be almost infinitely scaleable, but limits you at 300 (!) rps for FIFO queues.

withinboredom3y ago

I love explaining how exactly-once isn’t achievable. It’s my favorite topic… it also can be annoying when you have two systems with an at-most-once vs at-least-once mismatch.

j / k navigate · click thread line to collapse