Hello! It's been a while since I've done one of these so I figured I'd share what I've been working on. As usual, I'm mostly on Hivemind these days, though I'm also working on getting HAF to work on Postgres 18 but I'm waiting on some dependencies (pg_search) to be updated to work with Postgres 18 before I can start testing this. There's also some groundwork needed on Blocktrades' team in setting up new Docker images that work with PG 18 etc.

This month was focused on a major refactoring effort to move the community operations from Python to PostgreSQL, which was successfully completed. There was one performance optimization that didn't work out, but overall it was a productive month.

The bad: One optimization was attempted and merged but then reverted

https://gitlab.syncad.com/hive/hivemind/-/merge_requests/939

I attempted to optimize how we fetch user roles in communities by returning the role directly in process_hive_post_operation instead of fetching it in a separate query. This should have been a straightforward performance win by reducing database roundtrips. However, similar to past issues, this caused problems that only showed up during full sync testing. The optimization had to be reverted. These kinds of performance issues are tricky to catch because they require full sync testing, which is very time consuming. I'll get back to it later and make another attempt at making it work.

The good

SQLized all community operations

https://gitlab.syncad.com/hive/hivemind/-/merge_requests/940

This was the big project for November. We've been slowly moving away from Python implementations into PgSQL in Hivemind, mostly for performance but also as PgSQL is a stricter language than Python (one is typed, the other isn't) so it leaves less room for error. It is harder to write though.

Overall this took a lot of time and testing but we see a noticeable improvement across the board on operation processing speed ranging from 30% to 100% faster. This is good both for new nodes who will be able to sync faster but also API nodes in operation who will need less processing power to manage blocks as they arrive (in reality the processing power when operating live is relatively negligible but it also means you get to run more things in parallel!)

Subscribe performance optimization

https://gitlab.syncad.com/hive/hivemind/-/merge_requests/932

Before the full SQLization work, I also pushed a standalone optimization that made subscribe/unsubscribe operations process twice as fast. This was a nice early win that will yield similar benefits.

Fixed BigINT overflow issue

https://gitlab.syncad.com/hive/hivemind/-/merge_requests/926

Some numeric values on Hive are really big, like really really big. So big that they can exceed the limit for some JSON implementations. This means either an overflow or undefined behavior. This realistically happens very rarely because it takes someone with a LOT of stake (e.g., if someone were to vote with stake that wasn't meant to be used). We chatted about what was the best way to tackle this on the core dev meeting and decided that it was best to switch the fields that may overflow to text so API users can cast to the appropriate variable type for their language.

Updated and refreshed communities doc:

https://gitlab.syncad.com/hive/hivemind/-/merge_requests/919

Ever since Hive account@roadscape made the first design doc, the communities feature has changed a lot, mostly under my development. A lot of things in the doc that were written at the time are no longer true, or just incorrect due to how the project evolved. So it was high time to come back to it and update everything with up-to-date info for developers.

Various bug fixes

Then there's a ton of smaller things that aren't as worthy to mention. I figured I'd put a few here to give you an idea. Ever since we started using Claude Code there's a lot of velocity that goes towards smaller things that were not worth our time but that now are totally within reach (e.g., checking for typos or smaller errors/bugs).

• Removed all MySQL code: https://gitlab.syncad.com/hive/hivemind/-/merge_requests/934
• Fixed forgotten hardcoded test string in validation (minor optimization): https://gitlab.syncad.com/hive/hivemind/-/merge_requests/927
• Fixed a bunch of inaccuracies, bugs in code examples, renamed VSC to Magi + resize error in the devportal: https://gitlab.syncad.com/hive/devportal/-/merge_requests/153, https://gitlab.syncad.com/hive/devportal/-/merge_requests/152, https://gitlab.syncad.com/hive/devportal/-/merge_requests/151
• Updated mesh API stack for 1.28.5 + added checksum validation and error checking: https://gitlab.syncad.com/hive/mesh-api/-/commit/9926ee47433f4781b0e55516005d92f13ce7abf6

Conclusion

I'm really happy with the results of SQLized communities. I've been wanting to do that forever and happy to see it bear fruit. I am currently working on more optimizations and features for Hivemind. I have a bunch of ideas on how a few features that would be nice to Hive for communities to incentivize their members to be more active (leaderboards and events), as well as make moderation more transparent than with just notifications. I'm still working on the HAF for Postgres 18 as well but that's on standby as I said in the intro. Finally, I am currently syncing a node for a small experiment to check how reputation would look like if we didn't have the (arbitrary) requirement that only someone with more reputation than you can remove your reputation.

If you like what I'm doing please consider voting for my proposal and witness :)

https://peakd.com/me/proposals/344
https://peakd.com/me/witnesses