jonatas

ADA.rb

Sonic PI

&

Timescale

Jônatas Davi Paganini

@jonatasdp

@jonatasdp

jonatas

DROP TABLE "ticks" CASCADE;

CREATE TABLE "ticks" ("time" timestamp with time zone not null, "symbol" text, "price" decimal, "volume" float);

SELECT create_hypertable('ticks', 'time', chunk_time_interval => INTERVAL '1 week');

ALTER TABLE ticks SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time',
  timescaledb.compress_segmentby = 'symbol'

jonatas

SELECT * FROM crosstab($$
        WITH city_names AS (
                SELECT DISTINCT city_name as name
                FROM weather_metrics order by 1
        ),
pairs as (
SELECT a.name as first, b.name as second
FROM city_names a
JOIN city_names b ON true --# a.name != b.name
),

jonatas

watch the talk here

ADA.rb

Ruby

&

Timescale

Jônatas Davi Paganini

@jonatasdp

jonatas

require 'bundler/inline'

gemfile(true) do 
  gem 'timescaledb'
  gem 'pry'
end

require 'pp'
# ruby caggs.rb postgres://user:pass@host:port/db_name
ActiveRecord::Base.establish_connection( ARGV.last)

jonatas

-- 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'''

jonatas

select delete_job(job_id) from timescaledb_information.jobs where job_id >=1000;
drop table conditions cascade;
CREATE TABLE conditions (
      time TIMESTAMPTZ NOT NULL,
      device INTEGER NOT NULL,
      temperature FLOAT NOT NULL
);
SELECT * FROM create_hypertable('conditions', 'time');

INSERT INTO conditions

jonatas

websocket-client
psycopg2

jonatas

select delete_job(job_id) from timescaledb_information.jobs where job_id >=1000;
drop table conditions cascade;
CREATE TABLE conditions (
      time TIMESTAMPTZ NOT NULL,
      device INTEGER NOT NULL,
      temperature FLOAT NOT NULL
);
SELECT * FROM create_distributed_hypertable('conditions', 'time', 'device');
ALTER TABLE conditions SET (timescaledb.compress, timescaledb.compress_orderby='time');

jonatas

set search_path to public, toolkit_experimental, timescaledb_experimental ;

DROP TABLE if exists ticks CASCADE;

CREATE TABLE ticks ( time TIMESTAMP NOT NULL, symbol varchar, price decimal, volume int);

SELECT create_hypertable('ticks', 'time');
CREATE MATERIALIZED VIEW tv_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', time) as bucket,