Full Text Search

Short Summary

Full Text Search is included on all resources by default. It uses PostgreSQL's native support.

Requires a single ecto migration to add to a new resource. Search for results with a single function call:

  params = %{query: "search-value"}

  User
+ |> search_filter(params)
  |> Repo.all()

Pattern Overview

PostgreSQL has native support for Full Text Search.

Value Proposition

The value of this pattern is:

• Search isn't exclusive. The search query can be used in combination with other Repo functions like order, select and where clauses.
• Minimal setup. Extending search to a new resource can be done in a single ecto migration. A single function call adds search capabilities to a Context.
• Fewer architecture pieces. Since PostgreSQL natively supports Full Text Search, additional dedicated search platforms like ElasticSearch are not required.

The downsides of this pattern are:

• Inherent design limitations of Full Text Search:
  • Limited support for phrases. Multi-word phrases are broken down into individual words, which are searched separately. This means word order is not guaranteed.
  • Limited support for partial searches. Partial searches only return results if the search value starts a word. Any search values for the middle or end of words will not return results. Can be mitigated by pairing with a combination of Trigram search and LIKE statements.

Implementation

Ecto Migration

View a complete migration file.

1. Create Search Data Column

Define a column to store full text search data

alter table(:users) do
  add :tsv_search, :tsvector
end

2. Create Search Data Index

Create a GIN index on the full text search data column

create index(:users, [:tsv_search], name: :users_search_vector, using: "GIN")

3. Define a Coalesce Function

Coalesce the searchable fields into a single, space-separted, value. In the example below the following user attributes are included in search:

• email
• name
• first_name
• last_name

execute("""
  CREATE FUNCTION create_search_data_users() RETURNS trigger AS $$
  begin
    new.tsv_search :=
      to_tsvector(
        'pg_catalog.english',
        coalesce(new.email, ' ') || ' ' ||
        coalesce(new.name, ' ') || ' ' ||
        coalesce(new.first_name, ' ') || ' ' ||
        coalesce(new.last_name, ' ')
      );
    return new;
  end
  $$ LANGUAGE plpgsql;
""")

4. Trigger the Function

Call the function on INSERT and UPDATE actions

execute("""
  CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  ON users FOR EACH ROW EXECUTE PROCEDURE create_search_data_users();
""")