Is it expensive to keep database connections open?

Question

I was in a meeting with a few software developers, and was recommended to close database connections as soon as possible in my application code? Can somebody please tell me what is the harm of keeping a connection open in an application.

I was reading the data from a single table of a database

Answer 1

Think of it like seats on a bus.

As you open connections, you fill up those seats - eventually, the bus is full and can no longer accept passengers (or open more database connections). Any time the bus has to refuse a passenger because it's at capacity, that passenger has to wait for another bus to come by.

By closing your connections when you're done with them, you free up room for more connections - which means more programs that need to interact with the database can do what they need to, without having to wait around for connections to free up. Not closing your connections means the database needs to figure out what to do with all the connections sitting around, which can cause problems if your database isn't closing connections as quickly as you're opening new ones.

This changes when you're using a connection pool (see comments below); in those situations you'll want your pool to handle opening and closing connections for you. If you're not pooling your connections, keeping them open any longer than you need to is wasting resources.