Databases · Lesson 1.1
How does your photo reach your friend?
You take a photo, tap Share on Instagram, and put your phone away.
A minute later, your friend opens Instagram, and your photo is in their feed.
But how did your photo get from your phone to theirs?
Well, your phone never sent it to your friend's phone.
It sent it over the internet, to one of Instagram's computers.
That computer kept your photo, and sent it to your friend's phone when their app asked.
That computer is called a server.
A server is a computer that runs an app for everyone who uses it.
Now what happens when your friend likes your photo?
They press the like button, and their phone sends Instagram's server a short message.
It says which photo they liked, and who they are.
The server keeps a count of likes for every photo.
So it adds one to your photo’s count, and the count is now 1.
Later, you open Instagram, and your phone asks the server for your photo.
The server sends it back with its count, and you see 1 like.
And what happens when your friend sends you a DM?
They type "Love this photo!", and their phone sends it to Instagram's server.
The server keeps it until your phone asks for your messages.
Your phone could even be switched off when they send it.
The server still has the message, so it shows up when you turn your phone on.
So two phones never talk directly.
They all talk to a server, which stays on and keeps everything until someone asks for it.
You post a photo of your dog, then switch your phone off. An hour later, your sister opens Instagram and sees the photo.
Where did her phone get the photo from?
Explanation
First, your phone sent the photo to Instagram's server when you posted it.
Then, the server kept it, and sent it to your sister's phone when her app asked.
Your phone was off, so it couldn't have sent anything.
Therefore, her phone got it from Instagram's server.
You post one photo on Instagram. Then 5 friends each open it in their feed.
How many times does Instagram's server send the photo out to a phone?
Databases · Lesson 1.2
What survives a restart?
So your photo went from your phone to Instagram's server, and the server sends it to anyone who asks.
But servers restart often, for an update or after a crash.
So what happens to your photo when the server restarts?
That depends on where the server put it.
Because, like you, a server has two places to keep things.
Think about your desk and your bookshelf.
Your desk holds what you need right now, like tonight's homework.
It's quick to grab, but when you're done, you clear it, and nothing stays.
Your bookshelf holds what you might need later, like last year's notes.
It's slower to find things there, but they stay until you take them out.
A server stores information the same way.
What it needs right now goes on its desk, which is called memory. It's fast, but a restart wipes it.
What it might need later goes on its bookshelf, which is called disk. It's slower, but it survives a restart.
You've seen this on your phone.
Restart it halfway through a game, and the game starts again from its menu.
That's because the game was in memory.
But your camera roll photos are all still there.
That's because they were on disk.
So where should the server keep your photo?
Your friends will want to see your photo later, even after a restart, so it goes on disk.
Your DMs go on disk too, so a restart can't wipe a conversation.
But the feed page the server is building for you right now can sit in memory.
If the server restarts, it just builds the page again, and memory makes that fast.
So memory is the server's desk, where it keeps what it needs right now.
And disk is its bookshelf, where it keeps what it might need later.
Instagram's server restarts at 3 am. Just before, it held a half-built feed page in memory, and your new photo on disk.
After the restart, which one is still there, and why?
Explanation
First, the feed page was in memory, and memory is wiped on a restart.
Then, your photo was on disk, and disk survives a restart.
Therefore, your photo is still there, because it was on disk.
A chat app's server keeps a list of who is online right now in memory, and every message on disk. The server crashes and restarts.
What is lost?
A photo app's server keeps every photo in memory, because memory is faster. Then it restarts for an update.
What happens to the photos people posted?
A running app's server records your 5 km run. You want to see it in your history next month, and the server will restart several times before then.
Where must the server keep your run?
Disk survives a restart.
So why doesn't a server keep everything on disk?
A server has 4 new photos on disk and 3 half-built feed pages in memory. It restarts.
How many of those 7 things are still there?
You're halfway through a level of a game and restart your phone. The game starts again from the menu, but your camera roll photos are all still there.
Where was the half-finished level kept?
Databases · Lesson 1.3
Finding your photo among millions
So the server saves your photo to disk. That way it's still there after a restart.
But Instagram keeps photos for millions of people, plus their likes, comments and DMs.
When your friend opens your profile, the server must find just your photos among them.
So who keeps all that data on disk, and finds yours again?
Search Instagram for a friend's username, and their profile appears almost at once.
Something had to find their account and their photos among millions, instantly.
Think of the bookshelf again, but with millions of people's photos on it, like a huge library.
In a library, a librarian keeps every book in its place, and finds any book you ask for.
The server has a librarian like that too, a program called a database.
A database is a program whose whole job is to keep data on disk and answer questions about it.
Now what happens when you search Spotify for a song?
Spotify has over 100 million songs, and yours starts playing in a moment.
Spotify's server asks its database, which finds that one song among millions.
Like a librarian, the database keeps track of where everything is on disk.
So only the database touches the files on disk. The server just asks it.
You search YouTube for a video you watched last year, and it appears in a moment. YouTube's servers have restarted many times since.
Which program kept it, and found it again?
Explanation
First, YouTube's server needed that one video among millions, even after many restarts.
Then, the program whose job is to keep data on disk and find it is the database.
Therefore, YouTube's database kept it and found it.
Spotify's server hands every new playlist to one program. That program keeps the playlist on disk and sends it back whenever someone opens it.
What is that program?
Instagram's server needs a photo that's kept on disk by its database.
Who opens the file on disk?
A chat app's server keeps every message in a list in its memory, and has no database. It restarts after a crash.
What happens to the messages?
A notes app's server saves every note in its database, which keeps them on disk. The server restarts overnight. In the morning, you open a note you wrote last year.
What happens?
Which of these is a database's job?
You search Gmail and find an email from three years ago in a second. Gmail's servers have restarted many times since it arrived.
Where was the email kept all that time?
Databases · Lesson 1.4
The two jobs of a database
Instagram's database keeps millions of photos on disk.
So what can the server ask the database to do?
Just two things, and they're the same two things a librarian does.
When you post a photo, the server hands it to the database to keep.
This is like handing a librarian a new book, and they put it on the shelf.
This job is called storing.
Storing means the database saves the data the server gives it.
When your friend opens your profile, the server asks the database for your photos.
This is like asking a librarian for a book, and they go fetch it for you.
This job is called retrieving.
Retrieving means the database finds data and sends it back to the server.
So which job is each thing you do on Instagram?
When your friend likes your photo, the server sends the new like count to the database to save. That's storing.
When you search for a username, the server asks the database to find that account. That's retrieving.
When you change your bio, the server sends your new bio to the database to save. That's storing.
So when data goes into the database, that's storing. When data comes back out, that's retrieving.
You change your profile picture, and the server hands the new picture to the database. A minute later, your friend opens your profile and sees it.
Which two jobs did the database do, in order?
Explanation
First, the server gave the database your new picture to save. That's storing.
Then, the server asked the database for it, for your friend. That's retrieving.
Therefore, storing, then retrieving.
You leave a comment on a friend's Instagram post. The server hands your comment to the database to keep.
Which job is the database doing?
You open Instagram and scroll your feed. The server asks the database for your friends' newest posts.
Which job is the database doing?
On Instagram, which of these makes the server ask the database to retrieve data?
On Instagram, you post 2 photos and like 3 of a friend's photos.
How many times does the server ask the database to store something?
A chat app's server hands your message to its database, which stores it on disk. But the server never asks the database for it again.
Will your friend ever see the message?
You post 1 photo on Instagram. Then 3 friends each open your profile and see it.
How many times does the database retrieve your photo?
Databases · Lesson 1.5
How a server asks for your photos
When your friend opens your profile, the server retrieves your photos from the database.
But how does the server tell the database which photos it wants?
It sends the database a message, like "Give me the photos on this profile."
It's like asking a librarian for the book you want.
This message is called a query.
A query is a question or an instruction sent to the database.
The database finds those photos on disk and sends them back.
That answer is called the result, and every query gets one.
What does the server send when you post a photo?
This time, the query is an instruction, "Keep this new photo."
The database stores the photo on disk.
Then it sends back a result saying the photo is kept.
The server tells your phone it worked, and your photo appears on your profile.
Whenever the server wants something from the database, it sends a query, which is either a question or an instruction.
And whatever the query, the database sends back a result.
Your friend opens your profile. The server sends the database "Give me the photos on this profile.", and your 3 photos come back.
What are the 3 photos called?
Explanation
First, the message the server sent is the query.
Then, the answer the database sends back is the result.
Therefore, the 3 photos are the result.
You open your Instagram DMs with Sam. The server sends the database the message "Give me the messages between these two people."
What is that message called?
A server sends its database the message "Delete this comment." It's an instruction, not a question.
Is it a query?
Instagram's server sends its database 5 queries, 3 questions and 2 instructions.
How many results come back?
Leo taps Ana's profile on Instagram. Put these in order.
(1) the database sends back Ana's photos, (2) Leo's phone asks Instagram's server, (3) the server sends the database a query, (4) the server sends the photos to Leo's phone.
Which of these is NOT a query a server sends to its database?
Instagram's server wants a photo that its database keeps on disk.
Why does the server send the database a query, instead of opening the photo's file itself?