Updating an Oban job takes quite a long time

Special Sections Oban
,
1

I have a scheduled job that allows my pet to return home at a specific time using the scheduled_at field.

When a user does something in my app, I shorten that time by bringing that scheduled_at closer.

query =
  from(
    job in Oban.Job,
    where: job.worker == "MyApp.Workers.ReturnHome",
    where: job.state == "scheduled",
    where: fragment("? ->> ? = ?", job.args, "pet_id", ^pet.id),
    update: [set: [scheduled_at: ^new_town_arrives_at]]
)

Repo.update_all(query, [])

According to AppSignal, this query is taking 13 seconds, making the process very slow.

UPDATE "oban_jobs" AS o0 
SET "scheduled_at" = $1 
WHERE (o0."worker" = ?) AND (o0."state" = ?) AND (o0."args" ->> ? = $2)

Is there a better way to update the job?

2

Going to try using Oban’s built in replace option instead of manually finding and updating the Job record.

Oban.insert!(
  MyApp.Workers.ReturnHome.new(
    %{"pet_id" => pet.id},
    scheduled_at: new_town_arrives_at,
    unique: [replace: [:scheduled_at]]
  )
)
3

Using the unique + replace option is the “right” way to do this. However, if you want to search jobs by a value there are two things to keep in mind:

  1. Minimize the number of overall jobs (prune, don’t keep so much data around)
  2. Use the containment operator instead so it can use the GIN index
where: fragment("? @> ?", job.args, ^%{pet_id: ^pet.id})
2 Likes
4

Awesome thank you so much, brought it down from 13 seconds in worst case, to 5ms!

from(
  job in Oban.Job,
  where: job.worker == "MyApp.Workers.CallieReturnHome",
  where: job.state == "scheduled",
  where: fragment("? @> ?", job.args, ^%{pet_id: pet.id}),
  update: [set: [scheduled_at: ^new_town_arrives_at]]
)
1 Like