WITH (storage parameter)

The WITH (storage parameter) 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 will split.	Integer	The value of the sql.defaults.default_hash_sharded_index_bucket_count .	No
shard_columns	The first key column or columns to use when computing the shard column for a . The value must match a prefix of the index key columns. If this parameter is omitted, CockroachDB hashes all index key columns.	Tuple of column identifiers or a single column identifier	All index key columns	No
skip_unique_checks	Disables for indexes with columns, including indexes on tables. This avoids expensive cross-partition uniqueness checks in multi-region deployments. Can only be set on unique indexes with implicit partitioning. Warning: This should only be used if the application can guarantee uniqueness. Safe use cases include externally generated UUIDs, or columns using or as defaults (provided you do not also manually insert values into those columns). Incorrectly applying this parameter when uniqueness is not guaranteed could result in logically duplicate keys in different partitions of a unique index. Note that, by default, the optimizer does not perform uniqueness checks on columns using .	Boolean	false	Yes
geometry_max_x	The maximum X-value of the for the object(s) being covered. This only needs to be set if you are using a custom .		Derived from SRID bounds, else (1 <&lt; 31) -1.	No
geometry_max_y	The maximum Y-value of the for the object(s) being covered. This only needs to be set if you are using a custom .		Derived from SRID bounds, else (1 <&lt; 31) -1.	No
geometry_min_x	The minimum X-value of the 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 <&lt; 31).	No
geometry_min_y	The minimum Y-value of the for the object(s) being covered. This only needs to be set if you are using a custom .		Derived from SRID bounds, else -(1 <&lt; 31).	No
s2_level_mod	s2_max_level must be divisible by s2_level_mod. s2_level_mod must be between 1 and 3.	Integer	1	No
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 .	Integer	4	No
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 .	Integer	30	No

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	Exclude the data in this table from any future backups.	Boolean	false
infer_rbr_region_col_using_constraint	For tables, automatically populate the hidden crdb_region column on INSERT, UPDATE, and UPSERT by looking up the region of the referenced parent row. Set this parameter to the name of a constraint on the table that includes the crdb_region column. The foreign key cannot be dropped while the parameter is set.	String	NULL
schema_locked	Indicates that a is not currently ongoing on this table. By default, CockroachDB attempts to automatically unset this parameter before performing many schema changes and reapplies it when done. To require manual unlocks for all DDL on schema-locked tables, set to false. Enabling schema_locked can help running on this table.	Boolean	false
sql_stats_automatic_collection_enabled	Enable automatic collection of and statistics for this table.	Boolean	true
sql_stats_automatic_collection_min_stale_rows	Minimum number of stale rows in this table that will trigger a full statistics refresh.	Integer	500
sql_stats_automatic_collection_fraction_stale_rows	Fraction of stale rows in this table that will trigger a full statistics refresh.	Float	0.2
sql_stats_automatic_full_collection_enabled	Enable automatic collection of for this table. sql_stats_automatic_collection_enabled must be true.	Boolean	true
sql_stats_automatic_partial_collection_enabled	Enable automatic collection of for this table. sql_stats_automatic_collection_enabled must be true.	Boolean	true
sql_stats_automatic_partial_collection_min_stale_rows	Minimum number of stale rows that triggers for this table.	Integer	100
sql_stats_automatic_partial_collection_fraction_stale_rows	Target fraction of stale rows that triggers for this table.	Float	0.05
sql_stats_canary_window	New in v26.2: How long statistics for this table remain in before being promoted to stable status.	Duration	0
sql_stats_forecasts_enabled	Enable collection for this table.	Boolean	true

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

• autovacuum_enabled
• fillfactor

For the list of storage parameters that affect how works, see the list of .

Required privileges

The user must be a member of the or roles, or have the 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_expire_after = '3 mons':::INTERVAL, ttl_job_cron = '@hourly')
(1 row)

In this case, CockroachDB implicitly added the ttl and ttl_job_cron .

See also