PostgreSQL 教程: 分区表索引

一月 21, 2025

摘要:在本教程中,您将学习如何在 PostgreSQL 中为分区表建立索引。

目录

准备数据集

当您正在寻找开放的数据集时,“美国政府开放数据之家” 是一个很好的来源。它为研究、开发或出于任何原因都可以使用的数据,提供了公开的数据集。在本教程中,我们将使用交通违规数据集。它包含了大约 200 万行,是深入研究 PostgreSQL 分区的一个良好起点。

初始的导入会进入到一个仅包含文本列的表,我们稍后可以从那里加载到我们的分区。该表的结构为:

create table traffic_violations
( seqid text
, date_of_stop text
, time_of_stop text
, agency text
, subagency text
, description text
, location text
, latitude text
, longitude text
, accident text
, belts text
, personal_injury text
, property_damage text
, fatal text
, commercial_license text
, hazmat text
, commercial_vehicle text
, alcohol text
, work_zone text
, search_conducted text
, search_disposition text
, search_outcome text
, search_reason text
, search_reason_for_stop text
, search_type text
, search_arrest_reason text
, state text
, vehicletype text
, year text
, make text
, model text
, color text
, violation_type text
, charge text
, article text
, contributed_to_accident text
, race text
, gender text
, driver_city text
, driver_state text
, dl_state text
, arrest_type text
, geolocation text
);

要加载表,最简单的方法是使用 PostgreSQL 的 COPY 命令:

COPY traffic_violations FROM '/home/postgres/Traffic_Violations.csv'
  WITH ( format csv, header true, null 'null', delimiter ',');

所以我们有了大约 200 万行真实数据。由于此表仅包含文本列,因此我们将创建一个列表分区表,采用适合数据的数据类型。

列表分区

列表分区不是按范围(通常基于年、月、日)进行分区,而是使用定义的分区键的值,基于一个明确的列表进行分区。

应用程序可能想要请求与特定违规类型匹配的所有交通违规:

select distinct violation_type from traffic_violations;
 violation_type
----------------
 Citation
 ESERO
 SERO
 Warning
(4 rows)

按该方式进行分区,会产生 4 个分区(加上 1 个默认分区,用于将来任何其他可能的违规类型)。表及其分区的定义与范围分区几乎相同,此处我们进行列表分区:

create table traffic_violations_p_list
( seqid text
, date_of_stop date not null
, time_of_stop time
, agency text
, subagency text
, description text 
, location text
, latitude numeric
, longitude numeric
, accident text
, belts boolean
, personal_injury boolean 
, property_damage boolean
, fatal boolean
, commercial_license boolean
, hazmat boolean
, commercial_vehicle boolean
, alcohol boolean
, work_zone boolean
, state text
, vehicletype text 
, year smallint
, make text
, model text
, color text
, violation_type text 
, charge text
, article text
, contributed_to_accident boolean
, race text
, gender text
, driver_city text
, driver_state text
, dl_state text
, arrest_type text
, geolocation point)
partition by list (violation_type);

现在我们可以创建分区:

create table traffic_violations_p_list_warning
partition of traffic_violations_p_list
for values in ('Warning');

create table traffic_violations_p_list_sero
partition of traffic_violations_p_list
for values in ('SERO');

create table traffic_violations_p_list_Citation
partition of traffic_violations_p_list
for values in ('Citation');

create table traffic_violations_p_list_ESERO
partition of traffic_violations_p_list
for values in ('ESERO');

create table traffic_violations_p_list_default
partition of traffic_violations_p_list DEFAULT;

现在,我们为每个违规类型提供了一个分区。由于我们的分区表设置现在已经完成,我们可以加载数据:

insert into traffic_violations_p_list
select seqid
     , to_date(date_of_stop,'MM/DD/YYYY')
     , time_of_stop::time
     , agency
     , subagency
     , description
     , location
     , latitude::numeric
     , longitude::numeric
     , accident
     , belts::boolean
     , personal_injury::boolean
     , property_damage::boolean
     , fatal::boolean
     , commercial_license::boolean
     , hazmat::boolean
     , commercial_vehicle::boolean
     , alcohol::boolean
     , work_zone::boolean
     , state
     , vehicletype
     , case year
         when '' then null
         else year::smallint
       end
     , make
     , model
     , color
     , violation_type
     , charge
     , article
     , contributed_to_accident::boolean
     , race
     , gender
     , driver_city
     , driver_state
     , dl_state
     , arrest_type
     , geolocation::point
  from traffic_violations;

