> ## 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.

# SHOW SUPER REGIONS

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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `SHOW SUPER REGIONS` <InternalLink path="sql-statements">statement</InternalLink> lists the <InternalLink path="multiregion-overview#super-regions">super regions</InternalLink> for a multi-region cluster.

<Note>
  **This feature is in <InternalLink path="cockroachdb-feature-availability">preview</InternalLink>** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
</Note>

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/M1Nto-joXUTgisRs/images/sql-diagrams/v25.3/show_regions.svg?fit=max&auto=format&n=M1Nto-joXUTgisRs&q=85&s=44c7d049ae9578d4bbc0c9dc0d4bb085" alt="show_regions syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="723" height="233" data-path="images/sql-diagrams/v25.3/show_regions.svg" />

## Required privileges

To view the super regions in a database, the user must have one of the following:

* Membership to the <InternalLink path="security-reference/authorization#admin-role">`admin`</InternalLink> role for the cluster.
* Either <InternalLink path="security-reference/authorization#object-ownership">ownership</InternalLink> or the <InternalLink path="security-reference/authorization#supported-privileges">`CREATE` privilege</InternalLink> for the database.

## Parameters

| Parameter                     | Description                                                                                                               |
| ----------------------------- | ------------------------------------------------------------------------------------------------------------------------- |
| `FROM DATABASE database_name` | Show all <InternalLink path="multiregion-overview#super-regions">super regions</InternalLink> for the specified database. |

## Response

`SHOW SUPER REGIONS FROM DATABASE` returns the following fields for each super region:

| Field               | Description                                                 |
| ------------------- | ----------------------------------------------------------- |
| `database_name`     | The database name this super region is associated with.     |
| `super_region_name` | The name of a super region associated with the database.    |
| `regions`           | The list of database regions that make up the super region. |

## Considerations

To use super regions, keep the following considerations in mind:

