pg 10 分區表舉例

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