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

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

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

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

## やってみた
### テーブルを作成

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

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

### パーティションを作成

```sql
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の間のレコードをインサートしたとき、分割されて格納されます。

###データ作成

```sql
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$;
```

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

```sql
SELECT * from people_20 where some_id > 50;
```

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

```sql
SELECT * from people where age > 21;
```

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

```sql
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の新機能、デフォルトパーティションで解決ができます。

### デフォルトパーティションを作成
```sql
CREATE TABLE people_default PARTITION of people DEFAULT;
```

### データ作成
```sql
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に振り分けられているのがわかりますね。

```sql
select * from people_default;
```
# なんで動的sqlを使わなくていいのか？

## 実験

### 比較用データ作成
```sql
CREATE TABLE compare(
    name TEXT,
    age BIGINT,
    some_id BIGINT
);
```

```sql
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$;
```
何個か比較します。

```sql
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
```

```sql
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)
```

```sql
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)
```

```sql
EXPLAIN ANALYZE SELECT * from compare where age<22;
```

```sql
 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を減らすことができます。