Rails, Angular, Postgres, and Bootstrap

Indexing Derived and Partial Values

Postgres allows you to create an index on transformed values of a column. This means we can create an index on the lowercased value for each of our three fields. Further, we can configure the index in a way that allows Postgres to optimize for the “starts with” search we are doing. Here’s the basic syntax:

CREATE INDEX
  customers_lower_last_name
ON
  customers (lower(last_name) varchar_pattern_ops);

If you’re familiar with creating indexes in general, the varchar_pattern_ops might look odd. This is a feature of Postgres called operator classes. Specifying an operator class isn’t required; however, the default operator class used by Postgres will only optimize the index for an exact match. Because ...