Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> We can go back to Kevin's originally proposed simple feature:
> just allowing the DBA to limit the number of concurrently
> executing queries by role and overall.
Well, that's more sophisticated than what I proposed, but it's an
interesting twist on it.
> This would consist of two parameters,
> max_concurrent_statements and max_statement_wait; the second would
> say how long the connection would wait for a query slot before
> giving up and returning an error.
The timeout is also an embellishment to what I proposed, but another
interesting idea.
> even this solution has two thorny issues to be solved:
>
> a) the "waiting query storm" issue mentioned above
I fleshed out the idea a bit more on the thread titled "Built-in
connection pool", since this would effectively function in a very
similar way to a connection pool. If you look at that proposal, at
most one transaction would be released to execution when one
transaction completed. I'm not seeing anything resembling a "storm"
in that, so you must be envisioning something rather different.
Care to clarify?
> b) pending queries are sitting on idle connections, which could
> easily block higher-priority queries, so managing max_connections
> per role would become much more complex.
That is a good point. The biggest difference between the
functionality of the proposal on the other thread and the connection
pool built in to our application framework is that the latter has a
prioritized FIFO queue, with ten levels of priority. A small query
which is run as you tab between controls in a GUI window runs at a
much higher priority than a query which fills a list with a large
number of rows. This involves both connections reserved for higher
priorities and having higher priority transactions "jump ahead" of
lower priority transactions. This helps the user perception of
performance in the fat-client GUI applications. I suppose if we had
limits by role, we could approach this level of functionality within
PostgreSQL.
On the other hand, our web apps run everything at the same priority,
so there would effectively be *no* performance difference between
what I proposed we build in to PostgreSQL and what our shop
currently puts in front of PostgreSQL for a connection pool.
-Kevin