Partitioning allows you to physically divide a table or an index into smaller manageable parts. Partitioning is transparent to applications. For a partitioned table or index, only one table or index is logically visible for applications that access the database. However, physically, this table or index may consist of dozens of physical partitions. Each partition is an independent object that can be processed alone or as part of a larger object.

Scenarios

  • Partition a table when it is excessively large. For example, the table space is larger than the physical memory of the database server. This can improve database performance. For example, when a table stores data that is greater than 2 GB in size, you can partition the table.
  • Partition a large table when it stores historical data and new data needs to be written into the latest partition. For example, a large table is used to store the historical data of a year. You can store the data of the most recent month in a separate partition where data is updatable. You can then store the data of the previous months in other read-only partitions.

Benefits

You can partition tables to improve database performance in the following aspects:

  • Query performance can be significantly improved in specific situations. This is most obvious when the most frequently accessed rows of a table are in a single partition or a small number of partitions. Partitioning allows you to omit the partition key column from the front of an index. This reduces the index size and ensures that the frequently used parts of the index fit in memory.
  • When you query or update a large percentage of a partitioned table, performance may be improved. This is because the server performs a sequential scan of the partitions instead of using an index and random access reads scattered across the entire table.
  • If you want to add or delete large amounts of data at a time in a partition, you can add or delete partitions. The ALTER TABLE statement is faster than a bulk operation and can prevent excessive VACUUM operations caused by DELETE operations.

Partitioning types

Partitioning types include range partitioning, list partitioning, hash partitioning, and subpartitioning. This division is based on the internal attributes of tables.

  • Range partitioning

    Range partitioning maps the data of a table into partitions based on the specified partition key. This is the most commonly used partitioning method. In most cases, dates are used as partition keys. Interval range partitioning is an extension of range partitioning. For more information, see Interval range partitioning.

  • List partitioning

    List partitioning distributes data rows of a table into partitions based on the values of the partition key.

  • Hash partitioning

    Hash partitioning determines the partitions to place rows based on the hash values that are computed from column values. We recommend that you use hash partitioning when you cannot specify suitable conditions to match the column values.

Syntax

You can perform various operations on partitioned tables, such as creating partitioned tables, adding partitions to partitioned tables, merging partitions, splitting partitions, and deleting partitions. For more information about the commands and descriptions of table partitioning of different types, see Command list for partitioned tables.