— partition table
CREATE TABLE users(
user_id serial not null,
user_name varchar(20),
logdate timestamp(0) with time zone not null
) PARTITION BY RANGE (logdate);
— table partied by month, create pk
CREATE TABLE users_y2017m01 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-01-01’) TO (‘2017-02-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m02 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-02-01’) TO (‘2017-03-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m03 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-03-01’) TO (‘2017-04-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m04 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-04-01’) TO (‘2017-05-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m05 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-05-01’) TO (‘2017-06-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m06 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-06-01’) TO (‘2017-07-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m07 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-07-01’) TO (‘2017-08-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m08 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-08-01’) TO (‘2017-09-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m09 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-09-01’) TO (‘2017-10-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m10 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-10-01’) TO (‘2017-11-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m11 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-11-01’) TO (‘2017-12-01’) WITH (parallel_workers = 4);
CREATE TABLE users_y2017m12 PARTITION OF users(user_id primary key, user_name, logdate) FOR VALUES FROM (‘2017-12-01’) TO (‘2018-01-01’) WITH (parallel_workers = 4);
— index on partied table
CREATE INDEX ON users_y2017m01 (logdate);
CREATE INDEX ON users_y2017m02 (logdate);
CREATE INDEX ON users_y2017m03 (logdate);
CREATE INDEX ON users_y2017m04 (logdate);
CREATE INDEX ON users_y2017m05 (logdate);
CREATE INDEX ON users_y2017m06 (logdate);
CREATE INDEX ON users_y2017m07 (logdate);
CREATE INDEX ON users_y2017m08 (logdate);
CREATE INDEX ON users_y2017m09 (logdate);
CREATE INDEX ON users_y2017m10 (logdate);
CREATE INDEX ON users_y2017m11 (logdate);
CREATE INDEX ON users_y2017m12 (logdate);
