doctrine / dbal

Doctrine Database Abstraction Layer
https://www.doctrine-project.org/projects/dbal.html
MIT License
9.47k stars 1.34k forks source link

Partitioning Support in Doctrine for Relational Database #4657

Open cs-alok-agarwal opened 3 years ago

cs-alok-agarwal commented 3 years ago

Partitioning Support in Doctrine for Relational Databases

Q A
New Feature yes
RFC no
BC Break no

Summary

Request a support for partitioning support in native doctrine. https://www.postgresql.org/docs/12/ddl-partitioning.html

Main Challenge in implementing partitioning currently is, partitioning needs to be defined while create table itself. Currently as the option is not available we cannot use same

From Postgres Documentation: _It is not possible to turn a regular table into a partitioned table or vice versa. However, it is possible to add an existing regular or partitioned table as a partition of a partitioned table, or remove a partition from a partitioned table turning it into a standalone table. Link

Similar to index or unique constraint support we can have partitioning column support too for Databases. Like partition_option({"type": "range", column: "date", max_partition: 40})

pierre-cba commented 3 years ago

I'm looking for this feature !

morozov commented 3 years ago

I don't think we can just add a platform-specific feature to the DBAL API. Even if partitioning is supported by multiple platforms, there are high chances that their APIs and capabilities are different. The only reasonable way to support features like this is to break the Platform API down to smaller pieces and make them extensible independently. Something along the lines documented in https://github.com/doctrine/dbal/issues/3731.

SteppingHat commented 2 years ago

I second this! Horizontal partitioning is a powerful feature for large datasets where newer entries are accessed more frequently than older entries. If Doctrine can natively support this it would be a HUGE feature add. The amount of work required to manually implement this is huge and could definitely be simplified.

Sales pitch aside, I also am an advocate for #3731 as this opens a door to exploring platform specific features, and to my knowledge has no downsides (much like how Symfony is structured).

SteppingHat commented 2 years ago

Also this isn't PostgreSQL specific, MySQL/MariaDB supports this too.

BenMorel commented 1 year ago

Hi, we need this too. Is there a workaround we can implement (in code) in the meantime, to get doctrine:schema:validate correctly validate our table with partitions?

digitaltim-de commented 1 year ago

Hello, we need this too. Its important feature!

derrabus commented 1 year ago

Hello, we need this too. Its important feature!

Go ahead and build that feature, then.

vytsci commented 8 months ago

Hello, we need this too. Its important feature!

Go ahead and build that feature, then.

Rather than being sarcastic, maybe there is some guidelines, how we can approach this?

derrabus commented 8 months ago

Rather than being sarcastic

Nobody's being sarcastic. This is a community-driven open source project. If you want a change to happen, make it happen.

nikophil commented 2 months ago

Hello,

maybe at least we could make the dbal see "partiioned" table, and ignore "partitions"?

this would allow to work with manually partitioned tables as doctrine would consider them as normal tables, and would not suggest to remove partitions

see https://github.com/doctrine/dbal/pull/6516

nikophil commented 1 month ago

Is there a workaround we can implement (in code) in the meantime, to get doctrine:schema:validate correctly validate our table with partitions?

@BenMorel in #6516, in dbal v4.2, you'll be able to transparently use partitions tables with doctrine

BenMorel commented 1 month ago

@nikophil Well done, thanks!