CODEWITHSUNDEEP
questdb
Nick The Greek
Nick The Greek

Reputation: 453

How to change table partitioning in QuestDB?

I want to change partitioning from HOUR to MONTH in QuestDB since I only have a few records per hour and the queries suffer from opening too many partitions.

I cannot find any ALTER TABLE ... CHANGE PARTITION... Is there a way to change the partitioning of a table in QuestDB?

Upvotes: 0

Views: 138

Answers (1)

Alex des Pelagos
Alex des Pelagos

Reputation: 1485

QuestDB cannot change partitioning of an existing table, it can only set partition by in CREATE TABLE statement.

A workaround can be to copy data to the new table that is correctly partitioned. If you use QuestDB Web console, you can right click on the table and click Copy schema to clipboard. For example if you table Trades with partitioned by DAY and you want to change it to partition by MONTH, copy the schema into the clipboard:

enter image description here

and then paste it into the editor, change partitioning and the table name to Trades2

CREATE TABLE 'trades2' (
  symbol SYMBOL capacity 256 CACHE,
  side SYMBOL capacity 256 CACHE,
  price DOUBLE,
  amount DOUBLE,
  timestamp TIMESTAMP
) timestamp (timestamp) PARTITION BY MONTH WAL;

Here I changed partition by to be MONTH.

Then copy the data between the tables

insert into trades2
select * from trades

Then you can drop the Trades table and rename Trades2 into Trades

drop table Trades2;

rename table Trades2 to Trades;

Upvotes: 0

Related Questions