Skip to content

Instantly share code, notes, and snippets.

@jonatas

jonatas/poc_100k_hypertables.sql

Last active July 21, 2022 20:47
Show Gist options
  • Save jonatas/340294dfb66cddc9af072ee21d49dfff to your computer and use it in GitHub Desktop.
Save jonatas/340294dfb66cddc9af072ee21d49dfff to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jonatas revised this gist Jul 21, 2022. 1 changed file with 38 additions and 25 deletions.
    63 changes: 38 additions & 25 deletions poc_100k_hypertables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,40 +1,53 @@
    SELECT FORMAT('CREATE TABLE conditions_%s ( time TIMESTAMPTZ NOT NULL, device INTEGER NOT NULL, temperature FLOAT NOT NULL);', i) FROM generate_series(1,300,1) i;
    \gexec
    SELECT FORMAT($$SELECT create_hypertable('conditions_%s', 'time', chunk_time_interval => INTERVAL '1 hour' );$$, i) FROM generate_series(1,300,1) i;
    \gexec

    CREATE OR REPLACE PROCEDURE setup_initial_date(table_name varchar) AS $func$
    BEGIN
    EXECUTE FORMAT($$ INSERT INTO %s
    SELECT time, (random()*30)::int, random()*80 - 40
    FROM generate_series(TIMESTAMP '2000-01-01 00:00:00',
    TIMESTAMP '2000-01-03 00:00:00',
    INTERVAL '1 second') AS time;
    $$, table_name);
    END;

    $func$ language plpgsql;
    -- First set the numbers of hypertables you'd like to test
    \set hypertables_count 100
    -- Hypertable configuration with the chunk time interval for every hypertable
    \set chunk_time_interval '''1 hour'''
    -- How much data you'd like to append for every append_data call
    \set append_interval '''1 day'''
    -- How many devices would you like to simulate in parallel
    \set number_of_devices '''1'''
    -- When the data starts
    \set start_date '''2000-01-01'''
    -- Interval between each record
    \set interval_between_records '''1 second'''

    SELECT FORMAT($$call setup_initial_date('conditions_%s');$$, i) FROM generate_series(1,300,1) i;
    SELECT FORMAT('CREATE TABLE conditions_%s ( time TIMESTAMPTZ NOT NULL, device INTEGER NOT NULL, temperature FLOAT NOT NULL);', i) FROM generate_series(1,:hypertables_count,1) i;
    \gexec

    SELECT FORMAT($$SELECT create_hypertable('conditions_%s', 'time', chunk_time_interval => INTERVAL '%s' );$$, i, :chunk_time_interval) FROM generate_series(1,:hypertables_count,1) i;
    \gexec

    CREATE OR REPLACE PROCEDURE append_data(table_name varchar) AS $func$
    CREATE OR REPLACE PROCEDURE append_data(
    table_name varchar,
    start_date varchar,
    interval_between_records varchar,
    append_interval varchar,
    chunk_time_interval varchar,
    number_of_devices varchar
    ) AS $func$
    BEGIN
    EXECUTE FORMAT($sql$
    INSERT INTO %s
    WITH latest AS materialized (SELECT time FROM %s ORDER BY time DESC LIMIT 1 )
    WITH latest AS materialized (
    SELECT '%s'::timestamp as time
    UNION ALL
    SELECT time FROM %s ORDER BY time DESC LIMIT 1 )
    SELECT a.time, a.device, random()*80 - 40 AS temperature
    FROM latest LEFT JOIN lateral (
    SELECT * FROM
    generate_series(latest.time + INTERVAL '1 second',
    latest.time + INTERVAL '2 hours', INTERVAL '1 second') AS g1(time),
    generate_series(1, 300) AS g2(device)
    ) a ON true
    $sql$, table_name, table_name);
    generate_series(
    latest.time + INTERVAL '%s',
    latest.time + INTERVAL '%s', INTERVAL '%s') AS g1(time),
    generate_series(1, %s) AS g2(device)
    ) a ON true;
    $sql$, table_name, start_date, table_name, interval_between_records, append_interval, interval_between_records, number_of_devices);
    END;
    $func$ language plpgsql;

    SELECT FORMAT($$call append_data('conditions_%s');$$, i) FROM generate_series(1,300,1) i;
    SELECT FORMAT(
    $$call append_data('conditions_%s', '%s'::varchar, '%s'::varchar, '%s'::varchar, '%s'::varchar, '%s'::varchar );$$, i,
    :start_date, :interval_between_records, :append_interval, :chunk_time_interval, :number_of_devices) FROM generate_series(1,:hypertables_count,1) i;
    \gexec

    select hypertable_name, count(1) as chunks from timescaledb_information.chunks group by 1 ;
    select count(1) as total_chunks from timescaledb_information.chunks ;
  2. jonatas revised this gist Jul 21, 2022. 1 changed file with 0 additions and 2 deletions.
    2 changes: 0 additions & 2 deletions poc_100k_hypertables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,3 @@
    select delete_job(job_id) from timescaledb_information.jobs where job_id >=1000;
    drop table conditions cascade;
    SELECT FORMAT('CREATE TABLE conditions_%s ( time TIMESTAMPTZ NOT NULL, device INTEGER NOT NULL, temperature FLOAT NOT NULL);', i) FROM generate_series(1,300,1) i;
    \gexec
    SELECT FORMAT($$SELECT create_hypertable('conditions_%s', 'time', chunk_time_interval => INTERVAL '1 hour' );$$, i) FROM generate_series(1,300,1) i;
  3. jonatas revised this gist Jul 21, 2022. No changes.
  4. jonatas revised this gist Jul 21, 2022. 1 changed file with 13 additions and 17 deletions.
    30 changes: 13 additions & 17 deletions poc_100k_hypertables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -20,27 +20,23 @@ $func$ language plpgsql;
    SELECT FORMAT($$call setup_initial_date('conditions_%s');$$, i) FROM generate_series(1,300,1) i;
    \gexec

    CREATE OR REPLACE PROCEDURE insert_massive_data(job_id int, config jsonb) LANGUAGE PLPGSQL AS
    $$

    CREATE OR REPLACE PROCEDURE append_data(table_name varchar) AS $func$
    BEGIN
    RAISE NOTICE 'Inserting in the job % with config %', job_id, config;
    EXECUTE FORMAT('INSERT INTO %s
    WITH latest AS materialized (SELECT time FROM %s
    $$ ORDER BY time DESC LIMIT 1 )
    EXECUTE FORMAT($sql$
    INSERT INTO %s
    WITH latest AS materialized (SELECT time FROM %s ORDER BY time DESC LIMIT 1 )
    SELECT a.time, a.device, random()*80 - 40 AS temperature
    FROM latest LEFT JOIN lateral (
    SELECT * FROM
    generate_series(latest.time + INTERVAL \'1 second\',
    latest.time + INTERVAL \'2 hours\', INTERVAL \'1 second\') AS g1(time),
    generate_series(latest.time + INTERVAL '1 second',
    latest.time + INTERVAL '2 hours', INTERVAL '1 second') AS g1(time),
    generate_series(1, 300) AS g2(device)
    ) a ON true ', config->table_name, config->table_name);
    -- END;
    -- COMMIT;
    END
    $$;

    ) a ON true
    $sql$, table_name, table_name);
    END;
    $func$ language plpgsql;

    SELECT add_job('insert_massive_data',
    '5 seconds',
    initial_start => now() + INTERVAL '10 seconds', table_name => 'conditions_'||i) from generate_series(1,300) i;
    SELECT FORMAT($$call append_data('conditions_%s');$$, i) FROM generate_series(1,300,1) i;
    \gexec

  5. jonatas revised this gist Jul 21, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion poc_100k_hypertables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,5 +42,5 @@ $$;

    SELECT add_job('insert_massive_data',
    '5 seconds',
    initial_start => now() + INTERVAL '10 seconds', table_name => 'conditions_'||i) from generate_series(1,100,1) i;
    initial_start => now() + INTERVAL '10 seconds', table_name => 'conditions_'||i) from generate_series(1,300) i;

  6. jonatas created this gist Jul 21, 2022.
    46 changes: 46 additions & 0 deletions poc_100k_hypertables.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,46 @@
    select delete_job(job_id) from timescaledb_information.jobs where job_id >=1000;
    drop table conditions cascade;
    SELECT FORMAT('CREATE TABLE conditions_%s ( time TIMESTAMPTZ NOT NULL, device INTEGER NOT NULL, temperature FLOAT NOT NULL);', i) FROM generate_series(1,300,1) i;
    \gexec
    SELECT FORMAT($$SELECT create_hypertable('conditions_%s', 'time', chunk_time_interval => INTERVAL '1 hour' );$$, i) FROM generate_series(1,300,1) i;
    \gexec

    CREATE OR REPLACE PROCEDURE setup_initial_date(table_name varchar) AS $func$
    BEGIN
    EXECUTE FORMAT($$ INSERT INTO %s
    SELECT time, (random()*30)::int, random()*80 - 40
    FROM generate_series(TIMESTAMP '2000-01-01 00:00:00',
    TIMESTAMP '2000-01-03 00:00:00',
    INTERVAL '1 second') AS time;
    $$, table_name);
    END;

    $func$ language plpgsql;

    SELECT FORMAT($$call setup_initial_date('conditions_%s');$$, i) FROM generate_series(1,300,1) i;
    \gexec

    CREATE OR REPLACE PROCEDURE insert_massive_data(job_id int, config jsonb) LANGUAGE PLPGSQL AS
    $$
    BEGIN
    RAISE NOTICE 'Inserting in the job % with config %', job_id, config;
    EXECUTE FORMAT('INSERT INTO %s
    WITH latest AS materialized (SELECT time FROM %s
    $$ ORDER BY time DESC LIMIT 1 )
    SELECT a.time, a.device, random()*80 - 40 AS temperature
    FROM latest LEFT JOIN lateral (
    SELECT * FROM
    generate_series(latest.time + INTERVAL \'1 second\',
    latest.time + INTERVAL \'2 hours\', INTERVAL \'1 second\') AS g1(time),
    generate_series(1, 300) AS g2(device)
    ) a ON true ', config->table_name, config->table_name);
    -- END;
    -- COMMIT;
    END
    $$;


    SELECT add_job('insert_massive_data',
    '5 seconds',
    initial_start => now() + INTERVAL '10 seconds', table_name => 'conditions_'||i) from generate_series(1,100,1) i;