Pages That Stay Fast

A real shop may sell 100,000 products, and no answer should carry them all, so the list comes in pages. With LIMIT and OFFSET, a deep page like OFFSET 90000 makes the database walk past 90,000 rows only to throw them away: the deeper the page, the slower it gets.

A keyset page remembers where the last page ended instead: "the rows after id 6". The id is the primary key, which has its own index, so the database jumps straight to the first id after it, and page 1 and page 30,000 cost the same. Here is the idea for a list of orders, 5 per page:

last = request.args.get("last", 0, type=int)
rows = db.execute("SELECT id, status FROM orders WHERE id > ? ORDER BY id LIMIT 5", (last,)).fetchall()

Each answer also tells the client where the next page starts: "next_after" is the id of the page's last row when the page is full, and null on a shorter, last page. Because a page starts after an id, not after a count of rows, a product removed between two pages doesn't make the next page skip one.

GET /products          →  200  {"next_after": 3, "products": [{"id": 1, "name": "Mug", "price": 12}, {"id": 2, …}, {"id": 3, …}]}
GET /products?after=3  →  200  {"next_after": 6, "products": [{"id": 4, "name": "Pen", "price": 2}, {"id": 5, …}, {"id": 6, …}]}
GET /products?after=6  →  200  {"next_after": null, "products": [{"id": 7, "name": "Stapler", "price": 9}, {"id": 8, …}]}
GET /products?after=8  →  200  {"next_after": null, "products": []}
(then the Notebook, id 2, is removed)
GET /products?after=3  →  200  {"next_after": 6, "products": [{"id": 4, …}, {"id": 5, …}, {"id": 6, …}]}
GET /products          →  200  {"next_after": 4, "products": [{"id": 1, …}, {"id": 3, …}, {"id": 4, …}]}

Your task: add a constant PAGE_SIZE = 3, and make get_products read after from the query string (0 when it is missing), fetch the id, name and price of the products whose id is bigger, in id order, at most PAGE_SIZE of them, and answer {"products": […], "next_after": …}.

Assignment
Follow the Coding Tutorial to turn GET /products into keyset pages.

Hint
Read the parameter with request.args.get and type=int. Start with next_after = None, and only when the page is full, len(rows) == PAGE_SIZE, set it to the id of the last row, rows[-1]["id"].