TIL: text_pattern_ops

the problem

you create a btree index on a text column and expect this to be fast:

select *
from users
where username like 'damar%';

but postgres ignores the index and does a seq scan. pain.

this happens bc default text comparison is locale-aware (collation rules, case ordering, etc). LIKE with patterns doesn’t align cleanly with that ordering.

what `text_pattern_ops` does

text_pattern_ops is an operator class that changes how text is indexed.

it tells postgres: “index this column using byte-wise comparison, optimized for pattern matching.”

create index idx_users_username_pattern
on users (username text_pattern_ops);

now this becomes index-friendly:

where username like 'damar%'

what it works (and doesn’t)

works:

like 'prefix%'
predictable ascii-ish comparisons
autocomplete-style queries

does NOT work:

like '%suffix'
ilike
regex
locale-sensitive sorting expectations

this is strictly about prefix matching.

when to use it

use text_pattern_ops when:

you rely on like 'foo%'
you want btree (not gin/trgm - future tils, lfg!)
collation correctness is less important than speed

don’t use it when:

you need full-text-ish search
you rely on linguistic ordering
you’re actually looking for substring matches

mental model

default text index → “human language rules”
text_pattern_ops → “raw bytes, go fast”

variations

varchar_pattern_ops
bpchar_pattern_ops

postgresql operator classes

text_pattern_ops