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 |
New in v22.1: Excludes 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 |
ttl |
Signifies if a TTL is active. Automatically set and controls the reset of all TTL-related storage parameters. | N/A | N/A |
ttl_automatic_column |
If set, use the value of the crdb_internal_expiration hidden column. Always set to true and cannot be reset. |
Boolean | true |
ttl_delete_batch_size |
The number of rows to delete at a time. Minimum: 1. |
Integer | 100 |
ttl_delete_rate_limit |
The maximum number of rows to be deleted per second (rate limit). 0 means no limit. |
Integer | 0 |
ttl_expire_after |
The interval when a TTL will expire. This parameter is required to enable TTL. Minimum: '1 microsecond'.Use RESET (ttl) to remove from the table. |
Interval | N/A |
ttl_job_cron |
The frequency at which the TTL job runs. | CRON syntax | '@hourly' |
ttl_label_metrics |
Whether or not TTL metrics are labelled by table name (at the risk of added cardinality). | Boolean | false |
ttl_pause |
If set, stops the TTL job from executing. | Boolean | false |
ttl_range_concurrency |
The Row-Level TTL queries split up scans by ranges, and this determines how many concurrent ranges are processed at a time. Minimum: 1. |
Integer | 1 |
ttl_row_stats_poll_interval |
If set, counts rows and expired rows on the table to report as Prometheus metrics while the TTL job is running. Unset by default, meaning no stats are fetched and reported. | Interval | N/A |
ttl_select_batch_size |
The number of rows to select at one time during the row expiration check. Minimum: 1. |
Integer | 500 |
The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs:
autovacuum_enabledfillfactor
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_automatic_column = 'on', ttl_expire_after = '3 mons':::INTERVAL)
(1 row)
In this case, CockroachDB implicitly added the ttl and ttl_automatic_column storage parameters.
