Data Connect now supports full-text search, powered by Postgres. Full-text search lets you quickly and efficiently locate information within large datasets by searching for keywords and phrases across multiple columns at once.

There are many ways to search within a database, and using Data Connect, you can do search using:

• String pattern filters: matches exact text or regular expressions
• Vector similarity search: finds semantically similar rows for a single column (for example, recommendations or “More like this”)
• Full-text search: looks for data across multiple columns based on words and phrases rooted in the rules of a particular language (think your favorite search engine)

Let’s use the movie app to talk about examples for each of these searches.

String pattern filters

When you use a string or a regular expression to search, the search results will only contain that exact string. To that note, capitalization is important, and the search will find exact character matches. For example, if you search “justice”, only movies containing “justice” will come up. You can add as many filters as you want, so on top of filtering for “justice”, you can also search for movies with release year between 1900 and 2030 with a rating between 1 and 10.

Code example

To perform string pattern filters, use where in your query:

query SearchForMovies(
  $input: String
  $minYear: Int!
  $maxYear: Int!
  $minRating: Float!
  $maxRating: Float!
) @auth(level: PUBLIC) {
  moviesMatchingTitle: movies(
    where: {
      _and: [
        { releaseYear: { ge: $minYear } }
        { releaseYear: { le: $maxYear } }
        { rating: { ge: $minRating } }
        { rating: { le: $maxRating } }
        { title: { contains: $input } }
      ]
    }
  ) {
    id
    title
    genre
    rating
    imageUrl
  }

Vector similarity search

Vector similarity search is a bit more advanced: with the help of Vertex AI, Data Connect will look for content that is similar to the searched text. Let’s say your user is in the mood for a “romantic and sciency” movie. To see which movie descriptions match this complex mood, you can use vector similarity search.

As a side note, each movie also has a genre. In the case of vector search, you can only find matches for one column - in this example, the search goes through the description column. If you want to look in the genre column, you can do that instead, but you can’t look at both at the same time.

Code example

To perform vector search, you need to add a new field of Vector type in your schema. Using the example above to look for “romantic and sciency” movies in the description, add a field to hold the vector embeddings associated with the movie description. In this schema, descriptionEmbedding is added to store vector embeddings for the description field:

type Movie @table {
   id: ID! @col(name: "movie_id") @default(id: ID! @col(name: "movie_id") @default(expr: "uuidV4()")
   title: String!
   description: String
   descriptionEmbedding: Vector! @col(size:768)
   # other fields
}

Full-text search

Full-text search is even more advanced, as it can search an entire table across columns to find words, phrases, or numbers. It performs lexical stemming, which helps match different forms or tenses of the same word.

This is different from:

• string pattern filters, because it’s not looking for only exact matches
• vector similarity search, because it can search only in one column and requires Vertex AI. Full-text search searches across multiple columns and doesn’t need Vertex AI.

Different query formats

When you use full-text search, there are different query formats you can use:

• QUERY: familiar semantics to web search engines (e.g. quoted strings, AND and OR)
• PLAIN: matches all of the words but not necessarily together (“brown dog” will match “the brown and white dog” or “the white and brown dog”)
• PHRASE: matches an exact phrase (“brown dog” will match “the white and brown dog” but won’t match “brown and white dog”)
• ADVANCED: you can create complex queries using the full set of tsquery operators.

QUERY is the default, and if you don’t specify a queryFormat in your query, this is the format that gets used.

Let’s say you wanted to look for movies that have “justice AND not detective”. You’ll use QUERY in this case:

Now, let’s say you want to search for “crimson rising” - you can use either PHRASE or PLAIN, depending on what result you want. If you’re looking for an exact match of the phrase, use PHRASE, otherwise use PLAIN. Here, using PLAIN shows a result of “Rise of the Crimson Empire”:

Code example

To add full-text search, include the @searchable directive to the String in your schema that you want to search over. For example:

type Movie
  @table {

  # The fields you want to search over
  title: String! @searchable
  genre: String @searchable
  description: String @searchable
  tags: [String]

  # Some other fields that aren't searched over
  rating: Float
  imageUrl: String!
  releaseYear: Int
}

Once you add this directive, you can then perform full-text search by adding the <pluralType>_search field to a query. In this case, it’ll be movies_search:

query SearchMovies($query: String) @auth(level: PUBLIC) {
  movies_search(query: $query, queryFormat: PLAIN) {
    id
    title
    imageUrl
    releaseYear
    genre
    rating
    tags
    description
  }
}

As mentioned before, queryFormat can be QUERY, PHRASE, PLAIN, or ADVANCED. If queryFormat is not specified, QUERY is the default.

Why not always use full-text search?

If full-text search is the most advanced search and seemingly covers the use cases for string pattern filters and vector similarity search, why not just always use full-text search?

That’s a great question. There are two main main reasons:

• vector similarity search is better for when you want the semantic meaning of your search words to matter more than the actual words used to perform the search
• resource cost

Semantic meaning

Vector similarity search works better than full-text search when the meaning behind the search is more important than the actual words used for the search. For example, if you wanted to search for car insurance, vector similarity search might return results that include the words “vehicle” or “automobile”. Full-text search returns results with “car”, because it doesn’t look for words with similar meanings to perform your search nor does it use the underlying idea or concept of a query. Vector similary search does.

Resource cost

As is common in software development, more advanced solutions usually come at a cost of resources - and that’s one of the considerations when choosing which search to use.

String pattern filters use the least memory and disk space since it does not require any indexes or generated columns. However, it is less performant when searching larger documents.

Full-text search and vector similarity search are both performant over large documents, but full-text search adds memory and storage overhead to store a generated column and an index for each search.

As a result, while full-text search is ultimately a good choice for searching, it does come with tradeoffs. If memory and storage overhead (which usually translates into additional expenses for apps with many users) is a concern, you might choose either string pattern filters or vector similarity search, depending on what you’re searching for.

The documentation summarizes the differences among full-text search, vector similarity search, and string pattern filters.

Conclusion

Full-text search gives you powerful functionality to locate information in your apps’ data, and we’re incredibly excited to see what you build with it! Your feedback is invaluable as we use it for our roadmap going forward, so please continue to share your thoughts and requests on Firebase’s UserVoice!