Re: Built-in connection pooling

On 17.04.2018 20:09, Nikolay Samokhvalov wrote:

Understood.

One more question. Have you considered creation of pooling tool as a separate, not built-in tool, but being shipped with Postgres — like psql is shipped in packages usually called “postgresql-client-XX” which makes psql the default tool to work in terminal? I constantly hear opinion from various users, that Postgres needs “default”/official pooling tool.

There were a lot of discussions in hackers and in other mailing lists/forums concerning PostgreSQL and connection pooling.
From  the point of view of many PostgreSQL users which I know myself, lack of standard (built-in?) connection pooling is one of the main drawbacks of PostgreSQL.
Right now  we have pgbouncer which is small, fast and reliable but
- Doesn't allow you to use prepared statements, temporary table and session variables.
- Is single threaded, so becomes bottleneck for large (>100) number of active connections
- Can not be used for load balancing for hot standby replicas

So if you have a lot of active connections, you will have to setup pool of pgbouncers.
There is also pgpool  which supports load balancing, but doesn't perform session pooling. So it has to be used together with pgbouncer.
So to be able to use Postgres in enterprise system you will have to setup very complex pipeline of different tools.

Definitely we need some standard solution for it. As far as I know, Yandex is now working on their own version of external connection pooler which can eliminate single-threaded limitation of pgbouncer. Unfortunately their presentation was not accepted for pgconf (as well as my presentation about built-in connection pooling).

External connection pooler definitely provides more flexibility than built-in connection pooler. It can be installed either at client side, either at server side, either somewhere between them.
Alos it is more reliable, because it changes nothing in Postgres architecture.
But there are still use cases which can not be covered y external connection pooler.
1C company (Russian SAP) at presentation at PgConf.ru 2018 mentioned that lack of internal pooling is the main limitationg factor for replacing MS-SQL with Postgres.
They have a lot of clients which never close connections. And they need persistent session because of wide use of temporary tables.
This is why 1C can not use pgbouncer. We now try to provide to them prototype version of Postgres with builtin connection pool.
If results of such experiments will be successful, we will propose this connection pooler to community (but it available right now, so anybody who want can test it).

вт, 17 апр. 2018 г. в 0:44, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>:

On 13.04.2018 19:07, Nikolay Samokhvalov wrote:

On Fri, Apr 13, 2018 at 2:59 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

Development in built-in connection pooling will be continued in  https://github.com/postgrespro/postgresql.builtin_pool.git
I am not going to send new patches to hackers mailing list any more.

Why? 

Just do not want to spam hackers with a lot of patches.
Also since I received few feedbacks in this thread, I consider that this topic is not so interesting for community.

Please notice that built-in connection pool is conn_pool branch.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company 

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company