For a postgres varchar column that I would very frequently search by prefix, what type of index should I be using?
select * from customer.customers where name like 'James%'
My table is something like
James Bond
James Blond
James Sunderland
David Copperfield
David Beckham
Use a B-tree index with an operator class that supports pattern matching:
CREATE INDEX ON customer.customers (name text_pattern_ops);
Your query is fine. You can even use a regular index on it:
create index idx_customer_name on customer(name);
This works because like will use a b-tree index when the pattern does not start with wildcards. For more general patterns, you need a GIST or GIN index.
