Why is pooling connections in your application a bad thing you ask?
1. It keeps open N connections to the database for each one of your M server processes. This is especially bad in Postgresql, as that means you have N * M idle processes on the database server.
2. It is very easy to improperly reset a database connection before giving it back out of the pool. This leads to two problems.
problem 1: The first is that if code using a db connection has an error and doesn't issue a rollback, the connection will still be in a bad state when it is handed back out of the pool again. On a webserver, this means that you will see strange exceptions where a query failed, and is very hard to debug, because the actual error was on the previous request to use that connection.
problem 2: It is inevitable that somewhere in your code you will not issue a rollback on a connection after starting a transaction. In fact, if you are using Python the default dp-api behavior is to "transactional mode" where a BEGIN is sent immediately before any query on a new connection. It will automatically start the transaction, but it will not COMMIT or ROLLBACK automatically. The connection will be returned to the pool with a transaction started, and the transaction will be kept open, potentially for days or weeks. This can cause an incredible number of headaches, performance problems, and cause your database's on disk size to grow much more quickly.
3. If you use a server like gunicorn, or a library like multiprocessing, open file handles (including sockets) are shared upon fork(). This means that if you aren't very careful to completely remove and recreate all the connections in your connection pool after a fork, you will have multiple processes sharing the same sockets. The processes will then step all over each other and corrupt each others use of the database.
I have seen all of these problems multiple times in production code.
So, what should you do?
If you are using postgres, install PGBouncer on every server. Configure your application to make a new connection to PGBouncer whenever it needs a new connection. The overhead of this is tiny, since you connect to PGBouncer through a filesystem unix socket. PGBouncer keeps open as many connections as it needs to the database server, maintains those connections, and puts them in a good state before giving them to you. PGBouncer will also gradually reduce it's connections to the database server if they are idle and unused long enough.
I'm sure other databases have some similar database proxy.
Taking this advice will give you better performance, far fewer bugs, and hold many less connections to the database server. Additionally, all your database uses can benefit from connection pooling in this way, even shell scripts which call psql.
In application pools of database connections is something that will bite you over and over, just say no.