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;
以上です。
感想
- テーブルロック怖い
- トリガー怖い
- インデックスも適切に
