---
title: "Rebuild text indexes when the collation · Agent Wranglers"
url: https://agentwranglers.se/playbook/data/operations/collation-versions
updated: 2026-10-21
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Rebuild text indexes when the collation library changes

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Operations](https://agentwranglers.se/playbook/data/operations.md).

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers). Updated 2026-10-21.

- Level: Recommended
- Chapter: [Operations](https://agentwranglers.se/playbook/data/operations)
- Rule: Treat an operating system or ICU upgrade under a database as a change to its sort order. Rebuild the affected text indexes before the database takes writes again.
- How: A text index stores rows in the order its collation library sets. The PostgreSQL docs warn that changed collation definitions can corrupt indexes, such as after an upgrade to a new major OS version. Then lookups miss rows and unique indexes let duplicates in. PostgreSQL warns of a collation version mismatch, but keeps running. After each upgrade, run the queries below, `REINDEX` every object they list, then run `ALTER COLLATION ... REFRESH VERSION`. For the database default, run `ALTER DATABASE ... REFRESH COLLATION VERSION`. A new database can use the builtin provider with `C.UTF-8` or `PG_UNICODE_FAST`, which compare by code point. Sort for people with a locale collation in the query instead, as [Sort and compare text by locale](https://agentwranglers.se/playbook/website/languages-and-regions/locale-collation) says.
- Check: After each operating system or database upgrade, both queries below return no rows on every database.

## Examples

**Using these examples:** they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

### SQL example

```sql
-- Objects that depend on a collation whose version changed, expect no rows
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS collation,
       pg_describe_object(classid, objid, objsubid) AS object
FROM pg_depend d JOIN pg_collation c ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid);
-- Databases whose default collation changed, expect no rows
SELECT datname FROM pg_database WHERE datcollversion <> pg_database_collation_actual_version(oid);
```

### Shell example

```sh
# Rebuild, then record the new version
psql shop -c 'REINDEX DATABASE CONCURRENTLY shop'
psql shop -c 'ALTER DATABASE shop REFRESH COLLATION VERSION'
```

## Sources

- [PostgreSQL ALTER COLLATION](https://www.postgresql.org/docs/current/sql-altercollation.html)
- [PostgreSQL locale support](https://www.postgresql.org/docs/current/locale.html)

## Related rules

- [Sort and compare text by locale](https://agentwranglers.se/playbook/website/languages-and-regions/locale-collation) in Website

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/data/operations/collation-versions">Agent Wranglers data playbook</a>`. In code you write, add a comment next to it with the same link.

- Previous: [Turn on checksums and check for corruption](https://agentwranglers.se/playbook/data/operations/corruption-checks)
- [Operations](https://agentwranglers.se/playbook/data/operations)