DEV Community: Israa

Offset vs cursor pagination in SQL

Israa — Tue, 10 Nov 2026 17:33:20 +0000

Pagination means splitting a long list into smaller pages, like showing 10 posts at a time instead of every row in the table. It's a favorite backend interview topic, and the question usually sounds like this: offset or cursor, and why?

Every example here is plain SQL for PostgreSQL 18, the current version. You can run it in psql, the command-line tool that comes with Postgres, or in any SQL editor. You'll fill a table with 100,000 posts, watch offset pagination slow down and repeat rows, then fix both problems with a cursor. It takes about 10 minutes to read and try.

Offset pagination says "skip 20 rows and give me the next 10," while cursor pagination says "give me the 10 rows after this one."

1. Make a table with 100,000 posts

Start with one posts table and plenty of sample rows, so the slow queries actually show up.

CREATE TABLE posts (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO posts (title, created_at)
SELECT 'Post ' || n, timestamptz '2026-01-01 00:00+00' + n * interval '1 minute'
FROM generate_series(1, 100000) AS n;

GENERATED ALWAYS AS IDENTITY makes Postgres number each new row, so a bigger id always means a newer post, and timestamptz is a date and time with a time zone. A primary key is the column that identifies each row, and Postgres builds an index for it automatically: a sorted copy of the ids it can search quickly, like the index at the back of a book. generate_series(1, 100000) produces the numbers 1 to 100,000, so the INSERT adds one post per number, each a minute after the last.

2. Offset pagination

Offset pagination is the version most tutorials show. The app takes a page number, works out how many rows to skip, and puts that number in the query.

-- page 3, 10 posts per page: skip (3 - 1) * 10 = 20 rows
SELECT id, title
FROM posts
ORDER BY id DESC
LIMIT 10 OFFSET 20;

ORDER BY id DESC puts the newest posts first, LIMIT caps how many rows come back, and OFFSET says how many to skip, so this returns posts 99980 down to 99971. Always include ORDER BY when you paginate, because SQL doesn't promise any row order without it and two pages could share rows.

3. Problem one: deep pages get slow

Interviewers usually expect you to name two problems with offset. The first is speed, and EXPLAIN ANALYZE shows why: it runs the query and prints the plan, the list of steps the database took, with how many rows each step handled.

EXPLAIN ANALYZE
SELECT id, title
FROM posts
ORDER BY id DESC
LIMIT 10 OFFSET 90000;

These are the two lines that matter, with the timing details cut:

Limit  (...) (actual ... rows=10.00 ...)
  ->  Index Scan Backward using posts_pkey on posts  (...) (actual ... rows=90010.00 ...)

Read the plan from the bottom up: the index scan walked through 90,010 rows, and the Limit step threw away the first 90,000 and kept 10. Page 9,001 does all that work every time someone opens it, and it gets worse as the table grows. PostgreSQL 18 prints row counts with two decimals, so older versions show rows=10 instead.

4. Problem two: new rows shift the pages

The second problem appears when the data changes between two requests. Run these three statements in order, as if a user loads page 1, someone publishes a post, and then the user loads page 2.

SELECT id FROM posts ORDER BY id DESC LIMIT 2 OFFSET 0;  -- 100000, 99999

INSERT INTO posts (title) VALUES ('Brand new post');     -- gets id 100001

SELECT id FROM posts ORDER BY id DESC LIMIT 2 OFFSET 2;  -- 99999, 99998

Post 99999 shows up on both pages, because the new row pushed every post down one spot. A deleted row does the opposite and makes the user skip one, and since neither case throws an error, these bugs are easy to miss.

5. Cursor pagination

A cursor is a small value that marks the last row the user saw, like a bookmark. The next query asks only for rows older than the bookmark.

-- the last post the user saw was 99991, so get the 10 after it
SELECT id, title
FROM posts
WHERE id < 99991
ORDER BY id DESC
LIMIT 10;

This returns posts 99990 down to 99981. New posts get bigger ids and land above the bookmark, so they can't push anything onto the next page.

6. Why the cursor stays fast

To compare with section 3, ask for the same deep page, posts 10000 down to 9991, with a cursor.

EXPLAIN ANALYZE
SELECT id, title
FROM posts
WHERE id < 10001
ORDER BY id DESC
LIMIT 10;

Limit  (...) (actual ... rows=10.00 ...)
  ->  Index Scan Backward using posts_pkey on posts  (...) (actual ... rows=10.00 ...)
        Index Cond: (id < 10001)

Index Cond means Postgres used the index to jump straight to id 10000, so it handled 10 rows instead of 90,010. The cursor query does the same small amount of work on page 2 or page 9,001.

7. Know when to stop

The app also needs to know whether another page exists. Ask for one row more than you show, so 11 rows for a page of 10.

SELECT id, title
FROM posts
WHERE id < 11
ORDER BY id DESC
LIMIT 11;

Only 10 rows come back (posts 10 down to 1), so this is the last page. When the 11th row does come back, the app shows the first 10 and sends the 10th row's id as the next cursor, usually in JSON like { "items": [...], "nextCursor": 9991 }. The client sends that cursor with its next request, and it becomes the number in WHERE id < ....

8. When the sort column can repeat

Sorting by id works because ids never repeat. Many apps sort by created_at instead, where two posts can share the same time, and a plain WHERE created_at < ... would skip any post with exactly the same time as the bookmark. The fix is a tie-breaker, a second sort column that is unique, like id, plus an index on both columns.

CREATE INDEX posts_created_at_id_idx ON posts (created_at, id);

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-03-11 10:31:00+00', 99991)
ORDER BY created_at DESC, id DESC
LIMIT 10;

(created_at, id) < (x, y) compares pairs the way a dictionary compares words: time first, then id only when the times match. The cursor now holds two values, the last row's time and id, and because the index has the same columns in the same order as the ORDER BY, Postgres can still jump straight to the bookmark.

That's it

Offset pagination counts rows to skip, cursor pagination continues from a bookmark, and that bookmark keeps pages fast and free of repeats when the table changes.

Two practical tips:

Run EXPLAIN ANALYZE on your own pagination queries and compare the actual rows with the rows you return, since a big gap means wasted work.
In an interview, lead with the short answer: offset fits admin tables where people jump to page 7, and cursor fits feeds, infinite scroll, and tables that change while people read them.

Which one does your current project use, and have you ever caught the duplicate-post bug in the wild?

TypeScript Basics You Need for Next.js

Israa — Mon, 02 Nov 2026 14:33:55 +0000

If you're starting a Next.js project, it probably came with TypeScript turned on. That can feel scary if you've only written plain JavaScript.

The whole language is a lot. The part you need for Next.js is about seven small ideas, and this post sticks to those, with tiny examples. Ten minutes, tops.

TypeScript is just JavaScript with labels on your data. The labels tell your editor what a value is, so it can catch mistakes before you even run the code.

1. Basic types

You add a type after a colon. That's the whole syntax.

const name: string = "Sara";
const age: number = 25;
const isAdmin: boolean = false;
const tags: string[] = ["next", "react"];

Most of the time you can skip the type and TypeScript figures it out:

const name = "Sara"; // TypeScript already knows this is a string

2. Describing objects

For objects, you describe the shape once with type, then reuse it.

type User = {
  name: string;
  age: number;
};

const sara: User = { name: "Sara", age: 25 };

Now if you forget age or spell name wrong, your editor underlines it in red.

You'll also see interface User { ... }. For beginners, it does the same job as type. Pick one and don't worry about it.

3. Typing component props

This is the thing you'll do most in Next.js. Props are just an object, so you describe them the same way.

type ButtonProps = {
  label: string;
  onClick: () => void;
};

export default function Button({ label, onClick }: ButtonProps) {
  return <button onClick={onClick}>{label}</button>;
}

() => void means "a function that takes nothing and returns nothing."

Now when you write <Button /> somewhere and forget label, Next.js tells you right in the editor instead of showing a broken button in the browser.

4. Optional and "one of these" types

A ? after a name means "this is optional."

A | means "one of these values."

type ButtonProps = {
  label: string;
  variant?: "primary" | "secondary"; // optional, and only these two words
};

Now <Button label="Save" /> works, and <Button label="Save" variant="big" /> gets a red underline. You get autocomplete for variant too.

5. Typing state

useState usually guesses the type from the starting value, so this just works:

const [count, setCount] = useState(0); // number

It only needs help when it starts empty. Tell it what will go in using angle brackets:

const [user, setUser] = useState<User | null>(null);

That reads as "this state holds a User, or nothing yet." Angle brackets are how you pass a type to a function.

Don't forget "use client" at the top of any file that uses useState in Next.js.

6. Typing data you fetch

When you fetch from an API, TypeScript has no idea what comes back, so you have to tell it.

type Post = {
  id: number;
  title: string;
};

async function getPosts(): Promise<Post[]> {
  const res = await fetch("https://api.example.com/posts");
  return res.json();
}

Promise<Post[]> means "this async function will eventually give you a list of posts."

After that, anywhere you use posts, your editor knows each one has an id and a title.

7. The two Next.js-specific bits

Page params

In the App Router, a page like app/blog/[slug]/page.tsx gets its slug through params. Since Next.js 15, params is a Promise, so you type it like this and await it:

export default async function BlogPost({
  params,
}: {
  params: Promise<{ slug: string }>;
}) {
  const { slug } = await params;
  return <h1>{slug}</h1>;
}

If you see old tutorials with params: { slug: string } and no await, that's the pre-15 way. It will show a type error on current versions.

Metadata

Next.js ships its own types. The one you'll meet first is Metadata for your page title:

import type { Metadata } from "next";

export const metadata: Metadata = {
  title: "My Blog",
};

Adding : Metadata gets you autocomplete for every allowed field, which is most of the reason to use the built-in types at all.

That's it

Those seven ideas cover most of what you'll write as a beginner.

Two tips for the road:

When you're stuck, hover over the red underline. The error message usually says exactly what's missing.
Avoid any. It turns TypeScript off for that value, which hides the mistakes you wanted it to catch.

Everything else (generics, utility types, type guards) can wait until you actually need it. Build something first.

What tripped you up when you first used TypeScript with Next.js? Drop it in the comments.

I added a Postgres constraint to stop double bookings. It deadlocked instead.

Israa — Sat, 17 Oct 2026 18:31:23 +0000

I'm building a dental clinic booking API (NestJS, Drizzle, Postgres) as a portfolio project. The rule I care most about is that two patients can never have the same doctor at the same time.

I knew the textbook answer. If the app checks "is this slot free?" and then inserts, two requests can both check, both see "free", and both insert. So the database has to enforce the rule itself. Postgres has exclusion constraints for exactly this:

CREATE EXTENSION IF NOT EXISTS btree_gist; -- lets the index compare uuids with =

ALTER TABLE appointments ADD CONSTRAINT appointments_no_overlap
  EXCLUDE USING gist (doctor_id WITH =, tstzrange(starts_at, ends_at) WITH &&)
  WHERE (status <> 'cancelled');

In plain words: no two appointments that aren't cancelled may have the same doctor and overlapping times. An insert that breaks the rule fails with error code 23P01, which my API turns into a 409 "slot taken" response. Every code path gets the check for free, and nobody can forget it. Done, I thought.

What broke

I wrote a script that fires ten booking transactions for the same slot straight at the database, all at the same moment, and ran it ten times. I expected one winner and nine quick 23P01 errors per round, which the API would turn into 409s.

Each round did get exactly one winner. But 36 of the 90 losers never got a 23P01. They got a deadlock error (40P01) instead, and the slowest request took 9 seconds. Retrying on deadlock made things worse, because the retries collided with each other again.

The data was always correct, never more than one booking per slot. But a patient who waits nine seconds for an error page does not care about that.

Why it happens

A unique index checks for duplicates and writes its entry in one step, so two inserts can't both pass the check. An exclusion constraint works the other way round. The Postgres source (execIndexing.c) says it in one line: "we always insert first and then check".

So when two transactions, A and B, insert overlapping rows at nearly the same time:

A writes its row. B writes its row.
A checks for conflicts, finds B's unfinished row, and waits to see if B commits.
B checks, finds A's unfinished row, and waits for A.
Each waits for the other. After deadlock_timeout (1 second by default), Postgres notices and kills one of them.

With ten transactions at once, these waits chain into multi-second stalls. The same source file calls this deadlock "fairly harmless", since one of the two was going to fail anyway, and adds that you just get a different error message. That different error message, after a nine-second wait, is exactly the problem.

The fix: one line before the insert

The booking transaction now starts by locking the doctor's row:

SELECT id FROM doctors WHERE id = $1 FOR UPDATE

Only one transaction can hold that lock at a time, so bookings for the same doctor now wait in line. The next one gets the lock only after the previous one has finished. By then the earlier booking is committed, so the new insert meets a committed overlapping row and fails at once with 23P01. Two unfinished overlapping inserts never exist at the same time, so the deadlock can't form. Bookings for different doctors lock different rows and never wait on each other. The transaction still does its other checks, like whether the doctor offers that slot at all; the constraint only guards overlaps.

Same script, same ten rounds, Postgres 17 in Docker on my laptop. Your numbers will differ; the shape should not:

Variant	Slowest request	What the 90 losers got
Constraint only	9,079 ms	54 × `23P01`, 36 × deadlock
Constraint + retry 3× on deadlock	11,051 ms	27 × `23P01`, 63 × deadlock
Constraint + lock doctor row first	20 ms	90 × `23P01`

What I took from it

Keep both the constraint and the lock, because they do different jobs. The constraint is the guarantee: if some future code path forgets the lock, the worst case is a slow, ugly error, never a double booking. The lock makes losing fast and clean. The reverse, a lock with no constraint, is dangerous, because code that forgets the lock double-books silently.

And a concurrency test that passes once proves nothing. An earlier script of mine tested plain check-then-insert and looked safe, only because the ten transactions happened to run almost one after another. Adding a 20 ms sleep between the check and the insert, to stand in for real work, let all ten book the same slot.