Postgres and application sort order disagree ¶

A customer list is sorted by name. Page one ends with “Zimmermann”. Page two starts with “Ärztekammer” — a row that should have been near the beginning — and one customer never appears at all. Refreshing changes which one is missing.

Two different orderings are in play. The database sorted by its own collation to slice the page; the application re-sorted that page with a locale-aware collator for display. Neither sort is wrong, and together they cannot produce a coherent list.

Root cause: pagination is a promise about a total order ¶

LIMIT and OFFSET mean “the first twenty rows of a specific ordering” and “the next twenty of the same ordering”. The promise only holds if there is exactly one ordering. When the application re-sorts each fetched page, the rows on the page are rearranged locally while the page boundaries were drawn by a different order — so rows near a boundary land on the wrong side, appear twice, or disappear.

The two orders differ because the defaults differ. A text column with no explicit collation orders by whatever the column inherited, which on many clusters is a byte-order collation where every uppercase letter precedes every lowercase one and accented letters sort after z. An Intl.Collator orders the way a reader of that language expects. Both are self-consistent; neither is a refinement of the other.

The failure is intermittent because it depends on where the boundary falls. A list whose page boundary happens to sit between two unambiguous names looks fine, and the same query with one more row inserted does not.

Minimal reproducible example ¶

-- The database's idea of order
SELECT name FROM customers ORDER BY name LIMIT 5;
--  Ärztekammer   ← or last, depending on the inherited collation
--  Zimmermann

// The application's idea of order, applied to the page it received
rows.sort(new Intl.Collator('de').compare);   // rearranges within the page only

Run the two together across three pages and rows will move between them.

The fix: choose one ordering and put it where the paging happens ¶

If the database paginates — which is the usual and the scalable arrangement — the database must own the order, and the collation has to be named explicitly on the column so it is not inherited from a server-wide setting nobody chose.

-- PostgreSQL: an ICU collation matching the reader's language
CREATE COLLATION IF NOT EXISTS de_icu (provider = icu, locale = 'de-DE');
ALTER TABLE customers ALTER COLUMN name TYPE text COLLATE de_icu;

-- The index must be built for the same collation, or the sort cannot use it
CREATE INDEX customers_name_de ON customers (name COLLATE de_icu);

The application then renders the rows in the order it received them and does not sort at all.

// Display only. The order is the server's.
return rows.map((r) => <Row key={r.id} {...r} />);

Serving several reader locales from one table ¶

The awkward case is a product whose readers expect different orderings of the same data — a German reader wanting ä with a, a Swedish reader wanting it after z.

Three arrangements work, with different costs.

Sort per query with a collate expression. ORDER BY name COLLATE sv_icu is correct and cannot use an index built for a different collation, so it sorts the whole matching set each time. Acceptable for small tables and filtered result sets; expensive for a large unfiltered list.

Index per locale. One index per collation you support, and the query names the matching one. Fast, and it costs write throughput and storage proportional to the number of locales — reasonable at three, unreasonable at thirty.

Sort in the application, and page in the application. Fetch the whole set, collate it for the reader, and slice locally. Only viable when the set is genuinely small, and it is the arrangement people reach for accidentally when they add a client-side sort to a server-paginated list.

The decision is usually made by data size. What matters is that it is a decision: the failure at the top of this page comes from two of these being active at once.

Why OFFSET makes it worse ¶

Even with a single ordering, offset pagination is fragile under writes. A row inserted before the current offset shifts every subsequent page by one, so a reader paging through a changing list sees a row twice or misses one — the same symptom as a collation mismatch, with a different cause.

Keyset pagination removes it by paging on the ordering key rather than on a count:

-- Page after the last row seen, in the same collation
SELECT name, id FROM customers
WHERE (name COLLATE de_icu, id) > ($1 COLLATE de_icu, $2)
ORDER BY name COLLATE de_icu, id
LIMIT 20;

The tie-break on id matters: names are not unique, and two rows with the same name would otherwise make the boundary ambiguous. Adding a unique tie-break to every ordering is worth doing regardless of collation.

Spotting it before a customer does ¶

The symptom reaches support as “a record disappeared”, which is why this bug survives so long: nobody reports a sorting problem, they report missing data.

Three signals distinguish it from an actual data loss. The row reappears on a different page, which no deletion explains. The affected rows cluster around page boundaries rather than being distributed randomly. And the set of affected rows changes when the page size changes, because a different page size draws the boundaries in different places — which is the single most diagnostic test available and takes one query parameter to run.

A cheap detector belongs in the test suite regardless. Paging through a fixture list of a few hundred names, accumulating the ids seen, and asserting that the result contains every id exactly once catches every variant of this problem — collation mismatch, offset instability, and a missing tie-break — without needing to know which one it is.

The same check is worth running against a staging database with real data occasionally, because production data contains name shapes no fixture does: names with leading spaces, names in scripts nobody planned for, and the single-character names that end up adjacent under one collation and far apart under another.

Verification ¶

test('database and application agree on order', async () => {
  const fromDb = await db.query(
    'SELECT name FROM customers ORDER BY name COLLATE de_icu'
  );
  const inApp = [...fromDb.rows.map((r) => r.name)]
    .sort(new Intl.Collator('de').compare);

  expect(fromDb.rows.map((r) => r.name)).toEqual(inApp);
});

If that test fails, the two collations genuinely differ and no amount of pagination logic will reconcile them — pick one and delete the other. Running it against a fixture containing accented, uppercase and numeric-suffixed names is what makes it meaningful; a list of plain lowercase ASCII names passes under every collation.

-- Which collation is this column actually using?
SELECT attname, collname FROM pg_attribute a
JOIN pg_collation c ON a.attcollation = c.oid
WHERE attrelid = 'customers'::regclass;

When to escalate ¶

If the orders agree and pagination still skips rows, the cause is concurrent writes rather than collation — move to keyset pagination.

If an ICU collation is unavailable, the database may have been built without ICU support. That is a server-level constraint, and the fallback is to sort in the application and page there too, which is a size question rather than a correctness one.

If ordering is correct but slow, check whether the query is using an index built for the same collation. A collate expression that does not match any index turns an indexed scan into a full sort, which is the usual explanation for a locale-aware ordering being an order of magnitude slower — the performance note in Unicode text handling and collation.

FAQ ¶

Related ¶

• Unicode Text Handling & Collation — the collation model this page applies to a database.
• Search not matching accented names — the matching counterpart to ordering.
• Duplicate usernames from Unicode normalization — the same equivalence question applied to identity.
• Date & Number Formatting Standards — why a collator, like a formatter, is built once and reused.

Part of Unicode Text Handling & Collation.