Performance: jobs table index by default
bnauta opened this issue · 4 comments
We're currently using Rihanna in production and ran into a major issue where Job.lock was taking nearly 3 minutes to execute on ~160k records. The vast majority of performance hit can be blamed on these comparison and sort operations, which, conveniently, can be addressed by a single index.
CREATE INDEX CONCURRENTLY rihanna_jobs_enqueued_at_id ON rihanna_jobs (enqueued_at ASC, id ASC);Job.lock now runs on the same dataset at < 2ms.
I've forked and fleshed out the benchmark with a test for 100k records and the above index, but ultimately would like to include the index by default on table create/update.
Creating this issue to track progress and discussion.
@bnauta Amazing work! I can't believe I forgot the index...
I actually had this locally because I added it manually during my testing, but clean forgot to add it to the Rihanna SQL. If you like, would you be able to open a PR that includes an upgrade to add the index as well as adding it to the main create SQL?
@samphilipd thanks for handling this so quickly! I've been a little swamped.
I noted that CREATE INDEX ... IF NOT EXISTS is only supported in PostgreSQL 9.5+, and it seems we're aiming to support older versions.
We can ensure backwards compatibility by using an approach such as this (see: v9.3 and older solution). A little lower priority, but I could create a PR in the next few days.
Rihanna officially supports postgres 9.5+ so I think this is fine. Thanks for looking into it though!
EDIT: I forgot to add, the reason we require at least 9.5 is because we use FOR UPDATE SKIP LOCKED which is only available on these versions anyway.