An Interest In:
Web News this Week
- March 20, 2024
- March 19, 2024
- March 18, 2024
- March 17, 2024
- March 16, 2024
- March 15, 2024
- March 14, 2024
pRESTd release 1.1.4 - Query performance improvement
If 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/table2022/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/table2022/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.
Original Link: https://dev.to/prestd/prestd-release-114-query-performance-improvement-25fn
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To