Nick,
> I'll try that approach while testing. Is it the case that the sort memory
> is allocated for each connection and becomes unavailable to other processes
> while the connection exists? If so, since I'm using a connection pool, I
> should be able to control total usage precisely. Without a connection pool,
> I could start starving the rest of the system for resources if the number
> of users spiked unexpectedly. Correct?
Wrong, actually. Sort memory is allocated *per sort*, not per connnection or
per query. So a single complex query could easily use 4xsort_mem if it has
several merge joins ... and a pooled connection could use many times sort_mem
depending on activity. Thus connection pooling does not help you with
sort_mem usage at all, unless your pooling mechanism can control the rate at
which queries are fed to the planner.
--
Josh Berkus
Aglio Database Solutions
San Francisco