RESET (storage parameter)

On this page Carat arrow pointing down

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 ::=

ALTER TABLE IF EXISTS table_name RESET ( storage_parameter_key , )

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_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

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:

icon/buttons/copy
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:

icon/buttons/copy
ALTER TABLE ttl_test RESET (ttl);
icon/buttons/copy
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


Yes No
On this page

Yes No