Feature Summary
Right now upgrading to v6 creates a migration for m-n relationships that creates a new index and drops the old one. Instead we can just update the existing unique constraint as a primary index which is done in constant time.
Use Cases & Problem Description
Right now the generated prisma 6 upgrade migration and the suggestion in the guide does the following for m-n relationships:
-- AlterTable
ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_AB_pkey" PRIMARY KEY ("A", "B");
-- DropIndex
DROP INDEX "_PostToTag_AB_unique";
This is fine for small tables but when scaled to hundreds of millions of rows in my use case it caused database locking for long enough we had to revert.
Proposed Solution
However, there is a simpler solution that is just run the following command instead
ALTER TABLE "_PostToTag" ADD CONSTRAINT "_PostToTag_AB_pkey" PRIMARY KEY USING INDEX "_PostToTag_AB_unique";
This just updates the metadata on the existing constraint that is already a unique constraint and run in near constant time. It was instantaneous even at our scale. For future engineers it might be good to either add it to the docs or update the auto generated migration code. Or maybe there was a reason this wasn't done so apologies if this was already discussed I know Prisma is already on 7 and we just got to upgrading to 6
Alternative Solutions
There are definitely other solutions posted online that we explored about patching a secondary db and promoting that, or running the commands individually with concurrent true and then resolving the migration manually.
Potential Considerations
I don't know why you didn't do it before but maybe this was already discussed
Prisma Version
6.19.2
What part of Prisma does this affect?
Other
Additional Context
No response
Pre-Submission Checklist
Feature Summary
Right now upgrading to v6 creates a migration for m-n relationships that creates a new index and drops the old one. Instead we can just update the existing unique constraint as a primary index which is done in constant time.
Use Cases & Problem Description
Right now the generated prisma 6 upgrade migration and the suggestion in the guide does the following for m-n relationships:
This is fine for small tables but when scaled to hundreds of millions of rows in my use case it caused database locking for long enough we had to revert.
Proposed Solution
However, there is a simpler solution that is just run the following command instead
This just updates the metadata on the existing constraint that is already a unique constraint and run in near constant time. It was instantaneous even at our scale. For future engineers it might be good to either add it to the docs or update the auto generated migration code. Or maybe there was a reason this wasn't done so apologies if this was already discussed I know Prisma is already on 7 and we just got to upgrading to 6
Alternative Solutions
There are definitely other solutions posted online that we explored about patching a secondary db and promoting that, or running the commands individually with concurrent true and then resolving the migration manually.
Potential Considerations
I don't know why you didn't do it before but maybe this was already discussed
Prisma Version
6.19.2
What part of Prisma does this affect?
Other
Additional Context
No response
Pre-Submission Checklist