Skip to main content

Using PostgreSQL Partial Indexes in Prisma with Atlas

Introduction​

Partial indexes in PostgreSQL allow you to index only specific rows that match certain conditions, rather than indexing the entire table. This powerful feature significantly improves query performance and reduces storage overhead when working with subsets of your data.

Why Use Partial Indexes?​

Partial indexes are particularly useful when:

  • You frequently query a specific subset of your data.
  • You want to enforce unique constraints on a subset of rows.
  • You need to optimize storage space by indexing only relevant data.

Example: In an e-commerce application, indexing only active products can improve performance. In a user system, indexing only verified users enhances query efficiency.

Prisma and Partial Indexes​

While Prisma offers excellent ORM capabilities, it doesn't directly support defining partial indexes, limiting your ability to fully optimize the database.

This guide demonstrates how to overcome this limitation by integrating Atlas with Prisma to manage partial indexes. Using Atlas's Composite Schema, you can define advanced database features alongside your Prisma models.

Atlas Pro Feature

Atlas support for composite_schema is available exclusively to Pro users. To use this feature, run:

atlas login

Composite Schema for Prisma and Partial Indexes​

1. Define Your Prisma Models

Start with your Prisma models. In this example, we'll create a User model with verification status:

schema.prisma
model User {
  id         Int      @id @default(autoincrement())
  email      String
  isVerified Boolean  @default(false)
  createdAt  DateTime @default(now())
}

2. Create SQL File with Partial Indexes Definitions

Create a file named indexes.sql containing the indexes:

indexes.sql
-- Create a partial index for verified users
-- This index will only include users where isVerified = true,
-- optimizing queries that search for verified users by email
CREATE INDEX idx_verified_users ON "User"("email") WHERE "isVerified" = true;

-- Example of another partial index with complex condition
CREATE INDEX idx_verified_premium_users ON "User"("email", "createdAt") 
WHERE "isVerified" = true AND "email" LIKE '%@premium.com';

3. Configure Atlas to Use Composite Schema

Create a file named atlas.hcl and add the following configuration:

atlas.hcl
data "external_schema" "prisma" {
    program = [ 
        "npx",
        "prisma",
        "migrate",
        "diff",
        "--from-empty",
        "--to-schema-datamodel",
        "prisma/schema.prisma",
        "--script"
    ]
}

data "composite_schema" "prisma-extended" {
    schema {
        url = data.external_schema.prisma.url
    }
    schema {
        url = "file://indexes.sql"
    }
}

env "local" {
    dev = "docker://postgres/15/dev"
    schema {
        src = data.composite_schema.prisma-extended.url
    }
    migration {
        dir = "file://atlas/migrations"
    }
}

In this file, we first define an external_schema data source that loads the Prisma schema from the schema definition file and then use the composite_schema data source to combine the Prisma schema with the partial indexes defined in indexes.sql.

Managing Your Partial Indexes​

With your configuration in place, use Atlas to manage your database schema, including the partial indexes.

Inspect the Schema​

Verify your schema configuration:

atlas schema inspect --env local --url env://schema.src --format "{{ sql . }}"

Expected output:

-- Create extension "pgcrypto"
CREATE EXTENSION "pgcrypto" WITH SCHEMA "public" VERSION "1.3";
-- Create "User" table
CREATE TABLE "public"."User" ("id" serial NOT NULL, "email" text NOT NULL, "isVerified" boolean NOT NULL DEFAULT false, "createdAt" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("id"));
-- Create index "idx_verified_premium_users" to table: "User"
CREATE INDEX "idx_verified_premium_users" ON "public"."User" ("email", "createdAt") WHERE (("isVerified" = true) AND (email ~~ '%@premium.com'::text));
-- Create index "idx_verified_users" to table: "User"
CREATE INDEX "idx_verified_users" ON "public"."User" ("email") WHERE ("isVerified" = true);

Generate Migration Files​

Create migration files to apply your schema changes:

atlas migrate diff --env local

This command generates the necessary migration files under the atlas/migrations directory:

.
├── atlas
│   └── migrations
│       ├── 20241204145229.sql
│       └── atlas.sum
├── atlas.hcl
├── prisma
│   ├── migrations
│   │   ├── ...
│   │       └── migration.sql
│   └── schema.prisma
└── triggers.sql

Apply Migrations to the Database​

atlas migrate apply --env local --url "postgresql://root:pass@localhost:5432/postgres?sslmode=disable"

Sample output:

Output
Migrating to version 20241204145229 from 20241204141142 (1 migrations in total):

  -- migrating version 20241204145229
    -> CREATE EXTENSION "pgcrypto" WITH SCHEMA "public" VERSION "1.3";
    -> CREATE TABLE "public"."User" ("id" serial NOT NULL, "email" text NOT NULL, "isVerified" boolean NOT NULL DEFAULT false, "createdAt" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("id"));
    -> CREATE INDEX "idx_verified_premium_users" ON "public"."User" ("email", "createdAt") WHERE (("isVerified" = true) AND (email ~~ '%@premium.com'::text));
    -> CREATE INDEX "idx_verified_users" ON "public"."User" ("email") WHERE ("isVerified" = true);
  -- ok (40.629041ms)

  -------------------------
  -- 57.384458ms
  -- 1 migration
  -- 4 sql statements

Conclusion​

By integrating Atlas with Prisma, you can implement PostgreSQL partial indexes to optimize query performance and reduce storage overhead. This approach allows you to leverage advanced database features not directly supported by Prisma's migration tools.

See Also​