> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# WITH (storage parameter)
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
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](#storage-parameters) for a list of available parameters. |
## Storage parameters
### Index parameters
| Parameter name | Description | Data type | Default value | Alterable after index creation |
| ---------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- |
| `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. | No |
| `shard_columns` | The first key column or columns to use when computing the shard column for a hash-sharded index. The value must match a prefix of the index key columns. If this parameter is omitted, CockroachDB hashes all index key columns. | Tuple of column identifiers or a single column identifier | All index key columns | No |
| `skip_unique_checks` | Disables unique constraint checks for indexes with implicit partition columns, including indexes on `REGIONAL BY ROW` tables. This avoids expensive cross-partition uniqueness checks in multi-region deployments. Can only be set on unique indexes with implicit partitioning. **Warning:** This should only be used if the application can guarantee uniqueness. Safe use cases include externally generated UUIDs, or columns using `unique_rowid()` or `unordered_unique_rowid()` as defaults (provided you do not also manually insert values into those columns). Incorrectly applying this parameter when uniqueness is not guaranteed could result in logically duplicate keys in different partitions of a unique index. Note that, by default, the optimizer does not perform uniqueness checks on columns using `gen_random_uuid()`. | Boolean | `false` | Yes |
| `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`. | No |
| `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`. | No |
| `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](https://epsg.io/3857)). By default, SRID = 0 assumes `[-min int32, max int32]` ranges. | | Derived from SRID bounds, else `-(1 << 31)`. | No |
| `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)`. | No |
| `s2_level_mod` | `s2_max_level` must be divisible by `s2_level_mod`. `s2_level_mod` must be between `1` and `3`. | Integer | `1` | No |
| `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` | No |
| `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` | No |
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_canary_window` | New in v26.2: How long statistics for this table remain in canary status before being promoted to stable status. | Duration | 0 |
| `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
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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
* `BACKUP`
* `RESTORE`
* `ALTER TABLE ... SET`
* `ALTER INDEX ... SET`
* Batch Delete Expired Data with Row-Level TTL