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 .
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 << 31) -1.
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 << 31) -1.
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 << 31).
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 << 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 .	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 .	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	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. CockroachDB automatically unsets this parameter before performing a schema change and reapplies it when done. 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.	Boolean	true
sql_stats_automatic_partial_collection_enabled	Enable automatic collection of for this table.	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_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