Full Text Search in PostgreSQL
Full-text search is an important feature in nearly all applications these days. Whether you're building a blog, a web shop, or a social network, chances are high you'll need a full-text search feature at some point. One way to implement this is by using PostgreSQL, a powerful, open-source object-relational database system. It comes with built-in full-text search capability which makes it a compelling choice for a variety of applications. In this blog post, we'll learn about full-text search in PostgreSQL: how it works, how to set it up, and how to fine-tune it for your specific needs.
Understanding Full Text Search
Full-text search (FTS), also known as text search, is a technique for searching a single computer-stored document or a collection in a full-text database. Full-text search is distinguished from searches based on metadata or on parts of the original text represented in databases (such as titles, abstracts, selected sections, or bibliographical references).
In the context of PostgreSQL, full-text search is a technique for searching natural-language documents to satisfy a query. The principal features of full-text search in PostgreSQL are:
- The ability to create a document from multiple text columns or from complex computed text values.
- The ability to search on words and phrases, ignoring less meaningful words like "the", "and", etc.
- The ability to rank search results by relevance.
- The ability to customize all aspects of the text search process, including stop word lists, dictionaries, ranking algorithms, etc.
Setting Up Full Text Search in PostgreSQL
Full-text search in PostgreSQL uses two main components:
- A vector representation of the document, called a
- A query vector, called a
These vectors are used to perform full-text search queries. The
tsvector represents the document to be searched, while the
tsquery represents the query.
Let's assume we have a
articles table, that has two fields,
body. Here is an example:
CREATE TABLE articles ( id serial primary key, title text, body text );
To add a full-text search capability, we need to add a
ALTER TABLE articles ADD COLUMN tsvector_column tsvector;
tsvector_column will store a
tsvector representation of the document. A
tsvector is a sort of "index" of the words in the document. Here's how to populate it:
UPDATE articles SET tsvector_column = to_tsvector('english', title || ' ' || body);
to_tsvector function takes two arguments: the configuration (in this case 'english'), and the document to be converted to a
|| operator concatenates the
title and the
Querying Full Text Search in PostgreSQL
Now that we've set up our
tsvector column, let's learn how to query it. To query a full-text search in PostgreSQL, we use the
@@ operator, and the
to_tsquery function. Here's an example:
SELECT * FROM articles WHERE tsvector_column @@ to_tsquery('english', 'cat');
This query will return all articles where the
tsvector_column matches the
tsquery 'cat'. Note that the
to_tsquery function takes the same configuration as the
to_tsvector function. This ensures that both the document and the query are parsed in the same way.
Improving Performance with Indexes
Full-text search can be slow, especially on large datasets. To speed up full-text search queries,we can use an index. In PostgreSQL, the GiST (Generalized Search Tree) and GIN (Generalized Inverted Index) index types support full-text search.
Let's add a GIN index on our
CREATE INDEX tsvector_idx ON articles USING gin(tsvector_column);
GIN indexes provide fast full-text search, but they can be slow to update. If you're doing a lot of writes on your table, a GiST index might be a better choice. However, GiST indexes are larger and slower to search than GIN indexes. You should benchmark your own workload to choose the best index type.
Automatic Updates with Triggers
tsvector_column needs to be kept up to date whenever the
body columns change. One way to achieve this is by using a trigger.
A trigger is a function that is automatically executed in response to certain events on a particular table or view in a database. In this case, we'll create a trigger to update the
tsvector_column when an article is inserted or updated.
First, let's create a function that will generate our
CREATE OR REPLACE FUNCTION articles_tsvector_trigger() RETURNS trigger AS $$ begin new.tsvector_column := to_tsvector('english', new.title || ' ' || new.body); return new; end $$ LANGUAGE plpgsql;
Next, we'll create a trigger to call this function whenever an article is inserted or updated:
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON articles FOR EACH ROW EXECUTE FUNCTION articles_tsvector_trigger();
tsvector_column will be automatically updated whenever we insert or update an article.
Q: What languages does PostgreSQL support for full-text search?
A: PostgreSQL supports multiple languages for full-text search, including English, Spanish, French, German, Russian, Chinese, and more. You can see the full list by querying the
SELECT cfgname FROM pg_ts_config;.
Q: How can I optimize full-text search in PostgreSQL?
A: The most effective way to optimize full-text search is by using indexes. PostgreSQL supports two types of indexes for full-text search: GiST and GIN. GIN indexes are faster to search but slower to update, while GiST indexes are slower to search but faster to update. You should benchmark your own workload to choose the best index type.
Q: How does PostgreSQL handle stopwords in full-text search?
A: PostgreSQL uses a list of stopwords that are ignored in full-text search queries. Stopwords are words that are so common that they are considered to have no significance for search purposes. The list of stopwords is language-specific and can be customized.
Q: Can I search for phrases with full-text search in PostgreSQL?
A: Yes, you can use the
<->operator to search for phrases. For example,
to_tsquery('english', 'quick <-> fox')will search for the phrase "quick fox".
Remember, PostgreSQL's full-text search feature is powerful and customizable. While this article gives an overview of how to set it up and use it, there are many more features and options available to explore. Whether you're building a blog, an ecommerce site, or a social network, full-text search in PostgreSQL can help your users find exactly what they're looking for.
Sharing is caring
Did you like what Mehul Mohan wrote? Thank them for their work by sharing it on social media.
No comments so far
Leave a question/feedback and someone will get back to you
- Triggers in SQL