HNHacker News
TopNewBestAskShowJobs

russellthehippo

310 karma · joined January 11, 2017

submissionscomments
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
also this is designed for a single machine. 10k listeners on one machine seems like a lot!
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Interesting, thank you for the response and explanation. Honker workers/listerners are holding an open connection anyway. I do trust SQLite guarantees more than cross-platform sys behavior. I will explore the C API angle.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Apologies for not being specific.

The specific thing I'm talking about is this: write events don't fire until the file handle is closed. [1] I didn't validate this myself btw, but my original design was certainly trying to use notify events rather than stat polling. My research (heavily AI assisted of course) led me away from that path as platforms differ in behavior and I wanted to avoid that.

[1] https://github.com/notify-rs/notify/issues/240

russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
It's good for pubsub but not for claim/ack workflow unless you do If-None-Match CAS semantics on a separate filesystem which, actually, yeah that's probably fine. Feels heavy on S3 ops. But! you do save on inter-AZ networking, the Warpstream hypothesis.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
honk
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Actually need to test this. Will report back
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
See comment below - Darwin silently drops same-process notifs. I could change the behavior depending on same vs cross process and platform but I wanted to”just one thing to worry about”. Potentially a good optimization later. Would help reduce syscalls.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Absolutely! That’s the durable pubsub angle for sure.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Yep, definitely still in use. Do yall above have an opinion if the pragma is better than the syscall? What are the trade offs there? Another comment thread mentioned this as well and pointed to io uring. I was thinking that dism spam is worse than syscall spam.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Whoa I really hadn’t considered this. Do a litestream read replica, trigger across machines with S3 as the broker essentially. But you’re still stuck with the litestream sync interval. Maybe interesting for cross server notify?
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
10k listeners is a lot. Thundering herd issue at stat(). SQLite may not be your best choice at this scale.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Oh cool, so using io uring plus pragma data version would actually beat stat on Linux holistically speaking? The stat choice was all about cross platform consistency over inotify speed. But syscalls overwhelm can be real.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
I actually hadn’t thought about it this way. The killer app I was imagining was 1ms reactivity without SQL polling and messaging atomic with business commits, plus “one db” and no daemon.

But this is actually a great main benefit as well.

russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Nope! The extension just functions as a shortcut for raw SQL. Litestream edits the wal file but only like a normal checkpoint. So not too bad. Although I haven’t tested it directly. Probably need to
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
The WAL file sticks around but gets truncated so that counts as an update. Though I don’t have tests for this. Good input, thanks I’ll make sure
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Breaks cross-platform, specifically Macs swallow silently. stat just works
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Great plug very relevant
russellthehippo··on Technical, cognitive, and intent debt
Best thing I've read on hacker news in so long. I relate so hard. Simon Willison's stuff on cognitive debt and "your job is to ship code you have proven to work" have led me to work on a project about Intent-Driven Development, because prior intent always seemed to dim with each set of changes. Might put it together into a real protocol and post here on hacker news sometime.
russellthehippo··on Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite
Hey HN, I built this. Honker adds cross-process NOTIFY/LISTEN to SQLite. You get push-style event delivery with single-digit millisecond latency without a damon/broker, using your existing SQLite file. A lot of pretty high-traffic applications are just Framework+SQLite+Litestream on a VPS now, so I wanted to bring a sixer to the "just use SQLite" party.

SQLite doesn't run a server like Postgres, so the trick is moving the polling source from interval queries on a SQLite connection to a lightweight stat(2) on the WAL file. Many small queries are efficient in SQLite (https://www.sqlite.org/np1queryprob.html) so this isn't really a huge upgrade, but the cross-language result is pretty interesting to me - this is language agnostic as all you do is listen to the WAL file and call SQLite functions.

On top of the store/notify primitives, honker ships ephemeral pub/sub (like pg_notify), durable work queues with retries and dead-letter (like pg-boss/Oban), and event streams with per-consumer offsets. All three are rows in your app's existing .db file and can commit atomically with your business write. This is cool because a rollback drops both.

This used to be called litenotify/joblite but I bought honker.dev as a joke for my gf and I realized that every mq/task/worker have silly names: Oban, pg-boss, Huey, RabbitMQ, Celery, Sidekiq, etc. Thus a silly goose got its name.

Honker waddles the same path as these giants and honks into the same void.

Hopefully it's either useful to you or is amusing. Standard alpha software warnings apply.

