8 ms·

PostgreSQL 11 Partitioning Improvements

361 points by craigkerstiens 8y ago

koolba 8y ago
> With Postgres 11, you can create a unique index on the master > The columns in the index definition should be a superset of the partition key columns. This means that the uniqueness is enforced locally to each partition, and you can’t use this to enforce uniqueness of alternate-primary-key columns. Are there any workarounds for global indexes across partitions outside of the partition key? Say if you have a a table partitioned by (some_date) with a unique primary key (id), how would one speed up looks up that are purely by id? Having per-partition local indexes would require an index scan per partition. I supposed you could have a separate/smaller (id, some_date) non-partitioned table (or maybe partitioned on HASH(id)) and then do the indirection yourself wherever you're doing "SELECT ... WHERE id = ..." but seems like a kludge. Any other workarounds?
- Diggsey 8y ago
  That seems inherently impossible - either you need to include the date in the lookup, include the ID in the partition, or look at all the partitions.
  - anarazel 8y ago
    It's technically possible to have both global indexes, and constraint checking that just checks every single partition for conflicts. The former isn't trivial to implement in PG (there's a fixed-width scheme for the target tuples in several index implementations RN) and obviously adds a point of contention (but a smaller than the sidetable proposed nearby). The latter is fairly simple to implement (we do something very similar internally for exclusion constraints), but scales linearly with the number of partitions...
    - Diggsey 8y ago
      Yes, that's a reasonable solution and how ElasticSearch handles partitions. It still would "require an index scan per partition." though, which the OP wanted to avoid.
    - londons_explore 8y ago
      PG should allow an index to have a different partitioning scheme and locality from the data it refers to. That would solve nearly all the major remaining limitations.
      - yashap 8y ago
        Indeed. Or similarly, allow indexes to not be partitioned at all - i.e. your records and most indexes could be partitioned by date, but you could also declare indexes that apply to the entire logical table (very useful for things like primary keys).
        pgaddict 8y ago
        That is what was mentioned as "global indexes" in this discussion. The problem with this approach is twofold - firstly it negates many of the partitioning benefits (e.g. removing data is not merely a DROP PARTITION but you have to walk the global structure), secondly it requires fairly significant surgery in the indexes (that now point to rows in a single table, and so [block,item] is enough, but global indexes will require [partition,block,item]).
        anarazel 8y ago
        > so [block,item] is enough, but global indexes will require [partition,block,item]). That's probably the easier bit, I'd guess that adjusting a lot of the relevant in-memory structures to share relations, uniqueness checks across relations, adjusting the locking-model to deal with multiple underlying relations etc. is going to be more work.
    - pgaddict 8y ago
      I think eventually we'll go with the second option, and do stuff to eliminate the scalability issues with many partitions. For example we could maintain a global bloom index (still global, but tiny compared to the sidetable), which should tell us whether there might be a duplicate value. If yes, it might be a false positive, in which case we have to do the expensive per-partition stuff. I'm sure it's going to be more complicated than this in practice, of course ...
      - anarazel 8y ago
        I suspect we'll want both. The bloom index only helps if you have decent spatial locality. Which you won't for say a serial, uuid, etc. There's also types of indexes where it's probably going to be more efficient to have one index (I'd suspect a spatial indexes are often going to be that) for a number of usecases.
        pgaddict 8y ago
        Possibly, but considering how much easier is the second case to implement (compared to global indexes), I'd expect that to get in first. I'm not sure why the bloom filter would require good spatial locality?
- endymi0n 8y ago
  Having done this with MySQL before, there‘s basically three things you can do: 1) Do what you proposed, but this kinda defeats the whole purpose, as that side table can get very, very large (and if it‘s not fitting into working memory anymore, good luck keeping up a sane insert rate). 2) Just don‘t load that kind of data into an RDS subject to these scaling limitations at all (what I would do nowadays, but in a lot of ways, easier said than done) 3) (what we did back then): If you can, accept a window of uniqueness instead of real, true global uniqueness. If you can live with that, define a cronjob to build you a VIEW on the last TODAY-N partitions and check for duplicates using that in a SELECT CTE before inserting.
  - Shelnutt2 8y ago
    Using Spider storage engine in Mariadb it is possible to do this by having vertical partitions using different partition by clauses [1]. Essentially you will create sub tables with the partition by clauses, then use a spider table to reference these subtables. Spider than intelligently will select the partition to use based on the query and where clause. The downside of this is it will require storage of the data multiple times on disk, since each subtable needs the full data set. Spider does handle inserting the data, so once schemas are build, only the single spider table is needed for inserts/selects/updates. In addition to this, in 10.4 it was mentioned spider is working to have internal joins. This means it would be possible to have 1 table defined with the full data set, then a second table that just has id, datefield partition by id. When you select WHERE id = X, it will read from the small table partitioned by id, find the dates, then join against the larger table to pull in remaining columns/data. This isn't mentioned in the presentation linked, but it was brought up in discussion at the mariadb developers unconference. [1] page 6: https://mariadb.org/wp-content/uploads/2018/03/Merging_patches_into_MariaDB_10.4.pdf https://mariadb.org/wp-content/uploads/2018/03/Merging_patch... (note this is about pages for 10.4 but the VP functionality is in 10.3)
  - tinus_hn 8y ago
    Wouldn’t the solution be to automatically partition the index you use for ensuring uniqueness, along with the main table? I don’t see how it would be very difficult, as long as the number of partitions is stable.
