Description

Login
Back

Advanced · 18 min

Indexes and stable pages

Match an index to a filter and use a key-based page boundary.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Index the query pattern

An index can help locate matching rows without scanning the entire table. Composite column order matters. Indexes cost space and add work to writes, so choose them for actual queries and inspect the plan instead of indexing every column.

CREATE INDEX sessions_member_day ON sessions(member_id, day, id);
SELECT id, day FROM sessions WHERE member_id = 2 ORDER BY day, id;

Output

id | day
3 | 2026-01-01
4 | 2026-01-03

Continue after a known key

Keyset pagination filters after the last seen ordering key. This example uses the unique ID order, so the next page has a simple boundary. More complex sorting requires a boundary matching every sort key.

SELECT id FROM sessions WHERE id > 3 ORDER BY id LIMIT 2;

Output

id
4
5

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return the next two session IDs after 3 in ascending ID order.

Show solution
SELECT id FROM sessions WHERE id > 3 ORDER BY id LIMIT 2;

The exclusive boundary skips ID 3 and returns the following two rows.

Practice