BigBinary Blog

Faster JSON generation using PostgreSQL JSON function

There are various ways to generate JSON in Rails. There is to_json method built in Rails. We can also use jbuilder gem or active model serializer gem which can help us achieve the same.

As the number of records in the database grow, Rails can take a very long time to generate a response. The bottleneck can generally be traced back to JSON generation.

Recently, in one of our applications, we faced this issue where a page was taking too long to load. The load time was critical for us as this was the most visited page on the site. The page was loading a race, its racers and their lap details.

The page was loading fine for short races, with 10-15 racers and each racer having 30-50 laps. But for endurance races, with around 50-80 racers and each racer having around 700-800 laps, we were hitting the bottleneck with load times.

After benchmarking, JSON generation at the backend was found to be the culprit.

Looking out for solutions to fix the problem, we came across PostgreSQL JSON functions.

PostgreSQL 9.2 and above have built in support for generating JSON using functions row_to_json and array_to_json. Let's look into both of them in detail.

row_to_json

row_to_json returns each of the rows as JSON object.

select row_to_json(laps) from laps;

{"id":1,
 "number":1,
 "position":4,
 "time":"628.744",
 "flag_type":"Green"
}
.
.
.

We could use a subquery to only fetch the attributes/columns which we require.

select row_to_json(lap)
from (
  select id, number, position, time, flag_type from laps
) lap;

{"id":1,"number":1,"position":4,"time":"628.744","flag_type":"Green"}
{"id":2,"number":2,"position":4,"time":"614.424","flag_type":"Green"}
.
.
.

array_to_json

To understand array_to_json function, we must first look into array_agg. array_agg is an aggregate function. Aggregate functions compute a single result from a set of input values. sum, min, max are some other examples of aggregate functions. array_agg concatenates all the input values into a PostgreSQL array.

select array_agg(lap)
from (
  select id, number, position, time, flag_type from laps
) lap;

{"(1,1,4,\"628.744\",\"Green\")","(2,2,4,\"614.424\",\"Green\")", ... }

To convert this PostgreSQL array into JSON, we can use the array_to_json function.

select array_to_json(array_agg(lap))
from (
  select id, number, position, time, flag_type from laps
) lap;

[{"id":1,
  "number":1,
  "position":4,
  "time":"628.744",
  "flag_type":"Green"},
  ...]

A more complex example

We can use the above two functions together to generate custom JSON response.

select row_to_json(u)
from (
  select first_name, last_name,
    (
      select array_to_json(array_agg(b))
      from (
        select number, position, time, flag_type
        from laps
        inner join racer_laps
        on laps.id = racer_laps.lap_id
        where racer_laps.racer_id = racers.id
      ) b
    ) as laps
  from racers
  where first_name = 'Jack'
) u;

{
  "first_name": "Jack",
  "last_name": "Altenwerth",
  "laps": [
    {
      "number": 1,
      "position": 4,
      "time": "628.744",
      "flag_type": "Green"
    },
    {
      "number": 2,
      "position": 4,
      "time": "614.424",
      "flag_type": "Green"
    },
    ...
  ]
}

Using the functions in Rails

We can use the above mentioned functions in Rails as shown here.

query = <<~EOQ
select row_to_json(u)
from (
  select first_name, last_name,
    (
      select array_to_json(array_agg(b))
      from (
        select number, position, time, flag_type
        from laps
        inner join racer_laps
        on laps.id = racer_laps.lap_id
        where racer_laps.racer_id = racers.id
      ) b
    ) as laps
  from racers
  where first_name = 'Jack'
) u;
EOQ

generated_json = ActiveRecord::Base.connection.execute(query).values;
puts generated_json

{
  "first_name": "Jack",
  "last_name": "Altenwerth",
  "laps": [
    {
      "number": 1,
      "position": 4,
      "time": "628.744",
      "flag_type": "Green"
    },
    {
      "number": 2,
      "position": 4,
      "time": "614.424",
      "flag_type": "Green"
    },
    ...
  ]
}

Although the code to generate the JSON using the above way is more verbose and less readable compared to other ways to generate JSON in Rails, it is more performant.

Observations

On the racer's page, generating the JSON using the PostgreSQL functions, gave us the following improvements.

For short races (10-15 racers and each racer having 30-50 laps), the average API response time decreased from 40ms to 15ms.

For endurance races (50-80 racers and each racer having around 700-800 laps), the average API response time decreased from 1200ms to 20ms.

Conclusion

Use Rails way of generating JSON as long as you can. If performance starts to be an issue then don't be afraid of using the features made available by the database. In this case we would be trading performance for complexity in code. However sometimes this trade is worth it.