Returning JSON directly from PostgreSQL

I’m creating a lot of JSON APIs using Grape or Ruby on Rails lately. From time to time I’m facing an issue when I need to return a large amount of data in a single response.

The usual way would be to execute query via ActiveRecord and use some library like JBuilder or ActiveModelSerializers to generate JSON response. It’s nice and easy but sometimes too slow :(

In situation like this I’m relying on PostgreSQL to help me out with it’s row_to_json function.

Let’s convert the chart query from my previous post so it can return json that is ready to send to the chart library right away:

with intervals as (
  select start, start + interval '5min' as end
  from generate_series('2015-05-29 9:30', '2015-05-29 16:00', interval '5min') as start
)
select json_agg(row_to_json(ohlc)) from
(
    select distinct
      intervals.start as date,
      min(low) over w as low,
      max(high) over w as high,
      first_value(open) over w as open,
      last_value(close) over w as close,
      sum(volume) over w as volume
    from
      intervals
      join ticker_minute_bars tmb on
        tmb.key = 'AAPL.BATS' and
        tmb.date >= intervals.start and
        tmb.date < intervals.end
    window w as (partition by intervals.start)
    order by intervals.start
) as ohlc

Time: 76.968 ms

The best part is that JSON serialization overhead comparing to the original query is almost invisible!

Thanks!

Adrian Serafin

Adrian Serafin
I'm experienced Ruby developer. I love PostgreSQL, Redis and optimizing performance. I strongly believe in using right tool for the job and short feedback loops. How can I help you?

group_concat as an alternative for array_agg

For quite some time I have been hardcore [PostgreSQL](https://www.postgresql.org) user. I like to leveragedatabase capabilities when poss...… Continue reading

React router on Amazon S3

Published on March 23, 2016