petr-korobeinikov.com

Postgres Workqueue

· postgres, workqueue

This post is about running a deferred-task queue inside a database. In practice, a lot of developers struggle with the idea — they immediately raise concerns about the artificially generated load. On one hand, that’s a fair point. On the other, it’s the kind of barrier you have to learn to step over: watch your application and tune the database properly.

What won’t we cover? We won’t say anything about optimization or partitioning. That part of the problem depends on your application’s load profile.

So, the requirements:

We’ll keep tasks in a workqueue table. One row, one task:

create table workqueue
(
  id          bigserial primary key,
  payload     jsonb       not null,
  enqueued_at timestamptz not null default now(),
  locked_at   timestamptz,
  locked_by   text
)

The locked_at and locked_by fields are updated when a task is taken into work. A convenient value for locked_by is the host address or the pod name in k8s.

The payload field holds everything needed to execute the task.

What does enqueueing a task look like? Very simple:

insert into workqueue (payload)
values ('{"foo": "bar"}')

Now let’s talk about pulling the next task off the queue. We agreed that a task has a window in which it must complete. The task itself must be executed in a single instance — at any given moment only one worker is processing it.

with task as (select id
              from workqueue
              where locked_by is null
                 or locked_at < now() - :retry_timeout
              order by id for update skip locked
              limit 1)
update workqueue q
set locked_by = :worker_name,
    locked_at = now()
from task
where task.id = q.id
returning q.id, q.payload

As soon as the task is executed, it should be removed from the queue table:

delete
from workqueue
where id = :id

That’s enough to illustrate how the queue works. In practice, instead of deleting, we move the task into an archive table at delete time.

That’s pretty much all there is to it. A database-backed task queue is a fairly simple thing. Stick to the pattern:

The queue approach is used quite often. There’s nothing to worry about. In my experience, a properly tuned postgres handles substantial loads — no reason to fret about the “artificial” load. To put numbers on it: 2k TPS doesn’t affect performance, but it may be noticeable on your hardware and setup. Apply the approach wisely.

References