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
New in v23.2.1:schema\_locked	Disallow on this table. Enabling schema\_locked can help running on this table.	Boolean	false
sql\_stats\_automatic\_collection\_enabled	Enable automatic collection of 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
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