Kの系譜

ママは肉食 パパは草食 娘は雑食

【OSS】PostgreSQL 10 パーティションInsert性能検証

f:id:kkkklog:20171013133331j:plain

1.概要

PostgreSQL10でテーブルパーティションの INSERT性能検証

検証内容としては、
各テーブルパーティション機能で「2016年12月1日から12月10日まで」の
10日間の日単位のパーティションを作成し、クエリで生成される864,000件の
日時データをINSERTしたときの時間計測を行う

2.参考

PostgreSQL10でテーブルパーティションへのINSERT性能が大幅Upする件 - Qiita

3.従来型のテーブルパーティションの検証

3-1.従来型のテーブルパーティションの作成

まずはバージョンから

postgres=# SELECT version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.6.5, compiled by Visual C++ build 1800, 64-bit
(1 行)

従来型テーブルパーティション機能によるパーティションを配置するスキーマを作成

postgres=# CREATE SCHEMA traditional;
CREATE SCHEMA

作成したスキーマをデフォルトで参照するようにsearch_pathを設定

postgres=# SET search_path TO traditional, "$user", public;
SET

パーティションの親テーブルを作成

postgres=# CREATE TABLE parent (time timestamp PRIMARY KEY, val text);
CREATE TABLE

親テーブルに対するINSERTを子テーブルに振り分けるためのトリガを定義

postgres=# CREATE OR REPLACE FUNCTION insert_parent_function () RETURNS TRIGGERAS $$
postgres$# BEGIN
postgres$#   EXECUTE 'INSERT INTO child_' || to_char(NEW.time, 'YYYYMMDD') || 'VALUES (($1).*)' USING NEW;
postgres$#     RETURN NULL;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTIONgsql;

postgres=# CREATE TRIGGER insert_parent_trigger BEFORE INSERT ON parent FOR EACH ROW EXECUTE PROCEDURE insert_parent_function();
CREATE TRIGGER

2016年12月1日から10日までの各日付の子テーブルを作成

postgres=# CREATE TABLE child_20161201 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-01 00:00:00' <= time AND time < '2016-12-02 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161202 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-02 00:00:00' <= time AND time < '2016-12-03 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161203 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-03 00:00:00' <= time AND time < '2016-12-04 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161204 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-04 00:00:00' <= time AND time < '2016-12-05 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161205 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-05 00:00:00' <= time AND time < '2016-12-06 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161206 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-06 00:00:00' <= time AND time < '2016-12-07 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161207 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-07 00:00:00' <= time AND time < '2016-12-08 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161208 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-08 00:00:00' <= time AND time < '2016-12-09 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161209 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-09 00:00:00' <= time AND time < '2016-12-10 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

postgres=# CREATE TABLE child_20161210 (LIKE parent INCLUDING INDEXES, CHECK('2016-12-10 00:00:00' <= time AND time < '2016-12-11 00:00:00')) INHERITS (parent);
NOTICE:  継承される定義で列 "time" をマージしています
NOTICE:  継承される定義で列 "val" をマージしています
CREATE TABLE

3-2.従来型のテーブルパーティションの性能テスト

約25秒程度だった、こんなもんか

postgres=# select now();
              now
-------------------------------
 2017-10-13 13:06:05.029253+09
(1 行)


postgres=# INSERT INTO parent SELECT time, 'TEST' FROM generate_series('2016-12-01 00:00:00', '2016-12-10 23:59:59', '1 second'::interval) time;
INSERT 0 0
postgres=# select now();
              now
-------------------------------
 2017-10-13 13:06:29.721253+09
(1 行)

4.PostgreSQL 10 新機能のテーブルパーティションの検証

4-1.PostgreSQL 10 新機能のテーブルパーティションの作成

まずはバージョンから

postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit
(1 row)

PostgreSQL10のテーブルパーティション機能によるパーティションを配置するスキーマを作成

postgres=# CREATE SCHEMA modern;
CREATE SCHEMA

作成したスキーマをデフォルトで参照するようにsearch_pathを設定

postgres=# SET search_path TO modern, "$user", public;
SET

パーティションの親テーブルを作成

postgres=# CREATE TABLE parent (time timestamp, val text) PARTITION BY RANGE (time);
CREATE TABLE

2016年12月1日から10日までの各日付の子テーブルを作成

postgres=# CREATE TABLE child_20161201 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-01 00:00:00') TO ('2016-12-02 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161202 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-02 00:00:00') TO ('2016-12-03 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161203 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-03 00:00:00') TO ('2016-12-04 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161204 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-04 00:00:00') TO ('2016-12-05 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161205 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-05 00:00:00') TO ('2016-12-06 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161206 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-06 00:00:00') TO ('2016-12-07 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161207 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-07 00:00:00') TO ('2016-12-08 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161208 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-08 00:00:00') TO ('2016-12-09 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161209 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-09 00:00:00') TO ('2016-12-10 00:00:00');
CREATE TABLE
postgres=# CREATE TABLE child_20161210 PARTITION OF parent (PRIMARY KEY(time)) FOR VALUES FROM ('2016-12-10 00:00:00') TO ('2016-12-11 00:00:00');
CREATE TABLE

4-2.PostgreSQL 10 新機能のテーブルパーティションの性能テスト

約8秒程度だった

postgres=# select now();
              now
-------------------------------
 2017-10-13 13:04:57.517853+09
(1 row)


postgres=# INSERT INTO parent SELECT time, 'TEST' FROM generate_series('2016-12-
01 00:00:00', '2016-12-10 23:59:59', '1 second'::interval) time;
INSERT 0 864000
postgres=# select now();
              now
-------------------------------
 2017-10-13 13:05:04.847253+09
(1 row)

5 まとめ

PostgreSQL 9.6 だと約25秒かかったのが
PostgreSQL 10 だと約8秒と短くなった
1/3短くなった

time での検証だったけど文字列だと顕著に差が出るのかな
そのうちやろうと思う

ほなまた