Pretty cool apps! One thing i'd love to see for both is a historical view of the data - how do things change over time? Where is today relative the past five years for a given place? ten years? etc.
310 karma · joined January 11, 2017
YAAS (yet another agentic substrate)
Electrolite is “Electric-style Shapes, but for SQLite, embedded in an app.” The server defines authorized subsets of rows, the browser asks for one by name, gets an initial snapshot, then long-polls for logical changes captured by SQLite triggers.
It’s very experimental, not on npm yet, and the predicate language is intentionally small. I also wouldn’t describe it as deeply optimized yet. But in a local in-process smoke test, one SQLite write woke 1000 live Shape clients and all materialized the new row in ~100ms, so it looks promising for SQLite-first apps.
Would love your feedback and suggestions, and maybe it will even be useful for you.
Love Fly.
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.
PRAGMA data_version is pretty fast (1500ns with prepared statement) and doesn't have that issue.
Checking the wal-index is sub-nanosecond when mmapped but has slightly different behavior on Windows.
Here's the link to the thread on this, my scripts are all there.
notifs are extremely cheap, either in the old stat(2) mode or the new PRAGMA page_version (see my update on feeback comment). Some other comments mentioned that stat(2) is about 1µs.
Thanks all for your feedback, responses, and discussion. I've done a PR here taking your suggestions into account:
https://github.com/russellromney/honker/pulls/1
The PR implements a three-layer polling architecture: - PRAGMA data_version every 1ms - stat every 100ms - retry connection to handle blips
1. PRAGMA data_version every 1ms replaces stat-based (size, mtime) change detection. This is SQLite's own commit counter: monotonic, immune to clock skew, correctly handles WAL truncation and rolled-back transactions. ~3µs nonblocking query. Credit to ncruces for pointing to this. This is not done for performance but for correctness as it is slightly slower. tuo-lei also pointed out truncation risk, which turned out to be more real than i thought.
Interesting note: I found in testing that the C API's SQLITE_FCNTL_DATA_VERSION does not work cross-connection. So for now honker continues paying the cost of going through the VFS layer which vlovich123 pointed out and now we tradeoff explicitly.
2. Reconnect-on-error: if the data_version query fails (disk blip, NFS hiccup, corrupted connection), honker tries to reconnect and wakes subscribers as a precaution. zbentley pointed me in this direction.
3. stat identity check every 100ms: compares (dev, ino) against startup values to detect file replacement (atomic rename, litestream restore, volume remount). data_version can't catch this because it polls through the open fd, which follows the original inode even after replacement. Credit to zbentley for the file-replacement scenarios.
Again - thanks for the discussion, honker got better because of it and I learned some stuff. See you round
My version is roughly: if execution and verification are getting cheap, but taste, design, intent, and planning are still expensive, then the workflow should focus on how intent gets executed in each change and how it stays intact over time instead of slowly drifting.
I’m more hesitant to formalize all of that into heavy tooling, but there are clear parallels here. I wrote up more of what I mean here: https://russellromney.com/blog/intent-driven-development
I actually looked at fstat, but the "check for deletions" piece, given I'm polling at 1kHZ, was the reason I decided not to use it. Older hardware actually made this a big issue but it's fast enough now I decided it wasn't a problem.
I'll ignore the malicious ones bc [out of scope declaration]. Object paranoia is an artifact of build trama and I respect that lmao.
I've just looked into the device number and system clock issues. I think what i'll end up doing is actually a combo of ncruces's above comment and your feedback: a 1kHZ data_version and a 10HZ stat() with version check. This gets around syscall load, avoid clock issues, avoids the WAL truncation issues that others have mentioned, and is both lighter weight and less bugabooable than my previous design.
Thanks again.