3 ms·

Is it not normally bad practice to have 2 tables with basically identical fields and move rows between them? Isn't that exactly what indexes were designed for?

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 ago
  Yeah, 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 ago
  Partial indexes solve exactly this problem. You add a WHERE condition that restricts the index to a subset of the whole table.
  - frenchyatwork 3y ago
    Partial 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 ago
  In 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 ago
  The 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.
bobbyi 3y ago
Materialized views handle this scenario, but sqlite doesn't have them
- michaelcampbell 3y ago
  For a background action/job use case though, how often would you have to re-materialize the view?
drdaeman 3y ago
Well, many SQL databases, although not SQLite, have table partitioning feature which does exactly this. The main benefit is that one can configure tablespace (in PostgreSQL terms; essentially the drives to store data) and storage parameters separately for each partition. Indexes are just the tip of the iceberg (but sometimes just using partial indexes may do wonders), it’s ability to tweak stuff like fillfactor for a frequently updated small portion of the table (e.g. active sessions vs archived sessions) is that makes a lot of difference.

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 ago
Yeah, 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 ago
Partial indexes solve exactly this problem. You add a WHERE condition that restricts the index to a subset of the whole table.
- frenchyatwork 3y ago
  Partial 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 ago
In 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 ago
The 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.

bobbyi 3y ago

Materialized views handle this scenario, but sqlite doesn't have them

michaelcampbell 3y ago
For a background action/job use case though, how often would you have to re-materialize the view?

drdaeman 3y ago

Well, many SQL databases, although not SQLite, have table partitioning feature which does exactly this. The main benefit is that one can configure tablespace (in PostgreSQL terms; essentially the drives to store data) and storage parameters separately for each partition. Indexes are just the tip of the iceberg (but sometimes just using partial indexes may do wonders), it’s ability to tweak stuff like fillfactor for a frequently updated small portion of the table (e.g. active sessions vs archived sessions) is that makes a lot of difference.