I am building a web application and I using PostgreSQL as the database management system.
The following page details the maximum connections setting:
I read about tuning on the following page
and it mentions
Generally, PostgreSQL on good hardware can support a few hundred connections.
Question
I am confused about what defines a "connection". Many web sites have thousands or more users and it seems if it can only support a few hundred connections at once, then significant horizontal scaling would be required. Are connections very short?…Meaning, if a user loads a page and it queries the database, does it open and close a connection before and after?
Best Answer
In a web application you use a connection pool that establishes a fixed amount of database connections that are then shared between all sessions in your web applications.
Even when a web application has thousands of concurrent users, it might only need as much as 50 database connections (if the connection pooling is done right).
No, you shouldn't do that. Opening and closing connections is a relatively expensive operation. The connection pool will typically keep the connections open - at least for a defined amount of time. Your application takes connections from the pool and returns them to the pool.