Advanced PostgreSQL With Vapor

Full-Text Search

PostgreSQL comes with a few operators for pattern matching in text. The simplest of them is =. You can also use LIKE and ILIKE for more pattern-matching flexibility, or use SIMILAR, ~~ ~~* with regular expressions.

Although these are some helpful operators, they can result in unwanted matches when dealing with documents.

Imagine you want to find all sentences in a column in your database containing the word Knife. You could get away with filtering sentences using filters such as ILIKE '%knife%', which will return any columns that case-insensitively contain knife anywhere in them.

That wouldn’t be too bad! But what if you have this: “Remember to bring a few knives for cutting meats”?

This sentence has knives in it, which is the plural form of knife. Your ILIKE '%knife%' will fail to find this sentence even though it contains the plural form of knife.

Now, this is bad! You can try to use pattern matching using regular expressions for finding Knives alongside Knife. But that will be not only more complicated than you’d hope for but also slower.

Trying Full-Text Search

The good news is that PostgreSQL can do text search while understanding natural language.

It’s simple to start using PostgreSQL’s full text search. You only need to use its dedicated operator @@ instead of the previous operators.

Open MainController and look at getPeopleByHobby(_:). Right now, you have this:

func getPeopleByHobby(_ req: Request) async throws -> [Person] {
  let hobby = try req.query.get(String.self, at: "hobby")
  return try await Person
    .query(on: req.db)
    .filter(\.$hobby == hobby)
    .all()
}

Fluent doesn’t natively support @@, so you’ll need to provide a custom operator.

Change the filter part to use @@ like so:

.filter(\.$hobby, .custom("@@"), hobby)

Now, your filter will use PostgreSQL’s Full Text Search. Build and run Recruiter, and open your preferred REST client. Send a GET request to the URL http://localhost:8080/people/hobby?hobby=car to see if any people like hanging around cars. You’ll receive some people:

Look at hobby for a few people. Because you have 100,000 people, you’ll notice many people have the same interests around cars: Some mention Car spotting as their hobby, others Car riding and so on.

It’s important to note that although you have people with hobbies like Houseplant care or Wood carving, PostgreSQL never matched those for your car search because care and carving have nothing to do with a car.

Using Full-Text Search Operators

PostgreSQL also supports performing a full-text search for multiple words simultaneously.

For that, you need to turn your text input to tsquery using PostgreSQL’s to_tsquery() function. tsquery is the type that PostgreSQL uses for all Full Text Searches. The reason you didn’t need to_tsquery() before was that PostgreSQL automatically counted your text input as a single tsquery value. That won’t work when you want to use a more complex tsquery with different operators, so you need to use to_tsquery().

Change your filter part of the code to explicitly tell PostgreSQL your input is a tsquery. You’ll need to provide a custom filter to use the to_tsquery function:

.filter(.custom("\(Person.FieldKeys.hobby) @@ to_tsquery('\(hobby)')"))

Now, you can use different tsquery operators. The most important ones are | for OR, & for AND and ! for NOT.

Build and run Recruiter, and try the previous URL but with car & !ride hobby filter to find all hobbies that have car in them but no words related to ride. Be sure to use the URL-encoded form of &, which is %26. The new URL will be http://localhost:8080/people/hobby?hobby=car %26 !ride:

You’ll notice you can no longer find the Car riding hobby in any of the people, because it includes riding.

This isn’t all that PostgreSQL’s full-text search offers. Full-text search has many more handy features. It not only supports many languages other than English but also can rank results or highlight parts of them. Learning about those other features will have to wait for another time, though. :]

The only problem with any text search is that it can become very slow. For now, you only have 100,000 people, and your personal computer won’t have any problems searching through them. But imagine having millions of people and your PostgreSQL server having limited resources. Your users expect an answer in a second or two, while your text-search query alone can take tens of seconds.

That’s when indexes shine!

Indexing Columns

Indexes are like the index page of a book. They keep track of what is where in a table, so you can find what you want faster.

They can drastically improve your query times at the expense of more workload beforehand, a trade-off that’s likely worth it for big tables.

Running Queries in a Database Client

Open your database client and connect to your PostgreSQL database using the password you set when creating the database, which was vapor_password. It looks like this in Postico:

After connection, you’ll see an overview of your database. Choose SQL Query so you can run a few raw SQL queries to test your current query speed:

Now, try this raw SQL query statement. This will use normal pattern matching to find people with first names case-insensitively containing jul anywhere in them. Then, press Execute Statement or use the shortcut Command-Return:

SELECT * FROM person WHERE first_name ILIKE '%jul%';

You’ll see many people with first names like Jules, Julious, Julianna etc…:

Explaining SQL Queries

PostgreSQL comes with a handy command to explain and analyze the performance of any query. Simply add EXPLAIN ANALYZE to the previous Select command and execute:

EXPLAIN ANALYZE SELECT * FROM person WHERE first_name ILIKE '%jul%';

This explains what happens when you execute the Select command. The only important part for you is that this query takes about 55ms for PostgreSQL to execute.

This is acceptable for now, but your database is growing bigger every day. And with a few million people in it, your queries can start to take tens of seconds, which degrades your app’s user experience.

The solution is to use indexes. PostgreSQL supports a wide variety of indexes, each with a different purpose.

For searching through texts, you can use a Gin index.