PostgreSQL Collation Guide: How to Change VARCHAR Collation Safely

Collation in PostgreSQL is not a cosmetic setting. It determines how VARCHAR and TEXT values are compared, sorted, grouped, and validated by indexes. Changing it can improve multilingual search and ordering, but it can also change query results, break assumptions in unique indexes, and cause production locks if handled carelessly.

TLDR: To change a VARCHAR collation safely, identify the current column collation, test the new collation against real data, review affected indexes and constraints, then apply the change during a controlled migration window. For example, changing a customer name column from "C" to "en-US-x-icu" may make ORDER BY last_name more natural for users, but it may also alter index behavior. In one typical SaaS customer table with 2 million rows, a direct migration may take minutes and block writes, while a staged “new column plus backfill” approach can reduce exclusive lock time by more than 90%.

What PostgreSQL Collation Actually Controls

A PostgreSQL collation defines language-specific rules for comparing strings. These rules affect operations such as:

• ORDER BY on VARCHAR or TEXT columns
• GROUP BY and equality comparisons in some contexts
• LIKE, pattern matching behavior, and range scans
• Unique indexes, especially with ICU nondeterministic collations
• B-tree index ordering and query plans

The most important point is this: changing a column collation changes the rules, not the stored characters. The bytes in your VARCHAR values usually remain the same, but the database may sort and compare them differently afterward.

Check the Current Collation Before Changing Anything

Start by inspecting the current column definition. Do not assume the database default is the column collation, because a column can have its own explicit collation.

SELECT
  table_schema,
  table_name,
  column_name,
  data_type,
  character_maximum_length,
  collation_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'customers'
  AND column_name = 'last_name';

If collation_name is NULL, the column is using the database default collation. You can check the database default with:

SELECT datname, datcollate, datctype
FROM pg_database
WHERE datname = current_database();

You should also confirm which collations are available on your server:

SELECT collname, collprovider, collcollate, collctype
FROM pg_collation
WHERE collname ILIKE '%en%'
ORDER BY collname;

On modern PostgreSQL systems, you may see provider values such as c for libc and i for ICU. ICU collations are generally preferable for portable, language-aware behavior, but availability depends on how PostgreSQL was built and installed.

Understand the Risk: Indexes, Uniqueness, and Query Results

The dangerous part of a collation change is not the syntax. The dangerous part is the semantic change.

Suppose your application stores user handles in a VARCHAR column and has a unique index. Under one collation, two values might be treated as distinct. Under another, especially a case-insensitive or accent-insensitive ICU collation, they may compare as equivalent. That can cause a unique index rebuild to fail.

Before making the change, review dependent indexes:

SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'customers'
  AND indexdef ILIKE '%last_name%';

If the column participates in a unique index, run a duplicate-risk check that reflects the intended comparison behavior. For a simple case-folding example:

SELECT lower(last_name), COUNT(*)
FROM public.customers
GROUP BY lower(last_name)
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

This is not a perfect simulation of every collation, but it can expose obvious conflicts before the migration reaches production.

The Direct Method for Small Tables

For small tables, the simplest method is often acceptable. You can alter the column type while specifying the new collation:

ALTER TABLE public.customers
ALTER COLUMN last_name
TYPE varchar(120)
COLLATE "en-US-x-icu";

If the column is already varchar(120), the type stays the same, but the declared collation changes. You should still treat this as a schema migration that may require locks and index work.

A cautious direct migration looks like this:

1. Test the statement on a restored production snapshot.
2. Measure execution time and lock behavior.
3. Check affected indexes and constraints.
4. Schedule the production change during a low-traffic window.
5. Run validation queries immediately afterward.

For very small tables, this may be enough. For large or heavily written tables, it is usually not.

A Safer Pattern for Large Tables

For large production tables, consider using a staged migration. The idea is to avoid holding a long exclusive lock while transforming a critical column.

A typical staged approach is:

