[Epic] extendedIndexes GA

[do4gr]

The feature currently encompasses the sort and length arguments as well as the type argument on Postgres.
It was released in 3.5 and 3.6 (type).
Main docs here: https://www.prisma.io/docs/concepts/components/prisma-schema/indexes
Preview feature feedback can be found here. #10540

Stabilizing this is a BREAKING CHANGE.

Feature Work

• Fully investigate GiST and GIN index types
  • Then:
    • Support GiST: Support GiST Index type with Postgres #10634
    • Support GIN: Allow creating GIN index on JSONB fields in Postgres adapter #7410
    • Support BRIN
    • Support SP-GiST
    • Completions for PostgreSQL GIN/GiST/SP-GiST/BRIN Indices language-tools#1139
• Look at and handle potential differences for index types to CockroachDB #12818
  • Support Generalized Inverted Indices on CockroachDB #13065
• Investigate SQL Server index types
  • SQL Server index clustering settings Support defining index clustering #12871
  • Completion for SQL Server Index Clustering language-tools#1138

Bugs

• recreation of indexes Unique/Index keeps getting recreated #11631

Documentation

• PostgreSQL: GiST, GIN, SP-GiST and BRIN Indices docs#3102
  • PostgreSQL inverted indices docs#3123

Reference

Use cases

Polishing

• error message to use length on text in mysql Better error message if using TEXT or BLOB in MySQL @id/@index/@unique #10292
  • Cleanup connector-specific validations prisma-engines#2905
• error message incorrect Schema validation error mentions Error parsing attribute "@id" but it's actually about @@id #10566
  • Correct use of @ and @@ in validation errors prisma-engines#2893

Stabilizing

• Remove extendedIndexes preview feature from datamodel: Remove preview feature extendedIndexes #12777
  • Extended Indices: GA prisma-engines#2907
• Remove extendedIndexes preview feature from docs
• Upgrade guide since this is a breaking change

Issues to close after GA

• You cannot define an index on fields with Native type Text of MySQL. #8661

Out of scope for GA

• add info to dmmf Include @@index and @map information in getDMMF #3998
• Mysql length limits (discovery document)
  • Mysql length limits config dependent that one needs some more investigation ExtendedIndexes: length property is MySQL configuration specific (innodb_page_size), could lead to broken migrations #10990
  • Mysql length limit validation in schema validation Validating index length prefixes #10288
• diff and duplicate key error upon second migrate dev run Second Prisma migrate run (with no interim schema changes) reports diff and throws "duplicate key" error #10405
• Cyclic creation of the same migration Cyclic creation of the same migration #12693
• Document: Mysql length limits and db configuration
• Document: Sort order effects in singular vs compound index
• Document: covering indexes (this one is optional)
  • Index effect on queries docs#3131

[sourdzl]

really appreciate this work prisma team! I've been having trouble getting prisma to not create migrations dropping a GIN index i tried adding manually to the db, so this feature would really help!

[janpio]

Can you quickyl create a separate issue about what you are seeing @sourdzl? Currently GIN and GiST is not part of extendedIndexes, and any additional input could be valuable there. (As you can see above, looking into these two additional types is on our plan here.)

[sourdzl]

sure @janpio !

We have a JSONB column in postgres which is shallow (just string key/value pairs, no nesting.). We'd like to index the keys which will be used commonly for WHERE and JOIN filtering, and it seems a GIN index solves this perfectly.

We're happy with any solution that allows the GIN indices to exist - so far we keep having prisma generate migrations that drop the GIN index. specifying a type: GIN would be great, but are also fine with manually adding CREATE INDEX... queries to migrations ourself if that's easier to support.

thank you all for maintaining such a great project!

[pimeys]

@sourdzl I did some digging what we should be doing here. It's not final proposal, which I'm doing this week so we can write the code. Can you see if it misses something that you'd want to see in the feature?

https://github.com/pimeys/writing/blob/main/prisma/PostgreSQL%20Index%20Types.md

I'm trying to cover Gin, GiST, SP-GiST and BRIN indices, and the default built-in operators. The tsvector stuff for full-text queries will be coming later this year.

[pimeys]

Internal proposals:

• GIN/GiST/SP-GiST/BRIN for PostgreSQL https://www.notion.so/prismaio/Proposal-More-PostgreSQL-index-types-GiST-GIN-SP-GiST-and-BRIN-e27ef762ee4846a9a282eec1a5129270
• CLUSTERED/NONCLUSTERED for SQL Server https://www.notion.so/prismaio/Proposal-SQL-Server-Index-Clustering-4606716eb2814efabcde2cca6356cf63

[pimeys]

Discovery what is the issue with index lengths on MySQL and what we could do here: https://github.com/pimeys/writing/blob/main/prisma/Discovery:%20MySQL%20Index%20Length%20Limitations.md

[sourdzl]

@pimeys thanks for writing such a detailed proposal. It definitely meets our immediate need (GIN index on JSONB column type, so we can do fast checks of top level key presence). I suspect the GIN/GIST support will be broadly useful for other postgres users.

Farther down the line, we'd like to try some of the full text searching features as well, but also realize postgres is not the best choice for search overall. For us, GIN/GIST indexing are "must have" feature.

(sorry for the delayed response)

[pimeys]

GIN/GIST/SP-GiST/BRIN work going on here: prisma/prisma-engines#2858

[janpio]

And that is merged, you can use prisma@dev and the extendedIndexes preview feature if you want to play with it. Optimally start with a db pull on your existing database that uses these, and see if they are added correctly to the introspected schema. Afterwards you can also play with adding additional ones and then migrate (with db push or migrate dev).

Let us know how it goes please!

[pimeys]

We are GA.