PostgreSQL レンジパーティション(range partitioning)・リストパーティション(list partitioning)

リストパーティションのサンプルが見つけづらかったので、比較的にまとめてみました。
(この記事では、水平分割のみ取り扱います)

２つのパーティショニング方法では、いずれも同じデータを挿入していますが、
異なる分割定義に基づいて、異なるテーブルにデータが格納されます。

参考) https://www.postgresql.jp/document/current/html/ddl-partitioning.html

まずは、レンジパーティション

範囲を From 〜 To で指定してテーブルを分割する
Toは"以下"ではなく、"未満"として評価される
定義範囲外は例外が発生するので、defaultを仕掛けておく

psql

-- テーブル作成 (親)
drop table if exists range_partition;
create table range_partition (
    id serial
    , pref text
    , date date
    , event text
) partition by range (date)
;

-- テーブル作成 (子)
create table range_partition_child_2023 partition of range_partition for values from ('2023-01-01') to ('2024-01-01');
create table range_partition_child_2024 partition of range_partition for values from ('2024-01-01') to ('2025-01-01');
create table range_partition_child_default partition of range_partition default;

-- 主キー作成
alter table range_partition_child_2023 add constraint range_partition_child_2023_pkey primary key (id, pref);
alter table range_partition_child_2024 add constraint range_partition_child_2024_pkey primary key (id, pref);
alter table range_partition_child_default add constraint range_partition_child_default_pkey primary key (id, pref);

-- インデックス作成
create index on range_partition(date);

-- 手キー・インデックス確認
select tablename, indexname from pg_indexes where tablename like '%partition%' order by tablename;

-- データ挿入
insert into range_partition (date, pref, event) values ('2023-01-01', 'Aichi', 'event1');
insert into range_partition (date, pref, event) values ('2023-12-31', 'Aichi', 'event2');
insert into range_partition (date, pref, event) values ('2024-01-01', 'Aichi', 'event3');
insert into range_partition (date, pref, event) values ('2024-12-31', 'Aichi', 'event4');
insert into range_partition (date, pref, event) values ('2023-01-01', 'Gifu', 'event5');
insert into range_partition (date, pref, event) values ('2023-12-31', 'Gifu', 'event6');
insert into range_partition (date, pref, event) values ('2024-01-01', 'Gifu', 'event7');
insert into range_partition (date, pref, event) values ('2024-12-31', 'Gifu', 'event8');
insert into range_partition (date, pref, event) values ('2023-01-01', 'Mie', 'event9');
insert into range_partition (date, pref, event) values ('2023-12-31', 'Mie', 'event10');
insert into range_partition (date, pref, event) values ('2024-01-01', 'Mie', 'event11');
insert into range_partition (date, pref, event) values ('2024-12-31', 'Mie', 'event12');
insert into range_partition (date, pref, event) values ('2025-01-01', 'Tokyo', 'event13');

-- テータ確認
select * from range_partition;
select * from range_partition_child_2023;
select * from range_partition_child_2024;
select * from range_partition_child_default;

-- テーブル確認(レコード件数統計あり)
select schemaname, relname, n_live_tup from pg_stat_all_tables where relname like '%partition%' and schemaname <> 'pg_catalog' order by relname;

続けて、リストパーティション

値リストに基づいて、テーブルを分割する
リストを仕掛けられるのは、単一カラムのみ
こちらも未定義が来ると例外が発生するので、default指定付き

psql

drop table if exists list_partition;
create table list_partition (
    id serial
    , date date
    , pref text
    , event text
) partition by list (pref)
;

-- テーブル作成 (子)
create table list_partition_child_aichi partition of list_partition for values in ('Aichi');
create table list_partition_child_gifu partition of list_partition for values in ('Gifu');
create table list_partition_child_mie partition of list_partition for values in ('Mie');
create table list_partition_child_default partition of list_partition default;

-- 主キー作成
alter table list_partition_child_aichi add constraint list_partition_child_aichi_pkey primary key (id, pref);
alter table list_partition_child_gifu add constraint list_partition_child_gifu_pkey primary key (id, pref);
alter table list_partition_child_mie add constraint list_partition_child_mie_pkey primary key (id, pref);
alter table list_partition_child_default add constraint list_partition_child_default_pkey primary key (id, pref);

-- インデックス作成
create index on list_partition(date);

-- 手キー・インデックス確認
select tablename, indexname from pg_indexes where tablename like '%partition%' order by tablename;

-- データ挿入
insert into list_partition (date, pref, event) values ('2023-01-01', 'Aichi', 'event1');
insert into list_partition (date, pref, event) values ('2023-12-31', 'Aichi', 'event2');
insert into list_partition (date, pref, event) values ('2024-01-01', 'Aichi', 'event3');
insert into list_partition (date, pref, event) values ('2024-12-31', 'Aichi', 'event4');
insert into list_partition (date, pref, event) values ('2023-01-01', 'Gifu', 'event5');
insert into list_partition (date, pref, event) values ('2023-12-31', 'Gifu', 'event6');
insert into list_partition (date, pref, event) values ('2024-01-01', 'Gifu', 'event7');
insert into list_partition (date, pref, event) values ('2024-12-31', 'Gifu', 'event8');
insert into list_partition (date, pref, event) values ('2023-01-01', 'Mie', 'event9');
insert into list_partition (date, pref, event) values ('2023-12-31', 'Mie', 'event10');
insert into list_partition (date, pref, event) values ('2024-01-01', 'Mie', 'event11');
insert into list_partition (date, pref, event) values ('2024-12-31', 'Mie', 'event12');
insert into list_partition (date, pref, event) values ('2025-01-01', 'Tokyo', 'event13');

-- テータ確認
select * from list_partition;
select * from list_partition_child_aichi;
select * from list_partition_child_gifu;
select * from list_partition_child_mie;
select * from list_partition_child_default;

-- テーブル確認(レコード件数統計あり)
select schemaname, relname, n_live_tup from pg_stat_all_tables where relname like '%partition%' and schemaname <> 'pg_catalog' order by relname;

最後に補足

レコードをupdateした場合、分割先が再評価され、必要に応じたテーブル間移動が自動的に行われます。