一月 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)