create-tables.sql

-- The number of partitions
\set N 1024

SELECT format('CREATE TABLE students_%s(id int, name text) PARTITION BY RANGE (id)', :N) \gexec
SELECT format('CREATE TABLE child_%s_of_students_%s PARTITION OF students_%s FOR VALUES FROM (%s) TO (%s)', i, :N, :N, i * 100, (i + 1) * 100) FROM generate_series(0, :N - 1) AS i \gexec
SELECT format('INSERT INTO students_%s SELECT id, md5(id::text) FROM generate_series(0, %s) AS id', :N, :N * 100 - 1) \gexec

SELECT format('CREATE TABLE scores_%s(student_id int, course int, score int) PARTITION BY RANGE (student_id)', :N) \gexec
SELECT format('CREATE TABLE child_%s_of_scores_%s PARTITION OF scores_%s FOR VALUES FROM (%s) TO (%s)', i, :N, :N, i * 100, (i + 1) * 100) FROM generate_series(0, :N - 1) AS i \gexec
SELECT format('INSERT INTO scores_%s SELECT student_id, course, (random() * 100)::int FROM generate_series(0, %s) AS student_id, generate_series(1, 4) AS course', :N, :N * 100 - 1) \gexec

SELECT format('CREATE TABLE gpas_%s(student_id int, gpa double precision) PARTITION BY RANGE (student_id)', :N) \gexec
SELECT format('CREATE TABLE child_%s_of_gpas_%s PARTITION OF gpas_%s FOR VALUES FROM (%s) TO (%s)', i, :N, :N, i * 100, (i + 1) * 100) FROM generate_series(0, :N - 1) AS i \gexec
SELECT format('INSERT INTO gpas_%s SELECT student_id, random() * 5 FROM generate_series(0, %s) AS student_id', :N, :N * 100 - 1) \gexec

SELECT format('ALTER TABLE students_%s ADD PRIMARY KEY (id)', :N) \gexec
SELECT format('CREATE INDEX ON scores_%s (student_id)', :N) \gexec
SELECT format('ALTER TABLE gpas_%s ADD PRIMARY KEY (student_id)', :N) \gexec

SELECT format('ANALYZE students_%s', :N) \gexec
SELECT format('ANALYZE scores_%s', :N) \gexec
SELECT format('ANALYZE gpas_%s', :N) \gexec