Timescaledb: Separate TABLESPACE for older chunk tables

[AndyMender]

Since TimescaleDB chunks data into chunk tables, it would be super useful to store older chunks in a different TABLESPACE or several ones. However, when I try to enforce this manually via the bellow SQL call:
```language="postgresql"
ALTER TABLE _timescaledb_internal._hyper_1_1002_chunk
SET TABLESPACE btrfs_pg;

I get:

11:37:54: Error: FEHLER: Operation not supported on chunk tables.
```
Is there an alternative way to do this for instance utilizing the various hypertable functions operating on chunks?

[erimatnor]

@AndyMender we currently prohibit many operations that happen directly on chunks, mostly because we'd like to understand the implications better before opening things up (also note that the SET TABLESPACE command would involve potentially copying over a lot of data to the new tablespace). More fine-grain chunk management is certainly something we might consider supporting in the future. In the meantime, I think you can achieve something similar by attaching tablespaces to your hypertable with the attach_tablespace() function. Then as the tablespace fills up, you can later detach it and attach a new (empty) tablespace, which means no new chunks will be put on the detached tablespace, although the chunks already in it will remain and will be queried as normal. The upside of this approach is also that it involves no data copying.

[goodkiller]

I've a similar problem. My main disk is getting full and I added new storage and created tablespace. But I can not move old chunks to new tablespace. What I supposed to to in this situation? Do I need to make dump, delete, restore data to get new tablespace filled??

[AndyMender]

I actually went through different approaches in the meantime and by default I create a TimescaleDB database within its own TABLESPACE (either on a BTRFS or ZFS volume, with compression). This approach forces all new tables, indices and constraints to be placed in that TABLESPACE. The definite advantage is that after pg_restore, the data still ends up in the designated TABLESPACE, because again, it's inherited from the database.

__EDIT__: I like the attach_tablespace() approach as well. Very convenient and it would definitely fit my use case :). Thanks, @erimatnor

[erimatnor]

@goodkiller I'd recommend the approach with attaching and detaching tablespaces as suggested in my post above.

[esatterwhite]

I'm in a similar situation where data that is the most current ( last 6 months ) needs to be on high performance disks. Out side of 6 months, its rarely accessed and can be moved off onto slower less expensive storage, but I really want to keep it in the same hyper table. Here the down side of accessing older data is really just a time penalty.

I'd be fine executing a function on a regular job to do so, but it doesn't seem like that is possible.

select move_chunks(interval '6 months', 'anayltics', 'tablespace_coldstorage');

Something like that would fantastic.

[jamessewell]

@esatterwhite We would like to see this too.

[Joaggi]

@esatterwhite It would be amazing to see it 👍

[mennowo]

We'd also be very happy with a feature like this. Does the modified milestone mean this has been planned for the 1.5.0 release?

[davidkohn88]

Yes, the 1.5 release contains https://github.com/timescale/timescaledb/pull/1393, which addresses some of the issues, though it is an enterprise feature, we haven't yet implemented alter tablespace as we have locking concerns around it (addressed by the move_chunk command, which also performs a reorder).

[cevian]

As David mentioned this issue is resolved by #1393. Note that move_chunk is available on Timescale Cloud and in the enterprise version of our software. Please let us know if this doesn't meet your needs.

[Krummelz]

It's great that move_chunks exists, but not great that it's an Enterprise feature. Or at least, that Community users don't realise that they can't use this, until they inevitably run into space issues, by which time it's almost certainly too late.

Highlight this more in your documentation or, if you want to keep it an Enterprise feature for the sake of convenience, at least make it possible for anyone to move chunks manually somehow.

If I attach a new tablespace now that points to a different disk and detach the old ones, I cannot reclaim the used space on my primary drive, unless I delete stuff, or use COPY to dump data to csv and keep it offline somewhere. Neither of which are great options.