Compress_segmentby notice while creating compression policy on continues aggregates

TimescaleDB and PostgreSQL
1

Use case: Creating compression policy on continues aggregate and getting a notice message as “NOTICE: defaulting compress_segmentby to variableid”

Steps for adding compression policy:
PERFORM remove_compression_policy(‘public.five_minutes’, true);
ALTER MATERIALIZED VIEW public.five_minutes set (timescaledb.compress = true);
PERFORM add_compression_policy(‘public.five_minutes’, compress_after=> INTERVAL ‘{xaFiveMin} days’);

Question:

  1. Is this Notice message expected? If no, what is the reason?
  2. How can a compress_segmentby be set on continuous aggregate?

Note: compress_segmentby can be set while creating hyper table, but not sure about the materialized view for continues aggregates ( public.five_minutes).

Thank you

2

It’s saying that you’re not applying the segmentby and it’s guessing it’s the variableid for you.

This is a important message because it’s assuming an important configuration. Segmentby makes the compression much faster because the columns that you most use in your queries should be not be compressed but segmenting your data.

The segmentby works like an index for compressed data as the compressed chunks does not contain index.

You can use ALTER MATERIALIZED VIEW and then SET:

The option still not there but I’m also opening a PR to fix this error in our docs.

Example from my local:

playground=# alter materialized view cagg_top_domains_10i set (timescaledb.compress = true, timescaledb.compress_segmentby = "domain");

NOTICE:  defaulting compress_orderby to "time"
ALTER MATERIALIZED VIEW

Note I missed the compress_orderby option which is also very important for performance :wink:

1 Like
3

Will it be a good idea to document the entire procedure which will include timescaledb.compress_segmentby and defaulting compress_orderby for continues aggregates?

4

I think yes! feel free to contribute @Mohammed_Iliyas_pate !

1 Like