4 ms·
If one table had millions of rows and the other only a few dozen and you are querying the few dozen often then it could make sense to have two tables. Different
by postalrat 3y ago
If one table had millions of rows and the other only a few dozen and you are querying the few dozen often then it could make sense to have two tables. Different database treat indexes with sparse data differently.
- simonw 3y agoYeah, that seems like the reason for this design decision here. The queue_running table sits empty the majority of the time, very occasionally gaining a row or two just while they are being processed.
- giovannibonetti 3y agoPartial indexes solve exactly this problem. You add a WHERE condition that restricts the index to a subset of the whole table.
- frenchyatwork 3y agoPartial indexes are still slower, the database needs to scan the index, and then look for the data, and data is less likely to appear in the same pages.
- singron 3y agoIn Postgres this effect is huge since there aren't primary indexes. All tuples for a table get mixed up together in the heap, so a large table will tend to having only 1 hot tuple per page. Additionally, every update and delete leaves a dead tuple to implement MVCC, and these are much easier to clean up with VACUUM on a small table. I'm not as familiar with sqlite, but it might have a similar problem in this case since the primary clustered index is on rowid and not a value that correlates with whether the queue item is running.
- michaelcampbell 3y agoThe one table model is exactly what "delayed_jobs" for ruby does (at least with postgres). Furthermore there's no broker; each worker does his own query for the next job/thing/action to take on, so as you scale with more workers it can get really contentiony on the one table.