MonetDB : Partitioning Data by Time Intervals

Question

I am using MonetDB for time series data. I want to partition the tables by time intervals (e.g. by day). Each partition would therefore contain data of a particular day. That ideally would later accelerate the query runtime.

From the documentation, I can see that MonetDB provides partitioning as a feature, by I couldn't know how to implement it, I have tried for example: PARTITION BY DAY, such is implemented by other systems, by that didn't work.

How could a table be partitioned using a fixed time period interval in MonetDB?

Answer 1

In MonetDB there is no PARTITION BY DAY. But what you can do, is

CREATE MERGE TABLE merge_table (d DATE) PARTITION BY VALUES USING (d);

CREATE TABLE part_table_30_05_30(d DATE);
CREATE TABLE part_table_30_05_31(d DATE);


ALTER TABLE merge_table ADD TABLE part_table_30_05_30 AS PARTITION IN ('2022-05-30');
ALTER TABLE merge_table ADD TABLE part_table_30_05_31 AS PARTITION IN ('2022-05-31');

INSERT INTO merge_table VALUES ('2022-05-31');

SELECT * FROM merge_table;
+------------+
| d          |
+============+
| 2022-05-31 |
+------------+
1 tuple
SELECT * FROM part_table_30_05_31;
+------------+
| d          |
+============+
| 2022-05-31 |
+------------+
1 tuple
SELECT * FROM part_table_30_05_30;
+---+
| d |
+===+
+---+
0 tuples