4 ms·

Which database does this dynamic repartitioning and how without user intervention?

by tmd83 8y ago

kuschku 8y ago
All the partitioning tools for postgres allow it. You can say that it should aim for ~100GB per partition, partitioned by time, and it'll aromatically add or remove partitions. You can go even further with the time scale extension for postgres.
- arghwhat 8y ago
  The question was which DB's do this out-of-the-box like you requested, not plugins or tools. However, seeing that there already is an extension for postgres to do it, why is it a problem in the first place?
  - derefr 8y ago
    The partitioning features in PG10 and PG11 were also supported by said extensions, but now they're batteries-included features. The GP is implying that, as long as they're eating the lunch of these extensions, they should eat the whole lunch. (Which I personally agree with, given that you almost never get these extension—or support for arbitrary extensions—on Postgres DBaaS hosts. Upstreaming a feature = making it available on these hosts.)
    - tpetry 8y ago
      The development model of postgres is to start with a simply le implementation and improve it with every release. So maybe with pg13 or 14 this will be available in the core if one developer starts working on the remaining features.
    - arghwhat 8y ago
      There is a very sharp distinction between providing first-class partitioning, and providing automated maintenance and performance tuning. Providing first-class partitioning is something that extensions can't possibly provide, due to how many places this has to integrate with. On the other hand, manipulating partitions (which is effectively automatic table creation) seems like the sort of magic I expect to only exist in extensions. As mentioned in a different comment, it does sound like people are looking for round-robin load distribution rather than partitioning.
  - pgaddict 8y ago
    I don't think any of the extensions actually provides the type of auto-tuning / balancing you're asking for, actually. For example pg_partman helps with setting up some partitioning schemes (by time, by ID), but does not rebalance it later (e.g. to split a hot or unexpectedly large partition). It probably provides more convenient interface to do that, but it's still up to the DBA to do it. I'm sure this is one of the things people on pgsql-hackers will be talking about, but I wouldn't hold my breath for it to get into PG12. I do expect this to emerge in an extension first, and then getting the most useful bits into core (either in the form of built-in features, or hooks allowing extensions to be smarter).
londons_explore 8y ago
Bigtable does it. [1] . Spanner does it [2]. [1]: https://static.googleusercontent.com/media/research.google.com/en//archive/bigtable-osdi06.pdf https://static.googleusercontent.com/media/research.google.c..., Section 5, "and also splits tablets that have grown too large" [2]: https://storage.googleapis.com/pub-tools-public-publication-data/pdf/39966.pdf https://storage.googleapis.com/pub-tools-public-publication-..., Section 1, "it automatically migrates data across machines (even across datacenters) to balance load"
- tmd83 8y ago
  Those are distributed databases and have completely different characteristics. That's an interesting nice feature to have but no traditional RDBMS supports anything close I presume.

Which database does this dynamic repartitioning and how without user intervention?

by tmd83 8y ago

Which database does this dynamic repartitioning and how without user intervention?

All the partitioning tools for postgres allow it. You can say that it should aim for ~100GB per partition, partitioned by time, and it'll aromatically add or remove partitions. You can go even further with the time scale extension for postgres.

arghwhat 8y ago
The question was which DB's do this out-of-the-box like you requested, not plugins or tools. However, seeing that there already is an extension for postgres to do it, why is it a problem in the first place?
- derefr 8y ago
  The partitioning features in PG10 and PG11 were also supported by said extensions, but now they're batteries-included features. The GP is implying that, as long as they're eating the lunch of these extensions, they should eat the whole lunch. (Which I personally agree with, given that you almost never get these extension—or support for arbitrary extensions—on Postgres DBaaS hosts. Upstreaming a feature = making it available on these hosts.)
  - tpetry 8y ago
    The development model of postgres is to start with a simply le implementation and improve it with every release. So maybe with pg13 or 14 this will be available in the core if one developer starts working on the remaining features.
  - arghwhat 8y ago
    There is a very sharp distinction between providing first-class partitioning, and providing automated maintenance and performance tuning. Providing first-class partitioning is something that extensions can't possibly provide, due to how many places this has to integrate with. On the other hand, manipulating partitions (which is effectively automatic table creation) seems like the sort of magic I expect to only exist in extensions. As mentioned in a different comment, it does sound like people are looking for round-robin load distribution rather than partitioning.
- pgaddict 8y ago
  I don't think any of the extensions actually provides the type of auto-tuning / balancing you're asking for, actually. For example pg_partman helps with setting up some partitioning schemes (by time, by ID), but does not rebalance it later (e.g. to split a hot or unexpectedly large partition). It probably provides more convenient interface to do that, but it's still up to the DBA to do it. I'm sure this is one of the things people on pgsql-hackers will be talking about, but I wouldn't hold my breath for it to get into PG12. I do expect this to emerge in an extension first, and then getting the most useful bits into core (either in the form of built-in features, or hooks allowing extensions to be smarter).

londons_explore 8y ago

Bigtable does it. [1] . Spanner does it [2]. [1]: https://static.googleusercontent.com/media/research.google.com/en//archive/bigtable-osdi06.pdf https://static.googleusercontent.com/media/research.google.c..., Section 5, "and also splits tablets that have grown too large" [2]: https://storage.googleapis.com/pub-tools-public-publication-data/pdf/39966.pdf https://storage.googleapis.com/pub-tools-public-publication-..., Section 1, "it automatically migrates data across machines (even across datacenters) to balance load"

tmd83 8y ago
Those are distributed databases and have completely different characteristics. That's an interesting nice feature to have but no traditional RDBMS supports anything close I presume.