A lot of people use pgbench to benchmark a PostgreSQL instance and pgbench is also heavily used by the PostgreSQL developers. While declarative partitioning was introduced in PostgreSQL 10 there was no support for that in pgbench, even in the current version, which is PostgreSQL 12. With PostgreSQL 13, which is currently in development, this will change and pgbench will be able to create a partitioned pgbench_accounts tables you then can run your benchmark against.
Having a look at the parameters of pgbench in PostgreSQL 13, two new ones pop up:
postgres@centos8pg:/home/postgres/ [pgdev] pgbench --help
pgbench is a benchmarking tool for PostgreSQL.
Usage:
pgbench [OPTION]... [DBNAME]
Initialization options:
-i, --initialize invokes initialization mode
-I, --init-steps=[dtgvpf]+ (default "dtgvp")
run selected initialization steps
-F, --fillfactor=NUM set fill factor
-n, --no-vacuum do not run VACUUM during initialization
-q, --quiet quiet logging (one message each 5 seconds)
-s, --scale=NUM scaling factor
--foreign-keys create foreign key constraints between tables
--index-tablespace=TABLESPACE
create indexes in the specified tablespace
--partitions=NUM partition pgbench_accounts in NUM parts (default: 0)
--partition-method=(range|hash)
partition pgbench_accounts with this method (default: range)
--tablespace=TABLESPACE create tables in the specified tablespace
--unlogged-tables create tables as unlogged tables
Options to select what to run:
-b, --builtin=NAME[@W] add builtin script NAME weighted at W (default: 1)
(use "-b list" to list available scripts)
-f, --file=FILENAME[@W] add script FILENAME weighted at W (default: 1)
-N, --skip-some-updates skip updates of pgbench_tellers and pgbench_branches
(same as "-b simple-update")
-S, --select-only perform SELECT-only transactions
(same as "-b select-only")
Benchmarking options:
-c, --client=NUM number of concurrent database clients (default: 1)
-C, --connect establish new connection for each transaction
-D, --define=VARNAME=VALUE
define variable for use by custom script
-j, --jobs=NUM number of threads (default: 1)
-l, --log write transaction times to log file
-L, --latency-limit=NUM count transactions lasting more than NUM ms as late
-M, --protocol=simple|extended|prepared
protocol for submitting queries (default: simple)
-n, --no-vacuum do not run VACUUM before tests
-P, --progress=NUM show thread progress report every NUM seconds
-r, --report-latencies report average latency per command
-R, --rate=NUM target rate in transactions per second
-s, --scale=NUM report this scale factor in output
-t, --transactions=NUM number of transactions each client runs (default: 10)
-T, --time=NUM duration of benchmark test in seconds
-v, --vacuum-all vacuum all four standard tables before tests
--aggregate-interval=NUM aggregate data over NUM seconds
--log-prefix=PREFIX prefix for transaction time log file
(default: "pgbench_log")
--progress-timestamp use Unix epoch timestamps for progress
--random-seed=SEED set random seed ("time", "rand", integer)
--sampling-rate=NUM fraction of transactions to log (e.g., 0.01 for 1%)
--show-script=NAME show builtin script code, then exit
Common options:
-d, --debug print debugging output
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=USERNAME connect as specified database user
-V, --version output version information, then exit
-?, --help show this help, then exit
Report bugs to .
That should give us partitions according to the amount of partitions and partitioning method we chose, so let’s populate a new database:
postgres@centos8pg:/home/postgres/ [pgdev] psql -c "create database pgbench" postgres CREATE DATABASE Time: 326.715 ms postgres@centos8pg:/home/postgres/ [pgdev] pgbench -i -s 10 --partitions=10 --partition-method=range --foreign-keys pgbench dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... creating 10 partitions... generating data... 100000 of 1000000 tuples (10%) done (elapsed 0.20 s, remaining 1.78 s) 200000 of 1000000 tuples (20%) done (elapsed 0.40 s, remaining 1.62 s) 300000 of 1000000 tuples (30%) done (elapsed 0.74 s, remaining 1.73 s) 400000 of 1000000 tuples (40%) done (elapsed 1.23 s, remaining 1.85 s) 500000 of 1000000 tuples (50%) done (elapsed 1.47 s, remaining 1.47 s) 600000 of 1000000 tuples (60%) done (elapsed 1.81 s, remaining 1.21 s) 700000 of 1000000 tuples (70%) done (elapsed 2.25 s, remaining 0.97 s) 800000 of 1000000 tuples (80%) done (elapsed 2.46 s, remaining 0.62 s) 900000 of 1000000 tuples (90%) done (elapsed 2.81 s, remaining 0.31 s) 1000000 of 1000000 tuples (100%) done (elapsed 3.16 s, remaining 0.00 s) vacuuming... creating primary keys... creating foreign keys... done in 5.78 s (drop tables 0.00 s, create tables 0.07 s, generate 3.29 s, vacuum 0.84 s, primary keys 0.94 s, foreign keys 0.65 s).
The pgbench_accounts table should now be partitioned by range:
postgres@centos8pg:/home/postgres/ [pgdev] psql -c "d+ pgbench_accounts" pgbench 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) Foreign-key constraints: "pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid) Referenced by: TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid) Partitions: pgbench_accounts_1 FOR VALUES FROM (MINVALUE) TO (100001), pgbench_accounts_10 FOR VALUES FROM (900001) TO (MAXVALUE), pgbench_accounts_2 FOR VALUES FROM (100001) TO (200001), pgbench_accounts_3 FOR VALUES FROM (200001) TO (300001), pgbench_accounts_4 FOR VALUES FROM (300001) TO (400001), pgbench_accounts_5 FOR VALUES FROM (400001) TO (500001), pgbench_accounts_6 FOR VALUES FROM (500001) TO (600001), pgbench_accounts_7 FOR VALUES FROM (600001) TO (700001), pgbench_accounts_8 FOR VALUES FROM (700001) TO (800001), pgbench_accounts_9 FOR VALUES FROM (800001) TO (900001)
The same should work for hash partitioning:
postgres@centos8pg:/home/postgres/ [pgdev] pgbench -i -s 10 --partitions=10 --partition-method=hash --foreign-keys pgbench dropping old tables... creating tables... creating 10 partitions... generating data... 100000 of 1000000 tuples (10%) done (elapsed 0.19 s, remaining 1.69 s) 200000 of 1000000 tuples (20%) done (elapsed 0.43 s, remaining 1.71 s) 300000 of 1000000 tuples (30%) done (elapsed 0.67 s, remaining 1.55 s) 400000 of 1000000 tuples (40%) done (elapsed 1.03 s, remaining 1.54 s) 500000 of 1000000 tuples (50%) done (elapsed 1.22 s, remaining 1.22 s) 600000 of 1000000 tuples (60%) done (elapsed 1.59 s, remaining 1.06 s) 700000 of 1000000 tuples (70%) done (elapsed 1.80 s, remaining 0.77 s) 800000 of 1000000 tuples (80%) done (elapsed 2.16 s, remaining 0.54 s) 900000 of 1000000 tuples (90%) done (elapsed 2.36 s, remaining 0.26 s) 1000000 of 1000000 tuples (100%) done (elapsed 2.69 s, remaining 0.00 s) vacuuming... creating primary keys... creating foreign keys... done in 4.99 s (drop tables 0.10 s, create tables 0.08 s, generate 2.74 s, vacuum 0.84 s, primary keys 0.94 s, foreign keys 0.30 s). postgres@centos8pg:/home/postgres/ [pgdev] psql -c "d+ pgbench_accounts" pgbench 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: HASH (aid) Indexes: "pgbench_accounts_pkey" PRIMARY KEY, btree (aid) Foreign-key constraints: "pgbench_accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid) Referenced by: TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid) Partitions: pgbench_accounts_1 FOR VALUES WITH (modulus 10, remainder 0), pgbench_accounts_10 FOR VALUES WITH (modulus 10, remainder 9), pgbench_accounts_2 FOR VALUES WITH (modulus 10, remainder 1), pgbench_accounts_3 FOR VALUES WITH (modulus 10, remainder 2), pgbench_accounts_4 FOR VALUES WITH (modulus 10, remainder 3), pgbench_accounts_5 FOR VALUES WITH (modulus 10, remainder 4), pgbench_accounts_6 FOR VALUES WITH (modulus 10, remainder 5), pgbench_accounts_7 FOR VALUES WITH (modulus 10, remainder 6), pgbench_accounts_8 FOR VALUES WITH (modulus 10, remainder 7), pgbench_accounts_9 FOR VALUES WITH (modulus 10, remainder 8).
Looks fine. Now you can easily benchmark against a partitioned pgbench_accounts table.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)