SET (storage parameter)

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

Note:

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

Syntax

alter_table_set_storage_param ::=

ALTER TABLE IF EXISTS table_name SET ( storage_parameter_key = var_value )

Command parameters

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

List of storage parameters

Index parameters

Parameter name Description Data type Default value
bucket_count The number of buckets into which a hash-sharded index will split. Integer The value of the sql.defaults.default_hash_sharded_index_bucket_count cluster setting
geometry_max_x Maximum X-value of the spatial reference system for the object(s) being covered. This only needs to be set if you are using a custom SRID. Derived from SRID bounds, else (1 << 31) -1
geometry_max_y Maximum Y-value of the spatial reference system for the object(s) being covered. This only needs to be set if you are using a custom SRID. Derived from SRID bounds, else (1 << 31) -1
geometry_min_x Minimum X-value of the spatial reference system for the object(s) being covered. This only needs to be set if the default bounds of the SRID are too large/small for the given data, or SRID = 0 and you wish to use a smaller range (unfortunately this is currently not exposed, but is viewable on https://epsg.io/3857). By default, SRID = 0 assumes [-min int32, max int32] ranges. Derived from SRID bounds, else -(1 << 31)
geometry_min_y Minimum Y-value of the spatial reference system for the object(s) being covered. This only needs to be set if you are using a custom SRID. Derived from SRID bounds, else -(1 << 31)
s2_level_mod
s2_max_cells The maximum number of S2 cells used in the covering. Provides a limit on how much work is done exploring the possible coverings. Allowed values: 1-30. You may want to use higher values for odd-shaped regions such as skinny rectangles. Used in spatial indexes. Integer 4
s2_max_level The maximum level of S2 cell used in the covering. Allowed values: 1-30. Setting it to less than the default means that CockroachDB will be forced to generate coverings using larger cells. Used in spatial indexes. Integer 30

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

Parameter name
fillfactor

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
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. Currently always set to true and cannot be reset. Boolean true
ttl_delete_batch_size How many rows to delete at a time. Minimum: 1. Integer 100
ttl_delete_rate_limit 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 value: '1 microsecond'.

Use RESET (ttl) to remove from the table.
Interval N/A
ttl_job_cron 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:

Parameter name
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:

icon/buttons/copy
ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = true);

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

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

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

See also


Yes No

Yes No