5.10.1. Overview


5.10.1.概览


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 .


•如果将批量加载或删除需求考虑到分区设计中，那么可以通过添加或删除分区实现此需求。使用命令 ALTER TABLE DETACH PARTITION或使用DROP TABLE删除某一个单独分区，会比批量操作更加快速。这些命令也避免了由批量DELETE引起的VACUUM开销。


• 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:


PostgreSQL内置支持以下形式的分区：


 


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.


通过显式列出哪些键值出现在分区中来对表进行分区。


 


Hash Partitioning


哈希分区


The table is partitioned by specifying a modulus and a remainder for each partition. Each partition  will hold the rows for which the hash value of the partition key divided by the specified modulus  will produce the specified remainder.


通过为每个分区指定模数和余数来对表进行分区。每个分区根据分区键的哈希值除以指定的模数将产生指定的余数来保存行。


 


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.


如果应用程序要使用非以上受支持的分区，那么可以使用继承或者UNION ALL视图作为替代方案。这些方式提供了实现方案但是并不能像内置支持的分区那样提供很好的性能优势。