Benchmarking hivemind API calls after switching to postgREST server

I'll make a full report on our recent work soon, probably next week, but in the meantime I wanted to share the performance improvement we're seeing after switching hivemind from the python based server to postgREST.

We've had several devs working on this task for a few months now; it was a big job as there was a lot of python code in the "server" portion of hivemind that needed to be replaced by SQL code in order for us to switch to using postgREST as hivemind's web server.

But the performance improvements seem well worth it: of the API calls we've benchmarked, we're generally seeing speedups between 3-4x faster on average. We had a few regressions where we still need to tweak the SQL code to get optimal performance (e.g. get_discussions_by_created, get_following), but for our very first benchmark of the old and new code, I'm quite pleased.

Here's the old/new benchmark data below. The median column shows the median time for the API call to complete and is probably one of the most useful figures of merit for comparing the two servers.

Generally speaking, I think the most noticeable speedup will be for "quick" API calls, due to the reduced fixed-overhead latency of postgREST. But we can see cases where just the replacement of python code with equivalently-functioning SQL has resulted in good speedups as well.

Old Python-based Server:

New PostgREST Server:

Next up, production testing

Note these are results are from a synthetic benchmark we've created, not from real world traffic testing.

After we fix the API calls with performance regressions, we'll setup a server with the optimized code and direct a copy of production traffic to it as a final test.