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;

SELECT * FROM chunks_detailed_size('table1') ORDER BY chunk_name, node_name;

First run, using 30 categories instead of 3:

 psql $multinode_uri -f distributed_hypertable.sql 
Expanded display is used automatically.
Border style is 2.
Line style is unicode.
DROP TABLE
CREATE TABLE
psql:distributed_hypertable.sql:3: NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.
┌───────────────────────────────┐
│ create_distributed_hypertable │
├───────────────────────────────┤
│ (3,public,table1,t)           │
└───────────────────────────────┘
(1 row)

INSERT 0 301
┌─[ RECORD 1 ]─┬───────────────────────┐
│ chunk_schema │ _timescaledb_internal │
│ chunk_name   │ _dist_hyper_3_1_chunk │
│ table_bytes  │ 8192                  │
│ index_bytes  │ 32768                 │
│ toast_bytes  │ 8192                  │
│ total_bytes  │ 49152                 │
│ node_name    │ hp9jmls1sh            │
├─[ RECORD 2 ]─┼───────────────────────┤
│ chunk_schema │ _timescaledb_internal │
│ chunk_name   │ _dist_hyper_3_2_chunk │
│ table_bytes  │ 8192                  │
│ index_bytes  │ 32768                 │
│ toast_bytes  │ 8192                  │
│ total_bytes  │ 49152                 │
│ node_name    │ dd74k4n1kf            │
├─[ RECORD 3 ]─┼───────────────────────┤
│ chunk_schema │ _timescaledb_internal │
│ chunk_name   │ _dist_hyper_3_3_chunk │
│ table_bytes  │ 8192                  │
│ index_bytes  │ 32768                 │
│ toast_bytes  │ 8192                  │
│ total_bytes  │ 49152                 │
│ node_name    │ ynrumyn7sa            │
└──────────────┴───────────────────────┘

Now with 3 categories only:

⋊> ~/c/snippets psql $multinode_uri -f distributed_hypertable.sql 
Expanded display is used automatically.
Border style is 2.
Line style is unicode.
DROP TABLE
CREATE TABLE
psql:distributed_hypertable.sql:3: NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.
┌───────────────────────────────┐
│ create_distributed_hypertable │
├───────────────────────────────┤
│ (4,public,table1,t)           │
└───────────────────────────────┘
(1 row)

INSERT 0 301
┌─[ RECORD 1 ]─┬───────────────────────┐
│ chunk_schema │ _timescaledb_internal │
│ chunk_name   │ _dist_hyper_4_4_chunk │
│ table_bytes  │ 49152                 │
│ index_bytes  │ 57344                 │
│ toast_bytes  │ 8192                  │
│ total_bytes  │ 114688                │
│ node_name    │ ynrumyn7sa            │
└──────────────┴───────────────────────┘

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)