Thanks for reply.
I wonder whether your observations would be referring to responsiveness of Pi-hole's UI, rather then raw SQL statements?
It was specifically SQL statements, because I tested directly via the command line (no interface).
There is not a single delay of responsiveness of Pi-hole's UI.
The hardware is a server computer with 48 CPUs... we have over 300 devices/users on the network at the same time using pi-hole.
I already mentioned above what the problem is with queries. And specifically this query (you wrote template) is used with parameters api_db.php?getGraphData from, until and interval
I think it's not a problem with php because php can handle much more, but the way it is used.
You can always execute queries from command line (without UI) and see that there's problem with queries and large database. If your query executes for more than few seconds. there's problem with queries, logic and optimizations (and maybe database structure), not programming/scripting language itself.
For instance, you mentioned server-side load. It could be solved for example with lazy load in php while user is scrolling. Or even use pagination logic. I think SQLite supports LIMIT offset, rows. But queries need to be optimized (do not use mathematical operations directly in query, index SUMs, add field for CASE instead of use in query, do not use subqueries SELECT specially not with CASE, try with LEFT JOIN, index data so you do not need to have additional query etc.). This will boost performance at least 20 times. So there's not a problem with php/UI but the way it's used.
I will check Pi-hole V6 Beta Testing as you mentioned.