Simplified
Simplified

Chirp: make it fast

SQL → Supabase

The SQL you know, and the same query with Supabase's library.

Every query

const { data, error } = await supabase
  .from("posts")
  .select("body, likes");
if (error) throw new Error(error.message);

The rows come back as data. If Postgres refuses the query, error says why.

From Picking Columns ↗

Picking columns and rows

SELECT body, likes FROM posts;
↓Supabase's library
.from("posts").select("body, likes")
From Picking Columns ↗
SELECT * FROM posts;
↓Supabase's library
.from("posts").select("*")
From Picking Columns ↗
WHERE id = 20
↓Supabase's library
.eq("id", 20)
From Picking Rows ↗
WHERE likes > 100
↓Supabase's library
.gt("likes", 100)
From Comparing Values ↗
WHERE likes >= 100
↓Supabase's library
.gte("likes", 100)
From Comparing Values ↗
WHERE likes < 100
↓Supabase's library
.lt("likes", 100)
From Comparing Values ↗
WHERE likes <= 100
↓Supabase's library
.lte("likes", 100)
From Comparing Values ↗
WHERE likes <> 0
↓Supabase's library
.neq("likes", 0)
From Comparing Values ↗
WHERE name IS NULL
↓Supabase's library
.is("name", null)

Not .eq("name", null): like = NULL in SQL, it finds no row.

From Comparing Values ↗
WHERE name IS NOT NULL
↓Supabase's library
.not("name", "is", null)
From Comparing Values ↗
WHERE likes > 100 AND id < 10
↓Supabase's library
.gt("likes", 100).lt("id", 10)

AND is two filters, one after the other.

From Combining Conditions ↗
WHERE likes BETWEEN 50 AND 100
↓Supabase's library
.gte("likes", 50).lte("likes", 100)

BETWEEN keeps both ends, so it is >= and <=.

From Combining Conditions ↗
WHERE likes > 300 OR likes < 10
↓Supabase's library
.or("likes.gt.300,likes.lt.10")

OR takes one string: each condition as column.filter.value, split by commas.

From Combining Conditions ↗
WHERE id IN (1, 4, 20)
↓Supabase's library
.in("id", [1, 4, 20])
From Combining Conditions ↗

Matching text

WHERE body LIKE '%SQL%'
↓Supabase's library
.like("body", "%SQL%")
From Matching Text ↗
WHERE body ILIKE '%sql%'
↓Supabase's library
.ilike("body", "%sql%")

% and _ mean the same as in SQL.

From Matching Text ↗

Sorting

ORDER BY created_at
↓Supabase's library
.order("created_at")
From Sorting ↗
ORDER BY created_at DESC
↓Supabase's library
.order("created_at", { ascending: false })
From Sorting ↗
ORDER BY likes DESC, created_at
↓Supabase's library
.order("likes", { ascending: false })
.order("created_at")

The second .order() breaks ties, like the second column after the comma.

From Sorting ↗
LIMIT 10
↓Supabase's library
.limit(10)
From Sorting ↗

Counting

SELECT COUNT(*) FROM posts WHERE likes > 100;
↓Supabase's library
const { count, error } = await supabase
  .from("posts")
  .select("*", { count: "exact", head: true })
  .gt("likes", 100);

Read count, not data. head: true sends back no rows, only the number.

From Counting ↗
SELECT SUM(likes) FROM posts;
↓Supabase's library
.select("likes.sum()").single()

Comes back as data.sum. SUM, AVG and GROUP BY need two lines run once in the SQL Editor: Chirp: find and count posts, step 6.

From Counting ↗
SELECT AVG(likes) FROM posts;
↓Supabase's library
.select("likes.avg()").single()

Comes back as data.avg.

From Counting ↗
SELECT handle, COUNT(*) FROM posts
GROUP BY handle;
↓Supabase's library
.select("handle, count()")

There is no .group(). The library groups by every plain column you select.

From Counting ↗

Adding, changing and removing rows

