3 ms·
My CTO wrote custom PostgreSQL functions to generate all the JSON that our API returns. First time I saw it, I said "Holy crap, that query is two pages long.
by knodi123 12y ago
My CTO wrote custom PostgreSQL functions to generate all the JSON that our API returns. First time I saw it, I said "Holy crap, that query is two pages long. You could just tell Rails to serialize the object in 3 lines of code." He replied "I know. That's how we did it at first. But generating the JSON directly in postgres is about 700x faster."
Eventually we even moved a very complex scoring algorithm from an overnight Hadoop Java process, into Postgres as a compiled extension in C. We used to spend all night calculating those scoring results and caching them in an enormous table. But with the Postgres+C solution, we could calculate and store it in realtime by having Postgres do the hard part.
- pjungwir 12y agoWriting Postgres functions in C is not nearly as scary as it sounds. Here are some stats functions I wrote for a Rails app. The histogram function in particular was the driver, since it was parameterized on bucket size/count/start so uncacheable: https://github.com/pjungwir/aggs_for_arrays/ https://github.com/pjungwir/aggs_for_arrays/
- inopinatus 12y agoYou can skip the web tier entirely and have Pg returning JSON documents via an HTTP endpoint to a rich MVC js browser application that synchs to a local cache. Thus re-inventing Lotus Notes.
- spacemanmatt 12y agoI like OpenRESTy for such endpoints; YMMV. But I refuse. REFUSE. to re-invent Notes.
- lgas 12y agoI haven't used it yet but postgrest looks awesome for this. https://github.com/begriffs/postgrest https://github.com/begriffs/postgrest
- spacemanmatt 12y agoThat looks like a good tool, but have you met nginx before? I sorta view OpenRESTy as a reason to sneak nginx into my stack. :)
- StuffMaster 12y ago>Thus re-inventing Lotus Notes. I would like to veto this with deadly weapons - up to and including nuclear.