postgresql_partition.sql

CREATE TABLE orders (
    id serial,
    user_id int4,
    create_time timestamp(0) 
) PARTITION BY RANGE(user_id);


CREATE TABLE orders_100000 PARTITION OF orders FOR VALUES FROM (1) TO (100000);

CREATE TABLE orders_200000 PARTITION OF orders FOR VALUES FROM (100000) TO (200000);

CREATE TABLE orders_300000 PARTITION OF orders FOR VALUES FROM (200000) TO (300000);

CREATE TABLE orders_400000 PARTITION OF orders FOR VALUES FROM (300000) TO (400000);

CREATE TABLE orders_500000 PARTITION OF orders FOR VALUES FROM (400000) TO (500000);

CREATE TABLE orders_600000 PARTITION OF orders FOR VALUES FROM (500000) TO (600000);


create index orders_100000_user_id_index on orders_100000 using btree(user_id);
create index orders_200000_user_id_index on orders_200000 using btree(user_id);
create index orders_300000_user_id_index on orders_300000 using btree(user_id);
create index orders_400000_user_id_index on orders_400000 using btree(user_id);
create index orders_500000_user_id_index on orders_500000 using btree(user_id);
create index orders_600000_user_id_index on orders_600000 using btree(user_id);


INSERT INTO orders(user_id, create_time) SELECT round( 500000 * random() +1 ), generate_series('2016-12-01'::date,  '2017-12-01'::date, '1 minute');