DEV Community

loading...

Explain database connection pooling like I'm five

markadell profile image Mark Adel ・1 min read

Discussion

pic
Editor guide
Collapse
nestedsoftware profile image
Nested Software

Let's say a connection pool is like the books at your local library (we'll work on the assumption that the library is really close to your home). If you want to borrow a book, you stroll over, check it out (the connection from the pool is assigned to you), and start reading. The library has multiple copies of popular books (multiple connections in the pool), so other people can still borrow a book even if you've got a copy checked out. When you return the book to the library (return the connection to the pool), it becomes available again for the next person. Let's say a book is so popular that all of the local copies are checked out (no more connections in the pool). In that case, you may order the book from another branch (create a new connection), but this takes much longer. Alternatively, you may just wait until a copy is returned by someone else (wait for the pool to have a free connection again).

As users interact with an application, it's common for the application to connect to a database to get and save data. To speed things up, connection pools can be used by the application to maintain open sessions to the database. When these are not in use, the pool just keeps the connections open. When a user requests a connection, the pool assigns the connection to that user. When the user is done using the connection, it is returned to the pool. If you know how many users typically use your application at the same time, that gives you an idea of how many connections to set up for the pool.

Collapse
techbelle profile image
rachelle palmer

Finally I found a discussion I can contribute to! I love the books example below. If I can add to it a little:

  • now imagine that the library had an event for an author like JK Rowling. Everyone was wild to attend, including you, but you're locked outside. When the event starts and they open the doors, everyone rushes in. there's no priority, just "run for the door and shove your way through", but of course all the HP books are checked out right away. This is how I think of connection "storms" that result after a database node is down - there's a larger demand for connections than there is supply, and when the library is 'unavailable', those connections queue up outside

  • Next parallel... it turns out that just like freedom aint free, neither is your library book. There is some invisible quantity threshold. No one mentions this to you when you check out one book, or two, or ten, but when you breach the invisible limit, the books grow tiny hands with knives and kill you in your sleep. AFAIK databases require some amount of resources for connection management. If you're running an architecture with many thousands of connections (for example, microservices), then you could consume all the memory available to your instance and cause an OOM kill, especially if you dont close connections (return your books).

Collapse
keyboardinterrupt profile image
KeyboardInterrupt

I am no expert in "ELI5" so hopefully this is easy enough:

Setting up an Database connection/session is costly and "slow", There is protocol overhead, authentication, settings and so on.

If an application where to create a new Connection/Session for each Query individually this would have to happen every time, again, and again, so instead when a Session is created it is put into the Pool and reused.

also: en.wikipedia.org/wiki/Connection_pool