Jose: Hi guys, and welcome back.
In this video we're going to talk about connection pooling.
Right now we have a problem with our app which is that we're
constantly creating, using and then destroying connections
in the create connections function which gets
called in every method pretty much of our models.
But creating connections is expensive and it's slow,
so doing it over and over again is not ideal.
Obviously in our application that is only running
in our computer, It's not a big problem anyway,
but if you have an application that's being used
by a lot of people doing that over and over again
all the time is a problem.
So, instead we have connection pooling.
We create a few connections at once,
and we never destroy them.
Whenever you need one, you grab it
from the pool of connections,
and when you're done you commit it and you put it back
into the pool so that the next time you come back
you can grab it again.
That way, ideally your connection should always have a pool
already created and available for you
to grab whenever you need it.
Connections in the connection pool are stored
in our Python code.
So, all the connection pools that we're gonna be doing,
are Python connection pools.
Essentially what we'll do is basically, make a list where
we add and remove elements when they get put back or used.
But psycopg2 has some connection pool classes
that we can use as well, and we will be using them.
A couple of questions that you may have is how many
connections do you make in the pool when you start it off?
Every connection consumes a bit of RAM
in the database server.
So, if you're running your database server locally,
then that's your computer's RAM.
If you're using a server on the internet,
then that's that server's RAM.
And sometimes due to that as well as other reasons
there are hard limits on the number
of connections you can have.
It maybe 50, maybe a hundred, but there can be limits.
So, you should aim for the lowest number of connections
possible while still having enough for dealing with the
number of users that will interact
with your application at the same time.
So, let's say that you've got 10 users simultaneously
using your application, each one of them consuming
one connection, usually you need to have
probably 10 connections in your connection pool.
Even if sometimes you get 12 users using your application
at the same time, it's not a big deal.
If you have 50 users, though,
you may wanna create more connections.
We'll talk about what happens when you run out
of connections in a moment.
But something else that can happen
is you can run out of server RAM.
If you have too many connections
or you reach the connection limit,
users are gonna be waiting for connections to free up.
That can be a problem.
So, if this happens because you've got too many users
and you need too many connections to be open
at the same time, consider splitting the database
into multiple servers.
in Postgres, we usually do that via sharding,
which is not something we're covering in this course.
It's more of a deployment and DevOps type thing,
but it's something that can be done.
You can run your same database in two servers
so that you get basically double the number of connections.
The other thing that can happen as something that we're more
interested in is you can run out of connections
in your connection pool.
Usually the strategy is you start off your
connection pool with as few connections as possible,
and if you run out, you just create more, basically.
It's a bit slow, but when you create a new one,
you put it back in the pool when you're done,
and essentially that increases the size of the pool.
But many connections can be added over time.
So, do be careful because you may run out of server RAM
if you keep adding connections.
It does mean that having a pool that is too small
isn't the worst problem in the world.
So, let's add this to our app.
We can add connection pooling with this psycopg2 module.
So, let's go ahead and do that in the next video.
Thanks for watching, and I'll see you there.