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);
本文标题:pg10分区表举例
分享网址:http://myzitong.com/article/jsopgi.html