.tip

A B-tree can't help with a pattern that starts with a wildcard, so WHERE name LIKE '%acme%' scans the whole table. The trigram extension indexes three-letter chunks and turns it into an index scan.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);

The same index serves ILIKE and similarity search. For real full text search, use tsvector. For "find the row whose name contains this", use trigrams.