6 ms·

The native time partitioning in Postgres 10 was a great foundation, but definitely had a few rough edges. With Postgres 10 you mostly needed some additional ext

The native time partitioning in Postgres 10 was a great foundation, but definitely had a few rough edges. With Postgres 10 you mostly needed some additional extension like pg_partman (https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partman-creating-a-scalable-time-series-database-on-PostgreSQL/ https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partm...) or timescale to smooth out the rough edges and make things as usable as you'd expect. This set of changes in 11 is pretty great and makes it much more usable out of the box. For those not reading the article the short list of improvements: - Updates move records across partitions - A default/catch all partition - Automatic index creation - Foreign keys for partitions - Unique indexes - Hash partitioning (where as 10 was just time/range based)

jcadam 8y ago
Sounds great. Hopefully someone will do up a guide on how to migrate a partitioning scheme created using pg_partman over to Postgres 11. It would be great to remove that dependency.
odammit 8y ago
FKs and unique indexes are a solid win. Very excited for this!
- pgaddict 8y ago
  Keep in mind there are limitations - the unique index / constraint has to include the partition keys, and foreign keys are possible in one direction only. This may be improved in the future, of course.
  - arghwhat 8y ago
    Wait, so you cannot have a FK reference to the partitioned master table? :(
    - pgaddict 8y ago
      Unfortunately not in PG11, but not because we don't want it - it simply didn't get ready in time. https://github.com/postgres/postgres/commit/3de241dba86f3dd000434f70aebba725fb928032 https://github.com/postgres/postgres/commit/3de241dba86f3dd0... The relevant limitation is described by CREATE TABLE docs that say Partitioned tables do not support <literal>EXCLUDE</literal> constraints however, you can define these constraints on individual partitions. Also, while it's possible to define <literal>PRIMARY KEY</literal> constraints on partitioned tables, creating foreign keys that reference a partitioned table is not yet supported. (the wording is slightly broken in the commit, but got fixed later).
  - odammit 8y ago
    You've really got me on an emotional rollercoaster here. :D
londons_explore 8y ago
Still missing: * Dynamic repartitioning to balance query load/space/create new partitions when tables get over a certain size. Without this, when you get more than a few hundred TB of data, you'll end up with terrible hotspots where single partitions are much more loaded than others and theres nothing you can do about it. * Query replanning: Query plans can sometimes have a lot of error. By replanning, one can start on another query plan if the existing plan is grossly wrong in its estimates. * Lock Eliding instead of *Exclusive locks. Some major operations, such as schema changes, take locks which effectively stop the server responding to any other requests till the operation is done. Instead, these operations should work on a copy-on-write image of the database, repeatedly re-syncing with recently changed data until the final change necessary is small, and then atomically commit the change.
- tmd83 8y ago
  Which database does this dynamic repartitioning and how without user intervention?
  - 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.)

jcadam 8y ago

Sounds great. Hopefully someone will do up a guide on how to migrate a partitioning scheme created using pg_partman over to Postgres 11. It would be great to remove that dependency.

odammit 8y ago

FKs and unique indexes are a solid win. Very excited for this!

pgaddict 8y ago
Keep in mind there are limitations - the unique index / constraint has to include the partition keys, and foreign keys are possible in one direction only. This may be improved in the future, of course.
- arghwhat 8y ago
  Wait, so you cannot have a FK reference to the partitioned master table? :(
  - pgaddict 8y ago
    Unfortunately not in PG11, but not because we don't want it - it simply didn't get ready in time. https://github.com/postgres/postgres/commit/3de241dba86f3dd000434f70aebba725fb928032 https://github.com/postgres/postgres/commit/3de241dba86f3dd0... The relevant limitation is described by CREATE TABLE docs that say Partitioned tables do not support <literal>EXCLUDE</literal> constraints however, you can define these constraints on individual partitions. Also, while it's possible to define <literal>PRIMARY KEY</literal> constraints on partitioned tables, creating foreign keys that reference a partitioned table is not yet supported. (the wording is slightly broken in the commit, but got fixed later).
- odammit 8y ago
  You've really got me on an emotional rollercoaster here. :D

londons_explore 8y ago

Still missing: * Dynamic repartitioning to balance query load/space/create new partitions when tables get over a certain size. Without this, when you get more than a few hundred TB of data, you'll end up with terrible hotspots where single partitions are much more loaded than others and theres nothing you can do about it. * Query replanning: Query plans can sometimes have a lot of error. By replanning, one can start on another query plan if the existing plan is grossly wrong in its estimates. * Lock Eliding instead of *Exclusive locks. Some major operations, such as schema changes, take locks which effectively stop the server responding to any other requests till the operation is done. Instead, these operations should work on a copy-on-write image of the database, repeatedly re-syncing with recently changed data until the final change necessary is small, and then atomically commit the change.

tmd83 8y ago
Which database does this dynamic repartitioning and how without user intervention?
- 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.)