Enter the terms you wish to search for.

The Truth About Partitioning

Declarative partitioning introduced in PostgreSQL10 aims at improving performance. However, partition done wrong can lead to massive performance deterioration. Choosing a good partition strategy is the key to reap the performance benefits that partitioning can provide.

 

What you will learn in this webinar:

  • All the features and key improvements across versions including the newly introduced pgbench options that enable one to benchmark partition tables.
  • Learn when to use and when not to use partition with the help of benchmarking results, using partitions in different settings.

 

Webinar video:

 

Webinar slides:

 

Webinar Q&A:

  • Do you have any advice for creating partitions dynamically?

    • There is currently no support to dynamically create partitions in open source Postgres. However, EDB Postgres has added capability for automatic list and range interval partitioning and will be available in the next release. 

      INTERVAL Partition, which is a dynamic RANGE partition, is already part of v12.  AUTOMATIC Partition, which is dynamic LIST Partition, will be available in v13.

  • Do we have interval or Automatic partitioning with Declarative Partition?

    • No. We currently do not have automatic support in declarative partitioning. EDB Postgres, however, will support the automatic partitioning feature in the next release. As per the answer to Q1, above, INTERVAL partition is available in v12 and AUTOMATIC partition will be available in v13.

  • Is there a movement of data when a non partition table with huge data is partitioned?

    • There is no way to partition an unpartitioned table. The only way to achieve this would be to create new tables with the intended partition hierarchy and then copy the data from the original table into this. 

  • Do we need to rebuild indexes after attaching or detaching partition?

    • No, we do not. The indexes on the partitions are treated like indexes on any standalone table and hence the detach operation does not require to be followed by rebuild indexes command.  

  • Can range partition hold NULL values in PG?

    • Yes. NULL can be only held in the DEFAULT partition of a range partitioned table.

  • Can I exchange a partition with other tables in PG, like the exchange partition of Oracle?

    • No. But EDB Postgres does support this functionality.

  • Can we create automated partitions based on timestamp or date in PG?

    • Currently, this is not available in PG. It would be available in the next release of EDB Postgres.

  • Can the partition key column be a part of the multi-column index? Or should it be a single index?

    • Yes, a partition key column can be part of the multi-column index. If the index is unique then all the columns in partition key should be part of the index.

  • Are the partition tables virtual or physical?

    • The partitioned table (parent) is virtual. The partitions (Child table) are physical tables.

 

Other Resources