<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Piergiuseppe D'Abbraccio</title>
    <description>The latest articles on DEV Community by Piergiuseppe D'Abbraccio (@piergiuseppe).</description>
    <link>https://dev.to/piergiuseppe</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4252265%2Fbede8fc9-ff71-423f-9b10-773d2a219d89.jpg</url>
      <title>DEV Community: Piergiuseppe D'Abbraccio</title>
      <link>https://dev.to/piergiuseppe</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/piergiuseppe"/>
    <language>en</language>
    <item>
      <title>How a job queue fits in one SQLite file</title>
      <dc:creator>Piergiuseppe D'Abbraccio</dc:creator>
      <pubDate>Wed, 18 Nov 2026 16:16:59 +0000</pubDate>
      <link>https://dev.to/piergiuseppe/how-a-job-queue-fits-in-one-sqlite-file-3if2</link>
      <guid>https://dev.to/piergiuseppe/how-a-job-queue-fits-in-one-sqlite-file-3if2</guid>
      <description>&lt;p&gt;Most Python projects I have worked on needed background jobs long before they needed a message broker. A welcome email, a PDF to render, a nightly report. Each time the answer was the same: add Redis, add Celery, add a worker service, then monitor all three.&lt;/p&gt;

&lt;p&gt;A few months ago I got tired of it and started &lt;a href="https://github.com/Piergiuseppe/tinyjobs" rel="noopener noreferrer"&gt;tinyjobs&lt;/a&gt;, a job queue where the queue is a SQLite file. This post is about how that works: what stops two workers from running the same job, what happens when a worker dies in the middle of one, and where SQLite stops being enough.&lt;/p&gt;

&lt;h2&gt;
  
  
  A job is a row
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;tinyjobs&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;TinyJobs&lt;/span&gt;

&lt;span class="n"&gt;app&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nc"&gt;TinyJobs&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"sqlite:///jobs.db"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;


&lt;span class="nd"&gt;@app&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;task&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;max_attempts&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;5&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;send_welcome_email&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;user_id&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;int&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="kc"&gt;None&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;


&lt;span class="n"&gt;send_welcome_email&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;enqueue&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;42&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;enqueue&lt;/code&gt; inserts one row into a table called &lt;code&gt;tinyjobs_jobs&lt;/code&gt;: the task name, the arguments as JSON, the status &lt;code&gt;queued&lt;/code&gt;, and a &lt;code&gt;run_at&lt;/code&gt; time. That row is the whole message. A worker is a process that looks for rows it can run.&lt;/p&gt;

&lt;p&gt;The &lt;code&gt;run_at&lt;/code&gt; column does more work than it seems. A delayed job is a row with &lt;code&gt;run_at&lt;/code&gt; in the future. A retry is the same row with &lt;code&gt;run_at&lt;/code&gt; pushed forward by the backoff. A cron schedule inserts a row at each due time. Three features, one column, and one question for the worker: which rows are due?&lt;/p&gt;

&lt;h2&gt;
  
  
  Claiming: one statement, one winner
&lt;/h2&gt;

&lt;p&gt;The dangerous moment in any queue is the claim. If two workers both see a job as free and both take it, it runs twice. The classic bug is reading in one statement and writing in the next, because another worker can slip in between.&lt;/p&gt;

&lt;p&gt;tinyjobs finds and takes the jobs in a single statement:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="n"&gt;tinyjobs_jobs&lt;/span&gt;
   &lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'running'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;worker_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;attempts&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;attempts&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;claims&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;claims&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;lease_expires_at&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt;
 &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
       &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;tinyjobs_jobs&lt;/span&gt;
        &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'queued'&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;run_at&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;queue&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;?&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;priority&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;run_at&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt;
        &lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt;
 &lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;RETURNING&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;It runs inside a &lt;code&gt;BEGIN IMMEDIATE&lt;/code&gt; transaction, which takes SQLite's write lock up front. SQLite allows one writer at a time, and that is usually listed as its weakness. Here it is the feature: the lock puts the claims in a line. A second worker waits its turn (up to a busy timeout, five seconds by default), and when it gets the lock its subquery no longer sees the rows the first one took.&lt;/p&gt;

&lt;p&gt;The file is in WAL mode, so readers never wait for the writer. Your application, a dashboard or a &lt;code&gt;sqlite3&lt;/code&gt; shell can read the queue while workers claim from it.&lt;/p&gt;

&lt;h2&gt;
  
  
  When a worker dies
&lt;/h2&gt;

&lt;p&gt;The claim also writes &lt;code&gt;lease_expires_at&lt;/code&gt;, sixty seconds ahead. The lease is a promise with an expiry date: "this job is mine until then". While the job runs, the worker renews the lease every twenty seconds.&lt;/p&gt;

&lt;p&gt;A dead worker stops renewing. Every worker also runs a small loop, the reaper, every thirty seconds. It looks for rows that are &lt;code&gt;running&lt;/code&gt; with a lease in the past, records that attempt as &lt;code&gt;lost&lt;/code&gt;, and sets the job back to &lt;code&gt;queued&lt;/code&gt;. There is no coordinator and no leader: any worker that is alive does it, and the write lock makes sure only one of them wins each row.&lt;/p&gt;

&lt;p&gt;A graceful stop is kinder. On &lt;code&gt;SIGTERM&lt;/code&gt; the worker stops claiming, gives running jobs time to finish, and hands the ones it can interrupt back to the queue before it exits, so they don't wait for a lease.&lt;/p&gt;

