PostgreSQL Connection Pooling: Part 1 – Pros & Cons

A really very long time up to now, in a galaxy far distant, ‘threads’ had been a programming novelty infrequently used and barely trusted. In that environment, the first PostgreSQL builders decided forking a course of for each connection to the database is essentially the most safe different. It generally is a shame in case your database crashed, in any case.

Since then, quite a lot of water has flown beneath that bridge, nonetheless the PostgreSQL neighborhood has caught by their distinctive decision. It is troublesome to fault their argument – as a result of it is utterly true that:

Each shopper having its private course of prevents a poorly behaving shopper from crashing the entire database.
On modern Linux packages, the excellence in overhead between forking a course of and making a thread is way lesser than it was.
Moving to a multithreaded construction would require intensive rewrites.

However, in modern web capabilities, consumers are prone to open quite a lot of connections. (*1*) are typically strongly discouraged from holding a database connection whereas completely different operations occur. “Open a connection as late as doable, shut a connection as shortly as doable”. But that causes a problem with PostgreSQL’s construction – forking a course of turns into pricey when transactions are very temporary, as a result of the frequent data dictates they should be. In …

(*1*)Read More on Datafloq