Partitioning benchmark

create_partman.sql

CREATE TABLE journal (
    id      SERIAL PRIMARY KEY,
    dt      TIMESTAMP NOT NULL,
    level   INTEGER,
    msg     TEXT
);
CREATE INDEX journal_dt_idx ON journal (dt);
SELECT create_parent('public.journal', 'dt', 'time', 'daily', NULL, 290, NULL, '2016-01-01');
INSERT INTO journal (dt, level, msg)
SELECT g, random()*6, md5(g::text)
FROM generate_series('2016-01-01'::date, '2016-12-31'::date, '30 seconds') as g;

create_pathman.sql

CREATE TABLE journal (
    id      SERIAL PRIMARY KEY,
    dt      TIMESTAMP NOT NULL,
    level   INTEGER,
    msg     TEXT
);
CREATE INDEX journal_dt_idx ON journal (dt);
SELECT create_range_partitions('journal', 'dt', '2016-01-01'::date, '1 day'::interval, 365);
INSERT INTO journal (dt, level, msg)
    SELECT g, random()*6, md5(g::text)
    FROM generate_series('2016-01-01'::date, '2016-12-31'::date, '30 seconds') as g;

create_plain.sql

CREATE TABLE journal (
    id      SERIAL PRIMARY KEY,
    dt      TIMESTAMP NOT NULL,
    level   INTEGER,
    msg     TEXT
);
CREATE INDEX journal_dt_idx ON journal (dt);
INSERT INTO journal (dt, level, msg)
    SELECT g, random()*6, md5(g::text)
    FROM generate_series('2016-01-01'::date, '2016-12-31'::date, '30 seconds') as g;

insert.sql

\setrandom days 0 364
\setrandom seconds 0 86400
INSERT INTO journal (dt, level, msg) SELECT '2016-01-01'::date + '1 second'::interval * :seconds + '1 day'::interval * :days, ceil(6 * random()), md5((random()*10000)::text);

select_day.sql

\setrandom day 0 365
SELECT * FROM journal WHERE dt >= '2016-01-01'::date + '1 day'::interval * :day AND dt < '2016-01-01'::date + '1 day'::interval * :day + '1 day';

select_one.sql

\setrandom days 0 365
\setrandom minutes 0 1440
SELECT * FROM journal WHERE dt = '2016-01-01'::date + '1 day'::interval * :days + '1 minute'::interval * :minutes;

update.sql

\setrandom days 0 364
\setrandom halfminutes 0 1440
UPDATE journal SET level = ceil(6 * random()), msg = md5((random()*10000)::text) WHERE dt = '2016-01-01'::date + '1 minute'::interval * :minutes + '1 day'::interval * :days;