-
Notifications
You must be signed in to change notification settings - Fork 1.5k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Table Partitioning #1708
Comments
Right now we don't support this feature so I am marking this as a feature request. |
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 |
Is this functionality planned for the near future? :) |
I am also very interested in this feature, its a must for my project. |
I also need this feature. |
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. |
@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'); |
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 The partition (table) naming is arbitrary? |
Sorry for replying so late.
yes, the partition naming is arbitrary. |
This also exists in MySQL: https://dev.mysql.com/doc/refman/8.0/en/partitioning.html |
Oh! I really need this 😍 |
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. |
Issue with SQL examples to reproduce the current failure of Prisma was here: #4667 |
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/ |
Yeah one model per partition and one for the main table would make sense to me. |
We improved Introspection for partitioned tables in 4.10.0. This should allow everyone to manually modify the migration with |
or migrate command may not say db reset ? |
@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? |
We'll create public documentation for this soon. Right now we suggest you only model the main table with a |
@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. |
Hi @revero-doug |
@DewZ89 I don't have code snippets I can share, but when you understand the workflow, it's trivial
|
Indeed. |
Hello @janpio This is my case,
|
@ZhaoKunLong's expectations meet mine! (very similar use case.) |
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. |
please add support for table partitions this is a very used feature |
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. |
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 |
Hello. I love the DX of Prisma and would be very glad to see it built-in too! |
4 years ago 😁 |
Still, Prisma has come a long way since then. That doesn't mean I don't hope with all my heart that we'll be able to have a 'Magic' partitioning thanks to Prisma in the year to come. It would clearly be one more killer feature. |
Is it possible to create a partitioned table with schema language?
The text was updated successfully, but these errors were encountered: