Table Partitioning

[ansarizafar]

Is it possible to create a partitioned table with schema language?

[pantharshit00]

Right now we don't support this feature so I am marking this as a feature request.

[ansarizafar]

Declarative table partitioning is very important for query performance in production. Its a must have feature and Prisma should include this feature in Version 1.0

[Gmulti]

Is this functionality planned for the near future? :)

[agnese-kerubina]

I am also very interested in this feature, its a must for my project.

[AkifumiSato]

I also need this feature.
Is there any way to achieve a partition at this time?

[janpio]

Most probably this is transparent for Prisma Client when it already exists in the database. Can one of you share the minimal SQL required to set up a table with partitioning? Then we can test this a bit and see if that is true.

Note: You can definitely not represent the partitioning information of a table in Prisma Schema yet for it to be created by Prisma Migrate.

[AkifumiSato]

@janpio Does this fit what you are looking for?

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2019m01 PARTITION OF measurement
    FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');

CREATE TABLE measurement_y2019m02 PARTITION OF measurement
    FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');

CREATE TABLE measurement_y2019m03 PARTITION OF measurement
    FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');

[janpio]

Yes, now everyone has the context what it is we are talking about above @AkifumiSato - thanks!

So Prisma schema would need to represent 1. the PARTITION BY RANGE (logdate); thing for the main table, and then the 3 additional CREATE TABLE ... PARTITION of ... in some way so the partitions itself can also be represented. As they logically belong to the main table, this would probably make sense to all bind to the model in Prisma Schema (vs. the legacy representation as their own tables in the raw SQL).

The partition (table) naming is arbitrary?

[AkifumiSato]

Sorry for replying so late.

The partition (table) naming is arbitrary?

yes, the partition naming is arbitrary.
It seems that postgresql also has PARTITION BY LIST and PARTITION BY HASH.

[janpio]

This also exists in MySQL: https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

[Suniron]

Oh! I really need this 😍

[MrLoh]

I think the real problem here is #10870. Prisma wouldn't need to enable setting up partitions in the schema, but should allow setting up a partition with a custom migration script and in the schema as separate tables, but the mentioned issue prevents that.

[janpio]

Issue with SQL examples to reproduce the current failure of Prisma was here: #4667

[janpio]

Question to everyone posting here and interested in partition support in Prisma:

How would you expect Prisma to represent partitioned tables and their partitions?

(In #10870 and #17348 we have two users that created separate models for each of the partitions (additionally to the main table) - a sensible approach with what Prisma produced on Introspection/db pull on partitioned tables in older version. Unfortunately, that badly fails in other parts of Prima's tooling (migrate dev specifically).)

[MrLoh]

Yeah one model per partition and one for the main table would make sense to me.

[janpio]

We improved Introspection for partitioned tables in 4.10.0. This should allow everyone to manually modify the migration with migrate dev --create-only to put the correct SQL - and then partitioned tables should just work!

[pclokcer]

or migrate command may not say db reset ?

[revero-doug]

We improved Introspection for partitioned tables in 4.10.0. This should allow everyone to manually modify the migration with migrate dev --create-only to put the correct SQL - and then partitioned tables should just work!

@janpio could you elaborate a bit for posterity? should you create a model in schema.prisma for each of the partition tables, generate migration, then replace the create table statements for the partition tables?

[janpio]

We'll create public documentation for this soon. Right now we suggest you only model the main table with a model, and create the table and partition tables manually. Then interact with the partitions via raw queries if you have to. Is that adequate?

[revero-doug]

We'll create public documentation for this soon. Right now we suggest you only model the main table with a model, and create the table and partition tables manually. Then interact with the partitions via raw queries if you have to. Is that adequate?

@janpio that's basically what I ended up doing after some trial and error, and it seems to be working just fine, thanks for the response.

[martialanouman]

Hi @revero-doug
Could you post some code snippets?

[revero-doug]

@DewZ89 I don't have code snippets I can share, but when you understand the workflow, it's trivial

0. make sure you're on prisma 4.10.0 or newer
1. define your new model in your prisma schema like you would if you didn't need partition tables
2. run prisma migrate dev. this generates a migration.sql file
3. edit the migration.sql file to add your partition tables the same way you you would typically set up partitions in postgres (i.e. this part is not prisma-sepcific)
4. now test it out in a fresh non-production environment. if something doesn't work there's an issue with your workflow, your manual edits to migration.sql, your environment, or some combination

[martialanouman]

Indeed.
Thanks

[ZhaoKunLong]

Question to everyone posting here and interested in partition support in Prisma:

How would you expect Prisma to represent partitioned tables and their partitions?

(In #10870 and #17348 we have two users that created separate models for each of the partitions (additionally to the main table) - a sensible approach with what Prisma produced on Introspection/db pull on partitioned tables in older version. Unfortunately, that badly fails in other parts of Prima's tooling (migrate dev specifically).)

Hello @janpio This is my case,

• I'm using the AWS RDS and the PostgreSQL is 12.14.
• So there was a log table log_his have (id, createdAt) columns. You know It will become bigger and bigger over time.
• So I need to set log_his as the main table and partitioned, and use pg_partman to manage the partition tables.
• After I've done these on db. I prefer to do the query using Prisma.
• I express that in scheme.prisma has one model for the main table, And has a decorator like @Partitioned, Then in the service can still just use prisma.LogHis.findMany() that would be nice!

[RyKilleen]

@ZhaoKunLong's expectations meet mine! (very similar use case.)

[mohammedpatla]

Yea, I am looking for a use case where I can auto-create partitions on postgress. The business case for us is we have a time series table, and we want to bulk the data on a monthly data set so that we can drop data after a certain period of months pass. So, with partitions, we have the ability to drop old months in Bulk without hitting the delete data option on the table.

[DanhezCode]

please add support for table partitions this is a very used feature

[snspinn]

Reading through the postgres docs on this, seems like this is straight forward enough for table writes (write to the parent table and postgres takes care of the rest) but to be able to do reads you'd need some sort of dynamic SQL. This due to fact that you have to be able to reference changing table names, especially if you want to take advantage of indexing and joins.

I don't think Prisma has support for dynamic table selection yet #9534 and this doesn't really seem to fit into the syntax of an ORM well.

I haven't seen these points brought up in this thread yet. They seem underappreciated hurdles to get this working, or am I missing something?

Note: I'm referring more to how this would be used in queries, rather than generating of migrations from the schema file.

[DanhezCode]

You're right that it's probably a big effort to provide support for dynamic table selection, but I disagree that this doesn't fit into the syntax of an orm because I think it can be a great extra functionality for people who handle large amounts of data and need partition-based scaling for better performance. If you decide to support this functionality, the community can surely help you, including me if you need it.

[snspinn]

You're right that it's probably a big effort to provide support for dynamic table selection, but I disagree that this doesn't fit into the syntax of an orm because I think it can be a great extra functionality for people who handle large amounts of data and need partition-based scaling for better performance. If you decide to support this functionality, the community can surely help you, including me if you need it.

Not a developer on this, just wanted to acknowledge how much work this looks like. I retract my statement on it not fitting into the syntax: It does seem like it could work. Similar to the suggestion made here #1122 (comment) regarding multi-schema support, perhaps one could expand ZhaoKunLong's suggestion to be something like

prisma.model.findMany({ partition: noneDefaultParitition })  // partition default (when blank) = parent table