ALTER TABLE SET STORAGE POLICY

Sets, modifies, enables, disables, or removes a storage policy on a table. For the equivalent operations on materialized views, see ALTER MATERIALIZED VIEW SET STORAGE POLICY.

note

Storage policies are available in QuestDB Enterprise only.

Refer to the Storage Policy concept guide for a full overview.

Syntax​

Set or modify a storage policy​

ALTER TABLE table_name SET STORAGE POLICY(
    [TO PARQUET ttl,]
    [DROP NATIVE ttl,]
    [DROP LOCAL ttl,]
    [DROP REMOTE ttl]
);

Only the specified settings are changed. Omitted settings retain their current values.

Enable or disable a storage policy​

ALTER TABLE table_name ENABLE STORAGE POLICY;
ALTER TABLE table_name DISABLE STORAGE POLICY;

Disabling a policy suspends processing without removing the policy definition.

Remove a storage policy​

ALTER TABLE table_name DROP STORAGE POLICY;

This permanently removes the storage policy from the table.

Description​

A storage policy defines up to four TTL-based stages that control how partitions transition from native format to Parquet and eventually get removed:

SettingEffect
TO PARQUET <ttl>Convert partition from native format to Parquet locally
DROP NATIVE <ttl>Remove native binary files, keeping only the local Parquet copy
DROP LOCAL <ttl>Remove all local copies of the partition
DROP REMOTE <ttl>Reserved. Will remove the partition from object storage when remote upload is supported
info

DROP REMOTE is reserved syntax. It is rejected at SQL parse time with 'DROP REMOTE' is not supported yet. Automatic upload of Parquet files to object storage is not currently supported — storage policies operate locally only. Because the clause cannot take effect, the drop_remote column in the storage_policies view is always blank in the current release.

TTL format​

Follow each setting with a duration value using one of these formats:

  • Long form: 3 DAYS, 1 MONTH, 2 YEARS
  • Short form: 3d, 1M, 2Y

Supported units: HOUR/h, DAY/d, WEEK/W, MONTH/M, YEAR/Y. Both singular and plural forms are accepted.

Constraints​

  • TTL values must be in ascending order: TO PARQUET <= DROP NATIVE <= DROP LOCAL <= DROP REMOTE
  • All TTL values must be positive — 0 is rejected
  • Each setting can only appear once per statement
  • The table must have a designated timestamp and partitioning enabled
  • If the table has a TTL set, clear it with ALTER TABLE SET TTL 0 before setting a storage policy. Any non-zero SET TTL value is rejected in Enterprise with TTL settings are deprecated, please, create a storage policy instead
  • ENABLE and DISABLE require a policy to exist on the table; both return an error otherwise

Permissions​

Each operation requires a specific permission:

SQL commandRequired permission
SET STORAGE POLICYSET STORAGE POLICY
DROP STORAGE POLICYREMOVE STORAGE POLICY
ENABLE STORAGE POLICYENABLE STORAGE POLICY
DISABLE STORAGE POLICYDISABLE STORAGE POLICY

Examples​

Set a storage policy with all three currently-supported stages:

ALTER TABLE sensor_data SET STORAGE POLICY(
    TO PARQUET 3 DAYS,
    DROP NATIVE 10 DAYS,
    DROP LOCAL 1 MONTH
);

Update only the Parquet conversion threshold:

ALTER TABLE sensor_data SET STORAGE POLICY(TO PARQUET 7d);

Temporarily suspend a policy:

ALTER TABLE sensor_data DISABLE STORAGE POLICY;

Re-enable it:

ALTER TABLE sensor_data ENABLE STORAGE POLICY;

Remove a policy entirely:

ALTER TABLE sensor_data DROP STORAGE POLICY;

Check active policies:

SELECT * FROM storage_policies;

The storage policy also appears in SHOW CREATE TABLE output:

SHOW CREATE TABLE sensor_data;
CREATE TABLE 'sensor_data' (
    ts TIMESTAMP,
    value DOUBLE
) timestamp(ts) PARTITION BY DAY
STORAGE POLICY(TO PARQUET 3 DAYS, DROP NATIVE 10 DAYS, DROP LOCAL 1 MONTH) WAL;

Stages that are not set are omitted from the output.

See also​