Partial Indexes

A partial index is an index that is created only on a subset of rows in a table, defined by a specific WHERE condition. Instead of indexing all rows, the index only includes those that meet certain criteria, which can improve performance and reduce space usage.

In my particular case, I needed to index fields based on whether their values were NULL or NOT NULL. For example, the following partial indexes in SQL:

CREATE INDEX index_field_null 
ON table(field) 
WHERE field IS NULL;

CREATE INDEX index_field_not_null 
ON table(field) 
WHERE field IS NOT NULL;

It’s also possible to create partial indexes that involve multiple columns:

CREATE INDEX index_field1_null_field2_null 
ON table(field1, field2) 
WHERE field1 IS NULL AND field2 IS NULL;

CREATE INDEX index_field1_not_null_field2_not_null 
ON table(field1, field2) 
WHERE field1 IS NOT NULL AND field2 IS NOT NULL;

Not all databases support partial indexes. In my particular case, I’m using PostgreSQL

Problem

By default, Vapor doesn’t offer direct support for creating partial indexes, since the standard index generation doesn’t include the ability to add a WHERE clause in the index definition.

The following code snippet creates a normal index, either on one or several fields, but doesn’t allow specifying a condition for a partial index:

try await db.sqlDatabase
    .create($0.key)
    .on(table)
    .colums($0.colums)
    .run()

This code works for creating simple indexes, but lacks the ability to add a WHERE predicate.

This snippet has been adapted by me; the original builder’s .create method exposes more configuration options, but in this example I show a custom and simplified implementation that I currently use.

Alternative

The most direct way to create partial indexes is to execute raw SQL statements, as shown in the initial examples. This involves building a method to execute raw CREATE INDEX statements with the corresponding WHERE clause.

Although effective, this approach loses the advantage of abstraction and safety that Vapor offers when building migrations and database schemas using Swift code.

Solution

The SQLCreateIndexBuilder builder supports an optional predicate. If this is not nil, it adds a WHERE clause to the index.

Therefore, I extended this builder to include a where method that accepts a list of columns and a partial index type (for example, null or not null). This method builds the appropriate logical expression for the predicate and assigns it to the index.

extension SQLCreateIndexBuilder {
    @discardableResult
    func `where`(
        _ columns: [FieldKey],
        _ partialIndex: SQLPartialIndexEnum?
    ) -> Self {
        guard let partialIndex else {
            return self
        }
        let op: SQLBinaryOperator = partialIndex == .null ? .is : .isNot

        let conditions = columns.map {
            self.where($0, op)
        }

        let combined: SQLBinaryExpression = conditions.dropFirst()
            .reduce(conditions[0]) { .init($0, .and, $1) }

        return self.where(combined)
    }

    private func `where`(
        _ column: FieldKey,
        _ binary: SQLBinaryOperator
    ) -> SQLBinaryExpression {
        .init(
            left: SQLIdentifier(column.description),
            op: binary,
            right: SQLLiteral.null
        )
    }

    private func `where`(_ expression: SQLExpression) -> Self {
        self.createIndex.predicate = expression
        return self
    }
}

This code allows calling the where method for one or several fields, specifying whether you want a partial index for NULL or NOT NULL values. If no value is provided for partialIndex, the index is returned without a predicate, behaving like a normal index.

The logic consists of creating a list of binary expressions SQLBinaryExpression for each column, combining them with the logical operator AND and assigning the result as the index predicate.

Result

With this extension, I can now create partial indexes easily in Vapor, adding only one line to the original code:

try await db.sqlDatabase
    .create($0.key)
    .on(table)
    .colums($0.colums)
    .where($0.colums, $0.partial)
    .run()

Where $0.partial is an optional value that indicates the desired partial index type null or not null.

Final Notes

This approach is specifically designed for partial indexes based on the presence or absence of NULL values in columns. However, the implementation can be adapted to support other conditions and more complex use cases, simply by modifying the predicate construction.

The solution offers a clean and reusable way to create partial indexes within the Vapor ecosystem, maintaining the consistency and safety of Swift code.

If you also want to organize your tables into namespaces using Fluent’s space property, I covered that in Schemas and Namespaces.

Keep coding, keep running 🏃‍♂️

Partial Indexes

Table of contents

Partial Indexes

Problem

Alternative

Solution

Result

Final Notes