- derefr 8y ago
  Are your IDs monotonic by date (i.e. increasing the date will never decrease the ID)? Or, to put that another way, is partitioning by date equivalent to partitioning by ID for you, such that your date-based partitions will each end up with defined (if unknown) ranges of IDs? If so, you could potentially create 1. a table that maps ID ranges to partitions, and then 2. create a stored procedure that takes a set of IDs and returns the set of partitions you must scan to find them.
  - koolba 8y ago
    No they're entirely separate. The IDs are pure UUIDs with no structure tying them to the desired partition key.
    - derefr 8y ago
      Could you switch from UUIDv4s (what I assume you mean by “pure UUIDs”) to UUIDv1s?
      - koolba 8y ago
        No I've considered that as well as splitting the key to be a composite of the partition key but I'd like an eventual solution for this in the general case where there's no relationship between the partitioning method and the primary key.
        snissn 8y ago
        As far as I'm understanding your situtation it seems like you're using range partitioning and then hoping to get benefits of hash partitioning from a range partition. I think you would need to use hash partitions to avoid look ups on every every table. You could do something weird like have each range partition have an exclusion filter and make sure that each of your randomly generated IDs accommodate that exclusion filter, and generate IDs until you find one.. But it sounds like you would benefit from a hash partition here instead of a range one.
craigkerstiens 8y ago
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).
deleted 8y ago
[deleted]
jeffdavis 8y ago
Partitioning and parallel query go great together! For applicable workloads, it means you get nearly perfect scaling up to the number of partitions you have. PG11 will be huge.
john37386 8y ago
I use zabbix a lot with PG database and there are 2 tables that grow indefinitely. The zabbix housekeeper lags a bit when the tables become +200GB. I tried the autopartitioning and the housekeeper becomes irrelevant. I can now easily keep 1 year of data for my +200k monitored items. I like my critical alerts to keep 3 months at 60 seconds interval. Pg partitioning is really awesome
gigatexal 8y ago
Very excited about this. Postgres10 is awesome. But I am looking forward to working with 11.

> With Postgres 11, you can create a unique index on the master > The columns in the index definition should be a superset of the partition key columns. This means that the uniqueness is enforced locally to each partition, and you can’t use this to enforce uniqueness of alternate-primary-key columns. Are there any workarounds for global indexes across partitions outside of the partition key? Say if you have a a table partitioned by (some_date) with a unique primary key (id), how would one speed up looks up that are purely by id? Having per-partition local indexes would require an index scan per partition. I supposed you could have a separate/smaller (id, some_date) non-partitioned table (or maybe partitioned on HASH(id)) and then do the indirection yourself wherever you're doing "SELECT ... WHERE id = ..." but seems like a kludge. Any other workarounds?

