Closed bnauta closed 6 years ago
@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?
Fixed in #28
@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.
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.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.