DATABASE CONNECTION POOLING is one of the import concepts to know for every Software Developer (both front-end and back-end).

You should know how REST services are optimised using database connection pooling.

Let's discuss it in this thread

Let's say you have a backend service which has an API endpoint to read data from a database table.

This is how a naive developer would implement such an endpoint:

- open db connection
- read the data from database
- close db the connection
- return the response

Now there's nothing wrong with implementing it this way. The end point works perfectly fine and it returns the desired results.

The problem is when you get so many concurrent requests.

Every API call is going to open a database connection and that's not good because...

... generally opening a DB connection, closing a DB connection are generally time intensive operations. So the overall latency of your API increases.

Also you don't have any control over how many connections are created.

Let's say there's a bug in your code and you end up creating too many connections in a loop. This will case the database to go down.

This is not good if the database is shared across multiple teams in your organisation. Every product would be affected in this case.

This is where database connection pooling comes into the picture.

Basically we create a pool of DB connections during service initialisation. Three main parameters involved here are:

1. Max pool size: The number of connection to be created in the pool

2. Connection Timeout: If all the connections in the pool are busy, for how long a client should wait until the next connection becomes available.

3. Idle Timeout: Idle timeout is the timeout after which a database connection is released after being in the idle state.

Now let's discuss how connection pooling will solve the problems we discussed above.

1. Since we're creating db connection during service initialisation, we don't do time intensive operations like connection creation, connection release etc for every API call.

This reduces the API response time drastically and thus improving the overall performance of your service.

2. We have control over how many total database connections are created. Even if there's a bug in the code we will not end up creating a large number of connections.

I hope you now understand what database connection pooling is and why it's important in a REST service.

If you like this thread consider subscribing to my blog where I generally write detailed articles on topics like

- coding
- aws
- freelancing
- SaaS http://sunilkumarc.in