Re: psql cli tool and connection pooling

Hi D.J.,

Hope this helps.

Generally, I tend to think of it like there are three separate features provided by connection poolers:
Connection pooling, where you are trying to save connection overhead
Offloading read-only queries to standby’s
Delivering transparent client failover, where you can failover master/standby transparent to client connections

Depending on the solution you choose, it might implement some of these features.
Reading your mail, you are looking for all of them, and are not clear yet which to focus on.
I would bring in a specialist at this moment, but let me try to give you a head start:

I am aware if wo main connection pooling implementations and they all deliver some of these features:
The one built into the application language
Java has a connection pooling mechanisme built in
.NET has one too
There might be others
Libpq has native functionality for transparent client failover (psql is based on libpq)
Connection poolers that mimic a postgres backend,
Pgpool-II is one like that
Pgbouncer is another example
There are others, but let's stick to these two for now.

Since you mention psql, the first implementation will not help you that much (except for transparent client failover).
The second implementation will do what you require. You connect to the pooler, and the pooler connects to postgres.
To psql, connecting to the pooler is transparent. He connects to a port and gets a Postgres connection.
What happens in the background of that, is transparent.

Now, getting into your comments / questions:
> I would love to see a feature on psql cli tool where i can point it to a connection pool provider back end instead of a postgres host.
Great, look at Pgpool-II and PgBouncer. They have overlapping use cases, but depending on the exact situation, might be that one fits better than the other.

> I read that pgpool2 emulates a postgres server, but i've never tried it myself
Yes it does (as do all connection poolers that mimic a postgres backend,)

> I know that some languages provide connection pooling, but i'm relying heavily on psql and its features and i think built-in master/slave connection pooling in psql cli would make my day, and that of any other dirty bash scripters out there.
Sound like you are looking for client connection failover here.
Read this: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING> and specifically '34.1.1.3. Specifying Multiple Hosts’ for the most basic approach to implement this.
>
> Can anyone comment on their experience with pgpool2 in a high activity production environment?
Yes. It works, and depending on your use case, it can even add performance enhancing options.
On the other hand, it tries to fix many things in one tool, and that makes it a complex solution too.
And it adds limitations to the solution too. I have seen a lot of implementations, where people focussed on one thing, but neglected another important thing.
My best advice is: Bring in a specialist for this one.

> Are there other tools or suggestions anyone can point me to?
Well, read the documentation on Pgpool-II: http://www.pgpool.net/mediawiki/index.php/Documentation <http://www.pgpool.net/mediawiki/index.php/Documentation>
And look into PGBouncer too: https://pgbouncer.github.io/faq.html <https://pgbouncer.github.io/faq.html>

> Is there any appetite to support connection pooling natively by either the postmaster or the psql cli or some other device that could be contrib to the source tree?
There is client failover in libpq.
I think t was specifically decided to not fix connection pooling in core, since fixing it in the app layer / external connection poolers keeps Postgres cor code cleaner.
And there are a lot of situations, where you want connection pooler features, so let's keep lean code for that.
Fixing the 'read-only queries’ feature must be done on the client side at all times.

> Does it even matter? Is server version 10 ddos-proof, other than max_connections?
There is no real DDOS proof. In the end, any system can be brought down by a DDOS attack if done under the right circumstances.
And all mitigations for DDOS can be circumvented in one way or another.
This is not specific to Postgres. It is a very generic thing.
You can build a very DDOS-resilient solution with postgres. But that greatly depends on what you want to mitigate and how much effort you want to put into it.

An example is connection exhaustion: You can manage that in a lot of ways
Superuser connections vs normal connections
Limit max connections per user
You can do a lot with customer logon triggers
etc.
But every mitigation needs some thinking, setting some limit, and depending on what you want to do, you might need to code (like a logon trigger).

EnterpriseDB has a lot of experience with this regard. And we have a product that even extents possibilities here.
So I would say, bring in a professional with a lot of experience.
It is probably the best way to build a solution that fits best to the things you mentioned in this question.

Sebastiaan Alexander Mannem
Senior Consultant
Anthony Fokkerweg 1
1059 CM Amsterdam, The Netherlands
T: +31 6 82521560
www.edbpostgres.com

> On 21 Dec 2018, at 20:08, DJ Coertzen <patrolliekaptein(at)gmail(dot)com> wrote:
>
> I would love to see a feature on psql cli tool where i can point it to a connection pool provider back end instead of a postgres host. I read that pgpool2 emulates a postgres server, but i've never tried it myself. I know that some languages provide connection pooling, but i'm relying heavily on psql and its features and i think built-in master/slave connection pooling in psql cli would make my day, and that of any other dirty bash scripters out there.
>
> Can anyone comment on their experience with pgpool2 in a high activity production environment?
> Are there other tools or suggestions anyone can point me to?
> Is there any appetite to support connection pooling natively by either the postmaster or the psql cli or some other device that could be contrib to the source tree?
> Does it even matter? Is server version 10 ddos-proof, other than max_connections?
>
> Thanks.
>