russellthehippo··on Claude Opus 4.7
Initial testing today - 4.7 excels at abstractions/implementations of abstractions in ways that often failed in 4.5/4.6. This is a great update, I've had to do a lot of manual spec to ensure consistency between design and implementation recently as projects grow.
russellthehippo··on Show HN: Hippo, biologically inspired memory for AI agents
yep came here to say this. great to hear it's in process.
russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
your comment doesn't read like llm. the other one does (other fruit entirelY?). i commented in the wrong place. i apologize. >>> AbanoubRodolf 2 days ago | root | parent | prev | next [–] [flagged]
russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
I have implemented a full eviction suite for turbolite in https://github.com/russellromney/turbolite/pull/6. I includes connection-time and env-var-set eviction policies (size, count, etc.), user functions for manual eviction at the cache, tree, and even query-level (evict the pages that this query would have downloaded). I hope this definitively answers your question haha
russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
Also I'm curious how you would handle credentials - would you be proxying through your backend? To me turbolite is definitely a backend tool.
russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
Thanks! yeah it's definitely slow, but compared to other options it's quite fast:

- Neon startup is 500ms+

- a Fly Machine with a SQLite database on a volume takes 150ms+ to start up at minimum

- restoring a db from S3 with Litestream can take multiple seconds depending on how long from the last snapshot and how large the database is

- downloading a whole .sqlite file from S3 can be faster than Litestream restore but you still have to download the whole db file.

- One similar option is, you could save each table in a separate database and only download the db files for tables you need for a given query, then ATTACH. But this is an awkward setup even though it's simple

russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
Fantastic comment, thanks for jumping in. I have well-tuned replies here lol given we're working on exactly the same problems!

First: your prefetch solution is precisely what I have in the roadmap for the next level of optimization: B-tree introspection at the page-child level not the table level. I haven't launched it yet as I only realized the potential in the past couple days and I wanted to focus on stability before showing this to folks. I am 100% going to try to use sqlite-prefetch approach in that experiment. I'm curious what kind of results you're seeing.

On write amplification: you're right, re-uploading an entire page group when one page is dirty feels inefficient. This tradeoff is intentional because turbolite believes all PUTs are equal, and it is aimed at optimizing for queries on cold databases with bursty reads, not write-heavy workloads (though writes work too). Checkpoints are ideally infrequent due to upload latency, and the page group sizes are tunable (default 256 64KB pages = 16MB uncompressed, a few MB compressed to S3). For the use case I'm targeting, the read locality wins dominate. That being said, turbolite does let the user choose when to checkpoint locally (durable on disk) vs to S3 (atomic commit in case of disk crash or new compute).

On LTX temporal locality vs page-group spatial locality: agreed, they're different design goals. LTX optimizes for transaction-aware features like PITR. turbolite optimizes for cold query speed from object storage. You could imagine a system that does both (WAL shipping for durability + grouped pages for reads), which is roughly where my roadmap goes. In fact, WAL + immutable page groups on checkpoints gives a much faster restore time than Litestream's snapshots+WAL if only because uploading the entire snapshot on each commit is slow. I'm starting to explore embedded WAL shipping in my walrust project https://github.com/russellromney/walrust.

On frontrun vs reactive: I have very specific benchmarks for this I think you will be intrested in. The tiered-bench binary has a --plan-aware flag that toggles between prefetch schedule (reactive, similar in spirit to your sibling detection) and frontrun (EQP-based). Look at the benchmark/README.md for detail. On 100K rows, local to Tigris, EQP is:

- who-liked (one-to-many JOIN): 4.4x faster cold, 1.4x with interior cached

- scan + filter: 2.9x faster cold

- indexed filter: 2.9x faster cold

- point lookup + JOIN: 1.8x cold

- simple queries (mutual friends): 1.3x, less to gain

The wins are largest on cold index lookups and SCANs where reactive prefetch has to discover the scan through sequential misses. Frontrun knows the full plan upfront and fires everything in parallel. For single-table scans, reactive catches up quickly after 1-2 cache misses, so the gap is smaller.

Finally - I included a query tuner CLI in the repo that lets you compare different prefetch aggression levels for a given query on given data. You may be interested in using this.

russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
Not today, but the architecture isn't fundamentally incompatible. The page grouping and seekable compression would translate well to browser fetch + range GETs. It would need a new storage backend targeting OPFS/fetch instead of S3/disk. I'm happy to discuss more if you'd like to open a Github issue - abstracting the storage API seems like a decent idea in itself.
russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
Happy to hear from you, I'm a delighted customer. In fact I am building a database company on top of Tigris focused on exactly that use case. I'll reach out on X if that works.
russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
Rewrite on every checkpoint. The manifest is small (a few hundred KB for a million pages), so a single PUT is fine. The PUT is the atomic commit point - old page groups become garbage, cleaned up later by gc(). The SQLite engine fetches updated pages from the WAL so turbolite doesn't have to manage manifest updates until checkpoint. If it's not in WAL mode, checkpoints happen on every write so consistency is still there.
russellthehippo··on Show HN: Turbolite – a SQLite VFS serving sub-250ms cold JOIN queries from S3
the comment history speaks for itself
← PreviousPage 2 of 3Next →