Database request slows after a few previous requests (PostgreSQL)

Question

I am doing "for loop" in which I call another service by rest api endpoint, which sends requests to database to get data and return it to my service.

After 9 requests, 10th request slows a lot - from 1s to 30-40s (it's 10th request in 90% cases. Sometimes it's another). Then for example 11 - 17 request it is going very slow and then next 9 requests are going fast. Then it slows and this happens over and over again.

I realised that problem disappears when I delete from my query "like 'text%'" condition or when I for example: put breakpoint in my for loop -> pass 8 requests -> break connection to database -> recconect to database -> pass next 8 requests -> break connection -> reconnect -> repeate x times

I don't have idea what can be wrong here. Any ideas?

I want to add that I am using sping boot with hiberante and hikari to handle connections to database and for communication between services I use WebClient.

Answer 1

I could speculate on the following places where you can do a check on:

• The LIKE 'text%' condition might be a possible cause for the slowness in performance. Such filter conditions are reliant on efficient indexing.

• If your table is indexed at Postgres side, then likely it could be that your database table is large when the filter is applied. Queries with LIKE clauses can become slow as they require scanning through large amounts of data.

• If there is any caching involved too, there might be an issue there after some number of requests.

Also, on a side note could you elaborate if you are setting the Hikari CP properties which improve the performance (maximum-pool-size). Sometimes reducing that number can also improve performance.