Connection Pooling : Optimizing Database Access in Java Spring Boot.

Before going to connection polling, it is important to know what is a database connection?

When your Java Spring Boot application interacts with a database, it needs to establish a connection to communicate with the database. A connection is a network communication link that allows the application to send SQL queries to the database and retrieve results.

Typical Database Connection

In a simple setup.

• Open a connection to the database when a query is needed.
• Send a query wait for the result.
• Close the connection after completing the task.

        @Override
	public List<Users> listOfUsers() {

		Connection con = null;
                PreparedStatement ps = null;
                String sql = null;
                ResultSet rs = null;
                Users jm = new Users();
                List<Users> li = new ArrayList<Users>();

        try{

            con = dataSource.getConnection();  //start the connection

            sql = "select id,name,email from users";  //write the query

            ps = con.prepareStatement(sql); //prepare the statement

            rs = ps.executeQuery(); //execute the query and get result set

            while(rs.next()){

                us = new Users( 
                		
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("email")
                );
                li.add(us);
            }
            ps.close(); //close the prepare statement
            rs.close(); //close the record set
        }
        catch (SQLException e){
            e.printStackTrace();
        }
        finally {
            if(con != null){
                try{
                    con.close(); //close the connection
                }
                catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return li;
	}        

This process is repeated every time the application needs to interact with the database. While this sounds straightforward, there’s a significant problem. Creating and closing database connections is expensive.

Problems with Typical Connections

• Time-Consuming: Establishing a new connection for every request takes time. It involves network latency, handshaking, and resource allocation.
• High Resource Usage: Opening and closing connections repeatedly can consume a lot of resources on both the database and the application server.
• Scalability Issues: In high-traffic applications, where hundreds or thousands of requests come in, the overhead of managing database connections can degrade performance significantly.

Now let us see what is connection polling and how it solves the problem?

Connection pooling is a technique that solves the problem of repeatedly opening and closing database connections by reusing connections. Instead of creating a new connection for each database request, a pool of pre-established connections is maintained. When the application needs to access the database, it borrows a connection from the pool, uses it, and then returns it back to the pool rather than closing it.

How Connection Pooling Works?

• Pool Creation: A pool of database connections is created when the application starts up. These connections are open and ready to be used.
• Borrowing a Connection: When the application needs to execute a query, it borrows one of the open connections from the pool.
• Returning a Connection: After the query is executed, the connection is not closed but returned to the pool, making it available for the next query.
• Pool Management: The connection pool manager takes care of creating new connections if the pool is empty or closing them if they are no longer needed (based on defined limits).

How It Differs from a Typical Connection?

• Efficiency: With typical connections, each request opens and closes a new connection. With connection pooling, the connection is reused, significantly improving efficiency.
• Performance: By avoiding the overhead of constantly creating and closing connections, connection pooling enhances performance, especially in applications that need to handle many database queries.
• Scalability: Pooling allows the application to scale more effectively under heavy traffic, as it can handle more concurrent requests without overloading the database.

Connection Pooling in Spring Boot

In Spring Boot, connection pooling is usually managed by third-party libraries like HikariCP, which is the default connection pool provider. Here’s how Spring Boot manages connection pooling behind the scenes.

• HikariCP or Other Pooling Libraries: When you configure a datasource in Spring Boot, HikariCP (or other connection pooling libraries) is used to create a pool of database connections automatically.
• Optimized Connection Management: HikariCP efficiently manages the pool by adjusting the size of the pool based on the application's needs, monitoring connections for timeout, and reclaiming unused ones.
• Thread-Safe: Connection pools in Spring Boot are thread-safe, meaning multiple threads can request connections from the pool without conflict.

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=Pratyushraj@1234
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=1800000        

In this above code.

• maximum-pool-size defines the maximum number of connections in the pool.
• minimum-idle is the minimum number of idle connections kept in the pool, even if no one is using them.
• idle-timeout and max-lifetime manage how long unused connections are kept before they are closed or refreshed.

But wait a minute, how is it actually works, does it make 10 connection at a given point of time or 5?

When you configure a connection pool (like with HikariCP in Spring Boot), the number of predefined connections (like 10 in the above case) doesn't all get established immediately when you run the application. Instead, there are two key settings you should be aware of

• Maximum Pool Size (maximum-pool-size): This defines the maximum number of connections that can exist in the pool at any given time. For example, if you set this to 10, there can be up to 10 active connections when the application is under heavy load.
• Minimum Idle Connections (minimum-idle): This defines the minimum number of idle connections that are maintained in the pool at all times. These are the connections that are available for immediate use when a query comes in. For example, if you set this to 5, the pool will always try to keep at least 5 connections open and ready to be used.

How It Works

• When the application starts, the connection pool does not immediately create 10 connections (even if maximum-pool-size is set to 10). Instead, it creates a few connections based on your minimum-idle setting (e.g., 5).
• As more database requests come in, and more connections are needed, the pool will dynamically create new connections, up to the maximum defined (e.g., 10).
• If the load decreases, the pool will not keep unnecessary connections open. It will reduce the number of active connections, but never go below the minimum-idle value.

Let's say you configure the pool with these values

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5        

• When the application starts, 5 connections will be created and kept idle, ready for use.
• If traffic increases and more than 5 queries are made simultaneously, the pool will start creating new connections as needed, until it reaches 10 connections (maximum-pool-size).
• If the application load reduces, and some connections become idle, the pool will reduce the number of active connections, but it will always maintain at least 5 idle connections (minimum-idle).

But if my application doesn't need continuous data updates then why should I use it when it costs me 5 connections at any given point of time?

If your application doesn’t need constant access to the database and only interacts with it occasionally, you might wonder why connection pooling (with even 5 idle connections) is necessary. Let's break it down and address why it's usually worth it, and when it might not be.

Why Use Connection Pooling, Even for Occasional Data Access?

Connection Creation is Expensive

-> Opening a database connection involves several steps: network setup, authentication, and resource allocation. These steps take time and resources.

-> Even if your app doesn't access the database frequently, the first access would require creating a connection from scratch if you don't use pooling. Pooling ensures that a connection is readily available when needed, avoiding this delay.

Pooling Minimizes Latency

-> When an app occasionally needs to access the database, the time to establish a connection (especially in high-latency environments) can slow down the user experience. By keeping a few connections open, you avoid delays when users request data.

-> Even with 5 idle connections, the pool can quickly serve any requests without waiting for a new connection to be made.

Managing Traffic Spikes

-> Even if your application doesn't constantly query the database, it's likely that at certain points (such as specific times of the day or during peak usage), multiple requests may come in at once.

-> Connection pooling helps handle these spikes by ensuring that a few connections are available. Otherwise, you'd face performance degradation while multiple connections are being created at once.

Cost of Idle Connections is Low

-> Modern connection pooling libraries like HikariCP are highly optimized. They efficiently manage idle connections, consuming minimal resources.

-> The cost of keeping 5 idle connections open is generally low compared to the overhead of creating connections repeatedly.

-> Database servers are also designed to handle a large number of connections without performance degradation, so having a few idle connections doesn't usually strain the server.

When to Consider Lowering or Disabling Connection Pooling

There are situations where connection pooling might not be necessary or where you can configure it to be more efficient.

Infrequent Database Access

-> If your application only accesses the database very infrequently, or only during certain scheduled tasks, you can reduce the number of idle connections.

-> For example, you can set minimum-idle=1 or even minimum-idle=0 if you really don’t need connections to be constantly open.

Single-Threaded or Small Applications

-> If you have a small app with only one or two users, or it's a single-threaded app, connection pooling might not provide significant benefits. In such cases, you could minimize the pool size to reduce resource consumption

Optimizing for Low Traffic

-> In low-traffic environments, you could configure the pool to be smaller and still get the benefits of pooling without keeping too many connections open.

spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.minimum-idle=1
spring.datasource.hikari.idle-timeout=10000 # 10 seconds        

-> This setup would ensure that only 1 connection remains idle, and if it’s not used within 10 seconds, it will be closed. When the app needs a connection again, it will create one.

Ultimately, it's a balance between performance and resource optimization based on your application's specific needs.