Enabling compression in migrations

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
1

In brief:
I want to programmatically enable compression on my hypertables (we use flyway for other regular migrations).

But if I have, for example:

ALTER TABLE myhypertable
    SET (timescaledb.compress,
         timescaledb.compress_segmentby = 'tenant_id'
        );

I can get this failure:

SQL State  : 0A000
Error Code : 0
Message    : ERROR: cannot change configuration on already compressed chunks
  Detail: There are compressed chunks that prevent changing the existing compression configuration.

AFAIK, there is no general ALTER TABLE parameter to ignore failures. Is there a way to handle this? Are there better recommendations to manage compression on the hypertables, in a way that is GitOps friendly?

TimescaleDB version: v2.11.1
PostgreSQL version: 15.4
Other software:
OS: linux
Platform: amd64
Install method: timescale cloud hosted
Environment: test (so far)

This relates to this question, but I’m more interested in best practices for how to do this from code.

This might be a one-time problem caused by other testing, etc (ie enabling compression manually before running migrations). Maybe if I disable compression manually and then run my migrations, I won’t run into future issues because the migration will only run once. That might be the answer, but it feels like the migration should be able to handle this too (like if_not_exists => true on add_compression_policy).

1 Like
2

Hi @BenTatham, this is a very good point. We don’t have a if_not_exists option. I’d recommend you to use the timescaledb_information.compression_settings view to verify if it’s already configured as expected.

I think this flag is also an expected behavior! Feel free to suggest it as a feature in the github issues.

1 Like