Nice, this speedup is more than I would have expected. I really enjoy working with postgREST and the many benefits it brings - performance, security, simplification, flexibility.
Do you plan to use the postgREST native filtering via URI params? It can save a tremendous amount of work since each endpoint can have arbitrary filtering without needing to manually write-up the validation and logic for each parameter. I'm sure you already know this. But if you decide to use it, you have to follow particular steps in order for your function to be inlined and to have good performance: check this Q&A with the postgREST devs.
Regardless of whether you go for the filtering or not, since you are using plpgsql functions for the endpoints (just like I was), you can get substantial performance improvements just from switching to pure SQL and making the functions STABLE.
I wasn't aware that such filters could be used on anything except exposed tables and views, so it was interesting to learn it could be applied to functions, thanks for the link.
We'll experiment more with such features moving forward as we move away from the json RPC API to the new REST-based API. We haven't even started creating a REST API for hivemind yet, because first we wanted to replace the python server to postgREST.
Well, then maybe this is a good opportunity to share some experience I've had creating a REST API with postgREST, in case any of it is useful. Here are some endpoints for Hive Open Polls:
And so on. None of this needed to be manually coded in the WHERE or ORDER BY.
If you look at the response, you will see there are nested fields. Objects and arrays can be nested however one wishes - you can look at the polls repo if you want to see how I've built them as json.
Polls are just regular posts/comments with some json metadata, so hopefully this is all pretty generic.
This approach should also really enhance security since you don't need to handle any arguments yourself, you can let postgREST do all of it for you. (Of course, if for any reason you go for dynamic SQL, then that's a different matter - I follow this post's recommendations). If for any reason what is offered by the postgREST filtering is not sufficient, you can also have manual arguments. The two can be combined for the same endpoint, no problem. So a single API call can pass your manual argument(s) and on top of it add any of the other filters provided natively by postgREST.