Using Connection Pooling
Connection pooling allows you to reuse connections rather than creating a new one every time the data provider needs to establish a connection to the underlying database. The data provider automatically enables connection pooling for your .NET client application.
You can control connection pooling behavior by using connection string options. For example, you can define the number of connection pools, the number of connections in a pool, and the number of seconds before a connection is discarded.
Connection pooling in ADO.NET is not provided by the .NET Framework. It must be implemented in the ADO.NET data provider itself.
Creating a Connection Pool
Each connection pool is associated with a specific connection string. By default, when the first connection with a unique connection string connects to the database, the connection pool is created. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.
The pool remains active as long as any connections remain open, either in the pool or used by an application with a reference to a Connection object that has an open connection.
If a new connection is opened and the connection string does not match an existing pool, a new pool must be created. By using the same connection string, you can enhance the performance and scalability of your application.
In the following C# code fragments, three new PsqlConnection objects are created, but only two connection pools are required to manage them. Note that the first and second connection strings differ only by the values assigned for User ID and Password, and by the value of the Min Pool Size option.
DbProviderFactory Factory = DbProviderFactories.GetFactory("Pervasive.Data.SqlClient");
DbConnection conn1 = Factory.CreateConnection();
conn1.ConnectionString = "Server DSN=DEMODATA;User ID=test;
Password=test;Host=localhost;"conn1.Open();
// Pool A is created.
 
DbConnection conn2 = Factory.CreateConnection();
conn2.ConnectionString = "Server DSN=DEMODATA2;User ID=lucy;
Password=quake;Host=localhost;"
conn2.Open();
// Pool B is created because the connection strings differ.
DbConnection conn3 = Factory.CreateConnection();
conn3.ConnectionString = "Server DSN=DEMODATA;User ID=test;
Password=test;Host=localhost;"
conn3.Open();
// conn3 goes into Pool A with conn1.
Adding Connections to a Pool
A connection pool is created in the process of creating each unique connection string that an application uses. When a pool is created, it is populated with enough connections to satisfy the minimum pool size requirement, set by the Min Pool Size connection string option. If an application is using more connections than Min Pool Size, the data provider allocates additional connections to the pool up to the value of the Max Pool Size connection string option, which sets the maximum number of connections in the pool.
When a Connection object is requested by the application calling the Connection.Open(…) method, the connection is obtained from the pool, if a usable connection is available. A usable connection is defined as a connection that is not currently in use by another valid Connection object, has a matching distributed transaction context (if applicable), and has a valid link to the server.
If the maximum pool size has been reached and no usable connection is available, the request is queued in the data provider. The data provider waits for the value of the Connection Timeout connection string option for a usable connection to return to the application. If this time period expires and no connection has become available, then the data provider returns an error to the application.
You can allow the data provider to create more connections than the specified maximum pool size without affecting the number of connections pooled. This may be useful, for example, to handle occasional spikes in connection requests. By setting the Max Pool Size Behavior connection string option to SoftCap, the number of connections created can exceed the value set for Max Pool Size, but the number of connections pooled does not. When the maximum connections for the pool are in use, the data provider creates a new connection. If a connection is returned and the pool contains idle connections, the pooling mechanism selects a connection to be discarded so that the connection pool never exceeds the Max Pool Size. If Max Pool Size Behavior is set to HardCap, the number of connections created does not exceed the value set for Max Pool Size.
IMPORTANT: Closing the connection using the Close() or Dispose() method of the PsqlConnection object adds or returns the connection to the pool. When the application uses the Close() method, the connection string settings remain as they were before the Open() method was called. If you use the Dispose method to close the connection, the connection string settings are cleared, and the default settings are restored.
Removing Connections from a Pool
A connection is removed from a connection pool when it either exceeds its lifetime as determined by the Load Balance Timeout connection string option, or when a new connection that has a matching connection string is initiated by the application (PsqlConnection.Open() is called).
Before returning a connection from the connection pool to an application, the Pool Manager checks to see if the connection has been closed at the server. If the connection is no longer valid, the Pool Manager discards it, and returns another connection from the pool, if one is available and valid.
You can control the order in which a connection is removed from the connection pool for reuse, based on how frequently or how recently the connection has been used, with the Connection Pool Behavior connection string option. For a balanced use of connections, use the LeastFrequentlyUsed or LeastRecentlyUsed values. Alternatively, for applications that perform better when they use the same connection every time, you can use the MostFrequentlyUsed or MostRecentlyUsed values.
The ClearPool and ClearAllPools methods of the Connection object remove all connections from connection pools. ClearPool clears the connection pool associated with a specific connection. In contrast, ClearAllPools clears all of the connection pools used by the data provider. Connections that are in use when the method is called are discarded when they are closed.
NOTE: By default, if discarding an invalid connection causes the number of connections to drop below the number specified in the Min Pool Size attribute, a new connection will not be created until an application needs one.
Handling Dead Connection in a Pool
What happens when an idle connection loses its physical connection to the database? For example, suppose the database server is rebooted or the network experiences a temporary interruption. An application that attempts to connect using an existing Connection object from a pool could receive errors because the physical connection to the database has been lost.
The PSQL ADO.NET Data Provider handles this situation transparently to the user. The application does not receive any errors on the Connection.Open() attempt because the data provider simply returns a connection from a connection pool. The first time the Connection object is used to execute a SQL statement (for example, through the Execute method on the Command object), the data provider detects that the physical connection to the server has been lost and attempts to reconnect to the server before executing the SQL statement. If the data provider can reconnect to the server, the result of the SQL execution is returned to the application; no errors are returned to the application. The data provider uses the connection failover options, if enabled, when attempting this seamless reconnection. See Using Connection Failover for information about configuring the data provider to connect to a backup server when the primary server is not available.
NOTE: Because the data provider can attempt to reconnect to the database server when executing SQL statements, connection errors can be returned to the application when a statement is executed. If the data provider cannot reconnect to the server (for example, because the server is still down), the execution method throws an error indicating that the reconnect attempt failed, along with specifics about the reason the connection failed.
This technique for handling dead connections in connection pools allows for the maximum performance out of the connection pooling mechanism. Some data providers periodically ping the server with a dummy SQL statement while the connections sit idle. Other data providers ping the server when the application requests the use of the connection from the connection pool. Both of these approaches add round trips to the database server and ultimately slow down the application during normal operation of the application is occurring.
Tracking Connection Pool Performance
The data providers install a set of PerfMon counters that let you tune and debug applications that use the data provider. See PerfMon Support for information about using the PerfMon counters.