The SET (storage parameter) statement sets a storage parameter on an existing table.
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_enabledfillfactor
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.
