Vinicius MeselIf you want to use your database as a data source for an API you are creating, but you are not happy with building it from scratch: pRESTd is for you!
Basically pRESTd is a software that enables you to perform queries through API calls on your whole database system, improving delivery time for your development team.
In this new release (1.1.4), we were able to work on a performance improvement on our querying system. Our base query used to use the following query:
select json_agg(s) from (select * from your_awesome_table) s
Which, on our analysis, was 5x slower than using JSONB aggregation function.
Benchmark
As we can see above, implementing the functionality in Golang gives us better performance on smaller scenarios, but as we grow our data requests, the linearity of the equation is not followed.
That's why we chose to implement with JSONB_AGG as our serializer on the database end, enabling us to save time on development and test writing.
You can see the requests data available in our PR, on the subtitles below.
With JSON_AGG()
2022/11/04 16:54:35 [warning] adapter is not set. Using the default (postgres)
2022/11/04 16:54:35 [warning] command.go:920 You are running prestd in debug mode.
[prestd] listening on 0.0.0.0:80 and serving on /
2022/11/04 16:54:41 [debug] server.go:2084 generated SQL:SELECT json_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 1000 OFFSET(1 - 1) * 1000) s parameters: []
[negroni] 2022-11-04T16:54:41Z | 200 | 5.11392325s | 54.186.223.54 | GET /database/public/table
2022/11/04 16:55:18 [debug] server.go:2084 generated SQL:SELECT json_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 100 OFFSET(1 - 1) * 100) s parameters: []
[negroni] 2022-11-04T16:55:18Z | 200 | 504.78063ms | 54.186.223.54 | GET /database/public/table
With JSONB_AGG()
[prestd] listening on 0.0.0.0:80 and serving on /
2022/11/04 16:59:26 [debug] server.go:2084 generated SQL:SELECT jsonb_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 100 OFFSET(1 - 1) * 100) s parameters: []
[negroni] 2022-11-04T16:59:26Z | 200 | 479.260256ms | 54.186.223.54 | GET /database/public/table
2022/11/04 17:00:05 [debug] server.go:2084 generated SQL:SELECT jsonb_agg(s) FROM (SELECT * FROM "database"."public"."table" LIMIT 1000 OFFSET(1 - 1) * 1000) s parameters: []
[negroni] 2022-11-04T17:00:05Z | 200 | 1.912713761s | 54.186.223.54 | GET /database/public/table
About the benchmark
We used a table with 56 million rows, two indexes (on the primary date and id fields) and both of the API calls (and queries) were paginated.
