Text, Collation, and the Sort-Order Surprise

In Postgres, text, varchar and varchar(n) are the same storage with the same performance: nothing separates the character types beyond the extra space a blank-padded char(n) wastes and the few CPU cycles a length check costs. Arguing about varchar(255) is arguing about nothing.

What does change behaviour is the collation — the locale rules attached to a column that decide whether 'apple' < 'Banana', whether a B-tree index can serve a LIKE 'basmati%' prefix search, and whether an operating-system upgrade invalidates every text index in the cluster overnight. Nadia's audit finds nothing wrong with the types on Cartwheel's text columns and two things badly wrong with their collations.

text, varchar(n), and char(n)

All three are variable-length values with a header of one byte for strings up to 126 bytes and four bytes above that, capped at about 1 GB. varchar(n) adds a length constraint enforced on write; the maximum n you can declare is 10,485,760. char(n) blank-pads every value to the full width and is, in the documentation's own words, usually the slowest of the three because of that storage cost. It exists for portability and for nothing else.

The practical consequence is that a length limit is a business rule, not a storage decision, so it should be written where business rules go.

-- the same guarantee, but the rule is named and can be changed
-- without rewriting the column's type
ALTER TABLE products
  ADD CONSTRAINT products_sku_len CHECK (length(sku) <= 32);

Reach for varchar(n) only when an external contract — an EDI feed, a payment provider's field width — is the thing being enforced. Changing varchar(32) to varchar(64) is also cheap in modern Postgres, but the constraint form documents why the limit exists and puts it beside the other rules on the table. A named CHECK can be dropped, replaced or relaxed with a catalogue change and a validation scan.

What a Collation Decides

A collation is the rule set that comparison uses. It decides the order ORDER BY name produces, and with a nondeterministic collation it can decide equality too. Under C, comparison is raw byte comparison, so every uppercase letter sorts before every lowercase one and 'Zebra' comes before 'apple'. Under en_US.UTF-8 the ordering is dictionary order: case and punctuation are weighted rather than decisive, and 'apple' comes first.

SELECT name FROM products ORDER BY name COLLATE "C";
 Zebra Mussels (frozen)
 apple, Braeburn
 Apricot Jam

SELECT name FROM products ORDER BY name COLLATE "en_US.UTF-8";
 apple, Braeburn
 Apricot Jam
 Zebra Mussels (frozen)

Neither ordering is wrong. One is the order a machine wants and the other is the order a shopper wants, and a schema that never decides which it needs ends up with whatever the cluster's initdb locale happened to be. Collation can be set per database, per column, per index and per expression, and the per-column setting is the one that should carry the decision: products.sku is a machine identifier and belongs in C, products.name appears in a sorted list a human reads and belongs in a real locale.

Providers: libc, ICU, and the Built-in

The rules themselves come from a provider. libc, the default, hands the comparison to the operating system's C library, which means the ordering is a property of the host rather than of the database. icu uses the ICU library, which records a version number Postgres can store and check. The builtin provider arrived in 17 with the C and C.UTF-8 locales and gained PG_UNICODE_FAST in 18; it is implemented inside Postgres, so its behaviour cannot drift when the host is patched.

That difference is the whole argument. A libc collation is a promise made by a shared library that a package manager can replace on a Tuesday. An ICU collation carries its version in pg_collation, so Postgres can compare what it was built with against what the host now offers. The built-in provider has nothing to compare: its rules ship inside the server binary and move only when the server does.

The Upgrade That Breaks Indexes

glibc 2.28, released in August 2018, shipped a major rewrite of its locale data. Debian 10, Ubuntu 18.10 and RHEL 8 all crossed that line, and every cluster that moved with them inherited a set of text indexes sorted by rules that no longer exist. The project wiki's upgrade guidance is to reindex every index on a text, varchar, char or citext column before the instance goes back into production.

The failure mode is the dangerous kind. A B-tree binary-searches on the assumption that the index is sorted the way the current comparison function sorts, so when the rules move underneath it, a lookup walks into the wrong subtree and returns nothing. The row is physically present. The query says there is no such customer. Nothing is logged, nothing errors, and the only visible symptom is data that seems to have disappeared.

WARNING:  collation "xx-x-icu" has version mismatch
DETAIL:  The collation in the database was created using version 1.2.3.4,
         but the operating system provides version 2.3.4.5.
HINT:  Rebuild all objects affected by this collation and run
       ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, ...

Postgres can only raise that warning when it recorded a version in the first place: ICU supplies one on every platform, while libc supplies one only on the GNU C library, FreeBSD and Windows. Refreshing first hides the problem without fixing it: the refresh does not verify that anything was actually rebuilt. The order of operations therefore matters as much as the commands — rebuild every affected index with REINDEX, and only then run ALTER COLLATION … REFRESH VERSION to stamp the new version and silence the warning.

Prefix Search and text_pattern_ops

Cartwheel's product search runs WHERE name LIKE 'straw%' and sequential-scans products every time, with an index on name sitting right there. The planner is not broken. Under a non-C collation the index order is dictionary order, and a prefix match walks the string character by character, so the index's ordering is not the ordering the pattern needs.

CREATE INDEX products_name_pattern_idx
    ON products (name text_pattern_ops);

-- serves LIKE 'straw%' and left-anchored regular expressions;
-- ORDER BY name still needs the ordinary index

The text_pattern_ops operator class compares character by character instead of by locale rules, so a left-anchored pattern can walk it. It does not replace the default index. A column that is both searched by prefix and sorted for humans wants two indexes, one for each job, and pays for both on every insert. In a database that genuinely uses the C locale none of this applies: the default operator class already serves pattern matching there.

Case-Insensitive Done Right

Cartwheel's customers.email is compared with lower() in the application and stored as typed, so the unique constraint happily accepts two accounts whose addresses differ only in case, and half the login queries miss. The fixes cost different things. A unique index on lower(email) is explicit, needs no extension, and enforces the rule in the database. The citext extension makes the type itself case-insensitive by calling lower internally, at the cost of an extension dependency and slightly slower comparisons — its own documentation notes that citext cannot use B-tree deduplication and copies data to fold case. A nondeterministic ICU collation is the most elegant of the three and the most restrictive: comparison is slower, a B-tree on it cannot deduplicate, and some pattern-matching operations are still not possible — 18 added LIKE, not the whole set.

CREATE UNIQUE INDEX customers_email_lower_key
    ON customers (lower(email));

-- the query must use the identical expression to match the index
SELECT id FROM customers WHERE lower(email) = lower('Nadia@Cartwheel.example');

Cartwheel takes the first option, and the reason is the last line of it: the index is only used when the query writes the same expression the index was built on. The uniqueness rule now lives in the database rather than in one service's login handler, and Chapter 8 returns to expression indexes as a general tool. An application that switches to email ILIKE $1 gets a sequential scan and no error, so the expression has to be part of the contract rather than a convention.