pg10分区表举例

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