Retention Policies for System Relations

Some system relations can grow in space with time, because of the number of queries being run, sessions being opened, etc. We provide a retention mechanism for some of these relations:

• Removing all records older than a given age.
• Removing the oldest records when the relation takes more than a given amount of space in the storage.

Interface

The current retention configuration can be observed in sys.retention.

The retention history is stored in sys.log_retention.

To modify the retention configuration for a given relation, an ALTER TABLE statement needs to be run, for example:

ALTER TABLE <relation> SET RETENTION_SIZE <size>

See ALTER TABLE for the complete SQL syntax.

Notes

• The retention process will trigger every hour.
• When removing records because of space limit, no record younger than a day will be removed.
• The retention policy configuration does not survive upgrades and will need to be re-applied.

Default configuration

By default, the following retention policies are set up:

Relation	Age Retention	Size Retention	Minimum Age Retention	Minimum Size Retention
sys.log_analyze	3 months	1GB	1 day	128MB
sys.log_authentication	Disabled	Disabled	1 day	128MB
sys.log_backup	3 months	1GB	1 day	128MB
sys.log_cluster_event	3 months	1GB	1 day	128MB
sys.log_load	3 months	1GB	1 day	128MB
sys.log_query	1 month	32GB	1 day	1GB
sys.log_query_analyze	1 month	32GB	1 day	128MB
sys.log_query_usage	1 month	32GB	1 day	128MB
sys.log_restore	3 months	1GB	1 day	128MB
sys.log_replica_status	3 months	1GB	1 day	128MB
sys.log_retention	3 months	1GB	1 day	128MB
sys.log_session	Disabled	Disabled	1 day	128MB
sys.log_unload	3 months	1GB	1 day	128MB