ALTER MATERIALIZED VIEW (Hypercore)

ALTER MATERIALIZED VIEW statement can be used to modify some of the WITH clause options for the continuous aggregate view. ALTER MATERIALIZED VIEW statement also supports the following PostgreSQL clauses on the continuous aggregate view:

• RENAME TO clause to rename the continuous aggregate view
• RENAME [COLUMN] clause to rename the continuous aggregate column
• SET SCHEMA clause to set the new schema for the continuous aggregate view
• SET TABLESPACE clause to move the materialization of the continuous aggregate view to the new tablespace
• OWNER TO clause to set new owner for the continuous aggregate view

• Disable real-time aggregates for a continuous aggregate:

  ALTER MATERIALIZED VIEW contagg_view SET (timescaledb.materialized_only);

  Copy

• Enable hypercore for a continuous aggregate:

  ALTER MATERIALIZED VIEW assets_candlestick_daily set (
    timescaledb.enable_columnstore = true, 
    timescaledb.segmentby = 'symbol' );

  Copy

• Rename a column for a continuous aggregate:

  ALTER MATERIALIZED VIEW contagg_view RENAME COLUMN old_name TO new_name;

  Copy

• Enable indexing on data in the columnstore Early access: TimescaleDB v2.18.0

  ALTER MATERIALIZED VIEW assets_candlestick_daily 
     set access method hypercore,
     set (timescaledb.enable_columnstore = true, timescaledb.segmentby = 'symbol' );

  Copy

• Enable indexing on a chunk you are adding to the columnstore Early access: TimescaleDB v2.18.0

  ALTER MATERIALIZED VIEW  _timescaledb_internal._hyper_1_21_chunk
     set access method hypercore;

  Copy

The only options that currently can be modified with ALTER MATERIALIZED VIEW are materialized_only and compress. The other options continuous and create_group_indexes can only be set when creating the continuous aggregate.

The syntax is:

ALTER MATERIALIZED VIEW <view_name> SET (timescaledb.enable_columnstore,
   timescaledb.materialized_only = 'true' | 'false',
   timescaledb.orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
   timescaledb.segmentby = '<column_name> [, ...]',
   timescaledb.compress_chunk_time_interval='interval',
   SET ACCESS METHOD { new_access_method | DEFAULT }
);

Copy

Name	Type	Default	Required	Description
view_name	TEXT	-	✖	The materialized view to enable columstore for.
timescaledb.enable_columnstore	BOOLEAN	true	✖	Enable columnstore.
timescaledb.materialized_only	BOOLEAN	true	✖	Enable and disable real time aggregation
timescaledb.orderby	TEXT	Descending order on the time column in table_name.	✖	The order in which items are used in the columnstore. Specified in the same way as an ORDER BY clause in a SELECT query.
timescaledb.segmentby	TEXT	No segementation by column.	✖	Set the list of columns used to segment data in the columnstore for table. An identifier representing the source of the data such as device_id or tags_id is usually a good candidate.
column_name	TEXT	-	✖	The name of the column to orderby or segmentby.
timescaledb.compress_chunk_time_interval	TEXT	-	✖	EXPERIMENTAL: reduce the total number of chunks in the columnstore for table. If you set compress_chunk_time_interval, chunks added to the columnstore are merged with the previous adjacent chunk within chunk_time_interval whenever possible. These chunks are irreversibly merged. If you call [convert_to_rowstore][convert_to_rowstore], merged chunks are not split up. You can call compress_chunk_time_interval independently of other compression settings; timescaledb.enable_columnstore is not required.
SET ACCESS METHOD	TEXT	DEFAULT (heap)	✖	To enable indexing on the columstore, set to hypercore after you create a continuous aggregate. Early access: TimescaleDB v2.18.0