Why offset pagination skips and duplicates records in Medusa

Your nightly catalog export says it wrote 6,551 products. The count in the header is 6,551 and so is the line count. Then someone in merchandising asks why a product they edited last week never reached the feed. You look for it in the export and it isn't there. Two other SKUs appear twice. Every request returned a 200 and nothing was logged.

This is a known problem with Medusa v2 list endpoints. It is reported in medusajs/medusa#17522 against @medusajs/medusa 2.22.1 on PostgreSQL 17. The issue says the code on develop at d75a11e is the same. The source material names no fixed release, so assume current versions are affected until a changelog says otherwise.

The sort order is not unique, so pages overlap

Medusa list endpoints accept order together with limit and offset. If you leave order out, Medusa sorts by the primary key. applyDefaultOrdering sets the primary keys to ASC when no order is given, and the Product module falls back to { id: "ASC" }. Primary keys are unique, so every row has exactly one position and offset paging works.

If you pass order=created_at, that field replaces the fallback. prepareListQuery builds an order on that one field, and nothing appends id as a tie-breaker. The query becomes ORDER BY created_at LIMIT 24 OFFSET 48. When several rows share a created_at, PostgreSQL may return those rows in any order, and the order can change from one query to the next. The PostgreSQL docs say this directly: without an ORDER BY that "constrains the result rows into a unique order", you get "an unpredictable subset of the query's rows."

Page 3 and page 4 are separate queries. A row with a tied timestamp can sit at position 71 in the first query and position 73 in the second. If that moves it across a page boundary, it either appears on both pages or on neither. The total count is still correct because it comes from a separate count query, so a check of count against rows received passes.

Ordinary catalog data has the ties

You don't need unusual data to hit this. Products created in one batch request or by an import share created_at values. In the issue's first reproduction, one POST /admin/products/batch created 60 products with only 5 distinct created_at values. Paging with limit=7 gave:

order=created_at  returned=60 unique=55 missing=5
order=-created_at returned=60 unique=46 missing=14
order=id          returned=60 unique=60 missing=0

In the second reproduction, GET /store/products with limit=24 ran over a 6,551-product catalog that had been loaded in batches. Ordering by -created_at missed 346 products, created_at missed 337 and -updated_at missed 347. No order and order=-id both returned all 6,551.

The Store API makes it worse. A client can't add the tie-breaker itself, because order=created_at,id returns 400 Order field created_at,id is not valid. The Store API reference uses order: "created_at" and order: "-created_at" as its sort examples and says nothing about ties. A storefront built from those docs shows shoppers the same product twice and hides others. An integration built from them loses data.

Check whether you are affected

Audit every consumer of a Medusa list endpoint: ERP and PIM syncs, feed generators, search indexers, reporting jobs and migration scripts. You are exposed if a consumer sends order on a non-unique field and pages with offset.

Then measure it. Do what the issue's script does: page through the full result set, collect IDs into a set, and compare set.size to count. A difference means records were dropped. Run it more than once, because the result depends on the data and the query plan, and a single clean run proves nothing. The reporter saw 3 to 18 missing out of 60 across three runs.

To see how many ties you have, query the database:

SELECT created_at, count(*)
FROM product
WHERE deleted_at IS NULL
GROUP BY created_at
HAVING count(*) > 1
ORDER BY count(*) DESC;

Any rows mean the bug can affect your catalog. After a bulk import, expect a lot of them.

Use a unique sort key

The fix is a sort order where every row has exactly one position. The issue proposes two ways Medusa could provide it: append the primary key to any explicit order (ORDER BY created_at DESC, id DESC), or let order accept several fields. Until one of them ships, work around it.

Full exports and syncs: sort by id or send no order. For a complete traversal you usually don't care about creation order. You care that every record arrives once. The issue's reproductions show id and -id returning complete sets on both the Admin and Store APIs.

Custom routes and workflows: pass both keys. In your own backend code, set the order explicitly with a unique final key, for example { created_at: "DESC", id: "DESC" }. Then run the set-size check above against that route. Don't assume an order object is applied the way you wrote it until you have checked it.

Large or live datasets: use keyset pagination. Offset paging has a second problem that tie-breakers don't fix. Inserts and deletes during a long export shift every later offset. Keyset (cursor) paging asks for rows after the last row you saw, not the next N positions:

SELECT id, created_at FROM product
WHERE deleted_at IS NULL
  AND (created_at, id) > ($1, $2)
ORDER BY created_at, id
LIMIT 500;

Store the last (created_at, id) pair as the cursor. Each query has a unique start point, so concurrent writes can't make it skip or repeat rows, and the query doesn't slow down with depth the way a large OFFSET does. We use this for anything that reads a whole table in a background job.

The same bug is on the Magento side of a migration

A Magento-to-Medusa migration reads in bulk from the source and checks in bulk on the target, so it can hit this problem at both ends. Magento's REST searchCriteria with sortOrders on created_at and currentPage/pageSize has the same weakness: MySQL doesn't guarantee an order among tied rows either. Catalog imports in Magento leave clusters of identical timestamps. searchCriteria accepts several sort orders, so add entity_id as the final one. For extraction straight from the database, use keyset paging on entity_id.

On the target side, a reconciliation step that pages Medusa by created_at can report success while it hides the very records that failed to import. The check passes because it looked at the wrong set. Every reconciliation pass should count unique IDs, not rows, and should compare them against the source ID list instead of trusting count. We cover this in more detail on our Magento 2 / Adobe Commerce to Medusa or Vendure page.

What to do this week

Search your integration code for order= and order: on Medusa list calls. Change any full traversal to sort on id. Add a unique-ID assertion to every export and sync job so the job fails loudly instead of dropping records. Subscribe to issue #17522 and re-run the check when a fix lands, including on the Index module path for Store products, which the reporter didn't test. If you're planning a move to Medusa or Vendure and want the extraction and reconciliation designed this way from the start, get in touch.