Custom string collation (comparator function) for backends Intl.Collator can't match, e.g. Postgres glibc

[daveycodez]

Follow-up to #611. stringSort: 'locale' with localeOptions covers collations that Intl.Collator can express, but a common backend collation is not one of them, and there is no way to plug in the backend's own comparator.

The problem

Postgres databases on glibc's en_US.UTF-8 (the default for many hosted Postgres, including the one behind InstantDB) order text in multiple levels:

1. Letters and digits only, with spaces, punctuation and symbols ignored.
2. Accents, then case.
3. The ignored characters by position.

So +3 power sorts under "3", and pillowfort sorts before pillow fort.

Intl.Collator can ignore punctuation (ignorePunctuation: true), but not symbols. The Unicode extension that would do it (-u-ka-shifted-kv-symbol) is dropped by V8 and JavaScriptCore. On 5,519 real rows, the closest Intl setting misplaced a run of rows starting with symbols. A synced collection whose local order differs from the server's gets wrong windows: a pushed ORDER BY … LIMIT n returns the server's first n, and the local query sorts them differently.

Proposal: a custom string collation

defaultStringCollation: { stringSort: 'custom', compare: (a: string, b: string) => number }
// and per clause
q.orderBy(({ s }) => s.name, { direction: 'asc', stringSort: 'custom', compare })

We're running this as a patch on 0.9.2. It's small:

• ascComparator calls opts.compare(a, b) for strings when stringSort === 'custom'.
• buildCompareOptionsFromConfig and the builder's orderBy pass compare through, and expression-helpers copies it.
• BaseIndex.matchesCompareOptions matches a custom index only to the same compare function.
• usesLocaleStringSort in index-optimization becomes stringSort !== 'lexical', so custom collations aren't range-optimized.
• canExpressCursorOrder already only allows lexical, so custom collations fall back as locale does.

Happy to open a PR.

A related gap: comparisons ignore the collation

gt/gte/lt/lte evaluate strings with compareValues (code units) whatever the collection's collation, while SQL compares text in the column's collation. That's why cursor-based loading is limited to lexical strings. It also means a keyset predicate like or(gt(name, v), and(eq(name, v), gt(id, k))) can't be evaluated locally in the order the rows are sorted in. Deep pages over a non-lexical string sort have to load their prefix.

Would you consider collation-aware string comparisons, for example comparison operators that use the collection's collation (or an expression-level collate)? With that, string sorts could use cursor loading under any collation.

[daveycodez]

Update on the second half, collation-aware comparisons. We've patched that too, and it removed the prefix loads entirely:

• gt/gte/lt/lte take an optional third argument, a comparator: gt(ref, value, compare). The evaluator uses it when both operands are strings and otherwise compares as before. Every existing two-argument call is unchanged.
• followsBoundary passes compareOptions.compare for string values under stringSort: 'custom', and canExpressCursorOrder accepts custom when it has a comparator. TanStack's own cursor loading then works for custom string collations.

We checked for a way to do this without patching. fn.where can express the comparison, but it marks the ordered source requiresFullSource, so the whole collection loads. An index compareFn orders the index but not query results or comparisons.

With both parts, a deep page of a text-sorted list over a Postgres (glibc en_US) backend loads only its own rows: about 50 KB instead of 1.6 MB at page 60 of 5,500 rows, and exact against the server's order in both directions. Happy to turn both into a PR.