WITH (storage parameter)

On this page Carat arrow pointing down
Warning:
As of November 24, 2023, CockroachDB v22.1 is no longer supported. For more details, refer to the Release Support Policy.

The WITH (storage parameter) statement sets a storage parameter on a table.

Syntax

create_index_with_storage_param ::=

CREATE INVERTED INDEX opt_index_name IF NOT EXISTS index_name ON table_name ( index_params ) WITH ( storage_parameter_key = var_value , )

create_table_with_storage_param ::=

CREATE TABLE IF NOT EXISTS table_name ( table_definition ) WITH ( 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.

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 The 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 The 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 The 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 The 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_level must be divisible by s2_level_mod. s2_level_mod must be between 1 and 3. Integer 1
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:

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

Create a table with row-level TTL enabled

icon/buttons/copy 
CREATE TABLE ttl_test (
  id UUID PRIMARY KEY default gen_random_uuid(),
  description TEXT,
  inserted_at TIMESTAMP default current_timestamp()
) WITH (ttl_expire_after = '3 months');
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)

In this case, CockroachDB implicitly added the ttl and ttl_automatic_column storage parameters.

See also


Yes No
On this page

Yes No

Product

Resources

Learn

Support Channels

Company

Get developer news