22.3. Creating a Database #
In this example, the table pgbench_branches
is created, as well as colocated tables pgbench_accounts
and pgbench_history
. Each partition of the pgbench_history
table is additionally subpartitioned by range.
CREATE TABLE pgbench_branches ( bid integer NOT NULL PRIMARY KEY, bbalance integer, filler character(88) ) WITH (distributed_by = 'bid', num_parts = 8); CREATE TABLE pgbench_accounts ( aid integer NOT NULL, bid integer, abalance integer, filler character(84), PRIMARY KEY (bid, aid) ) WITH (distributed_by = 'bid', num_parts = 8, colocate_with = 'pgbench_branches'); CREATE TABLE public.pgbench_history ( tid integer, bid integer, aid integer, delta integer, mtime timestamp without time zone, filler character(22) ) WITH (distributed_by = 'bid', colocate_with = 'pgbench_branches', partition_by = 'mtime', partition_bounds = $${{minvalue, '2021-01-01 00:00'},{'2021-01-01 00:00', '2022-01-01 00:00'},{'2022-01-01 00:00', maxvalue}}$$ );