索引

在 PostgreSQL 10 引入声明式分区时,还存在相当多的限制。从那时起,PostgreSQL 的分区特性已经有了很大的改进。

我们将使用列表分区表。表上没有一个约束或索引,分区也是如此。我们无法创建主键或唯一索引,因为分区表中有重复的行。但是,我们可以创建一个标准的 btree 索引:

CREATE INDEX i1 on traffic_violations_p_list ( model );

这是一个所谓的分区索引,你可以用下面方法来验证它:

select * from pg_partition_tree('i1');
                    relid                     | parentrelid | isleaf | level
----------------------------------------------+-------------+--------+-------
 i1                                           |             | f      |     0
 traffic_violations_p_list_citation_model_idx | i1          | t      |     1
 traffic_violations_p_list_esero_model_idx    | i1          | t      |     1
 traffic_violations_p_list_sero_model_idx     | i1          | t      |     1
 traffic_violations_p_list_warning_model_idx  | i1          | t      |     1
 traffic_violations_p_list_default_model_idx  | i1          | t      |     1
(6 rows)

实际上,索引会向下级联到所有分区。一旦你添加了另一个分区,它就会自动建立索引:

create table traffic_violations_p_list_demo
partition of traffic_violations_p_list
for values in ('demo');

您也可以仅在特定分区上创建索引(可能是因为您知道,应用程序正在该分区上的特定列上进行搜索):

CREATE INDEX i2 on traffic_violations_p_list_citation (make);

现在还不能支持的是,并发创建一个分区索引:

CREATE INDEX CONCURRENTLY i_con on traffic_violations_p_list (zip_codes);
ERROR:  cannot create index on partitioned table "traffic_violations_p_list" concurrently

这意味着,当你要在一个会话中创建一个分区索引时,会有锁定:

CREATE INDEX i_mun on traffic_violations_p_list (municipalities);

如果同时在另一个会话中插入一些东西,它会出现阻塞,直到在第一个会话中索引创建完成:

insert into traffic_violations_p_list ( seqid, date_of_stop ) values ( 'xxxxx', date('01.01.2023'));
-- blocks until index above is created

您可以仅在分区表上创建分区索引时限制锁定时间,但不要向下级联到分区:

create index i_demo on only traffic_violations_p_list (accident);

这样将会使索引处于无效状态:

select indisvalid from pg_index where indexrelid = 'i_demo'::regclass;
 indisvalid
------------
 f
(1 row)

现在,您可以在所有分区上并发创建索引:

CREATE INDEX CONCURRENTLY i_demo_citation on traffic_violations_p_list_citation (accident);
CREATE INDEX CONCURRENTLY i_demo_demo on traffic_violations_p_list_demo (accident);
CREATE INDEX CONCURRENTLY i_demo_esero on traffic_violations_p_list_esero(accident);
CREATE INDEX CONCURRENTLY i_demo_sero on traffic_violations_p_list_sero(accident);
CREATE INDEX CONCURRENTLY i_demo_warning on traffic_violations_p_list_warning(accident);
CREATE INDEX CONCURRENTLY i_demo_default on traffic_violations_p_list_default(accident);

一旦你有了索引,你就可以将所有索引附加到分区索引了:

ALTER INDEX i_demo ATTACH PARTITION i_demo_citation;
ALTER INDEX i_demo ATTACH PARTITION i_demo_demo;
ALTER INDEX i_demo ATTACH PARTITION i_demo_esero;
ALTER INDEX i_demo ATTACH PARTITION i_demo_sero;
ALTER INDEX i_demo ATTACH PARTITION i_demo_warning;
ALTER INDEX i_demo ATTACH PARTITION i_demo_default;

这样将会自动使分区索引变得有效:

select indisvalid from pg_index where indexrelid = 'i_demo'::regclass;
 indisvalid 
------------
 t
(1 row)

了解更多

PostgreSQL 教程