timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
16.82k stars 852 forks source link

Calendar-based chunking PoC #7006

Open erimatnor opened 4 weeks ago

erimatnor commented 4 weeks ago

Initial PoC for calendar-based chunking.

A lot of functionality is missing.

Here's some example output:

create table hyper (time timestamptz, device int, temp float);
CREATE TABLE
select create_hypertable('hyper', by_range('time', '1 month'::interval, partition_origin => '2024-01-01'::timestamptz), create_default_indexes => false);
psql:../../tsdb-scripts/calendar-chunking.sql:2: NOTICE:  adding not-null constraint to column "time"
DETAIL:  Dimensions cannot have NULL values.
 create_hypertable 
-------------------
 (1,t)
(1 row)

select * from _timescaledb_catalog.hypertable;
 id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizi
ng_func_schema |  chunk_sizing_func_name  | chunk_target_size | compression_state | compressed_hypertable_id | 
status 
----+-------------+------------+------------------------+-------------------------+----------------+-----------
---------------+--------------------------+-------------------+-------------------+--------------------------+-
-------
  1 | public      | hyper      | _timescaledb_internal  | _hyper_1                |              1 | _timescale
db_internal    | calculate_chunk_interval |                 0 |                 0 |                          | 
     0
(1 row)

select * from _timescaledb_catalog.dimension;
 id | hypertable_id | column_name |       column_type        | aligned | num_slices | partitioning_func_schema 
| partitioning_func | interval_origin | interval | interval_length | compress_interval | compress_interval_leng
th | integer_now_func_schema | integer_now_func 
----+---------------+-------------+--------------------------+---------+------------+--------------------------
+-------------------+-----------------+----------+-----------------+-------------------+-----------------------
---+-------------------------+------------------
  1 |             1 | time        | timestamp with time zone | t       |            |                          
|                   | 757378800000000 | 1 mon    |                 |                   |                       
   |                         | 
(1 row)

insert into hyper values ('2024-01-04 13:41', 1, 1.0);
INSERT 0 1
select * from timescaledb_information.chunks;
 hypertable_schema | hypertable_name |     chunk_schema      |    chunk_name    | primary_dimension |  primary_
dimension_type  |      range_start       |       range_end        | range_start_integer | range_end_integer | i
s_compressed | chunk_tablespace |      chunk_creation_time      
-------------------+-----------------+-----------------------+------------------+-------------------+----------
----------------+------------------------+------------------------+---------------------+-------------------+--
-------------+------------------+-------------------------------
 public            | hyper           | _timescaledb_internal | _hyper_1_1_chunk | time              | timestamp
 with time zone | 2024-01-01 00:00:00+01 | 2024-02-01 00:00:00+01 |                     |                   | f
             |                  | 2024-06-05 18:48:14.076832+02
(1 row)