# The PostgreSQL Collation Trap That Breaks Fractional Indexing

2026-01-21

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.

```typescript
import { 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:

| Collation | Order | Why |
|-----------|-------|-----|
| JavaScript / `COLLATE "C"` | `Zz`, `a0`, `a1` | ASCII: `Z` (90) < `a` (97) |
| `en_US.utf8` (Postgres default) | `a0`, `a1`, `Zz` | Alphabetic: lowercase before uppercase |

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:

```sql
-- 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:

```typescript
// 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"`:

```sql
SELECT * FROM items ORDER BY index COLLATE "C";
```

With Drizzle ORM:

```typescript
import { 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:

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

## Check Your Database

Run this to see available collations:

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

And check your database's default:

```sql
SELECT 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.