The PostgreSQL Collation Trap That Breaks Fractional Indexing

If you're using the fractional-indexing library with PostgreSQL, there's a subtle bug waiting to bite you in production.

What Makes Fractional Indexing Cool

Traditional array reordering requires updating every item's position after the moved element. Move item 5 to position 2? Now you need to update positions 2, 3, 4, and 5.

Fractional indexing solves this elegantly: instead of integers, you use lexicographically-sortable strings. To insert between "a0" and "a1", you generate "a0V". Move to the beginning? Generate "Zz". The key insight: the client computes the new index, sends it to the server, and the server just stores it. No locks, no position recalculation, no n+1 updates.

Copyimport { generateKeyBetween } from "fractional-indexing";

// Insert at the beginning (before "a0")
generateKeyBetween(null, "a0");  // → "Zz"

// Insert between "a0" and "a1"  
generateKeyBetween("a0", "a1");  // → "a0V"

// Append to end (after "a1")
generateKeyBetween("a1", null);  // → "a2"

The library guarantees these keys sort correctly with standard string comparison—as long as you're comparing them the same way JavaScript does.

The Bug: PostgreSQL Doesn't Sort Strings Like JavaScript

JavaScript uses Unicode code point comparison (essentially ASCII/byte ordering). PostgreSQL, by default, uses locale-aware collation.

The difference:

When you prepend an item, fractional-indexing generates "Zz". JavaScript expects this to sort before "a0". But PostgreSQL with en_US.utf8 collation puts it after.

How This Breaks Your App

The failure mode is insidious. Everything works in development (Docker Postgres often defaults to C collation), then breaks in production (cloud Postgres typically uses en_US.utf8).

When you query items ordered by their fractional index:

Copy-- What you expect: Zz, a0, a1
-- What you get:    a0, a1, Zz
SELECT * FROM items ORDER BY index;

Now your frontend receives items in the wrong order. Worse, when you try to generate a new key:

Copy// Frontend thinks the order is: Zz, a0, a1
// But database returned: a0, a1, Zz
// User drags to insert between "a1" and "Zz"
generateKeyBetween("a1", "Zz");  
// 💥 Error: "a1 >= Zz" - library expects a1 < Zz

The Fix: Explicit Collation

Force ASCII byte ordering with COLLATE "C":

CopySELECT * FROM items ORDER BY index COLLATE "C";

With Drizzle ORM:

Copyimport { asc, sql } from "drizzle-orm";

// ❌ Uses database default collation
orderBy: asc(items.index)

// ✅ Explicit ASCII ordering
orderBy: asc(sql`${items.index} COLLATE "C"`)

For btree indexes, specify collation in the index definition:

Copyindex("idx_items_index").on(sql`${table.index} COLLATE "C"`)

Check Your Database

Run this to see available collations:

CopySELECT collname, collencoding 
FROM pg_collation 
WHERE collname IN ('C', 'POSIX', 'en_US.UTF-8', 'en_US.utf8');

And check your database's default:

CopySELECT datcollate FROM pg_database WHERE datname = current_database();

Prevention

1. Always use COLLATE "C" when ordering fractional index columns
2. Create indexes with explicit collation for query performance
3. Test with production-like collation settings in CI
4. Document the gotcha for your team—this will bite someone else

The lesson: when a library promises "lexicographic ordering," make sure your database agrees on what that means.