Skip to content

Instantly share code, notes, and snippets.

Avatar
🏠
Working from home

Jônatas Davi Paganini jonatas

🏠
Working from home
View GitHub Profile
View massive_distributed_inserts.sql
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');
View timevector.sql
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,
View distributed_hypertable.sql
drop table if exists table1;
create table if not exists table1(time timestamp, category varchar, value numeric(10,2));
SELECT create_distributed_hypertable('table1','time','category',3, chunk_time_interval => Interval '3 hours');
--SELECT add_dimension('table1', 'col1', number_partitions => 3);
INSERT INTO table1
SELECT time, 'category-'||((random()*3)::int), (random()*100)::numeric(10,2)
FROM generate_series(TIMESTAMP '2000-01-01 00:01:00',
TIMESTAMP '2000-01-01 00:01:00' + INTERVAL '5 minutes',
INTERVAL '1 second') AS time;
View caggs_scaled.sql
DROP TABLE if exists ticks CASCADE;
DROP view if exists ohlc_1m CASCADE;
CREATE TABLE ticks
( time TIMESTAMP NOT NULL,
symbol varchar,
price decimal,
volume int);
SELECT create_hypertable('ticks', 'time', chunk_time_interval => INTERVAL '1 day');
View notify.sql
drop table batteries cascade;
CREATE TABLE batteries (t timestamp not null, batt_uid varchar, charge int);
SELECT create_hypertable('batteries', 't');
DROP FUNCTION IF EXISTS watch_charge;
CREATE OR REPLACE FUNCTION watch_charge(INOUT t timestamp, INOUT batt_uid varchar, INOUT charge int) AS
$BODY$
BEGIN
IF charge > 100 then
raise notice 'Battery % charge is too high: %', batt_uid, charge;
View caggs.sql
DROP TABLE if exists ticks CASCADE;
DROP view if exists ohlc_1m CASCADE;
CREATE TABLE ticks ( time TIMESTAMP NOT NULL, symbol varchar, price decimal, volume int);
SELECT create_hypertable('ticks', 'time');
CREATE MATERIALIZED VIEW ohlc_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1m', time) as bucket,
symbol,
FIRST(price, time) as open,
@jonatas
jonatas / caggs.sql
Created Sep 15, 2021
Continuous aggregates example - TimescaleDB
View caggs.sql
DROP TABLE if exists ticks CASCADE;
DROP view if exists ohlc_1m CASCADE;
CREATE TABLE ticks ( time TIMESTAMP NOT NULL, symbol varchar, price decimal, volume int);
SELECT create_hypertable('ticks', 'time');
CREATE MATERIALIZED VIEW ohlc_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1m', time) as bucket,
symbol,
FIRST(price, time) as open,
View Gemfile
source 'https://rubygems.org'
git_source(:github) { |repo| "https://github.com/#{repo}.git" }
ruby '2.6.5'
# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '~> 6.0.3', '>= 6.0.3.6'
# Use postgresql as the database for Active Record
gem 'pg', '>= 0.18', '< 2.0'
# Use Puma as the app server
View massive_inserts.sql
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
jonatas / hypertable_trigger.sql
Last active Nov 19, 2021
Timescale continuous aggregates over top of continuous aggregates - Caggs over top of caggs (POC)
View hypertable_trigger.sql
DROP TABLE ticks CASCADE;
DROP TABLE ohlc_1s CASCADE;
CREATE TABLE ticks ( time TIMESTAMP NOT NULL, symbol varchar, price decimal, volume int);
CREATE TABLE ohlc_1s ( time TIMESTAMP NOT NULL, symbol varchar, o decimal, h decimal, l decimal, c decimal, v int);
SELECT create_hypertable('ticks', 'time');
SELECT create_hypertable('ohlc_1s', 'time');
CREATE OR REPLACE FUNCTION feed_ohlc_1s() RETURNS trigger AS
$BODY$
DECLARE