Connection pooling - Python Connector for Snowflake

Connection pooling

Connecting to a database server typically consists of several time-consuming steps. Connection pooling can significantly improve the performance and scalability of an application by reducing the number of times that new database connections must be opened. This is particularly useful for applications that involve many connect/disconnect operations. Connection pooling uses a cache of database connections, which enables an application to reuse a connection from a pool instead of opening a new connection when future requests to the database are required.

When you close a connection object using the close() method, the connection remains alive and is added to a pool. When a new connection object is created with the connect() method, the module returns an existing connection from the pool if the connection pooler hasn't detected the severed connection and marked it as invalid. A new connection will be established if the pool is empty or doesn't have a valid connection.

To enable connection pooling, set the value of the connection_pool.enabled module attribute to True. Additional options include connection_pool.min_size, connection_pool.max_size, connection_pool.lifetime, and connection_pool.validate. For more information about these attributes, see the connection pool class.

The following example sets the attributes for the default connection pool, which implicitly has the ID 0.

devart.snowflake.connection_pool.min_size = 0
devart.snowflake.connection_pool.max_size = 1000
devart.snowflake.connection_pool.lifetime = 60000
devart.snowflake.connection_pool.validate = True
devart.snowflake.connection_pool.enabled = True

You can define several connection pools with different settings. To define settings for a connection pool with a particular ID, use the syntax connection_pool[pool_id: int], where pool_id is the ID of the pool. You can also pass the PoolId connection string parameter to specify which connection pool will be used for a particular connection.

devart.snowflake.connection_pool[42].max_size = 100
devart.snowflake.connection_pool[42].lifetime = 120000
devart.snowflake.connection_pool.enabled = True
my_connection = devart.snowflake.connect("Domain=your_instance;UserId=your_username;Password=your_password;Database=your_database",PoolId=42)

Database connections belong to the same pool when they have identical parameters in the connection string. Two connections with different connection string parameters will be placed into separate pools with the same identifiers. The connector creates a separate pool when a new connection has the same pool ID as an existing pool but different connection parameters.

The connection_pool.enabled attribute is global. If pooling is enabled, all new connections will be pooled. Pooling can be disabled for a particular connection using the DisablePooling connection string parameter.

my_connection = devart.snowflake.connect("Domain=your_instance;UserId=your_username;Password=your_password;Database=your_database",DisablePooling=True)

Database connections in a pool are validated every 30 seconds to ensure that a broken connection won't be returned from the pool when a connection object is constructed. Invalid connections are destroyed. The connection pooler also validates connections when they are added or released back into the pool (for example, when you call the connection.close() method).

If you set the validate attribute to True, connections will also be validated when they're drawn from the pool. In the event of a network issue, all connections to a database may become broken. Therefore, if a fatal error is detected in one connection from the pool, the pooler will validate all connections in the pool.

The pooler removes a connection from the pool after it's been idle for approximately 4 minutes. If no new connections are added to the pool during this time, it becomes empty to save the resources. If you set the min_size attribute to a non-zero value, the pool won't destroy all idle connections and become empty unless the remaining connections are marked as invalid.

The max_size pool attribute limits the number of connections that can be stored in a pool at the same time. When the maximum number of connections in a pool is reached, all future database connections will be destroyed once the connection object releases them.

You can limit the connection lifetime using the lifetime attribute. When a connection object is closed, and a database connection is returned to the pool, the creation time of the connection is compared with the current time, and the connection is destroyed if that timespan exceeds the lifetime value. This technique serves for load balancing.