Support Partial/Filtered Unique Index for PostgreSQL and SQL Server

[bkrausz]

Problem

tl;dr - Support for Postgres Partial Indexes and related functionality

Consider, for ex, a versioned CMS schema

model Page {
  id                Int       @id @default(autoincrement)
  staticId          String
  isLatest          Boolean
  previousVersionId Int?
  previousVersion   Workflow? @relation("pagePreviousVersion", fields: [previousVersionId], references: [id])
  nextVersion       Workflow  @relation("pagePreviousVersion")
}

New entries for the same page (i.e. identical staticId) are created by adding a new entry with isLatest=true and setting isLatest=false on the old one. We want to enforce that only one version is ever latest, so we have a partial index on staticId where isLatest=true.

It looks like Prisma is mostly peaceful with this except for a few things:

1. Introspect will try to create these as full unique indexes (i.e. @@unique), which is incorrect
2. post.findOne won't allow {where: {staticId: '...', isLatest: true}} because there's no way to tell it that this guarantees uniqueness.

Suggested solution

Partial index support ala @@unique([staticId], where={isLatest: true}) would be great.

Alternatives

I can probably work around this with findMany(...)[0], but flagging as a feature request.

Additional context

There's another challenge in supporting this data model: AFAICT there is no way to update an object at the same time as connecting it. It seems like right now I can run:

const page = prisma.page.create({
  data: {
    staticId: prevVersion.staticId,
    isLatest: false,
    previousVersion: {
      connect: {id: prevVersion.id}
    }
  }
})
prisma.page.update({
  where: { id: page.id },
  data: {
    isLatest: true,
    previousVersion: {
      update: { isLatest: false }
    }
  }
})

Whereas I'd ideally like to run:

const page = prisma.page.create({
  data: {
    staticId: prevVersion.staticId,
    isLatest: true,
    previousVersion: {
      connect: {id: prevVersion.id},
      update: {isLatest: false}
    }
  }
})

Happy to file as a separate feature request as it seems mostly unrelated to partial indexes.

[bkrausz]

Slight update: my example prisma.page.update call doesn't work because of the ordering of the execution within the transaction (there's no way to make the isLatest: false happen first). This does, however, work with the transactionApi as you can specify ordering.

[janpio]

Can you share the SQL of the Page table above? Will make it a lot easier to understand.

[bkrausz]

How we're using this (we're currently moving over from Python/SQLAlchemy to Prisma):

CREATE TABLE pages (
    id SERIAL PRIMARY KEY,
    staticId character varying NOT NULL,
    isLatest boolean NOT NULL,
    previousVersionId integer REFERENCES workflows(id),
    # ...content/title/etc fields
);

CREATE UNIQUE INDEX pages_pkey ON pages(id int4_ops);
CREATE UNIQUE INDEX "idx_pages_unique_staticId" ON pages(staticId text_ops) WHERE isLatest = true;

Reading

The frontend gets the page for display via

SELECT * FROM pages WHERE staticId = ${staticId} AND isLatest = true;

Updating

Given a newly created page with a single revision:

id	staticId	isLatest	previousVersionId
1	page_abc	true	NULL

One would create a new version of page staticId from current latest version currentPageId:

BEGIN;
UPDATE pages SET isLatest = false WHERE id = ${currentPageId};
INSERT INTO pages ("staticId", "isLatest", "previousVersionId") VALUES (${staticId}, true, ${currentPageId});
COMMIT;

Resulting in:

id	staticId	isLatest	previousVersionId
2	page_abc	true	1
1	page_abc	false	NULL

[janpio]

Happy to file as a separate feature request as it seems mostly unrelated to partial indexes.

Just saw the second part of the original issue @bkrausz: Yes please 👍

[Sytten]

@albertoperdomo This should be revisited now, it could start by just removing NULL from the index.

[kakserpom]

Any news?

[janpio]

On SQL Server these are called "Filtered Indexes" (via): https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver15

[petrbela]

FWIW, it seems #7263 (comment) the extendedIndexes preview feature now supports some additional parameters https://www.prisma.io/docs/concepts/components/prisma-schema/indexes, however, still no support for conditional/partial indexes for Postgres. I'm guessing @janpio that would be something we could expect soon?

[janpio]

Partial indexes are not part of that preview feature, so I can not really say if this feature request here will come "soon".
But we are certainly working through the index related gaps we have and closing them one by one.

[Jolg42]

Note from Tyler: This is needed as a performance optimization for large databases.

something I used frequently in the past and would love support for are indexes with predicates.
Example:
CREATE INDEX IDX_User_Email_ActiveOnly ON User (email) WHERE isActive = 1
The index is only utilized for queries with the isActive = 1 predicate, but the index size is significantly smaller than the alternative:
CREATE INDEX IDX_User_IsActive_Email ON User (isActive, email)
It's needed when tables are very large and an index on the whole thing is unnecessary.

[Jolg42]

Note: In a related issue, an alternative feature request was mentioned (of course full support for these indexes would be better)
#16658

[JoniBR]

Any news?

[janpio]

Yes, I posted a comment in a duplicate/related issue asking for SQL examples so we can design this feature better: #6974 (comment) Everyone who commented here or is subscribed to this issue, please go over there and comment. Thanks!

And because these issues are effectively about the same feature, I am closing this one here now and ask you please to continue the discussion over at #6974. 🙏