PostgreSQL の宣言的パーティションでパーティションのメンテナンスを行う。
- pgbench(PostgreSQL 13) でパーティションテーブルを作成して、データをロードする。
$ /usr/pgsql-13/bin/pgbench -i -s 100 -U awsuser -h aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com -d postgres --partitions=10 --partition-method=range
- クラスターエンドポイントに接続する。
$ psql "host=aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=postgres port=5432"
- 定義を確認する
> \d+ pgbench_accounts Partitioned table "public.pgbench_accounts" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------+---------------+-----------+----------+---------+----------+--------------+------------- aid | integer | | not null | | plain | | bid | integer | | | | plain | | abalance | integer | | | | plain | | filler | character(84) | | | | extended | | Partition key: RANGE (aid) Indexes: "pgbench_accounts_pkey" PRIMARY KEY, btree (aid) Partitions: pgbench_accounts_1 FOR VALUES FROM (MINVALUE) TO (1000001), pgbench_accounts_10 FOR VALUES FROM (9000001) TO (MAXVALUE), pgbench_accounts_2 FOR VALUES FROM (1000001) TO (2000001), pgbench_accounts_3 FOR VALUES FROM (2000001) TO (3000001), pgbench_accounts_4 FOR VALUES FROM (3000001) TO (4000001), pgbench_accounts_5 FOR VALUES FROM (4000001) TO (5000001), pgbench_accounts_6 FOR VALUES FROM (5000001) TO (6000001), pgbench_accounts_7 FOR VALUES FROM (6000001) TO (7000001), pgbench_accounts_8 FOR VALUES FROM (7000001) TO (8000001), pgbench_accounts_9 FOR VALUES FROM (8000001) TO (9000001)
- テーブルを作成する
> CREATE TABLE pgbench_accounts_1_new ( aid int, bid int, abalance int, filler char(84), PRIMARY KEY (aid) );
- インデックスを作成する
create index idx_pgbench_accounts_1_bid on pgbench_accounts_1(bid);
環境
- DB: Aurora PostgreSQL 12.4
- Client: pgbench、psql(PostgreSQL 13)