Hacker News Reader

Comment by londons_explore | Hacker News Reader

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?

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.

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.

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.

Partial indexes solve exactly this problem. You add a WHERE condition that restricts the index to a subset of the whole table.

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.

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.

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.

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

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

Reply on news.ycombinator.com