How many queries do you execute in average in a request response cycle

I’m a curious on how many queries you execute in a complete request cycle:

  • Average number of queries
  • Min/Max number of queries

And maybe you’re take on what is “normal” and what should look like “cautious”.

I haven’t spent the time to figure it out, but a small set of queries is going off anywhere up to 1000 times in total during the life of a particular live view and the only performance impact so far is that the trace display is a mess. OTP and Postgres are shrugging it off like it’s no biggie.

I had a look though a recent server-side rendered codebase web app codebase of mine and I estimate the median number of database queries is 4 per page. Network hops can be fairly expensive so I try and reduce the number of queries as much as possible, and it makes performance debugging a lot easier as there’s fewer moving parts + you can use the database’s EXPLAIN feature on queries.

1 Like

Thanks @Ipil for your great explanation. I will like to know if the 4 queries include authentication query?

Yes, 1 for the current user and then more for fetching records from tables. If I need to load data from a table I try to only make a single query for that, and if possible I avoid loading data into the application by moving computation into the database where possible. This also has the nice effect of making some operations atomic (assuming an appropriate database isolation level has been set) when a version that performs multiple queries may be more open to race conditions.

I have other more complex pages which perform more than 4 requests, but 4 seems the most common number. That number isn’t very scientific so take it with a pinch of salt, it was largely me skimming over various controllers in the application :slight_smile:

1 Like