Databases · Lesson 1.1
How a query reaches Postgres
Simplified runs two app servers side by side: server A and server B.
Each learner's request goes to one of them, so each does about half the work.
When Ada opens Courses on Simplified, server A builds that page for her.
To build it, server A asks the database server for Ada's row, and the lessons.
That's the computer that runs Postgres, for server A and server B alike.
Postgres finds Ada's row, with her 120 XP, and sends it back to server A.
But how does server A's query actually reach Postgres?
It works a lot like calling your bank.
First, you call, and say who you are.
The bank checks it's really you, before it answers anything.
Then you ask a question, and get an answer, on the same line.
Then another question, and another, without calling again.
When you're done, you hang up.
Server A talks to the database server the same way: it logs in, then asks.
That open, logged-in line is called a connection.
A connection is a logged-in line between an app server and the database server, opened once and used for many queries.
First, server A calls the database server, with a username and a password.
Postgres checks them, so only Simplified's own servers can ask for its rows.
Then the connection stays open.
Server A asks for Ada's row, and her 120 XP comes back over it.
Then it asks for the lessons, over the same connection, without calling again.
When it's done, server A hangs up.
Now how might Instagram's server load your feed?
Say Instagram's server keeps its data in Postgres too.
It calls its database server, and logs in once, with a username and a password.
Then it asks for the people you follow, over that connection.
Then it asks for their newest photos, over the same connection.
Then it hangs up: two queries, and one login.
So a connection logs in once, then carries query after query until it hangs up.
When Ada opens her dashboard on Simplified, server A opens one connection to the database server. Over it, server A sends 3 queries: one for Ada's lessons, one for her XP, and one for her last answer. Then it hangs up.
How many times does server A log in?
Explanation
First, server A logs in once, when it calls the database server and opens the connection.
Then, all 3 queries go over that same open connection, so none of them logs in again.
Therefore, server A logs in 1 time.
Ada opens Courses on Simplified. Server A opens a connection to the database server, sends 2 queries over it, and hangs up. Then Ben opens Courses, and server A opens a new connection, sends 2 queries over it, and hangs up.
How many times does server A log in, in all?
Databases · Lesson 1.2
Dial once, borrow after
So server A opens a connection, and logs in, before it sends any query.
Opening it is like dialing your bank: the call, then the login.
That takes a few messages back and forth: say about 5 milliseconds.
And a simple query, like asking for Ada's XP, takes about 1 millisecond.
So far, server A opens a new connection for every request, and hangs up after it.
Say Ada's request needs just one query, for her XP.
Server A spends 5 milliseconds dialing, then 1 asking.
So most of her request is spent dialing, not asking.
And server A gets about 200 requests a second, from learners like Ada.
200 requests, times 5 milliseconds each, makes 1,000 milliseconds.
Added up across its requests, that's 1 whole second, every second, spent just opening connections.
So how can server A stop dialing for every request?
But a connection can carry query after query, so it doesn't have to hang up when a request is done.
So server A keeps a few connections open, and shares them between its requests.
Say it opens 20 when it starts, and logs in on each one.
Ada's request borrows one of the 20.
It's already open and logged in, so there's no dialing.
It sends its query for her XP, 1 millisecond, and gets 120 back.
Then it returns the connection, still open, for the next request.
These shared connections are called a connection pool.
A connection pool is a few open connections an app server keeps, and lends to its requests.
So the 5 milliseconds are paid once per connection, not once per request.
Without a pool, dialing takes 1,000 milliseconds of every second.
With a pool of 20, server A dials 20 times when it starts.
20 times 5 milliseconds is 100 milliseconds, and it's paid once.
After that, its 200 requests a second open no new connections at all.
So a connection pool pays the dialing once per connection, and every request after just borrows.
Server A keeps a connection pool of 20. Say it serves 1,000 requests, and each one borrows a connection, sends its queries, and returns it.
How many connections does server A open?
Explanation
First, server A opens its 20 connections once, when it starts.
Then, each of the 1,000 requests borrows one of those 20 and returns it, still open, so none opens a new one.
Therefore, server A opens 20 connections.
Say server B opens a new connection for every request, and hangs up after it. Opening a connection takes about 5 milliseconds. Server B gets 100 requests a second.
How many milliseconds of each second does server B spend opening connections?
Opening a connection to the database server takes about 5 milliseconds, and a simple query takes about 1 millisecond. A request opens a new connection, sends 2 simple queries over it, then hangs up.
How many milliseconds does the request take, in all?
Server A keeps a connection pool. Ben's request borrows a connection from it, and sends its queries for Ben's progress page.
What happens to the connection once Ben's request is done?
Server B keeps a connection pool of 10, and opening each connection takes about 5 milliseconds. Since it started, it has served 5,000 requests, each borrowing a connection and returning it.
How many milliseconds has server B spent opening connections, in all?
Server B keeps a connection pool. Cy opens Courses, and his request borrows a connection from the pool.
Why does his request skip the 5 milliseconds of dialing?
Instagram's server opens 20 connections to its database when it starts, and keeps them open. Each request borrows one, sends its queries, and returns it, still open, for the next request.
What are these shared connections called?
Databases · Lesson 1.3
How many connections is too many?
So server A keeps a connection pool of 20, and so does server B.
That's 2 times 20: 40 connections open on the database server at once.
And for each one, Postgres keeps some memory set aside, as long as it's open.
The database server's memory only holds so much.
But how many connections can one database server keep open at once?
So Postgres allows only a fixed number of connections at once.
That number is called max connections.
Max connections is the most connections Postgres keeps open at once: 100, unless someone changes it.
Server A and server B use 40, well under 100.
Now say a Forbes article brings a rush of new learners.
So Simplified runs 10 app servers, server A to server J, to share the work.
But can all 10 open their pools of 20?
10 servers times 20 connections makes 200, but the limit is 100.
Say servers A to E open their pools first: 5 times 20 is 100.
Then server F calls, and Postgres refuses, with an error: too many connections.
It's like calling your bank when every line is busy: you don't get through.
So servers F to J can't open their pools, and their requests that need the database fail.
So Simplified could give each server a smaller pool: 10 pools of 10 make 100.
Or it could raise the limit, if the database server has the memory for more.
So the limit matters whenever you add servers: servers times pool size must not go over max connections.
After a Forbes article, say Simplified runs 8 app servers, each with a connection pool of 20. Postgres's max_connections is 100.
How many connections does Postgres refuse?
Explanation
First, the 8 app servers ask for connections.
Then, Postgres keeps at most 100 open, its max_connections, so are left over.
Therefore, Postgres refuses 60 connections.
Simplified runs 4 app servers, each with a connection pool of 20, all open on one database server. Postgres's max_connections is 100.
How many more connections can Postgres open before it starts refusing them?
Each of Simplified's app servers keeps a connection pool of 20, and Postgres's max_connections is 100.
What is the most app servers that can open their whole pools on one database server?
Postgres allows only a fixed number of connections open at once, its max_connections.
Why does it limit them?
Postgres's max_connections is 100, and 100 connections are already open on the database server. Then Simplified's server F calls to open one more.
What happens?
Postgres keeps at most a fixed number of connections open at once, 100 unless someone changes it, and refuses any more.
What is that number called?
Postgres's max_connections is 100. Simplified runs server A and server B, and raises each one's connection pool from 20 to 40.
Can both pools open?