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.

1
2select row_to_json(laps) from laps;
3
4{"id":1,
5 "number":1,
6 "position":4,
7 "time":"628.744",
8 "flag_type":"Green"
9}
10.
11.
12.

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

1select row_to_json(lap)
2from (
3  select id, number, position, time, flag_type from laps
4) lap;
5
6{"id":1,"number":1,"position":4,"time":"628.744","flag_type":"Green"}
7{"id":2,"number":2,"position":4,"time":"614.424","flag_type":"Green"}
8.
9.
10.

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.

1select array_agg(lap)
2from (
3  select id, number, position, time, flag_type from laps
4) lap;
5
6{"(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.

1select array_to_json(array_agg(lap))
2from (
3  select id, number, position, time, flag_type from laps
4) lap;
5
6[{"id":1,
7  "number":1,
8  "position":4,
9  "time":"628.744",
10  "flag_type":"Green"},
11  ...]

A more complex example

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

1select row_to_json(u)
2from (
3  select first_name, last_name,
4    (
5      select array_to_json(array_agg(b))
6      from (
7        select number, position, time, flag_type
8        from laps
9        inner join racer_laps
10        on laps.id = racer_laps.lap_id
11        where racer_laps.racer_id = racers.id
12      ) b
13    ) as laps
14  from racers
15  where first_name = 'Jack'
16) u;
17
18{
19  "first_name": "Jack",
20  "last_name": "Altenwerth",
21  "laps": [
22    {
23      "number": 1,
24      "position": 4,
25      "time": "628.744",
26      "flag_type": "Green"
27    },
28    {
29      "number": 2,
30      "position": 4,
31      "time": "614.424",
32      "flag_type": "Green"
33    },
34    ...
35  ]
36}

Using the functions in Rails

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

1query = <<~EOQ
2select row_to_json(u)
3from (
4  select first_name, last_name,
5    (
6      select array_to_json(array_agg(b))
7      from (
8        select number, position, time, flag_type
9        from laps
10        inner join racer_laps
11        on laps.id = racer_laps.lap_id
12        where racer_laps.racer_id = racers.id
13      ) b
14    ) as laps
15  from racers
16  where first_name = 'Jack'
17) u;
18EOQ
19
20generated_json = ActiveRecord::Base.connection.execute(query).values;
21puts generated_json
22
23{
24  "first_name": "Jack",
25  "last_name": "Altenwerth",
26  "laps": [
27    {
28      "number": 1,
29      "position": 4,
30      "time": "628.744",
31      "flag_type": "Green"
32    },
33    {
34      "number": 2,
35      "position": 4,
36      "time": "614.424",
37      "flag_type": "Green"
38    },
39    ...
40  ]
41}

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.