Database Compatibility: Table Partitioning v14

Suggest edits

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

  • Query performance can improve dramatically, 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. This approach reduces index size and makes it more likely that the heavily used parts of the index fit in memory.
  • When a query or update accesses a large percentage of a single partition, performance might improve. This improvement happens because the server performs a sequential scan of the partition instead of using an index and random-access reads scattered across the whole table.
  • If you plan the requirement into the partitioning design, you can implement a bulk load or unload by adding or removing partitions. ALTER TABLE is far faster than a bulk operation. It also avoids the VACUUM overhead caused by a bulk DELETE.
  • You can migrate seldom-used data to less-expensive or slower storage media.

Table partitioning is worthwhile when a table is becoming very large. The exact point at which a table benefits from partitioning depends on the application. A good guideline is for the size of the table not to exceed the physical memory of the database server.

EDB Postgres Advanced Server supports aspects of table partitioning that are compatible with Oracle databases.

Note

The declarative partitioning feature, introduced with PostgreSQL version 10, is not covered here. However, PostgreSQL declarative partitioning is supported in EDB Postgres Advanced Server 10 in addition to the table partitioning compatible with Oracle databases described here. For information about declarative partitioning, see the PostgreSQL core documentation.

The PostgreSQL INSERT... ON CONFLICT DO NOTHING/UPDATE clause, commonly known as UPSERT, isn't 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, an error occurs.

Note

EDB Postgres Advanced Server doesn't support global indexes, so the index isn't inherited when you define a primary key on the partitioned table that doesn't include partition key columns. However, all partitions defined in CREATE TABLE have an independent primary index on the column. You can re-create the primary key on all newly added partitions by using ALTER TABLE ... ADD CONSTRAINT. This primary index enforces uniqueness in each partition but not across the entire partition hierarchy. In other words, you can have the same value repeated for the primary index column in two or more partitions.

Next →

Selecting a partition type