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 |
Exclude the data in this table from any future backups. | Boolean | false |
infer_rbr_region_col_using_constraint |
For REGIONAL BY ROW 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 foreign key 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 schema change 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 sql.schema.auto_unlock.enabled to false. Enabling schema_locked can help improve performance of changefeeds running on this table. |
Boolean | false |
sql_stats_automatic_collection_enabled |
Enable automatic collection of full and partial 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 full statistics for this table. sql_stats_automatic_collection_enabled must be true. |
Boolean | true |
sql_stats_automatic_partial_collection_enabled |
Enable automatic collection of partial statistics 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 partial statistics collection for this table. | Integer | 100 |
sql_stats_automatic_partial_collection_fraction_stale_rows |
Target fraction of stale rows that triggers partial statistics collection for this table. | Float | 0.05 |
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.
