hydradatabase / hydra

Hydra: Column-oriented Postgres. Add scalable analytics to your project in minutes.
https://www.hydra.so
Apache License 2.0
2.83k stars 76 forks source link

support moving tables between tablespaces #172

Open kreopt opened 1 year ago

kreopt commented 1 year ago

What's wrong?

H! We have two separate tablespaces: default(ssd with relatively small capacity for current data) and hdd (high capacity, historical data) and don't want to store columnar tables on ssd. But there is an error on moving columnar table:

alter table fact_events_1_2021 set tablespace hdd;
ОШИБКА:  columnar_relation_copy_data not implemented

Is there a way to move this data or some plans to implement this feature?

wuputah commented 1 year ago

Thanks for the report. I believe tablespaces themselves should work but moving tables isn't supported yet.

The best I can suggest is to copy the table like so:

-- copy table to a new table
create table fact_events_1_2021_hdd (like fact_events_1_2021) using columnar tablespace hdd;
insert into fact_events_1_2021_copy select * from fact_events_1_2021;
-- swap the two table names
alter table fact_events_1_2021 rename to fact_events_1_2021_ssd;
alter table fact_events_1_2021_hdd rename to fact_events_1_2021;
-- when you are ready, drop the old table on the ssd.
drop table fact_events_1_2021_ssd;