PostgreSQL の 10 では宣言的パーティショニングが使えるようになったそうですね (しかし僕は 10 以前でパーティショニングしたことがなかったので感想がない)
これを使ってデータの一括削除、一括追加を実現するために、パーティションの切り替えについて練習します。
準備
Docker で PostgreSQL 10 を用意します
$ docker run --name postgres -p 5432:5432 -d postgres:10
テーブルの作成
こんな感じのテーブルを作ります。ユーザーがいつどこに居たかをひたすら集める謎のテーブルです。
| user_id |
latitude |
longitude |
created_at |
| integer |
decimal(11, 8) |
decimal(11, 8) |
timestamp |
パーティションは created_at の range で切って見ます。
create database testgres;
\c testgres
create table locations (
user_id integer not null,
latitude decimal(11, 8) not null,
longitude decimal(11, 8) not null,
created_at timestamp not null
) partition by range (created_at);
パーティションは partitions スキーマ以下に作っていきます。一秒刻みで適当なデータを作成します。 20181010 と 20181011 のパーティションを作成しました。
create schema partitions;
create table partitions.locations_20181011 partition of locations for values from ('2018-10-11') to ('2018-10-12');
insert into partitions.locations_20181011 select
(random() * 10000)::int,
130 + (20 * random())::decimal(11, 9),
20 + (20 * random())::decimal(11, 9),
generate_series('2018-10-12'::timestamp - '1 sec'::interval, '2018-10-11', -'1 sec'::interval)
;
create table partitions.locations_20181010 partition of locations for values from ('2018-10-10') to ('2018-10-11');
insert into partitions.locations_20181010 select
(random() * 10000)::int,
130 + (20 * random())::decimal(11, 9),
20 + (20 * random())::decimal(11, 9),
generate_series('2018-10-11'::timestamp - '1 sec'::interval, '2018-10-10', -'1 sec'::interval)
;
クエリしてみると、ちゃんとパーティションを使った検索が行われているのがわかります。
explain select * from locations where created_at = '20181010 12:00:00';
QUERY PLAN
Append (cost=0.00..1716.00 rows=1 width=28)
-> Seq Scan on locations_20181010 (cost=0.00..1716.00 rows=1 width=28)
Filter: (created_at = '2018-10-10 12:00:00'::timestamp without time zone)
(3 rows)
以下が概要になります。
- 普通のテーブルとして
locations_20181011 を作成します。
- 現在のパーティション
partitions.locations_20181011 を locations から Detach します
- 新しいパーティションとして
locations_20181011 を locations に Attach します
- 必要なくなった
partitions.locations_20181011 を削除します
- (跡片付け) 新しいパーティションとして Attach した
locations_20181011 を partitions.locations_20181011 にリネームします
手順 3, 4 と、なるべく alter table locations をまとめて行うことで、テーブルロック時間を少なくしていこうと思います。
新しく locations_20181010 を普通のテーブルとして public スキーマに作成します。(すでにあるものと被らなければ他の方法でもいい)
ここでは check 制約を追加して、Attach される際のパーティションの制約チェックを先立って行っています。これがないとテーブルロック時間が attach の際に増えてしまうそうです。
create table locations_20181011 (
user_id integer not null,
latitude decimal(11, 8) not null,
longitude decimal(11, 8) not null,
created_at timestamp not null,
check (created_at >= '2018-10-11' and created_at < '2018-10-12')
);
insert into locations_20181011 select
(random() * 10000)::int,
130 + (20 * random())::decimal(11, 9),
20 + (20 * random())::decimal(11, 9),
generate_series('2018-10-12'::timestamp - '1 sec'::interval, '2018-10-11', -'1 sec'::interval)
;
パーティションの切り替えを行います。現在使用されている partitions.locations_20181011 を切り離し、新しく作った locations_20181011 をくっつけます。
begin;
alter table locations detach partition partitions.locations_20181011;
alter table locations attach partition locations_20181011 for values from ('2018-10-11') to ('2018-10-12');
commit;
いらなくなったパーティションの削除、跡片付けとして作成したパーティションのリネームを行います。
drop table partitions.locations_20181011;
alter table locations_20181011 set schema partitions;
以上です。
感想
- テーブルロック怖い
- トリガー怖い
- インデックスも適切に