Skip to content

Instantly share code, notes, and snippets.

@jonatas
Created October 20, 2021 19:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jonatas/bfc0f2f0dbf4b3a7689895d92df45f41 to your computer and use it in GitHub Desktop.
Save jonatas/bfc0f2f0dbf4b3a7689895d92df45f41 to your computer and use it in GitHub Desktop.
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;
SELECT * FROM chunks_detailed_size('table1') ORDER BY chunk_name, node_name;
@jonatas
Copy link
Author

jonatas commented Oct 20, 2021

tsdb=> select ('category-'||i)::varchar(20) as category from generate_series(1,4)  i;
┌────────────┐
│  category  │
├────────────┤
│ category-1 │
│ category-2 │
│ category-3 │
│ category-4 │
└────────────┘
(4 rows)

tsdb=> with categories as (select ('category-'||i)::varchar(20) as category from generate_series(1,4)  i) select _timescaledb_internal.get_partition_hash(category::anyelement) from categories;
┌────────────────────┐
│ get_partition_hash │
├────────────────────┤
│         1991290738 │
│         1770729028 │
│         1620869871 │
│          698144148 │
└────────────────────┘
(4 rows)

tsdb=> with categories as (select ('category-'||i)::varchar(20) as category from generate_series(1,4)  i) select _timescaledb_internal.get_partition_hash(category::anyelement) % 4 from categories;
┌──────────┐
│ ?column? │
├──────────┤
│        2 │
│        0 │
│        3 │
│        0 │
└──────────┘
(4 rows)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment