Postgres Npgsql Connection Pooling

Question

I'd like to better understand Connection Pooling when using Npgsql for Postgres. (http://www.npgsql.org/)

When I use the connection string:

UserID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;Pooling=true;Minimum Pool Size=0;Maximum Pool Size=100;

Where is the "Pooling" going to take place? On my application server or on the database?

When I call connection.Open(), what happens? Is a connection taken from the pool if one exists and if not, a pool is created?

Any other general info around Connection Pooling would be appreciated.

Thanks.

Min and Max Pool Size are now named: "Minimum Pool Size" and "Maximum Pool Size" npgsql.org/doc/connection-string-parameters.html#pooling — mpiliszcz
– mpiliszcz, Commented Feb 26, 2021 at 9:49

Shay Rojansky · Accepted Answer · 2017-05-30 22:01:59Z

73

Npgsql connection pooling is implemented inside your application process - it has nothing to do with PostgreSQL, which is completely unaware of it.

The mechanism is very simple. When you close a pooled connection, instead of physically closing the connection to PostgreSQL the physical connection is kept around idle in memory (in a "pool"). The next time you open a new connection, if its connection string matches a physical connection already present in the pool, that physical connection is reused instead of opening a new physical connection.

Since opening/closing physical connections is an expensive process, this considerably speeds up your application.

answered May 30, 2017 at 22:01

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

29 Comments

Shay Rojansky Over a year ago

@ignacio yes, it's understood within the context of this conversation that "physical connection" refers to a TCP socket connection (which is very expensive to create) as opposed to a pooled connection (which is very lightweight).

Ignacio Over a year ago

@ShayRojansky thank you for your reply and please, let me congratulate you because of the great job you do with Npgsql. If anyone want to go deeper in how a connection is build can check this link postgresql.org/docs/11/protocol-flow.html . That link describes the process from the PostgreSQL server point of view but we can get an idea of why opening a connection is expensive. We have to also add the tasks the operating systems have to do to open, maintain and close the TCP connection (through sockets).

Shay Rojansky Over a year ago

That's right. Each connection string has its own pool, and the MaxPoolSize is part of that connection string, so also part of that pool's setting. Different pools (and connection strings) can have different maximum pool sizes. So you may have DB1 with MaxPoolSize=5, and DB2 with MaxPoolSize=100, coming up to a total have 105 physical connections in total.

Serg046 Over a year ago

What happens to "set" variables postgresql.org/docs/current/sql-set.html? Are they unique or re-used from the pool? Say I open a connection connection1 and set a var to "val1". Then I open 2 other connections, both of them re-use connection1. Then I set "val2" and "val3" for the variable in different instances. Is it safe or there might be a race-condition?

Shay Rojansky Over a year ago

@Serg046 by default, when a physical connection is returned to the pool, a DISCARD ALL command is enqueued which will be executed the next time that connection is used; this resets all parameters (and other connection state) to their defaults, as if a new physical connection was opened. This prevents state leakage through the pool.

|

Collectives™ on Stack Overflow

Postgres Npgsql Connection Pooling

1 Answer 1

29 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

29 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related