WITH (storage parameter)

On this page

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

Syntax

create_index_with_storage_param ::=

create_table_with_storage_param ::=

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

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');

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.

Pricing

Contact us

Sign In