Simplified
Simplified

L1.1How a query reaches Postgres

YOU

How a query reaches Postgres

Ada opens CoursesCy opens lesson 3Ben answers a questionDee answers a questionSimplified's serverSimplified's serverbuilding itchecking answerssaving themserver Aserver Aserver Bserver Bhalf the workhalf the work
Ada opens Coursesserver Aserver Bdatabase serverusersidnamexp1Ada120Ada's rowthe lessons120?
youyour bank12who you arequestionanswerquestionanswerquestionanswer3hang up
connectionserver Adatabase server12username + passwordAda's row120the lessonslesson rows3hang up
connectionInstagram's serverdatabase server12username + passwordpeople you followtheir namestheir newest photosthe photos3hang up?

Databases · Lesson 1.1

Sharing a Few Connections

How a query reaches Postgres

Simplified
0:00 / 2:02

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.

Ada opens Coursesserver Aserver Bdatabase serverusersidnamexp1Ada120Ada's rowthe lessons120

But how does server A's query actually reach Postgres?

It works a lot like calling your bank.

youyour bank12who you arequestionanswerquestionanswerquestionanswer3hang up

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.

connectionserver Adatabase server12username + passwordAda's row120the lessonslesson rows3hang up

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?

connectionInstagram's serverdatabase server12username + passwordpeople you followtheir namestheir newest photosthe photos3hang up

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.

Example: How a query reaches Postgres

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.

Question 1

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?

logins

Dial once, borrow after

connectionserver Adatabase server12username + passwordAda's XP1205 ms1 ms3hang up
Ada's requestcall + log inquery5 ms1 mseach second200 × 5 ms1 second?
server Adatabase serverusersidnamexp1Ada120Ada's request1borrow2query1 ms1203return20connection pool20 open
no pool200 × 5 ms = 1,000 mseach secondpool of 2020 × 5 ms = 100 ms, onceafter that: 0 new connections

Databases · Lesson 1.2

Sharing a Few Connections

Dial once, borrow after

Simplified
0:00 / 2:04

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.

Ada's requestcall + log inquery5 ms1 ms

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.

Ada's requestcall + log inquery5 ms1 mseach second200 × 5 ms = 1,000 ms1 second

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.

server Adatabase serverusersidnamexp1Ada120Ada's request1borrow2query1 ms1203return20connection pool20 open

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.

no pool200 × 5 ms = 1,000 mseach secondpool of 2020 × 5 ms = 100 ms, onceafter that: 0 new connections

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.

Example: Dial once, borrow after

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.

Question 1

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?

milliseconds

Question 2

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?

milliseconds

Question 3

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?

Question 4

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?

milliseconds

Question 5

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?

Question 6

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?

How many connections is too many?

server A20server B202 × 20 = 40database servermemorymax_connections: 100?
server A20server B20server C20server D20server E20server F20server G20server H20server I20server J2010 × 20 = 200database servermemorymax_connections: 100?

Databases · Lesson 1.3

Sharing a Few Connections

How many connections is too many?

Simplified
0:00 / 1:35

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.

server A20server B202 × 20 = 40database servermemorymax_connections: 100

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.

server A20server B202 × 20 = 40database servermemorymax_connections: 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?

server A20server B20server C20server D20server E20server F20server G20server H20server I20server J2010 × 20 = 200database servermemorymax_connections: 100

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.

Example: How many connections is too many?

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 8×20=1608 \times 20 = 1608×20=160 connections.

Then, Postgres keeps at most 100 open, its max_connections, so 160−100=60160 - 100 = 60160−100=60 are left over.

Therefore, Postgres refuses 60 connections.

Question 1

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?

connections

Question 2

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?

app servers

Question 3

Postgres allows only a fixed number of connections open at once, its max_connections.

Why does it limit them?

Question 4

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?

Question 5

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?

Question 6

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?

Assistant

Highlight lesson text and click Ask AI, or type a question below.