{"id":150928,"date":"2018-11-15T04:14:16","date_gmt":"2018-11-15T04:14:16","guid":{"rendered":"https:\/\/webkul.com\/blog\/?p=150928"},"modified":"2018-11-15T04:14:16","modified_gmt":"2018-11-15T04:14:16","slug":"integrate-pgbouncer-with-odoo","status":"publish","type":"post","link":"https:\/\/webkul.com\/blog\/integrate-pgbouncer-with-odoo\/","title":{"rendered":"Integrate Pgbouncer with Odoo!!"},"content":{"rendered":"

Why do we need a Connection Pooler?<\/span><\/h2>\n

Saturating db connections is always problematic when you need more & can be rectified by increasing the allowed no of db connections considering database constraints at the same time. Performance issues can still persist if db connection creation\/termination time is considerable.\u00a0<\/span><\/p>\n

 <\/p>\n

\n

PostgreSQL Error : – FATAL: sorry, too many clients already<\/strong><\/p>\n<\/blockquote>\n

 <\/p>\n

Generally, the db connection creation & closing operations are expensive in terms of resources & time. Eg. PostgreSQL forks entirely a new thread\/process for a new connection which increases the overall transaction time. The better option is to avoid this irrelevant expense by reusing those already existing\/established db connections. Hence, enhance the performance. This is exactly what a connection pooler does.<\/span><\/p>\n

Those who cannot remember the past are condemned to repeat it \u2013 Dynamic Programming.<\/b><\/i><\/span><\/p>\n

What is a Connection Pooler?<\/span><\/h2>\n

A connection pooler is an intermediate tool, mainly responsible for managing\/handling db connections and provides an abstracted interface to the web servers. It maintains a pool of db connections for future use. It helps you avoid the cost of creating\/initiating db connections as the connections are kept dormant and can used as & when required. As a result, pooler allows you to serve more db requests\/queries than the allowed no of connections and in a bit lesser time too.\u00a0<\/span><\/p>\n

How does it work?<\/span><\/h2>\n

The architecture is pretty straightforward. First of all, the connection pooler would sit between the web-server and database as a catalyst and speeds up the transaction. Only few configuration values need changes at server level. <\/span>E.g. db port,etc.\u00a0\u00a0<\/span><\/p>\n

The web server can perform transactions on db without having to create\/drop any db connection explicitly. Therefore, avoiding the extra overhead. This also means the connection pooler works as a reverse-proxy(likewise). Consequently taking away most of the overhead from database application. Pooler maintains a set of alive db connections. And spins up newer ones only if all existing are exhausted or to maintain a desired count(min_pool_size & reserve_pool_size<\/span>)\u00a0as per the specified configurations.<\/span><\/p>\n

Secondly, the web-server would pick an existing db connection from the pool and would perform the transaction. Once the transaction completes, the connection returns to the pool. Pgbouncer\u2019s internal computations consume additional capacity. But trading off that additional capacity to avoid expense of db connection creation is acceptable.<\/span><\/p>\n

\n

Note: –\u00a0<\/span> Connection pooler doesn\u2019t make db queries execute faster, it rather expedites the process of implementation i.e. queries will hit the db sooner. The query execution time remains same with or without pooler. Query optimisation is entirely a different story.<\/span><\/p>\n<\/blockquote>\n

<\/h2>\n

Pgbouncer<\/span><\/h2>\n

Pgbouncer is a stable, in-production connection pooler for PostgreSQL. PostgreSQL doesn’t realise the presence of PostgreSQL. Pgbouncer can do load handling, connection scheduling\/routing and load balancing.<\/span><\/p>\n

It provides three types of pooling(default to session) methods, described below: –<\/span><\/p>\n

1. Session Pooling<\/strong> :- The client uses connection for the entire duration it stays connected & only returns once disconnected.<\/span><\/p>\n

2. Transaction Pooling<\/strong> :- Client can use connection only for a transaction. Connection is taken away once the transaction is completed. Therefore, session-based features of PostgreSQL can\u2019t be achieved.<\/span><\/p>\n

3. Statement Pooling<\/strong> :- This is transaction pooling with multi-statement transactions disallowed. This is meant to enforce\u00a0“autocommit” mode on client, mostly targeted for PL\/Proxy.<\/span><\/p>\n

Check <\/span>this<\/a> for compatible PostgreSQL features for different Pooling methods.<\/span><\/p>\n

Pgbouncer with Odoo<\/span><\/h2>\n

The blog will present you how you can integrate Pgbouncer(for PostgreSQL) with Odoo. <\/span><\/p>\n

\n

\u00a0This<\/a>\u00a0can help you setup Odoo if you haven’t yet.\u00a0<\/span><\/span><\/p>\n<\/blockquote>\n

How to Setup Pgbouncer with Odoo: –<\/span><\/h1>\n