CREATE TABLE s_tbl (
section_num int NOT NULL,
id bigserial NOT NULL,
foo int,
PRIMARY KEY (section_num, id)
) PARTITION BY RANGE (section_num);
CREATE OR REPLACE PROCEDURE create_partitions(a int, b int)
LANGUAGE plpgsql AS $$
DECLARE
i int;
BEGIN
FOR i IN a..b-1 LOOP
EXECUTE format(
'CREATE TABLE s_tbl_%s PARTITION OF s_tbl FOR VALUES FROM (%s) TO (%s);',
i, i, i + 1
);
END LOOP;
END $$;
CALL create_partitions(0,500);
CREATE TABLE r_tbl (
section_num int NOT NULL,
id bigserial NOT NULL,
foo int,
PRIMARY KEY (section_num, id)
);
CREATE TABLE batch_tbl AS
SELECT
floor(random() * 500)::int section_num,
floor(random() * 100000)::int foo
FROM generate_series(1, 1000000);
EXPLAIN (analyze, verbose, buffers, costs, settings, timing, wal)
INSERT INTO r_tbl (section_num, foo) TABLE batch_tbl;
EXPLAIN (analyze, verbose, buffers, costs, settings, timing, wal)
INSERT INTO s_tbl (section_num, foo) TABLE batch_tbl;
Telegram: DB developers channel