INSERT INTO posts (body) VALUES ('Hello');
↓Supabase's library
.from("posts").insert({ body: "Hello" })

Each key is a column. Columns you leave out get their default.

From Adding Rows ↗
INSERT INTO posts (body)
VALUES ('One'), ('Two');
↓Supabase's library
.insert([{ body: "One" }, { body: "Two" }])
From Adding Rows ↗
INSERT … RETURNING id;
↓Supabase's library
.insert({ body: "Hello" })
.select("id")
.single()

The new row's id comes back as data.id.

From Adding Rows ↗
UPDATE posts SET body = 'Edited'
WHERE id = 21;
↓Supabase's library
.from("posts")
.update({ body: "Edited" })
.eq("id", 21)

The filter is your WHERE. Never leave it out.

From Changing and Removing Rows ↗
DELETE FROM posts WHERE id = 21;
↓Supabase's library
.from("posts").delete().eq("id", 21)

The filter is your WHERE. Never leave it out.

From Changing and Removing Rows ↗
DELETE FROM posts WHERE id = 21;
-- Postgres reports: DELETE 1
↓Supabase's library
const { count, error } = await supabase
  .from("posts")
  .delete({ count: "exact" })
  .eq("id", 21);

count is how many rows the write changed. update() takes it as its second argument.

From Changing and Removing Rows ↗

Across tables

SELECT posts.body, users.handle
FROM posts
JOIN users ON users.id = posts.user_id;
↓Supabase's library
.from("posts").select("body, users(handle)")

The library finds the ON from the foreign key. Each post comes back with users: { handle }.

From Reading Across Tables ↗
… JOIN users ON users.id = posts.user_id
WHERE users.handle = 'ada';
↓Supabase's library
.select("body, users!inner(handle)")
.eq("users.handle", "ada")

!inner keeps only the posts whose user passes the filter.

From Reading Across Tables ↗

When Postgres says no

ERROR: duplicate key value violates
unique constraint …
↓Supabase's library
if (error?.code === "23505") {
  // a UNIQUE column already has this value
}

error.code is Postgres's code for the error, and error.message its words.

From Why the Database Says No ↗
ERROR: … violates foreign key constraint …
↓Supabase's library
if (error?.code === "23503") {
  // a row points at a row that isn't there
}
From Why the Database Says No ↗

Only in the SQL Editor

CREATE TABLE posts ( … );
↓SQL Editor

The library reads and writes rows. Tables, indexes and their rules are made in SQL.

From The Schema ↗
CREATE INDEX ON posts (user_id);
↓SQL Editor
From Indexes ↗
EXPLAIN SELECT * FROM posts
WHERE user_id = 7;
↓SQL Editor

Shows the plan: Seq Scan reads every row, Index Scan uses an index.

From When an Index Helps ↗

A row appears here once a lesson has taught its SQL.

YOU

Chirp: make it fast

Time
About 35 min
Steps
5
Where
Your editor

You'll need

  • •Your chirp repository, with checkpoint 6 done
  • •Your Supabase project, simplified-practice

Puts into practice

  • Chirp: rules
  • 6.1 Indexes
  • 6.2 When an Index Helps

Chirp has 7 people and 20 posts from its seed, plus any posts you wrote.

Since the last checkpoint, two people can't share a handle, because handle has UNIQUE.

With 20 rows, every query Chirp sends comes back at once.

So what happens to Chirp's pages when posts holds half a million rows?

In this project you fill Chirp with practice users and posts.

Then you ask Postgres for its plan for the profile page, with EXPLAIN.

You'll find the search that reads every row, and give it an index.

The goal

Chirp with 500,000 posts, and a profile page that goes straight to a person's posts with an index.

When you're done

  • ✓Chirp holds 500,000 posts.
  • ✓A person's page finds their posts with an index, not a full scan.
  • ✓EXPLAIN shows you the difference, before and after.

What you're working toward

The profile of user00042: 50 posts, newest first, starting with Practice post 490041 from Dec 7, 2025.
1

Step 1: Fill Chirp with practice posts