* Your cluster must be a <InternalLink path="multiregion-overview">multi-region cluster</InternalLink>.
* Super regions [must be enabled](#enable-super-regions)(/docs/v25.3/alter-database#enable-super-regions).
* Super regions can only contain one or more <InternalLink path="multiregion-overview#database-regions">database regions</InternalLink> that have already been added with <InternalLink path="alter-database#add-region">`ALTER DATABASE ... ADD REGION`</InternalLink>.
* Each database region can only belong to one super region. In other words, given two super regions *A* and *B*, the set of database regions in *A* must be [disjoint](https://wikipedia.org/wiki/Disjoint_sets) from the set of database regions in *B*.
* You cannot <InternalLink path="alter-database#drop-region">drop a region</InternalLink> that is part of a super region until you either <InternalLink path="alter-database#alter-super-region">alter the super region</InternalLink> to remove it, or <InternalLink path="alter-database#drop-super-region">drop the super region</InternalLink> altogether.

## Examples

The examples in this section use the following setup.

#### Setup

Only a <InternalLink path="multiregion-overview#cluster-regions">cluster region</InternalLink> specified <InternalLink path="cockroach-start#locality">at node startup</InternalLink> can be used as a <InternalLink path="multiregion-overview#database-regions">database region</InternalLink>.

To follow along with the examples in this section, start a <InternalLink path="cockroach-demo">demo cluster</InternalLink> with the <InternalLink path="cockroach-demo#general">`--global` flag</InternalLink> to simulate a multi-region cluster:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo --global --nodes 9
```

To see the regions available to the databases in the cluster, use a <InternalLink path="show-regions#view-the-regions-in-a-cluster">`SHOW REGIONS FROM CLUSTER`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW REGIONS FROM CLUSTER;
```

```
     region    |  zones
---------------+----------
  europe-west1 | {b,c,d}
  us-east1     | {b,c,d}
  us-west1     | {a,b,c}
(3 rows)
```

#### Set up movr database regions

Execute the following statements. They will tell CockroachDB about the database's regions. This information is necessary so that CockroachDB can later move data around to optimize access to particular data from particular regions. For more information about how this works at a high level, see <InternalLink path="multiregion-overview#database-regions">Database Regions</InternalLink>.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr PRIMARY REGION "us-east1";
ALTER DATABASE movr ADD REGION "europe-west1";
ALTER DATABASE movr ADD REGION "us-west1";
```

#### Set up movr global tables

Because the data in `promo_codes` is not updated frequently (a.k.a., "read-mostly"), and needs to be available from any region, the right table locality is <InternalLink path="table-localities#global-tables">`GLOBAL`</InternalLink>.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE promo_codes SET locality GLOBAL;
```

Next, alter the `user_promo_codes` table to have a foreign key into the global `promo_codes` table. This will enable fast reads of the `promo_codes.code` column from any region in the cluster.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE user_promo_codes
  ADD CONSTRAINT user_promo_codes_code_fk
    FOREIGN KEY (code)
    REFERENCES promo_codes (code)
    ON UPDATE CASCADE;
```

#### Set up movr regional tables

All of the tables except `promo_codes` contain rows which are partitioned by region, and updated very frequently. For these tables, the right table locality for optimizing access to their data is <InternalLink path="table-localities#regional-by-row-tables">`REGIONAL BY ROW`</InternalLink>.

Apply this table locality to the remaining tables. These statements use a `CASE` statement to put data for a given city in the right region and can take around 1 minute to complete for each table.

* `rides`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER TABLE rides ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE rides ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE rides SET LOCALITY REGIONAL BY ROW AS "region";
  ```

* `user_promo_codes`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER TABLE user_promo_codes ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE user_promo_codes ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE user_promo_codes SET LOCALITY REGIONAL BY ROW AS "region";
  ```

* `users`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER TABLE users ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE users ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE users SET LOCALITY REGIONAL BY ROW AS "region";
  ```

* `vehicle_location_histories`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER TABLE vehicle_location_histories ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE vehicle_location_histories ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE vehicle_location_histories SET LOCALITY REGIONAL BY ROW AS "region";
  ```

* `vehicles`

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ALTER TABLE vehicles ADD COLUMN region crdb_internal_region AS (
    CASE WHEN city = 'amsterdam' THEN 'europe-west1'
         WHEN city = 'paris' THEN 'europe-west1'
         WHEN city = 'rome' THEN 'europe-west1'
         WHEN city = 'new york' THEN 'us-east1'
         WHEN city = 'boston' THEN 'us-east1'
         WHEN city = 'washington dc' THEN 'us-east1'
         WHEN city = 'san francisco' THEN 'us-west1'
         WHEN city = 'seattle' THEN 'us-west1'
         WHEN city = 'los angeles' THEN 'us-west1'
    END
  ) STORED;
  ALTER TABLE vehicles ALTER COLUMN REGION SET NOT NULL;
  ALTER TABLE vehicles SET LOCALITY REGIONAL BY ROW AS "region";
  ```

### Enable super regions

To enable super regions, set the `enable_super_regions` <InternalLink path="set-vars">session setting</InternalLink> to `'on'`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET enable_super_regions = 'on';
```

```
SET
```

You can also set the `sql.defaults.super_regions.enabled` <InternalLink path="cluster-settings">cluster setting</InternalLink> to `true`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING  sql.defaults.super_regions.enabled = true;
```

```
SET CLUSTER SETTING
```

### View the super regions from a database

`SHOW SUPER REGIONS FROM DATABASE` returns the <InternalLink path="multiregion-overview#super-regions">super regions</InternalLink> for the specified database.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW SUPER REGIONS FROM DATABASE movr;
```

```
  database_name | super_region_name |       regions
----------------+-------------------+----------------------
  movr          | usa               | {us-east1,us-west1}
(1 row)
```

<Note>
  The preceding example shows the super region that was added in <InternalLink path="alter-database#add-a-super-region-to-a-database">`ADD SUPER REGION`</InternalLink>.
</Note>

## See also

* <InternalLink path="multiregion-overview">Multi-Region Capabilities Overview</InternalLink>
* <InternalLink path="multiregion-overview#super-regions">Super regions</InternalLink>
* <InternalLink path="alter-database#add-super-region">`ADD SUPER REGION`</InternalLink>
* <InternalLink path="alter-database#drop-super-region">`DROP SUPER REGION`</InternalLink>
* <InternalLink path="alter-database#alter-super-region">`ALTER SUPER REGION`</InternalLink>
* <InternalLink path="multiregion-overview#secondary-regions">Secondary regions</InternalLink>
* <InternalLink path="alter-database#set-secondary-region">`SET SECONDARY REGION`</InternalLink>
* <InternalLink path="alter-database#drop-secondary-region">`DROP SECONDARY REGION`</InternalLink>
* <InternalLink path="zone-config-extensions">Zone Config Extensions</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>