SET (storage parameter)

On this page

The SET (storage parameter) statement sets a storage parameter on an existing table.

Note:

The SET (storage parameter) is a subcommand of ALTER TABLE.

To set a storage parameter on an existing index, you must drop and recreate the index with the storage parameter.

Syntax

alter_table_set_storage_param ::=

Command parameters

Parameter	Description
`table`	The table to which you are setting the parameter.
`parameter_name`	The name of the storage parameter. See Storage parameters for a list of available parameters.

Storage parameters

Table parameters

Parameter name	Description	Data type	Default value
`exclude_data_from_backup`	New in v22.1: Excludes the data in this table from any future backups.	Boolean	`false`
`sql_stats_automatic_collection_enabled`	Enable automatic statistics collection for this table.	Boolean	`true`
`sql_stats_automatic_collection_min_stale_rows`	Minimum number of stale rows in this table that will trigger a statistics refresh.	Integer	500
`sql_stats_automatic_collection_fraction_stale_rows`	Fraction of stale rows in this table that will trigger a statistics refresh.	Float	0.2
`ttl`	Signifies if a TTL is active. Automatically set and controls the reset of all TTL-related storage parameters.	N/A	N/A
`ttl_automatic_column`	If set, use the value of the `crdb_internal_expiration` hidden column. Always set to `true` and cannot be reset.	Boolean	`true`
`ttl_delete_batch_size`	The number of rows to delete at a time. Minimum: `1`.	Integer	`100`
`ttl_delete_rate_limit`	The maximum number of rows to be deleted per second (rate limit). `0` means no limit.	Integer	`0`
`ttl_expire_after`	The interval when a TTL will expire. This parameter is required to enable TTL. Minimum: `'1 microsecond'`. Use `RESET (ttl)` to remove from the table.	Interval	N/A
`ttl_job_cron`	The frequency at which the TTL job runs.	CRON syntax	`'@hourly'`
`ttl_label_metrics`	Whether or not TTL metrics are labelled by table name (at the risk of added cardinality).	Boolean	`false`
`ttl_pause`	If set, stops the TTL job from executing.	Boolean	`false`
`ttl_range_concurrency`	The Row-Level TTL queries split up scans by ranges, and this determines how many concurrent ranges are processed at a time. Minimum: `1`.	Integer	`1`
`ttl_row_stats_poll_interval`	If set, counts rows and expired rows on the table to report as Prometheus metrics while the TTL job is running. Unset by default, meaning no stats are fetched and reported.	Interval	N/A
`ttl_select_batch_size`	The number of rows to select at one time during the row expiration check. Minimum: `1`.	Integer	`500`

The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs:

autovacuum_enabled
fillfactor

Required privileges

The user must be a member of the admin or owner roles, or have the CREATE privilege on the table.

Examples

Exclude a table's data from backups

New in v22.1: In some situations, you may want to exclude a table's row data from a backup. For example, you have a table that contains high-churn data that you would like to garbage collect more quickly than the incremental backup schedule for the database or cluster holding the table. You can use the exclude_data_from_backup = true parameter with a CREATE TABLE or ALTER TABLE statement to mark a table's row data for exclusion from a backup.

For more detail and an example through the backup and restore process using this parameter, see Take Full and Incremental Backups.

To set the exclude_data_from_backup parameter for a table, run the following:

ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = true);

The CREATE statement for this table will now show the parameter set:

SHOW CREATE user_promo_codes;

table_name         |                                                create_statement
-------------------+------------------------------------------------------------------------------------------------------------------
user_promo_codes   | CREATE TABLE public.user_promo_codes (
                   |     city VARCHAR NOT NULL,
                   |     user_id UUID NOT NULL,
                   |     code VARCHAR NOT NULL,
                   |     "timestamp" TIMESTAMP NULL,
                   |     usage_count INT8 NULL,
                   |     CONSTRAINT user_promo_codes_pkey PRIMARY KEY (city ASC, user_id ASC, code ASC),
                   |     CONSTRAINT user_promo_codes_city_user_id_fkey FOREIGN KEY (city, user_id) REFERENCES public.users(city, id)
                   | ) WITH (exclude_data_from_backup = true)
(1 row)

Backups will no longer include the data within the user_promo_codes table. The table will still be present in the backup, but it will be empty.

To remove this parameter from a table, run:

ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = false);

This will ensure that the table's data is stored in subsequent backups that you take.

Pricing

Contact us

Sign In