On this page 
Warning:
GA releases for CockroachDB v23.1 are no longer supported. Cockroach Labs will stop providing LTS Assistance Support for v23.1 LTS releases on November 13, 2025. Prior to that date, upgrade to a more recent version to continue receiving support. 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_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 |
Exclude 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 |
sql_stats_forecasts_enabled |
Enable forecasted statistics collection for this table. | Boolean | true |
The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs:
autovacuum_enabledfillfactor
For the list of storage parameters that affect how Row-Level TTL works, see the list of TTL storage parameters.
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_expire_after = '3 mons':::INTERVAL, ttl_job_cron = '@hourly')
(1 row)
In this case, CockroachDB implicitly added the ttl and ttl_job_cron TTL storage parameters.
See also
CREATE TABLE- Take Full and Incremental Backups
BACKUPRESTORESET(storage parameter)RESET(storage parameter)- Batch Delete Expired Data with Row-Level TTL
Was this helpful?