Diggsey 8y ago
That seems inherently impossible - either you need to include the date in the lookup, include the ID in the partition, or look at all the partitions.
- anarazel 8y ago
  It's technically possible to have both global indexes, and constraint checking that just checks every single partition for conflicts. The former isn't trivial to implement in PG (there's a fixed-width scheme for the target tuples in several index implementations RN) and obviously adds a point of contention (but a smaller than the sidetable proposed nearby). The latter is fairly simple to implement (we do something very similar internally for exclusion constraints), but scales linearly with the number of partitions...
  - Diggsey 8y ago
    Yes, that's a reasonable solution and how ElasticSearch handles partitions. It still would "require an index scan per partition." though, which the OP wanted to avoid.
  - londons_explore 8y ago
    PG should allow an index to have a different partitioning scheme and locality from the data it refers to. That would solve nearly all the major remaining limitations.
    - yashap 8y ago
      Indeed. Or similarly, allow indexes to not be partitioned at all - i.e. your records and most indexes could be partitioned by date, but you could also declare indexes that apply to the entire logical table (very useful for things like primary keys).
      - pgaddict 8y ago
        That is what was mentioned as "global indexes" in this discussion. The problem with this approach is twofold - firstly it negates many of the partitioning benefits (e.g. removing data is not merely a DROP PARTITION but you have to walk the global structure), secondly it requires fairly significant surgery in the indexes (that now point to rows in a single table, and so [block,item] is enough, but global indexes will require [partition,block,item]).
        anarazel 8y ago
        > so [block,item] is enough, but global indexes will require [partition,block,item]). That's probably the easier bit, I'd guess that adjusting a lot of the relevant in-memory structures to share relations, uniqueness checks across relations, adjusting the locking-model to deal with multiple underlying relations etc. is going to be more work.
  - pgaddict 8y ago
    I think eventually we'll go with the second option, and do stuff to eliminate the scalability issues with many partitions. For example we could maintain a global bloom index (still global, but tiny compared to the sidetable), which should tell us whether there might be a duplicate value. If yes, it might be a false positive, in which case we have to do the expensive per-partition stuff. I'm sure it's going to be more complicated than this in practice, of course ...
    - anarazel 8y ago
      I suspect we'll want both. The bloom index only helps if you have decent spatial locality. Which you won't for say a serial, uuid, etc. There's also types of indexes where it's probably going to be more efficient to have one index (I'd suspect a spatial indexes are often going to be that) for a number of usecases.
      - pgaddict 8y ago
        Possibly, but considering how much easier is the second case to implement (compared to global indexes), I'd expect that to get in first. I'm not sure why the bloom filter would require good spatial locality?
endymi0n 8y ago
Having done this with MySQL before, there‘s basically three things you can do: 1) Do what you proposed, but this kinda defeats the whole purpose, as that side table can get very, very large (and if it‘s not fitting into working memory anymore, good luck keeping up a sane insert rate). 2) Just don‘t load that kind of data into an RDS subject to these scaling limitations at all (what I would do nowadays, but in a lot of ways, easier said than done) 3) (what we did back then): If you can, accept a window of uniqueness instead of real, true global uniqueness. If you can live with that, define a cronjob to build you a VIEW on the last TODAY-N partitions and check for duplicates using that in a SELECT CTE before inserting.
- Shelnutt2 8y ago
  Using Spider storage engine in Mariadb it is possible to do this by having vertical partitions using different partition by clauses [1]. Essentially you will create sub tables with the partition by clauses, then use a spider table to reference these subtables. Spider than intelligently will select the partition to use based on the query and where clause. The downside of this is it will require storage of the data multiple times on disk, since each subtable needs the full data set. Spider does handle inserting the data, so once schemas are build, only the single spider table is needed for inserts/selects/updates. In addition to this, in 10.4 it was mentioned spider is working to have internal joins. This means it would be possible to have 1 table defined with the full data set, then a second table that just has id, datefield partition by id. When you select WHERE id = X, it will read from the small table partitioned by id, find the dates, then join against the larger table to pull in remaining columns/data. This isn't mentioned in the presentation linked, but it was brought up in discussion at the mariadb developers unconference. [1] page 6: https://mariadb.org/wp-content/uploads/2018/03/Merging_patches_into_MariaDB_10.4.pdf https://mariadb.org/wp-content/uploads/2018/03/Merging_patch... (note this is about pages for 10.4 but the VP functionality is in 10.3)
- tinus_hn 8y ago
  Wouldn’t the solution be to automatically partition the index you use for ensuring uniqueness, along with the main table? I don’t see how it would be very difficult, as long as the number of partitions is stable.
derefr 8y ago
Are your IDs monotonic by date (i.e. increasing the date will never decrease the ID)? Or, to put that another way, is partitioning by date equivalent to partitioning by ID for you, such that your date-based partitions will each end up with defined (if unknown) ranges of IDs? If so, you could potentially create 1. a table that maps ID ranges to partitions, and then 2. create a stored procedure that takes a set of IDs and returns the set of partitions you must scan to find them.
- koolba 8y ago
  No they're entirely separate. The IDs are pure UUIDs with no structure tying them to the desired partition key.
  - derefr 8y ago
    Could you switch from UUIDv4s (what I assume you mean by “pure UUIDs”) to UUIDv1s?
    - koolba 8y ago
      No I've considered that as well as splitting the key to be a composite of the partition key but I'd like an eventual solution for this in the general case where there's no relationship between the partitioning method and the primary key.
      - snissn 8y ago
        As far as I'm understanding your situtation it seems like you're using range partitioning and then hoping to get benefits of hash partitioning from a range partition. I think you would need to use hash partitions to avoid look ups on every every table. You could do something weird like have each range partition have an exclusion filter and make sure that each of your randomly generated IDs accommodate that exclusion filter, and generate IDs until you find one.. But it sounds like you would benefit from a hash partition here instead of a range one.

craigkerstiens 8y ago

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).

deleted 8y ago

[deleted]

jeffdavis 8y ago

Partitioning and parallel query go great together! For applicable workloads, it means you get nearly perfect scaling up to the number of partitions you have. PG11 will be huge.

john37386 8y ago

I use zabbix a lot with PG database and there are 2 tables that grow indefinitely. The zabbix housekeeper lags a bit when the tables become +200GB. I tried the autopartitioning and the housekeeper becomes irrelevant. I can now easily keep 1 year of data for my +200k monitored items. I like my critical alerts to keep 3 months at 60 seconds interval. Pg partitioning is really awesome

gigatexal 8y ago

Very excited about this. Postgres10 is awesome. But I am looking forward to working with 11.