Advanced REST API series: 1. Idempotent methods, URLs and pagination · 2. Async jobs, Idempotency-Key and API docs
REST API design and Spring Boot covered the basics (URLs, versioning, error format, HTTP caching); this post covers what it takes for an API to “work in production”: method semantics, URLs behind a gateway, paginating tables with tens of millions of rows, sorting and relationships. Part 2 moves on to exporting a 500 MB file, duplicate payment requests and API documentation. Read first: Web and HTTP (method semantics, status codes) and Indexes and query performance (why OFFSET is slow).
At a glance:
mindmap
root((Advanced REST API))
Idempotency
PUT, DELETE are idempotent
POST not, PATCH not guaranteed
Retry only idempotent calls
URLs
Service name only internally
Pagination
OFFSET slow, skips rows
Cursor: rows after last one
Deferred join still offset-bound
Sort via whitelist
1. Mindset: design first, code second
Key idea: designing the API before coding is far cheaper than fixing it later, because a public API is the hardest thing to change: you don’t control the clients.
(The “soft” in software means easy to change.) Designing first helps you:
- Think at a high level: cover errors, pagination, retries and permissions up front.
- Fix things cheaply: changing a line of design beats changing an API a shipped mobile app depends on.
- Work in parallel: frontend, mobile and partners build against the contract and a mock server generated from the spec.
Four principles for every API review:
- Scalable: does it still hold when data grows 100×? (OFFSET, synchronous exports and unbounded responses usually break here.)
- Consistency: every service shares the same field names, pagination, error format and error codes.
- Inspect every single aspect: errors, pagination, sorting, authentication, authorisation, idempotency, timeouts, rate limits, payload size.
- No one fits all: only trade-offs that fit a context.
2. Safe and idempotent: getting the definitions right
Key idea: safe = doesn’t change server state; idempotent = sending it once or N times leaves the server in the same final state. Both are about the effect on the server, not about the response.
| Method | Safe | Idempotent | Used for |
|---|---|---|---|
| GET, HEAD, OPTIONS, TRACE | Yes | Yes | Reading |
| PUT | No | Yes | Replacing a whole resource |
| DELETE | No | Yes | Deleting, disabling |
| POST | No | No | Creating, actions |
| PATCH | No | Not guaranteed | Partial updates |
Writing a log line or bumping a statistics counter doesn’t count as “changing state”.
Two nuances that come up in interviews:
- PATCH isn’t forbidden from being idempotent, it just isn’t guaranteed: a JSON Merge Patch
{"age": 34}is, a JSON Patch saying “incrementbalanceby 10” is not. - POST can be made idempotent with an Idempotency-Key (part 2): the server guarantees it, HTTP doesn’t.
Why it matters: clients, proxies, HTTP libraries and service meshes retry automatically on timeouts. Blindly retrying a money-transfer POST creates a second transaction.
3. URL conventions and routing through an API gateway
Key idea: URLs use plural nouns and a version, following one company-wide convention. A service name at the start of the path keeps gateway routing simple, but it belongs on internal APIs only.
- Nouns, not verbs:
/orders, not/createOrder. Actions outside CRUD become sub-resources:POST /orders/145/cancellation. - Plural nouns for collections:
/users,/users/34. - Nest URLs to show relationships, at most two levels:
/posts/{post_id}/comments. - Version in the path:
/v1/.... - kebab-case for URLs (
/order-items), snake_case for JSON fields (total_amount) or camelCase, as long as it’s consistent. The course uses snake_case.
A small exercise from the course: URLs behind an API gateway
An API gateway sits in front of User Service (team A) and Order Service (team B). The first path segment is the service name so the gateway can route by prefix:
| Requirement | Method + URL |
|---|---|
| Create an order | POST /order-service/v1/orders |
| Get the details of order 145 | GET /order-service/v1/orders/145 |
| Update only the age of user 34 | PATCH /user-service/v1/users/34 with body {"age": 35} |
| Disable user 34 | DELETE /user-service/v1/users/34 (even if it’s a soft delete underneath) |
Reverse proxying with Nginx builds the exercise above with Docker Compose (Nginx strips the service name and forwards /v1/...), plus the proxy_pass with/without URI mistake and the X-Forwarded-* headers.
4. Pagination at scale
Key idea: OFFSET slows down with page depth and skips rows when data changes. Public APIs use cursors; use OFFSET only when you must “jump to page N”, together with a deferred join.
4.1. Two parameter styles
Key idea: page/size and offset/limit have different names but are both offset pagination in SQL.
page+size:GET /users?page=0&size=20, fits admin screens with page numbers.offset+limit:GET /events?offset=40&limit=20, common for infinite scroll, logs and events.
A real, common bug: not documenting whether page starts at 0 or 1. Spring Data defaults to 0, many frontends assume 1; when they disagree, the first page silently disappears. Always cap size (say, 100) and return 400 rather than truncating quietly. In Spring Boot, spring.data.web.pageable.one-indexed-parameters=true makes ?page=1 the first page, while max-page-size quietly clamps size to the cap rather than returning 400 (the DTOs, responses and validation post).
4.2. The two problems with OFFSET
Key idea: OFFSET costs O(offset), and rows deleted or inserted between two calls slide data across page boundaries.
Problem 1: performance. OFFSET 100000 LIMIT 10 reads and throws away 100,000 rows. A SELECT COUNT(*) for the page total counts every matching row: neither PostgreSQL nor InnoDB stores a row count, because of MVCC (the database keeps several versions of each row, so every transaction may see a different number of rows).
Problem 2: resource skipping (missed records). Tested on PostgreSQL 16, a users table with ids 1..200000, 5 rows per page:
SELECT id FROM users ORDER BY id LIMIT 5 OFFSET 0; -- page 1: 1,2,3,4,5
DELETE FROM users WHERE id IN (2, 3); -- someone deletes two rows from page 1
SELECT id FROM users ORDER BY id LIMIT 5 OFFSET 5; -- page 2: 8,9,10,11,12 (6 and 7 are gone!)
SELECT id FROM users WHERE id > 5 ORDER BY id LIMIT 5; -- keyset: 6,7,8,9,10
Rows 6 and 7 slide up onto page 1, which was already read, so they never show up. Inserts at the head do the reverse: items get duplicated on the next page. In a feed that’s annoying; in a sync job that walks pages, it’s data loss.
4.3. Cursor (keyset) pagination
Key idea: instead of “skip N rows”, filter for “rows after the last row of the previous page”. A deep page is as fast as the first one.
-- index: (created_at DESC, id DESC)
SELECT id, name, created_at
FROM users
WHERE (created_at, id) < (:last_created_at, :last_id) -- MySQL: use the expanded OR form
ORDER BY created_at DESC, id DESC
LIMIT 20;
On the test table this reads exactly 10 rows (Index Cond: ROW(created_at, id) < ROW(...), 0.02 ms) however deep the cursor is. Three requirements:
- A unique, stable sort key:
created_atcan repeat, so addidas a tie-breaker (an extra column that breaks ties). Avoid columns that change often (likeupdated_at): rows would jump between pages. - An index that matches the
ORDER BYexactly. - An opaque cursor: return a
next_cursorstring instead of a barelast_id, so clients don’t depend on internals and the server can change the sort key without a breaking change.
A suggested response: {"data": [...], "meta": {"next_cursor": "MTc5MzUyNzIwMDAwMDo5ODE", "has_next": true}}. Fetch limit + 1 rows to know has_next without a COUNT(*). The Threads-style news feed design uses exactly this kind of cursor (the id of the last post seen), because new posts inserted at the top make OFFSET return repeats.
Anyone can decode and edit a base64 cursor: the server always re-applies filters and permissions and never trusts the cursor.
Java code to encode/decode the cursor, and the base64 trap
record Cursor(Instant createdAt, long id) {
String encode() {
String raw = createdAt.toEpochMilli() + ":" + id;
return Base64.getUrlEncoder().withoutPadding()
.encodeToString(raw.getBytes(StandardCharsets.UTF_8));
}
static Cursor decode(String token) {
try {
String raw = new String(Base64.getUrlDecoder().decode(token), StandardCharsets.UTF_8);
String[] parts = raw.split(":", 2);
return new Cursor(Instant.ofEpochMilli(Long.parseLong(parts[0])), Long.parseLong(parts[1]));
} catch (RuntimeException e) {
throw new IllegalArgumentException("invalid cursor"); // map to 400, don't let it become a 500
}
}
}
4.4. Deferred join when you’re stuck with OFFSET
Key idea: paginate over a small index that only returns id, then join back for full rows for just those 10 ids. Roughly 10× cheaper, but still O(offset).
Use it when an admin screen needs “jump to page 37” and a cursor won’t do. The slide’s SQL mixes USING id with JOIN ... ON and doesn’t run; here is a working version:
-- runs on both PostgreSQL and MySQL
SELECT u.*
FROM (
SELECT id FROM users
ORDER BY created_at DESC, id DESC
LIMIT 10 OFFSET 150000
) AS page
JOIN users u USING (id)
ORDER BY u.created_at DESC, u.id DESC; -- a join doesn't preserve order, so sort again
It’s cheaper only because it walks the index instead of full rows. On PostgreSQL, before VACUUM, the trick can even be slower.
Deferred join measurements on PostgreSQL 16 and the visibility-map trap
200,000 rows, each with a 500-byte bio column, index (created_at DESC, id DESC), after VACUUM:
| Query | Main plan node | Buffers read |
|---|---|---|
SELECT * ... LIMIT 10 OFFSET 150000 |
Index Scan, fetches 150,010 full rows from the heap | ~11,900 pages |
| Deferred join | Index Only Scan (Heap Fetches: 0) + 10 PK lookups | ~1,200 pages |
A page total costs a COUNT(*) on every request; the default for public APIs is has_next + cursor.
Ways to return a page total
5. Sorting and filtering
Key idea: only allow sorting by fields on an allowlist, map API field names to real columns, and always append id as a tie-breaker.
Syntax: GET /products?sort=price:asc,name:desc or sort=+price,-name. The allowlist is there for:
- Security: column names in
ORDER BYcan’t be bound like values; concatenating them is SQL injection. - Performance: every sortable field needs an index; sorting by any column lets clients trigger full scans and filesorts (sorting outside an index) on big tables.
Java code: building ORDER BY from an allowlist
static final Map<String, String> SORTABLE = Map.of(
"price", "p.price", "name", "p.name", "created_at", "p.created_at");
static String orderBy(String sort) {
if (sort == null || sort.isBlank()) return "p.created_at DESC, p.id DESC";
String[] items = sort.split(",");
if (items.length > 3) throw new IllegalArgumentException("too many sort fields");
StringJoiner sql = new StringJoiner(", ");
for (String item : items) {
String[] kv = item.trim().split(":");
String column = SORTABLE.get(kv[0]); // API field name -> real column
if (column == null) throw new IllegalArgumentException("unsupported sort field: " + kv[0]);
String dir = kv.length > 1 ? kv[1].toLowerCase(Locale.ROOT) : "asc";
if (!dir.equals("asc") && !dir.equals("desc")) throw new IllegalArgumentException("bad direction: " + dir);
sql.add(column + " " + dir.toUpperCase(Locale.ROOT));
}
return sql.add("p.id ASC").toString(); // tie-breaker: the order is always deterministic
}
// orderBy("price:desc,name") -> "p.price DESC, p.name ASC, p.id ASC"
6. Modelling relationships
Key idea: express relationships with nested URLs; add one item with PUT (idempotent), add many with a bulk endpoint that caps the batch size.
- 1-N:
GET /articles/123/comments. - N-N (classes and students):
GET /classes/{class_id}/students. - Adding one student to a class: the slide uses
POST /classes/{id}/students/{student_id}. I preferPUT: the URL fully identifies the relationship, and repeating the call changes nothing (exactly idempotent semantics). Removing:DELETE /classes/{id}/students/{student_id}. - Adding many students:
POST /classes/{id}/studentswith body{"student_ids": ["s1", "s2", "s3"]}.
Exercises
Exercise 1: URLs for a class management system
Task (paraphrased): a course-service manages classes and students. Write method + URL for: listing the students of class 7, adding student s9 to class 7, adding several students at once, removing s9 from the class, renaming the class.
Solution to exercise 1
GET /course-service/v1/classes/7/students?limit=20&cursor=...
PUT /course-service/v1/classes/7/students/s9 # idempotent, 204 whether or not s9 was there
POST /course-service/v1/classes/7/students # {"student_ids": ["s1","s2"]}, max 100
DELETE /course-service/v1/classes/7/students/s9 # 204; a repeat may return 204 or 404, both fine
PATCH /course-service/v1/classes/7 # {"name": "Advanced Java K12"}
A senior would also ask: what do we return when the class is full (409 or 422 with a clear type), and is the bulk add all-or-nothing or per item?
Exercise 2: Move a transaction list API to cursors
Task: GET /v1/transactions?page=&size= is slow on deep pages and the reconciliation job misses transactions. Redesign it.
Solution to exercise 2
GET /v1/transactions?limit=50&cursor=<opaque>; sortORDER BY created_at DESC, id DESC.- Index
(account_id, created_at DESC, id DESC)because we always filter by account. - Cursor = base64 of
created_at|id; returnnext_cursorandhas_next(fetch 51 rows). - The reconciliation job iterates by an increasing key (
id > :last_id), not by page number. - Keep the old API for a while and flag it with a
Deprecationheader.
Remember
- Explain why a second DELETE returning 404 is still idempotent, and when PATCH is idempotent.
- Name the two problems with OFFSET and why a sync job that walks page numbers loses data.
- List the three requirements of cursor pagination, and why you never trust data inside the cursor.
- Know when to use a deferred join and why on PostgreSQL it can be slower.
- Know when to keep the service name in the URL and when to let the gateway map resources.
Interview questions
Junior
What’s the difference between safe and idempotent? Give a method that is idempotent but not safe.
Safe means not changing server state; idempotent means once or N times ends in the same state. PUT and DELETE are idempotent but not safe.
Every safe method (GET, HEAD) is idempotent, but not the other way round: PUT and DELETE change data yet repeating them yields the same state. Idempotency doesn’t require identical responses: a second DELETE may return 404.
Why must you document whether page starts at 0 or 1?
Because the two sides default differently (Spring Data starts at 0, many UIs at 1), and a mismatch loses or repeats a page without any error.
Document it, validate negative values, and cap size.
Mid
Why is OFFSET slow on big tables, and what other problem does it have?
The database reads and discards every row before the offset, so the cost grows linearly with page depth; and data changing between calls makes rows go missing or repeat.
A COUNT(*) for the total also scans every matching row. Resource skipping: rows deleted or inserted between two calls slide across page boundaries. For a data-sync job, a missed row is lost data.
What does cursor pagination need to be correct?
A unique, stable sort key, an index that matches the ORDER BY, and an opaque cursor.
Add id as a tie-breaker and avoid columns that change often. The cursor is opaque so clients don’t depend on internals. The server still re-applies every filter and permission check on each request and never trusts data inside the cursor. The cost is that you can’t jump to an arbitrary page.
What is a deferred join and when doesn’t it help?
Paginate over a small index that only returns ids, then join back for full rows for just that page; fewer pages read, but still O(offset).
It doesn’t help when the index can’t answer the subquery on its own (so the table is read anyway), or on PostgreSQL when the visibility map hasn’t been vacuumed, in which case the index-only scan still visits the heap and can be slower than the plain query.
Why allowlist sortable fields?
Because column names in ORDER BY can’t be bound like parameters, so concatenating them is SQL injection; and sorting by an unindexed column causes a full scan.
A full scan plus a memory-hungry sort hurts on a big table; an allowlist exposes only indexed fields. Append id as a tie-breaker so the order is deterministic during pagination.
Senior
Should the service name be part of a public URL?
Not for public APIs: splitting or merging services changes the URL, which is a breaking change. For internal APIs it’s fine.
A service name in the path lets the gateway route with one prefix rule and each team own its namespace, but it exposes internal structure. I keep service names for internal service-to-service APIs, and map public APIs for mobile or partners by resource at the gateway or a BFF so URLs stay stable and business-oriented.
How would you design an endpoint that adds many items at once?
Cap the batch size and pick explicit semantics: all-or-nothing in one transaction, or partial success with a result per item.
All-or-nothing is easy to reason about; the client retries the whole batch. Partial success returns 207, or 200 with a status list; then retries must be idempotent: re-adding an existing item must not fail, so use an upsert or ON CONFLICT DO NOTHING. Document the behaviour and test the failure-in-the-middle case.
Useful phrases for interviews
- “Strictly speaking, idempotency is about the server’s state, not about getting the same response back.”
- “We work design-first: the OpenAPI spec gets reviewed before anyone writes a controller.”
- “Offset pagination degrades linearly with depth, and it also skips rows when data changes between page loads.”
- “We switched to keyset pagination with an opaque cursor and an id tie-breaker, so every page costs the same.”
- “When the product insists on page numbers, a deferred join keeps the offset scan on the index instead of on full rows.”
- “Sortable fields go through an allowlist; otherwise you’re one query parameter away from SQL injection or a full table scan.”
- “Putting the service name in a public URL leaks our topology, so the gateway maps resources to services instead.”
- “For bulk endpoints we return a result per item and make retries idempotent with an upsert.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.