pgbench でパーティションを使う

Amazon Linux 2 に PostgreSQL 13 をインストールする - ablog
データをロードする

$ /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"Password for user awsuser:
psql (13.3, server 12.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \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)

postgres=>

参照負荷をかける

$ /usr/pgsql-13/bin/pgbench -Sn -c 4 -j 4 -t 1000 -U awsuser -h aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com -d postgres -p 5432

パーティションを detach してみる

$ psql "host=aurora-postgres124.cluster-********.ap-northeast-1.rds.amazonaws.com user=awsuser dbname=postgres port=5432"Password for user awsuser:

> postgres=> ALTER TABLE pgbench_accounts DETACH PARTITION pgbench_accounts_1;
postgres=> ALTER TABLE pgbench_accounts attach PARTITION pgbench_accounts_1_old FOR VALUES FROM (MINVALUE) TO (1000001);