Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

github.com

Post reply on HN
Print view View original

61–70 of 95 posts

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#61

Post by xenadu02 »

russellthehippo wrote: post #39
vlovich123 wrote: post #19

Earlier quoted context omitted.

Filesystem stuff tends to be slower than average syscalls because of all the locks and complicated traversals needed. If this is using stat instead of fstat then it’s also going through the VFS layer - repeated calls likely go through the cache fast path for path resolution but accessing the stat structure. There’s also hidden costs in that number like atomic accesses that need to acquire cache line locks that are go…

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.

If you're interested you can use kqueue on FreeBSD and Darwin to watch the inode for changes. Faster than a syscall, especially if all you need is a wakeup when it changes.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#62

Post by xenadu02 »

russellthehippo wrote: post #45
Retr0id wrote: post #44

Earlier quoted context omitted.

I don't believe this to be true.

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.

I have no idea why they aren't using kqueue but that works on macOS and FreeBSD. It has for years.

You want EVFILT_VNODE with NOTE_WRITE. That's hooked up to VNOP_WRITE in the kernel, the call made to the relevant filesystem to actually perform the write.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#63

Post by vlovich123 »

Polizeiposaune wrote: post #31
vlovich123 wrote: post #20

Earlier quoted context omitted.

Small correction on ambiguous wording - syscalls do not evict all your stuff from CPU caches. It just has to page in whatever is needed for kernel code/data accessed by the call, but that’s no different from if it was done in process as a normal function call.

Depending on implementation details of your CPU and OS, the syscall path may need to flush various auxillary caches (like one or more TLBs) to prevent speculation attacks, which may put additional "drag" on your program after syscall return.

Correct but you’d also still have that drag just from the kernel dirtying those caches in the first place.

But I was clarifying because the wording could be taken as data/instruction cache and there generally isn’t a full flush of that just to enter/leave kernel.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#65

Post by vlovich123 »

russellthehippo wrote: post #39
vlovich123 wrote: post #19

Earlier quoted context omitted.

Filesystem stuff tends to be slower than average syscalls because of all the locks and complicated traversals needed. If this is using stat instead of fstat then it’s also going through the VFS layer - repeated calls likely go through the cache fast path for path resolution but accessing the stat structure. There’s also hidden costs in that number like atomic accesses that need to acquire cache line locks that are go…

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.

“Beat” is all relative. It depends on load and how frequently you’re doing it, but generally yes. But if you’re doing io_uring, you may as well use inotify because you’re in the platform specific API anyway as that’s the biggest win because you’re moving from polling to change detection which is less overhead and lower latency. Inotify can be accessed by io_uring and there may even be cross-platform libraries for your language that give you a consistent file watcher interface (although probably not optimally over io_uring). Whether it’s actually worth it is hard as I don’t know what problem you’re trying to solve, but the super lowest overhead looks like inotify+iouring (it also has the lowest latency)

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#66

Post by russellthehippo »

zbentley wrote: post #60

Very neat! I like this a lot, nice work. After peeking the source, a few possible areas of improvement: - You can use `fstat` and keep a file handle around, likely further improving performance (well, reducing the performance hit to other users of the filesystem by not resolving vfs nodes). If you do this, you'll have to check for file deletions. - If you do stick with stat(2), it might be a good idea to track the in…

Wow, thanks for the great feedback.

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.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#67

Post by russellthehippo »

Retr0id wrote: post #48
russellthehippo wrote: post #45

Earlier quoted context omitted.

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.

I believe you are mistaken. If you are referring to the comment from ArielTM, that's an LLM bot regurgitating your readme.

If this has been fixed somewhere or there is a better alternative I'd love to use that over polling. Current plan is to move to polling data version for speed + occasional stat for safety. Getting rid of polling was my original goal but i compromised with syscalls.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#68

Post by psadri »

ncruces wrote: post #23
psadri wrote: post #22

Earlier quoted context omitted.

For one it seems to be deprecated.

It's not.

You are correct. I apologize. I seemed to have read the next pragma’s depreciation notice!

Aside from this - SQLite has tons of cool features, like the session extension.

Re: Show HN: Honker – Postgres NOTIFY/LISTEN Semantics for SQLite

#70

Post by russellthehippo »

infogulch wrote: post #29
ncruces wrote: post #21

Earlier quoted context omitted.

Probably missing something, why is `stat(2)` better than: `PRAGMA data_version`? https://sqlite.org/pragma.html#pragma_data_version Or for a C API that's even better, `SQLITE_FCNTL_DATA_VERSION`: https://sqlite.org/c3ref/c_fcntl_begin_atomic_write.html#sql...

Yeah the C API seems like a perfect fit for this use-case: > [SQLITE_FCNTL_DATA_VERSION] is the only mechanism to detect changes that happen either internally or externally and that are associated with a particular attached database. Another user itt says the stat(2) approach takes less than 1 μs per call on their hardware. I wonder how these approaches compare across compatibility & performance metrics.

I just tested this out. PRAGMA data_version uses a shared counter that any connection can use while the C API appears to use a per-connection counter that does not see other connections' commits.
Post reply on HN