max_connections is a frequent point of contention between users and
developers. Users want to set it high so they don't have to deal with
Yet More Software (pgpool or pgBouncer); PG developers freak out because
backends are pretty heavyweight, there's some very hot code that's
sensitive to the size of ProcArray, lock contention, etc.
One solution to this would be to segregate connection handling from
actual backends, somewhere along the lines of separating the main loop
from the switch() that handles libpq commands. Benefits:
- External connections become very cheap
- Authentication is not an issue (unlike with external poolers)
- This is similar to what's necessary for some of the "Async
Transaction" scenarios being discussed
- This is somewhat related to parallel query processes, though obviously
those need a lot of extra shared state
- This could pave the way for transaction-controlling stored procedures
(though certainly extra work would need to be done)
Downsides:
- Would presumably require at least one new parent process for spawning
either backends or connection handlers.
- Lots of changes necessary to untangle backend and connection handling
in all the code above PostgresMain (as well as some of the code in
PostgresMain itself)
- Need a good way to pass errors back to the connection handler; it
might be best not to put them in the same "pipe" as query results.
- Similarly, need a way to handle notifications... though maybe those
don't need a full backend.
IMHO, the authentication issues alone make this very attractive from a
user standpoint. There's no good way for an external pooler to use
Postgres credentials for authentication, and even if they could there's
still a problem of the pool being able to switch to a non-privileged
user and back again.
To be clear, I won't be able to work on this myself (unless someone
steps up to sponsor it). But I wanted to put the idea out there because
of the potential overlap with some of the other features.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)