> ## 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 |
| ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | --------------------------------------------------------------------------------------------------------------------------------------------- |
| `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](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. CockroachDB automatically unsets this parameter before performing a schema change and reapplies it when done. 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. | Boolean | `true` |
| `sql_stats_automatic_partial_collection_enabled` | Enable automatic collection of partial statistics for this table. | 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_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`
* `SET` (storage parameter)
* `RESET` (storage parameter)
* Batch Delete Expired Data with Row-Level TTL