宣言的パーティションを使ってみる

弊社のサービスの一部では、テーブル名に分割キーを含める+継承+参照系には動的sqlをつかうことでパーティショニングを実現しています。
ここではPostgreSQL10以降の機能である宣言的パーティションで、パーティショニングを実現してみます。

パーティショニングとは

レコードのいずれかのキーによりテーブルを分けることで、１テーブル内のレコードを減らし、クエリのパフォーマンスを向上させる手法のことです。
そのテーブル内で完結するクエリにおいてはパフォーマンスの向上が見込めますが。分割されたものを複数参照するようなクエリを使う場合は、パフォーマンスが悪くなることもあります。

ここでは基本的に水平パーティショニングについて考えます。例えば弊社における上記のようなパーティショニングは水平パーティショニングにあたります。

やってみた

テーブルを作成

CREATE TABLE people (
    name TEXT,
    age BIGINT,
    some_id BIGINT
) PARTITION BY LIST(age);

PARTITION BYで分割キーを指定します。値を列挙するLISTと、区間を指定できるRANGEが使えます。

パーティションを作成

CREATE TABLE people_20 PARTITION OF people
    FOR VALUES IN(20);
CREATE TABLE people_21 PARTITION OF people
    FOR VALUES IN (21);
CREATE TABLE people_22 PARTITION OF people
    FOR VALUES IN (22);

このように設定することでageが20から22の間のレコードをインサートしたとき、分割されて格納されます。

###データ作成

DO $BODY$ DECLARE i BIGINT;
  BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO people VALUES(
      'name_' || i::TEXT
      ,20 + i%3
      ,i
    );  
  END LOOP;
END
$BODY$;

参照する場合、以下のようにすればよいです。

SELECT * from people_20 where some_id > 50;

またこのように分割されていないpeopleを参照することもできます。

SELECT * from people where age > 21;

このときageが例えば50のものをinsertしようとするとエラーになります。

INSERT INTO people VALUES(
    'error_man'
    ,50
    ,10
);

ERROR:  no partition of relation "people" found for row
DETAIL:  Partition key of the failing row contains (age) = (50).

これでは困る場合、PostgreSQL11の新機能、デフォルトパーティションで解決ができます。

デフォルトパーティションを作成

CREATE TABLE people_default PARTITION of people DEFAULT;

データ作成

TRUNCATE TABLE people;
DO $BODY$ DECLARE i BIGINT;
  BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO people VALUES(
      'name_' || i::TEXT
      ,20 + i%5
      ,i
    );  
  END LOOP;
END
$BODY$;

以下のクエリをたたくとageが23と24のレコードはpeople_defaultに振り分けられているのがわかりますね。

select * from people_default;

なんで動的sqlを使わなくていいのか？

実験

比較用データ作成

CREATE TABLE compare(
    name TEXT,
    age BIGINT,
    some_id BIGINT
);

TRUNCATE TABLE compare;
DO $BODY$ DECLARE i BIGINT;
  BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO compare VALUES(
      'name_' || i::TEXT
      ,20 + i%5
      ,i
    );  
  END LOOP;
END
$BODY$;

何個か比較します。

select COUNT(*) from people;
EXPLAIN ANALYZE SELECT * from people where age=20;

 100000
(1 row)

 Seq Scan on people_20  (cost=0.00..398.00 rows=20000 width=26) (actual time=0.007..1.877 rows=20000 loops=1)
   Filter: (age = 20)
 Planning Time: 0.157 ms
 Execution Time: 2.391 ms
(4 rows)q

select COUNT(*) from compare;
EXPLAIN ANALYZE SELECT * from compare where age=20;

100000
(1 row)

 Seq Scan on compare  (cost=0.00..1986.00 rows=19823 width=26) (actual time=0.008..6.743 rows=20000 loops=1)
   Filter: (age = 20)
   Rows Removed by Filter: 80000
 Planning Time: 0.067 ms
 Execution Time: 7.186 ms
(5 rows)

EXPLAIN ANALYZE SELECT * from people where age<22;

 Append  (cost=0.00..1791.01 rows=40001 width=26) (actual time=0.010..7.713 rows=40000 loops=1)
   ->  Seq Scan on people_20  (cost=0.00..398.00 rows=20000 width=26) (actual time=0.009..1.804 rows=20000 loops=1)
         Filter: (age < 22)
   ->  Seq Scan on people_21  (cost=0.00..398.00 rows=20000 width=26) (actual time=0.009..1.740 rows=20000 loops=1)
         Filter: (age < 22)

EXPLAIN ANALYZE SELECT * from compare where age<22;

 Seq Scan on compare  (cost=0.00..1986.00 rows=39990 width=26) (actual time=0.010..6.940 rows=40000 loops=1)
   Filter: (age < 22)
   Rows Removed by Filter: 60000
 Planning Time: 0.039 ms
 Execution Time: 7.821 ms
(5 rows)

分割元を見るようなクエリでも、分割したテーブルを参照してくれるようです！
分割先のテーブル名を参照する必要がないので動的sqlを減らすことができます。