WITH (storage parameter)

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`
New in v23.2.1: `schema_locked`	Disallow schema changes on this table. Enabling `schema_locked` can help improve performance of changefeeds running on this table.	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_enabled
fillfactor

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.

Pricing

Contact us

Sign In