On this page 
Warning:
CockroachDB v22.1 is no longer supported as of November 24, 2023. For more details, refer to the Release Support Policy.
The RESET (storage parameter) statement reverts the value of a storage parameter on a table to its default value.
Note:
The RESET (storage parameter) is a subcommand of ALTER TABLE.
To reset a storage parameter on an existing index, you must drop and recreate the index with the storage parameter.
Syntax
alter_table_reset_storage_param ::=
Command parameters
| Parameter | Description |
|---|---|
table |
The table to which you are setting the parameter. |
parameter_name |
The name of the storage parameter you are changing. 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
Reset a storage parameter
Following the example in WITH (storage parameter), the ttl_test table has three TTL-related storage parameters active on the table:
SHOW CREATE TABLE ttl_test;
table_name | create_statement
-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ttl_test | CREATE TABLE public.ttl_test (
| id UUID NOT NULL DEFAULT gen_random_uuid(),
| description STRING NULL,
| inserted_at TIMESTAMP NULL DEFAULT current_timestamp():::TIMESTAMP,
| crdb_internal_expiration TIMESTAMPTZ NOT VISIBLE NOT NULL DEFAULT current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL ON UPDATE current_timestamp():::TIMESTAMPTZ + '3 mons':::INTERVAL,
| CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC)
| ) WITH (ttl = 'on', ttl_automatic_column = 'on', ttl_expire_after = '3 mons':::INTERVAL)
(1 row)
To remove these settings, run the following command:
ALTER TABLE ttl_test RESET (ttl);
SHOW CREATE TABLE ttl_test;
table_name | create_statement
-------------+--------------------------------------------------------------------------
ttl_test | CREATE TABLE public.ttl_test (
| id UUID NOT NULL DEFAULT gen_random_uuid(),
| description STRING NULL,
| inserted_at TIMESTAMP NULL DEFAULT current_timestamp():::TIMESTAMP,
| CONSTRAINT ttl_test_pkey PRIMARY KEY (id ASC)
| )
(1 row)
See also
CREATE TABLE- Take Full and Incremental Backups
BACKUPRESTORESET(storage parameter)WITH(storage parameter)
Was this helpful?
