Full Text Search

Short Summary

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

New tables can be added with a single ecto migration. Search for results with a single function call:

  import Artemis.Helpers.Search

  User
+ |> search_filter(%{query: "search-value"})
  |> Repo.all()

Contributors

Name	GitHub
Chris Laskey	@chrislaskey
Jack Hefner III	@jmhthethird

Pattern Overview

PostgreSQL natively supports Full Text Search. Using an Ecto migration the following behaviours are defined:

1. A new tsv_search column and index is added to each table that will support search.
2. A PostgreSQL Coalesce function is created, which defines the custom list of table columns that should be searchable.
3. A PostgreSQL Trigger is defined to call the coalesce function and update the tsv_search value whenever a new row is inserted or updated.

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();
""")

Search Helpers

Functions

Helper functions are defined in Artemis.Helpers.Search.

These make it so adding a support for a search query can be done with a single addition of search_filter:

  import Artemis.Helpers.Search

  User
+ |> search_filter(%{query: "search-value"})
  |> Repo.all()

The search_filter function takes an existing Ecto query and a set of params. It:

• Verifies the query key is set in the params
• Splits the query into separate words
• Adds a search query for each word

Macros

Helper macros are also created to make ad-hoc querying Postgres Full Text Search values easier:

to_tsquery(query)
ts_rank_cd(tsv, query)

Discussion

Thoughts? Feedback? View the discussion thread for this pattern.