I currently have a hypertable that is partitioned only by time and I want to add an extra dimension e.g. account_id.
My timescaledb version is 2.8.1 and reading through the docs, I found this:
You can only execute this add_dimension command on an empty hypertable.
My hypertable is not empty.
So, is there a way to add a dimension to an existing hypertable?
If so, then how?
There's no real way. You'll need to create a new hypertable for it as all metadata behind the scenes changes. Think that all unfolding mechanisms will be doing it.
My advise is make it paginating the requests to optimize and organize your data already inserting in the right order.
insert into new_hypertable from hypertable where time between ...; #
then you can make it like one year/month for each request and move it.
Later you can rename it to the official name and drop the old one. It also allows you to build a intermediate temporary view which you keep dropping data from the old view and keep another view on top collecting data from both sources.