WITH (storage parameter)

On this page Carat arrow pointing down

The WITH (storage parameter) statement sets a storage parameter on a table.

Syntax

create_index_with_storage_param ::=

create_table_with_storage_param ::=

CREATE TABLE IF NOT EXISTS table_name ( table_definition ) WITH ( storage_parameter_key = var_value , )

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

icon/buttons/copy
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');
icon/buttons/copy
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


Yes No
On this page

Yes No