1. Add a new column with the desired collation.
2. Backfill it in batches.
3. Create supporting indexes concurrently.
4. Keep the old and new columns synchronized temporarily.
5. Perform a short final swap during a maintenance window.

Example:

ALTER TABLE public.customers
ADD COLUMN last_name_new varchar(120)
COLLATE "en-US-x-icu";

Then backfill in controlled batches from your application, migration tool, or a server-side process:

UPDATE public.customers
SET last_name_new = last_name
WHERE last_name_new IS NULL
LIMIT 10000;

PostgreSQL does not support LIMIT directly in a plain UPDATE in all forms, so in practice you may use a primary-key range or a common table expression:

WITH batch AS (
  SELECT id
  FROM public.customers
  WHERE last_name_new IS NULL
  ORDER BY id
  LIMIT 10000
)
UPDATE public.customers c
SET last_name_new = c.last_name
FROM batch
WHERE c.id = batch.id;

This reduces pressure on locks, WAL generation, replication lag, and autovacuum. It also gives you a safe rollback path: if something looks wrong, you still have the original column untouched.

Rebuild or Recreate Indexes Carefully

When collation changes affect indexed string columns, indexes need special attention. If you are creating a new index on the new column, use CONCURRENTLY where possible:

CREATE INDEX CONCURRENTLY idx_customers_last_name_new
ON public.customers (last_name_new);

For an existing index that must be rebuilt, PostgreSQL supports:

REINDEX INDEX CONCURRENTLY idx_customers_last_name;

This avoids blocking normal reads and writes for most of the operation, although it still has restrictions and brief lock moments. Also remember that CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY cannot run inside a normal transaction block.

If the index is unique, test thoroughly before creating it. A new collation may reveal duplicates that were previously allowed. In that case, the correct fix is not to force the index through; it is to define the business rule, clean the data, and only then enforce uniqueness.

Validate Application Behavior, Not Just SQL Syntax

After changing collation, run application-level checks. Focus on screens or APIs that use sorting, filtering, autocomplete, pagination, and search.

Important checks include:

• Does ORDER BY now produce the expected human-friendly order?
• Do paginated results remain stable between page requests?
• Are unique constraints still valid under the new comparison rules?
• Did query plans change after index recreation?
• Did replication lag or table bloat increase during migration?

Run ANALYZE after major data movement so the planner has current statistics:

ANALYZE public.customers;

Do Not Ignore Collation Version Changes

PostgreSQL tracks collation versions for some providers, especially ICU. Operating system or ICU library upgrades can change collation behavior. After such upgrades, PostgreSQL may warn that a collation version has changed.

You can inspect collation versions with:

SELECT collname, collversion
FROM pg_collation
WHERE collversion IS NOT NULL;

If PostgreSQL reports a version mismatch, you may need to rebuild affected indexes and refresh the collation version:

ALTER COLLATION "en-US-x-icu" REFRESH VERSION;

Use this only after reviewing the PostgreSQL documentation for your version and rebuilding dependent objects as needed. Refreshing the version records acceptance of the new behavior; it does not magically validate your indexes.

Recommended Production Checklist

• Inventory: identify all columns, indexes, constraints, views, and queries affected by the collation.
• Test: restore a production snapshot and run the migration there first.
• Measure: record migration time, locks, WAL volume, and replication lag.
• Validate data: check for duplicate risks before rebuilding unique indexes.
• Plan rollback: keep the old column or a restorable backup until validation is complete.
• Communicate: schedule a maintenance window if exclusive locks are possible.

Conclusion

Changing VARCHAR collation in PostgreSQL is a serious schema change because it can alter the meaning of comparisons and the behavior of indexes. The safest approach is to treat it as a data migration, not a quick metadata edit. For small tables, a direct ALTER TABLE may be acceptable after testing. For large production systems, a staged migration with backfill, concurrent index creation, and careful validation is usually the more reliable path.