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(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)ru>>:
>
>
>
> On 13.04.2018 19:07, Nikolay Samokhvalov wrote:
>> On Fri, Apr 13, 2018 at 2:59 AM, Konstantin Knizhnik
>> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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