Advanced PostgreSQL With Vapor

Creating Indexes

Go back to Recruiter. Make a file named CreateIndexes in Migrations. Add this migration to it:

import Fluent
import SQLKit

struct CreatePG_TRGMExtension: AsyncMigration {
  
  func prepare(on database: Database) async throws {
    let sqlDatabase = (database as! SQLDatabase)
    try await sqlDatabase.raw("CREATE EXTENSION pg_trgm").run()
  }
  
  func revert(on database: Database) async throws {
    let sqlDatabase = (database as! SQLDatabase)
    try await sqlDatabase.raw("DROP EXTENSION pg_trgm").run()
  }
}

This enables PostgreSQL’s pg_trgm module, which is disabled by default. You’ll need this module to instruct PostgreSQL on how to make the a Gin index over the columns you want.

Add another migration to this file, this time to create a Gin index over the first_name and last_name columns:

struct CreatePersonFirstNameAndLastNameIndex: AsyncMigration {
  
  private typealias FieldKeys = Person.FieldKeys
  
  func prepare(on database: Database) async throws {
    let sqlDatabase = (database as! SQLDatabase)
    try await sqlDatabase.raw("""
    CREATE INDEX person_first_name_last_name_idx
    ON person
    USING GIN
    (\(raw: FieldKeys.firstName.description) gin_trgm_ops,
    \(raw: FieldKeys.lastName.description) gin_trgm_ops)
    """).run()
  }
  
  func revert(on database: Database) async throws {
    let sqlDatabase = (database as! SQLDatabase)
    try await sqlDatabase
      .raw("DROP INDEX person_first_name_last_name_idx")
      .run()
  }
}

In prepare(on:), you ask PostgreSQL to create a new index:

• Named person_first_name_last_name_idx.
• On the person table.
• Using the Gin index type.
• Over the columns first_name and last_name and using the gin_trgm_ops operator class. Gin indexes support specifying more than one column, so here you create a multi-column index on two columns instead of creating two indexes.

gin_trgm_ops is part of the pg_trgm module, which is why you needed to enable pg_trgm in the last migration.

Don’t forget to add your new migrations to your configure.swift:

app.migrations.add([
    CreateCompany(),
    SeedCompany(),
    CreatePerson(),
    SeedPerson(),
    CreateEmployee(),
    CreatePG_TRGMExtension(),
    CreatePersonFirstNameAndLastNameIndex(),
])

Build and run Recruiter to create the index. Gin indexes are helpful and flexible, but they’re also quite heavy, so creating and keeping them updated is costly. Luckily, you have only 100,000 people, so the index creation shouldn’t take more than a second!

Using Indexes in SQL Queries

Now, open Postico and rerun the Explain statement you did last time:

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

PostgreSQL automatically uses the index you made earlier. That’s because PostgreSQL’s query planner can identify that the first_name column has an index available and that it’ll be helpful for your specific query.

You can see the execution time has decreased from 55ms, when you had no index over the first_name column, to 2.3ms when there’s a useful index available. That’s more than 20 times faster than before, and this execution time difference will only increase with more people in the person table.

Note that the execution times might differ on your device, but they will remain close to what you see here.

Where to Go From Here?

You can download the sample project by clicking Download Materials at the top or bottom of this tutorial.

To learn more about SQLKit, you can read SQLKit’s readme.

If you want to learn more about PostgreSQL’s great features, see the official PostgreSQL documentation.

We hope you enjoyed this tutorial. If you have any questions or comments, please join the forum discussion below.