Postgresql - Table Partitioning （一）

先来看一下PG官档中对分区表介绍，划个重点吧！

###########################################################################

Partitioning refers to splitting what is logically one large table 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. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.

# 提高查询性能，减小索引大小，让索引更有效的缓存进内存

When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index and random access reads scattered across the whole table.

# 利用该分区的顺序扫描来提高性能，而不是使用索引和随机访问读取散布在整个表中。

Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. Doing ALTER TABLE DETACH PARTITION or dropping an individual partition using DROP TABLE is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.

# 对一个分区表的处理要远快于对一个大表的操作。

Seldom-used data can be migrated to cheaper and slower storage media.

# 很少使用的数据可以迁移到更便宜和更慢的存储介质。

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

# 只有当表会非常大时，收益才是有价值的。

PostgreSQL offers built-in support for the following forms of partitioning:

Range Partitioning

The table is partitioned into “ ranges” defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. For example, one might partition by date ranges, or by ranges of identifiers for particular business objects.

# 该表被划分为由键列或列集合定义的“范围”，在分配给不同分区的值范围之间没有重叠。

List Partitioning

The table is partitioned by explicitly listing which key values appear in each partition.

# 通过显式列出每个分区中出现的键值，可以对表进行分区。

If your application needs to use other forms of partitioning not listed above, alternative methods such as inheritance and UNION ALL views can be used instead. Such methods offer flexibility but do not have some of the performance benefits of built-in declarative partitioning.

# 如果应用程序需要使用上面未列出的其他形式的分区，则可以使用替代方法，例如继承和联合所有视图。这种方法提供了灵活性，但不具备内置声明性分区的一些性能优势。