> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# WITH (storage parameter)

The `WITH (storage parameter)` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) sets a storage parameter on a table.

## Syntax

**create\_index\_with\_storage\_param ::=**

![create\_index\_with\_storage\_param syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/create_index_with_storage_param.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=9c0a32e83061364962f55e163b8fdc4c)

**create\_table\_with\_storage\_param ::=**

![create\_table\_with\_storage\_param syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/create_table_with_storage_param.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=3712b9c753dcdde7edea9455993bb46f)

## 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](https://docs.cockroachlabs.com/docs/v24.2/hash-sharded-indexes) will split. | Integer | The value of the `sql.defaults.default_hash_sharded_index_bucket_count` [cluster setting](https://docs.cockroachlabs.com/docs/v24.2/cluster-settings). |
| `geometry_max_x` | The maximum X-value of the [spatial reference system](https://docs.cockroachlabs.com/docs/v24.2/architecture/glossary#spatial-reference-system) for the object(s) being covered. This only needs to be set if you are using a custom [SRID](https://docs.cockroachlabs.com/docs/v24.2/architecture/glossary#srid). | | Derived from SRID bounds, else `(1 << 31) -1`. |
| `geometry_max_y` | The maximum Y-value of the [spatial reference system](https://docs.cockroachlabs.com/docs/v24.2/architecture/glossary#spatial-reference-system) for the object(s) being covered. This only needs to be set if you are using a custom [SRID](https://docs.cockroachlabs.com/docs/v24.2/architecture/glossary#srid). | | Derived from SRID bounds, else `(1 << 31) -1`. |
| `geometry_min_x` | The minimum X-value of the [spatial reference system](https://docs.cockroachlabs.com/docs/v24.2/architecture/glossary#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](https://docs.cockroachlabs.com/docs/v24.2/architecture/glossary#spatial-reference-system) for the object(s) being covered. This only needs to be set if you are using a custom [SRID](https://docs.cockroachlabs.com/docs/v24.2/architecture/glossary#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](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/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` |
| `schema_locked` | Disallow [schema changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes) on this table. Enabling `schema_locked` can help [improve performance of changefeeds](https://docs.cockroachlabs.com/docs/v24.2/create-changefeed#disallow-schema-changes-on-tables-to-improve-changefeed-performance) running on this table. | Boolean | `false` |
| `sql_stats_automatic_collection_enabled` | Enable automatic collection of [full statistics](https://docs.cockroachlabs.com/docs/v24.2/cost-based-optimizer#full-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 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](https://docs.cockroachlabs.com/docs/v24.2/show-statistics#display-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](https://docs.cockroachlabs.com/docs/v24.2/row-level-ttl) works, see the list of [TTL storage parameters](https://docs.cockroachlabs.com/docs/v24.2/row-level-ttl#ttl-storage-parameters).

## Required privileges

The user must be a member of the [`admin`](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#roles) or [owner](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#object-ownership) roles, or have the [`CREATE` privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges) 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](https://docs.cockroachlabs.com/docs/v24.2/row-level-ttl#ttl-storage-parameters).

## See also

* [`CREATE TABLE`](https://docs.cockroachlabs.com/docs/v24.2/create-table)
* [Take Full and Incremental Backups](https://docs.cockroachlabs.com/docs/v24.2/take-full-and-incremental-backups)
* [`BACKUP`](https://docs.cockroachlabs.com/docs/v24.2/backup)
* [`RESTORE`](https://docs.cockroachlabs.com/docs/v24.2/restore)
* [`SET` (storage parameter)](https://docs.cockroachlabs.com/docs/v24.2/alter-table#set-storage-parameter)
* [`RESET` (storage parameter)](https://docs.cockroachlabs.com/docs/v24.2/alter-table#reset-storage-parameter)
* [Batch Delete Expired Data with Row-Level TTL](https://docs.cockroachlabs.com/docs/v24.2/row-level-ttl)
