Cron Until It Hurts, Then a Table

Every product I've shipped started with a crontab. Tallowbrook's first background job was a line that emailed overdue-invoice reminders at nine each morning. It's still there, and I have no plans to touch it.

The mistake isn't using cron. It's reaching for a queue before cron has failed you, then running a broker you don't need and paying for it in attention.

What cron is good at

Cron is good at one thing: starting something at a time. If the job is "once a day, do this for everyone", it's perfect. It has no moving parts to break, and when it fails you read a log file.

Cron is bad at four things, and they're the signals that you've outgrown it:

• Work triggered by a user action, such as "send this receipt now".

• Work that must be retried when it fails, with a count.

• Work that must not run twice at once.

• Work you want to see: what's waiting, what's stuck, what failed.

If you have none of those, stop reading and go and enjoy your crontab.

The step before a broker

When the signals show up, the next tool isn't a message service. It's a table in the database you already run. A job is a row. A worker claims a row, does the work, and marks it done. With SQLite 3.35 or later, claiming is one statement, because UPDATE can return the row it changed:

CREATE TABLE jobs (
  id INTEGER PRIMARY KEY,
  payload TEXT NOT NULL,
  state TEXT NOT NULL DEFAULT 'queued',
  attempts INTEGER NOT NULL DEFAULT 0
);

UPDATE jobs SET state='running', attempts=attempts+1
WHERE id=(SELECT id FROM jobs WHERE state='queued' ORDER BY id LIMIT 1)
RETURNING id, payload;

I loaded three jobs and ran the claim repeatedly. It returned 1|send-receipt:41, then 2|send-receipt:42. I put job 1 back in the queue by hand, and the next claim returned it again (a final SELECT showed its attempts at 2), followed by 3|rebuild-sitemap. A fifth claim printed nothing, which is how a worker knows the queue is empty. Each job came out once per claim, in order.

Because the select and the update are one statement, two workers can't claim the same row. I checked that rather than trusting it: eight worker processes draining 400 jobs, each with a busy timeout set, claimed 400 jobs and not one twice. SQLite serialises writers, so the lock you'd otherwise hand-roll is already there. On a database that allows concurrent writers you'd add row locking. Here it's free.

What it doesn't give you

Honesty time. This table doesn't schedule things in the future, so add a run_at column and a condition. It doesn't rescue jobs from a worker that died while running, so add a timestamp and a sweeper that puts stale rows back. It doesn't fan out across machines. Each of those is a handful of lines, and I'd rather add them as I need them than carry a system built for the version of my company I hope to have.

My line for switching

I'll move to a real queue when more than one machine needs to claim jobs, or when the jobs table itself becomes the busiest thing in the database. I measure the second by watching write latency. Until one of those two happens, cron handles the clock, the table handles everything else, and I get to read the whole system in one sitting.