Open your Supabase project, simplified-practice, and press SQL Editor.

Paste this query, and press Run once.

INSERT INTO users (handle, name, created_at)
SELECT 'user' || lpad(n::text, 5, '0'), 'User ' || n, timestamp '2025-01-01' + n * interval '1 minute'
FROM generate_series(1, 10000) AS n;

INSERT INTO posts (user_id, body, likes, created_at)
SELECT users.id, 'Practice post ' || n, n % 10, timestamp '2025-01-01' + n * interval '1 minute'
FROM generate_series(1, 500000) AS n
JOIN users ON users.handle = 'user' || lpad((n % 10000 + 1)::text, 5, '0');

generate_series(1, 500000) hands back the numbers 1 to 500,000, one row each. No lesson in this course covers it.

So the first INSERT adds 10,000 people, user00001 to user10000.

The second adds 500,000 posts, shared out in turn, so each of them has 50.

Every practice post is dated in 2025, and has fewer than 10 likes.

So your newest posts stay at the top of the feed, and Trending stays the same.

It can take a few seconds to run.

Your turn 1

How many rows does posts hold now? Write the query, and run it.

A right answer gives

500,020, and one more for each post you've written yourself.

SELECT COUNT(*) FROM posts;

You should see

At localhost:3000, the same feed and Trending as before, starting with Dee's 311 likes.

And localhost:3000/user00042 says 50 posts, with "Practice post 490041" at the top.

posts now takes about 46 MB of the free plan's 500 MB.

The profile of user00042: 50 posts, newest first, starting with Practice post 490041 from Dec 7, 2025.
2

Step 2: Your turn: keep the sidebar short

Reload localhost:3000, and look at the sidebar.

Most active groups every post by its author, and that's over 10,000 people now.

And Switch user lists every row of users: after ada to you, hundreds of practice people.

So how can each list ask Postgres for only the rows it shows?

Most active is meant for people posting this year, and every practice post is from 2025.

So a WHERE can drop those posts before Postgres groups the rest.

In Supabase's library, WHERE created_at >= '2026-01-01' is .gte("created_at", "2026-01-01").

Switch user only needs the newest few people: an ORDER BY and a LIMIT, as in your feed.

Your turn 1

Make getMostActive() count only the posts written since January 1, 2026.

Done when

Most active lists the same people as before, in any order, and no practice people:

handleposts
ada4
ben4
cy3
dee4
eli2
fay3

You're in it too, if you've posted.

// SELECT users.handle, COUNT(*) FROM posts JOIN users ON users.id = posts.user_id
//   WHERE posts.created_at >= '2026-01-01' GROUP BY users.handle;
export async function getMostActive() {
  const { data, error } = await supabase
    .from("posts")
    .select("count(), users(handle)")
    .gte("created_at", "2026-01-01");
  if (error) throw new Error(error.message);
  return data;
}

Your turn 2

Make getUsers() return only the ten people who joined most recently, newest first.

Done when

Switch user lists ten people: you, fay, eli, dee, cy, ben and ada, then user10000, user09999 and user09998.

// SELECT id, handle FROM users ORDER BY created_at DESC LIMIT 10;
export async function getUsers() {
  const { data, error } = await supabase
    .from("users")
    .select("id, handle")
    .order("created_at", { ascending: false })
    .limit(10);
  if (error) throw new Error(error.message);
  return data;
}

You should see

A short Most active list with no practice people, and ten people in Switch user.

Ada's profile beside a short Most active list: ada 4, ben 4, cy 3, dee 4, eli 2 and fay 3 posts, and no practice users.
3

Step 3: Find the full scan

To show localhost:3000/ada, Chirp needs two searches.

First, Ada's row in users, found by her handle, ada.

Then her posts, found by her user_id, 1, newest first.

So does either one read all 500,020 posts?

Put EXPLAIN in front of each, in the SQL Editor. Postgres sends back its plan, and runs nothing.

Some lines of the plan end with numbers in brackets. You can skip those.

