ushumpei’s blog

生活で気になったことを随時調べて書いていきます。

PostgreSQL 10 でパーティションをまるっと切り替えてみる

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_atrange で切って見ます。

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)

パーティションの切り替え

f:id:ushumpei:20181015011230j:plain

以下が概要になります。

  1. 普通のテーブルとして locations_20181011 を作成します。
  2. 現在のパーティション partitions.locations_20181011locations から Detach します
  3. 新しいパーティションとして locations_20181011locations に Attach します
  4. 必要なくなった partitions.locations_20181011 を削除します
  5. (跡片付け) 新しいパーティションとして Attach した locations_20181011partitions.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;

以上です。

感想

  • テーブルロック怖い
  • トリガー怖い
  • インデックスも適切に