&lt;p&gt;Either way, a job that was interrupted starts again from the beginning. The function's progress lived in the memory of a process that is gone, and the row only says which function to call and with which arguments. So the guarantee is at-least-once: a job is never lost, but it can run twice, and tasks should be safe to repeat.&lt;/p&gt;

&lt;h2&gt;
  
  
  The answer that arrives late
&lt;/h2&gt;

&lt;p&gt;There is a subtler case. Worker A claims a job and then freezes for two minutes: a paused VM, a laptop lid, a very long garbage collection. Its lease expires, the reaper gives the job to worker B, and then A wakes up and tries to record "success".&lt;/p&gt;

&lt;p&gt;Every write of an outcome checks who holds the job:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="n"&gt;tinyjobs_jobs&lt;/span&gt;
   &lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'success'&lt;/span&gt;
 &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'running'&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;worker_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;claims&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;?&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;claims&lt;/code&gt; is a counter that goes up on every claim, so an old attempt can't be mistaken for the current one, even on the same worker. A's write matches zero rows and changes nothing. The job belongs to B now.&lt;/p&gt;

&lt;h2&gt;
  
  
  Does it hold?
&lt;/h2&gt;

&lt;p&gt;I didn't want to trust the reasoning alone, so I ran it for ten hours on a small shared VPS: one SQLite file, three worker processes, 25 jobs per second, a graceful restart every fifteen minutes and a &lt;code&gt;SIGKILL&lt;/code&gt; every twenty.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;897,300 jobs, 24.9/s sustained, zero enqueue failures
68 worker restarts, 29 of them by SIGKILL
nothing lost
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Worker memory went from 37.1 MB in the first hour to 37.3 MB in the last, and the p95 from enqueue to finish stayed around 400 ms. For raw throughput, four worker processes drain about 5,200 no-op jobs per second from one file on a local SSD. The limit is &lt;code&gt;fsync&lt;/code&gt;, not Python.&lt;/p&gt;

&lt;p&gt;You don't have to take my word for it: &lt;code&gt;tinyjobs verify&lt;/code&gt; reruns the experiment on your own machine, kills included. The full numbers are on the &lt;a href="https://tinyjobs.readthedocs.io/en/latest/measured/" rel="noopener noreferrer"&gt;Measured&lt;/a&gt; page.&lt;/p&gt;

&lt;h2&gt;
  
  
  Two things a broker can't give you
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;The queue is a database you can read.&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight shell"&gt;&lt;code&gt;sqlite3 jobs.db &lt;span class="s2"&gt;"SELECT task, status, attempts FROM tinyjobs_jobs WHERE status = 'failed';"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;A job can commit with your own data.&lt;/strong&gt; If the queue lives in the file your application already uses, a job can be enqueued in the same transaction as your rows:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;connection&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;connection&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"INSERT INTO orders (id) VALUES (?)"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;42&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;send_receipt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;with_options&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;tx&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;connection&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;enqueue&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;42&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If the transaction rolls back, the job never existed. If it commits, the job is there. No order without a receipt, and no receipt for an order that failed.&lt;/p&gt;

&lt;h2&gt;
  
  
  Where SQLite stops
&lt;/h2&gt;

&lt;p&gt;It would be dishonest to stop here, so:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;One machine.&lt;/strong&gt; The file has to be on a local disk. SQLite's locking over NFS or SMB is unreliable, so workers on several servers can't share it.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;One writer.&lt;/strong&gt; Thousands of jobs per second is the ceiling, not the starting point.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Workers poll.&lt;/strong&gt; SQLite can't wake a process up, so an idle worker checks the file, backing off to twice a second.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;When you hit one of these, the same tasks run on Postgres or Redis by changing the URL:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="n"&gt;app&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nc"&gt;TinyJobs&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"postgresql://app@localhost/shop"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;On Postgres the claim uses &lt;code&gt;FOR UPDATE SKIP LOCKED&lt;/code&gt; and workers are woken with &lt;code&gt;LISTEN/NOTIFY&lt;/code&gt; instead of polling. The &lt;code&gt;tinyjobs move&lt;/code&gt; command carries the jobs still to run from one store to the other.&lt;/p&gt;

&lt;h2&gt;
  
  
  That's the whole trick
&lt;/h2&gt;

&lt;p&gt;A table, one atomic statement, a lease, and a check before every write. Most of the work went into the cases around them: shutdowns, signals, clocks and crashes at the worst possible moment.&lt;/p&gt;

&lt;p&gt;tinyjobs is on &lt;a href="https://github.com/Piergiuseppe/tinyjobs" rel="noopener noreferrer"&gt;GitHub&lt;/a&gt; and &lt;a href="https://pypi.org/project/tinyjobs/" rel="noopener noreferrer"&gt;PyPI&lt;/a&gt;, with no dependencies on SQLite. It can run &lt;a href="https://tinyjobs.readthedocs.io/en/latest/fastapi/" rel="noopener noreferrer"&gt;inside a FastAPI process&lt;/a&gt; and works as a &lt;a href="https://tinyjobs.readthedocs.io/en/latest/django/" rel="noopener noreferrer"&gt;&lt;code&gt;django.tasks&lt;/code&gt; backend&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;If you have run a queue on SQLite, or decided not to, I'd like to hear what you ran into.&lt;/p&gt;

</description>
      <category>python</category>
      <category>sqlite</category>
      <category>django</category>
      <category>fastapi</category>
    </item>
  </channel>
</rss>
