Using Postgres to create JSON response for you

We are trying to build a search for our product which needs to fast, we were using Django and Postgres. So, we decide to go with raw SQL query as Django ORM can be slow for some tasks. Also, we need to have response time below 200ms for worst case and below 100ms for average case.

Iteration 1

Our query was complex using multiple union, distinct and similarity query which use pg_trgm extension for Postgres. We implemented everything and created our api and made request in which we got around 1089ms of response time that too in local with limit of 10. That was bad very bad, we were nowhere near our target goal.

After analyzing our SQL query we found that our SQL was taking around 600ms to 800ms. If you don't know you can check time it will require to execute in using following:

explain analyze select * from employees;

So we decided to remove union and distinct and run each query separately and we found that each queries were not taking that much time, average was around 0.5ms to 2 ms (thanks to our thought out schema design).

So in total we got around 6 to 10ms for SQL and we considered it as improvement but still response time from api was not upto the mark, we were around 800ms to 900ms.

Iteration 2

This time we looked at our python code which was modifying SQL response to required JSON response and we found we are looping over a huge list of dictionaries to create our response.

If we can have something where python will just send the response and not have to loop over that chunk of data.

Postgres came to rescue, postgres have in-built function to convert row to json directly and what surprised us was it was not that hard.

Let us consider following table

create table employee (
    id auto increment,
    name char(50),
    code text,
    department_name char(25),
    doj date
);

For this table we can create json response in postgres using following way:

select row_to_json(t)  from
(select name, code, department_name from employee)
as t;

This will return json response as follows:

{
    "name": "John Doe",
    "code": "897231143AD",
    "department_name": "sales"
}

Pretty neat, this reduced our api response time to 220ms. Still not good, time to more optimization.

Iteration 3

While developing api we also had to keep in mind that iOS app need to serialize data for consumption which will add another overhead or some milliseconds in time and requirement was to reduced it below 100ms end to end.

We discussed with iOS team and came up with plan to send small responses which improve not only api performance but also app performance and we added hard limit in api what can be limit and offset of request.

After all this we were able to make request - response to 89ms which was 1089ms when we started with this problem thats 1000ms to time saving and which also gave some (11ms) overhead time for frontend to serialize and do its stuffs.

I learned a lot of things while working on this feature about backend, python, database and also mobile apps.

More you know, more you grow.