## TL;DR
Supabase upgrades sometimes move your Postgres to a host with newer collation libraries, which triggers the collation version mismatch warning. Open the Supabase SQL editor, run `REINDEX DATABASE postgres;` followed by `ALTER DATABASE postgres REFRESH COLLATION VERSION;`, and repeat for any other affected databases. The whole fix is two SQL statements; there is nothing to change in your app code.

## Error / query
```text
how to fix postgres collation mismatch on Supabase after an upgrade
```

## Steps

### Step 1: Confirm the mismatch in the SQL editor
Open your Supabase project, go to the SQL editor, and run:
```sql
SELECT datname,
       datcollversion AS recorded_version,
       pg_collation_actual_version(datcollate) AS library_version
FROM pg_database;
```
Expected: one or more rows (usually `postgres`, often your app database too) where the recorded version differs from the library version. Those are the databases to fix.

### Step 2: Reindex the postgres database
In the same SQL editor, run:
```sql
REINDEX DATABASE postgres;
```
Expected: the statement succeeds. The SQL editor runs each statement on its own, so the no-transaction-block rule for `REINDEX DATABASE` is satisfied automatically. This rebuilds indexes under the new collation.

### Step 3: Refresh the recorded collation version
```sql
ALTER DATABASE postgres REFRESH COLLATION VERSION;
```
Expected: success confirmation. The mismatch warning for the `postgres` database stops after this.

### Step 4: Repeat for your application database and template1
Run steps 2 and 3 again, swapping in each affected database name from step 1. Expected: every mismatched database now reports matching versions.

### Step 5: Verify in the dashboard logs
Re-run the step 1 query, then check Database and Logs in the Supabase dashboard for fresh entries. Expected: versions match everywhere and no new mismatch warnings appear.

## When to use
- Supabase logs or the dashboard show a collation version mismatch after a Supabase Postgres upgrade
- You just accepted a Supabase-proposed major version upgrade and the notice appeared
- The step 1 query shows version drift on a Supabase-hosted database

## When not to use
- The database is self-hosted or on another managed provider (same SQL works, but the dashboard steps and roles differ)
- The Supabase upgrade itself failed or the database wont start (fix the upgrade first)
- You are migrating between Supabase projects; collation there is a dump-and-restore concern

## Tool compatibility
Supabase Postgres (all current Postgres versions Supabase ships, 15 and newer for the refresh command); the Supabase dashboard SQL editor; the `postgres` role, which holds enough privilege on Supabase to reindex and alter its databases.

## Variant phrasings

### "Supabase database collation version mismatch warning after upgrade"
Same fix. The warning is informational but the reindex matters: stale indexes can misorder text queries silently.

### "supabase postgres upgrade collation warning reindex"
Yes, reindex first, then refresh. Refreshing without reindexing clears the warning but leaves the stale indexes in place.

### "do I need to do anything about collation mismatch on Supabase"
If the step 1 query shows drift, yes, run the two statements. It takes minutes on small databases and prevents wrong sort orders in text indexes.

## Why it happens
Supabase runs your Postgres on managed hosts, and a platform upgrade can move the database to hosts with newer glibc or ICU libraries. Postgres records the library version each database was built with and warns when the running libraries differ, because sort behavior may have changed. The fix rebuilds the collation-dependent indexes and records the new version.

## Edge cases
- Big databases: `REINDEX DATABASE` locks tables while it runs; on a large Supabase database, schedule it off-peak or reindex the biggest indexes concurrently first.
- The SQL editor times out on a very long reindex: connect with psql using your Supabase connection string and run it there instead, one statement at a time.
- Read replicas: the fix applies on the primary; replicas pick it up through normal replication.
- If Supabase upgrades you again later and the warning returns, the host libraries changed again; just repeat the two statements.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_RC2XreRu_VPEIvRUUyeucg