Durable queues, streams, pub/sub, and a cron scheduler – inside your SQLite file

"Idle cost is that one lightweight SELECT per millisecond per database — no page-cache pressure, no writer-lock contention, no kernel file watcher in the mix."

I think (respectfully) the LLM that probably wrote this overshot the mark here because busy-polling a select does not actually sound better to me than a "kernel file watcher".

>honker polls SQLite’s PRAGMA data_version every millisecond

I don't understand why you need busy polling if you're in-process anyway (what you usually have with Sqlite). Just make the event processing thread wait on a condition and wake it up after a transaction commits. In my Sqlite-based pet project, I have a Transaction interface that keeps track of whether an event was actually published inside a transaction, and if it was, the event processing thread is immediately notified inside Commit() to wake up and issue SELECTs. When no events are published, the Sqlite database is not touched and CPU has zero usage. Additionally, on application startup, we need to SELECT in case some events were written to the queue but did not have the chance to be processed because the application restarted or crashed.

On edge this misses Durable Objects + alarms — same primitives, no polling, no Redis to skip in the first place.

> Once real work flows through a SQLite-backed app, you need a queue. The usual answer is “add Redis + Celery.”

Are they joking? SQLite is usually used for single-process (mutliple threads) applications. The proper way to communicate between threads/processes is a ring buffer, where you allocate structs (allocation typically is incrementing a pointer), and futex/eventfd for notifications (+ some spinlocking to avoid going to kernel when the tasks arrive quickly). Why do you need redis for that? If you need persistent tasks, then you can store them in the table, and still use futex for notifications. This polling is inefficient and they should not make it a library which will cause other lazy developers add it to their app.

> honker polls SQLite’s PRAGMA data_version every millisecond. That’s a monotonic counter SQLite increments on every commit from any connection, journal mode, or process — a ~3 µs read for a precise wake signal

That's 3 ms per second = 0.3% CPU time wasted for every waiting thread.

Like Electron, this feels like written by a web developer and not a real programmer.

Why not just use https://github.com/conductor-oss/python-sdk provide durability, distributed and orchestration.

Author here - previously posted here: https://news.ycombinator.com/item?id=47874647

Key difference vs SQL polling is that we’re touching metadata instead of data pages. I have work in process to make this work without any polling (innotify, kqueue, mmap’d shm file check) after the original stat(2) direction proved unreliable if lightweight.

Would love your feedback and or contributions in the repo - still figuring out the end shape.

Prior discussion a few days ago: https://news.ycombinator.com/item?id=47874647

It’s an interesting approach and can be quite fun to use for new projects.

> How it works: honker polls SQLite’s PRAGMA data_version every millisecond. That’s a monotonic counter SQLite increments on every commit from any connection, journal mode, or process — a ~3 µs read for a precise wake signal.

I've implemented something similar in the past, but using inotify. You need to watch the -wal file for IN_MODIFY. To make it work reliably I found I had to run:

    BEGIN IMMEDIATE TRANSACTION; ROLLBACK;

Otherwise the new changes weren't guaranteed to be visible to the process. I'm sure there's a more targetted approach that would work instead - maybe flock on a particular byte in the `-shm` file.

Reminds me of Litestack for Rails. Eventually, it was abandoned because Rails itself started going all out on SQLite.

https://github.com/oldmoe/litestack

At the end it says: "pg-boss and Oban are the Postgres-side gold standards" -- but Oban supports SQLite now too https://github.com/oban-bg/oban

Almost feels like someone is trying to joke about similar postgres application .

To make it look even more absurd . SQLite is not concurrent and you’ll have tons of problems using it practically .

This seems especially appealing in the awkward middle: too serious for in-memory queues, not big enough to justify Kafka-shaped machinery.

Suggestion for the author wind back the polling to once a second when nothing is happening.

I can’t see any benchmarks or performance stats.

I’d like to see messages per second.

Could this work with Turso, the SQLite rust rewrite?