Database Compatibility Table Partitioning v14

In a partitioned table, one logically large table is broken into smaller physical pieces. Partitioning can provide several benefits:

  • Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning allows you to omit the partition column from the front of an index, reducing index size and making it more likely that the heavily used parts of the index fits in memory.
  • When a query or update accesses a large percentage of a single partition, performance may improve because the server performs a sequential scan of the partition instead of using an index and random access reads scattered across the whole table.
  • A bulk load (or unload) can be implemented by adding or removing partitions, if you plan that requirement into the partitioning design. ALTER TABLE is far faster than a bulk operation. It also entirely avoids the VACUUM overhead caused by a bulk DELETE.
  • Seldom-used data can be migrated to less-expensive (or slower) storage media.

Table partitioning is worthwhile only when a table would otherwise be very large. The exact point at which a table benefits from partitioning depends on the application; a good rule of thumb is that the size of the table should exceed the physical memory of the database server.

This document discusses the aspects of table partitioning compatible with Oracle databases that are supported by EDB Postgres Advanced Server.

Note

This document and particularly the partitioning presented in this chapter don't describe the declarative partitioning feature, which has been introduced with PostgreSQL version 10. Note that PostgreSQL declarative partitioning is supported in EDB Postgres Advanced Server 10 in addition to the table partitioning compatible with Oracle databases as described in this chapter. For information about declarative partitioning, see the PostgreSQL core documentation available at: https://www.postgresql.org/docs/current/static/ddl-partitioning.html

The PostgreSQL 9.6 INSERT... ON CONFLICT DO NOTHING/UPDATE clause (commonly known as UPSERT) is not supported on Oracle-styled partitioned tables. If you include the ON CONFLICT DO NOTHING/UPDATE clause when invoking the INSERT command to add data to a partitioned table, the server returns an error.

Note

EDB Postgres Advanced Server does not support global indexes, so the index is not inherited when a primary key that does not include partition key columns is defined on the partitioned table. However, all partitions defined within the CREATE TABLE have an independent primary index on the column. You can recreate the primary key on all newly added partitions by using ALTER TABLE ... ADD CONSTRAINT. This primary index enforces uniqueness only within each partition and not across the entire partition hierarchy (basically, you can have the same value repeated for the primary index column in two or more partitions).