Your turn 1

Ask Postgres for its plan for finding Ada's row in users by her handle.

A right answer gives

A table with one column, QUERY PLAN, and two rows:

Index Scan using users_handle_key on users
  Index Cond: (handle = 'ada'::text)

users_handle_key is the index UNIQUE built on handle, in the last checkpoint. (::text only names the value's type.)

EXPLAIN SELECT id FROM users WHERE handle = 'ada';

Your turn 2

Ask Postgres for its plan for Ada's posts: the posts whose user_id is 1, newest first.

A right answer gives

A plan with no index in it:

Sort
  Sort Key: created_at DESC
  ->  Seq Scan on posts
        Filter: (user_id = 1)

The top two lines are your ORDER BY. Under them, a Seq Scan: Postgres would read every row of posts.

Your plan may start with Gather Merge, and say Parallel Seq Scan instead. That's a full scan split between helpers, still reading every row.

EXPLAIN SELECT id, body, likes, created_at
FROM posts
WHERE user_id = 1
ORDER BY created_at DESC;

You should see

An Index Scan for Ada's handle, and a Seq Scan on posts for her posts.

Why does one search get an index, and the other not?

handle has UNIQUE, and UNIQUE builds an index. user_id is a foreign key, and a foreign key builds none.

So to send back Ada's 4 posts, Postgres would read all 500,020.

4

Step 4: Your turn: add the index

Every profile page searches posts by user_id, so posts needs an index on it.

Write it in the SQL Editor, the way Simplified indexed attempts' user ID.

Your turn 1

Give posts an index on user_id, and run it.

A right answer gives

Success. No rows returned under the editor.

CREATE INDEX ON posts (user_id);

Your turn 2

Ask Postgres again for its plan for Ada's posts, newest first.

A right answer gives

No Seq Scan this time:

Sort
  Sort Key: created_at DESC
  ->  Bitmap Heap Scan on posts
        Recheck Cond: (user_id = 1)
        ->  Bitmap Index Scan on posts_user_id_idx
              Index Cond: (user_id = 1)

The bottom line names your new index, posts_user_id_idx, and Index Cond is what Postgres looks up there.

Postgres calls this kind of index scan Bitmap. It finds Ada's entries in the index first.

Then the line above, Bitmap Heap Scan, reads only the rows of posts those entries point to.

EXPLAIN SELECT id, body, likes, created_at
FROM posts
WHERE user_id = 1
ORDER BY created_at DESC;

You should see

A plan that names posts_user_id_idx, and no Seq Scan.

localhost:3000/ada still shows her 4 posts, and localhost:3000/user00042 its 50.

The index takes about 3.5 MB more, and every new post now writes an entry in it too.

5

Step 5: Save your work

Commit your changes, and push them to GitHub.

git add -A
git commit -m "Practice data, a short sidebar and an index on posts"
git push

The index and the practice rows live in your database, not in your repository.

You should see

Your commit on GitHub, with the new getMostActive() and getUsers() in lib/data.ts.

Supabase says your project is paused

Free projects pause after a week with no activity. Press Restore project on its page, and wait a few minutes.

The generator stops with a timeout, or ran only partly

Count the rows in users and in posts. If only users grew by 10,000, run only the second INSERT; if neither did, run both.

An error that names users_handle_key when you run the generator

The practice users are already there, and UNIQUE refuses them twice. Run only the second INSERT, if posts doesn't have its 500,000 yet.

COUNT says 1,000,020, not 500,020

The second INSERT ran twice. Run DELETE FROM posts WHERE body LIKE 'Practice post %';, then run the second INSERT once.

The plan still says Seq Scan after the index

Check that CREATE INDEX said Success. No rows returned, in this project, and that it names posts and user_id.

Most active is empty

Run its SQL in the SQL Editor. If the rows are right there, the bug is in your code: check the date and the column, created_at.

Finish the project

Tick every step once you've seen what it says you should see. Then mark the project complete to unlock the next lesson.

0 of 5 steps done
Assistant

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