PostgreSQL Triggers — Full Text Search Tutorial

This is how organized your code will be after you start using triggers 📚

Do you find yourself writing sprawling logic around your application code to handle data fetching or data entry?

Have you noticed a pattern of rewriting the same logic across multiple systems that utilize a single API endpoint?

If you reacted to any of those questions with a “…yes” then this article is for you!

In a moment, I will help you alleviate these problems by delving into the magical world of database triggers and showing you how and why you should use them to build scalable applications.

What exactly are database triggers?

Here is a definition from Wikipedia’s page about database triggers:

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database.

In incredibly simplified terms, database triggers are events that automatically happen after another event takes place within your database.

Why should I use database triggers?

Almost every database out there has a way to implement triggers.

NoSQL and SQL databases alike employ this ability because their data stores would not be scalable without them. Since every data system that matters implements them, it is a good investment of your time to learn how and when to utilize them.

Here is a very simple example that demonstrates when you may want to use database triggers. You are building an application that stores information about customers. When a customer gets created in your database, you want to classify that customer into different categories.

To handle this situation, you could

• write application logic to determine, based on the data you have, what type of customer type they are and then send that information directly to the database.

or

• setup a database trigger in your database to run whenever a customer is created and determine a customer type from the data automatically.

Both strategies will work fine in small codebases, but the second trigger approach becomes particularly beneficial when you scale. Imagine your company has 3 or 4 external services that can create a customer through an API endpoint.

Instead of requiring each of those services to build out logic to determine a customer type, your trigger will handle that at the database layer so none of your external services have to reproduce code.

Database triggers essentially allow you to offload application layer code and move it inside your database which creates an incredibly durable and consistent backend system at scale.

Basic PostgreSQL Trigger Example

Continuing with our customer example, lets make a PostgreSQL trigger that runs before a customer is created and inserted in the customer table. This trigger should determine, based on the number of employees, which type of customer the new entry should be assigned.

Let’s look at what’s happening.

The first query, beginning with CREATE OR REPLACE FUNCTION that creates a function that contains logic you run when your trigger is invoked.

Within this function, you’ll notice on line 3 we use theNEW keyword. This is a reference to the newly created row that invoked this function through a trigger. When we enter our IF statement on line 3, we use the NEW keyword to check whether our row has an employee_count above 500.

If those conditions are met, we assign this customer type to enterprise, and if not we label that customer as a startup.

You’ll always want to make sure you return the new table entry with RETURN NEW; because PostgreSQL expects that record to be returned. In this way, the new values you set (i.e.New.type := ‘startup’) can be entered correctly into the database.

Next, we’re going to look at a more advanced trigger example you can utilize to enable a powerful full text search for your app using PostgreSQL and triggers.

Pro tip: if you’re debugging triggers like this on your local machine, it’s helpful to use RAISE INFO to see when triggers are being run and print out local variables.

Learn more about info and error messages in PostgreSQL.

Full Text Search: Advanced PostgreSQL Trigger Example

Now we are going to combine a few more advanced topics and strategies for using triggers that build on the last example.

Our advanced trigger below will have one objective:

We want to update a customer’s vectors column whenever that customer leaves a comment on a separate table.

This newly introduced vectors column is going to be a TSVECTOR type in PostgreSQL. Put simply, PostgreSQL TSVECTOR type allows you to implement general text search queries on sparse information in your database.

At a very basic level, PostgreSQL TSVECTOR converts normal sentences or words into a sorted list of distinct lexemes. Lexemes merge similar words together to prevent redundancy and optimize search performance.

Here is what a TSVECTOR type in PostgreSQL looks like made from the string “a fat cat sat on a mat — it ate a fat rats” :

'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4

Learn more about text search types and general search abilities of PostgresSQL here.

Now that you have more context on the TSVECTORS type in PostgreSQL, we can introduce the first half of our advanced trigger:

Starting at the top of this file, you’ll see we’re creating our new trigger called update_customer_vectors(). One of the first things we do in this trigger is DECLARE customer_comments TSVECTOR;.

This will create a local variable that will contain all our ts_vectors values. We’ll then hold those results temporarily in memory until we assign them on line 12 to our customer’s vectors column with NEW.vectors := coalesce(customer_comments, '');.

With this first trigger, we now have logic that collects all the customer’s comments from another table, creates a ts_vector for them and adds them to the vectors column on the new entry in the database. This will allow your users to search the customer’s table to find customers by the comments they’ve made.

Moving down the file, on line 18, we specify that this trigger should be invoked BEFORE INSERT OR UPDATE ON public.customers or simply, before we create or update a customer.

Now this is just the first part of the query, but stay with me because we are going to tie it together below.

Let’s revisit our objective:

We want to update our customer’s vectors column whenever that customer leaves a comment on a separate table.

So, if this first trigger above runs whenever a customer gets updated or created, how will we then invoke it when a comment is created on another table?

To do this, we are going to create another trigger that is much simpler than the first and this new trigger will be invoked AFTER INSERT OR UPDATE ON public.comments.

You’ll see in the query, once a comment is created, we immediately update the customer’s table with this line:

UPDATE customers SET vectors_version = CURRENT_TIMESTAMP WHERE customer.id = NEW.customer_id;

We update the customer’s vectors_version column whenever they create a new comment. This UPDATE will now invoke our first trigger and rebuild the text search vectors for the customer that just created a new comment.

With these two triggers and logic, you now have a database system that will detect when a customer creates a new comment and will automatically rebuild their text search vectors.

This will allow your users to immediately find and search for customers by the text in the comments they create.

This two trigger strategy is a very powerful pattern you can use to supercharge your app with generalized, full text search abilities to assist the user in find database items using text across various tables.

Now Backfill

Once your new trigger is up and running, it’s probably a good idea to run a table wide update with something like:

This will retroactively update all previously entered data to inherit the new logic of your trigger.

Whenever you update your trigger in the future you can run this one-line command and all your data will inherit those changes.

This is another powerful technique to ensure past data in your database gets rebuilt by utilizing your new trigger logic.

Learn More

I hope you enjoyed this deep dive into PostgreSQL triggers and found some new inspiration and strategies to use in your own application to delight users with powerful full text search.

If you’d like to learn more about various technical topics I’ve written about, check out a few of my other articles below:

Flux Vision: An Open Source Package to Supercharge Your Shopify Analytics With Segment

Don’t Waste Time While Testing: Level Up With GraphQL and Apollo

Thanks for making it to the end.

If you liked this post, hit that ❤ button and share this article with your team.

Follow me on twitter: http://twitter.com/graybuxx

Here is the full code for the full text search tutorial above: