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}}$$
);