SET (storage parameter)

On this page Carat arrow pointing down

The SET (storage parameter) statement sets a storage parameter on an existing table.

Note:

The SET (storage parameter) is a subcommand of ALTER TABLE.

To set a storage parameter on an existing index, you must drop and recreate the index with the storage parameter.

Syntax

alter_table_set_storage_param ::=

ALTER TABLE IF EXISTS table_name SET ( 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

Table parameters

Parameter name Description Data type Default value
exclude_data_from_backup Excludes the data in this table from any future backups. Boolean false

The following parameters are included for PostgreSQL compatibility and do not affect how CockroachDB runs:

  • autovacuum_enabled
  • fillfactor

Required privileges

The user must be a member of the admin or owner roles, or have the CREATE privilege on the table.

Examples

Exclude a table's data from backups

In some situations, you may want to exclude a table's row data from a backup. For example, you have a table that contains high-churn data that you would like to garbage collect more quickly than the incremental backup schedule for the database or cluster holding the table. You can use the exclude_data_from_backup = true parameter with a CREATE TABLE or ALTER TABLE statement to mark a table's row data for exclusion from a backup.

For more detail and an example through the backup and restore process using this parameter, see Take Full and Incremental Backups.

To set the exclude_data_from_backup parameter for a table, run the following:

icon/buttons/copy
ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = true);

The CREATE statement for this table will now show the parameter set:

icon/buttons/copy
SHOW CREATE user_promo_codes;
table_name         |                                                create_statement
-------------------+------------------------------------------------------------------------------------------------------------------
user_promo_codes   | CREATE TABLE public.user_promo_codes (
                   |     city VARCHAR NOT NULL,
                   |     user_id UUID NOT NULL,
                   |     code VARCHAR NOT NULL,
                   |     "timestamp" TIMESTAMP NULL,
                   |     usage_count INT8 NULL,
                   |     CONSTRAINT user_promo_codes_pkey PRIMARY KEY (city ASC, user_id ASC, code ASC),
                   |     CONSTRAINT user_promo_codes_city_user_id_fkey FOREIGN KEY (city, user_id) REFERENCES public.users(city, id)
                   | ) WITH (exclude_data_from_backup = true)
(1 row)

Backups will no longer include the data within the user_promo_codes table. The table will still be present in the backup, but it will be empty.

To remove this parameter from a table, run:

icon/buttons/copy
ALTER TABLE movr.user_promo_codes SET (exclude_data_from_backup = false);

This will ensure that the table's data is stored in subsequent backups that you take.

See also


Yes No
On this page

Yes No