# Databases Page

The Databases page of the CockroachDB Cloud Console provides details of the following:

- The databases configured.
- The tables in each database and the indexes on each table.
- The grants assigned to each role and SQL user.
- Index recommendations.

To view this information, select a cluster from the Clusters page, and click Databases in the Data section of the left side navigation menu. This will give you access to the following:

- Databases List Page: initial page that lists the databases on the cluster.
- Database Details Page
  - Tables List Tab: lists tables in a database.
  - Database Grants Tab: lists privileges on a database.
- Table Details Page
  - Overview Tab: displays a table’s details.
  - Table Grants Tab: lists privileges on a table.
  - Indexes List Tab: lists indexes on a table with index recommendations and actions.
- Index Details Page: displays an index’s details with index recommendations.

## Index recommendations

The Indexes List Tab of the Table Details Page shows recommendations to drop indexes based on index usage with a Drop index button. Admin users can click this to drop an unused index.

The Index Details Page also shows recommendations to drop the selected index based on index usage.

To configure the threshold for when CockroachDB will recommend that you drop an index due to low usage, change the `sql.index_recommendation.drop_unused_duration` cluster setting. The default value is 7 days.

## Databases List Page

To view this page, click Databases in the left side navigation menu.

The Databases List Page shows:

- Whether automatic statistics collection is enabled for the cluster. The Auto stats collection indicator is on the top right.
- A list of the databases on the cluster.

The following information is displayed for each database:

Column | Description
--- | ---
Name | The name of the database. Click a database name to view the Database Details Page for the selected database.
Size | The approximate total disk size across all table replicas in the database.
Tables | The total number of tables in the database.
Regions/Nodes | Regions/Nodes on which the database tables are located. Hover on a region for a list of nodes in that region. Note: Not available on Standard or Basic clusters.

By default, the Databases List Page displays up to 10 databases on page 1. If more than 10 databases exist, use the page navigation at the bottom of the page to display the additional databases.

To search for specific databases, use the search field above the list table:

1. Enter a string in the search box.

2. Press `Enter`.

   The list of databases is filtered by the string.

To filter databases based on the nodes on which the database tables are located, use the nodes multi-select dropdown above the list table:

1. Click the dropdown arrow.

2. Select one or more nodes. You may need to scroll down for nodes in different regions. You may also type in the beginning of the node name to narrow the list.

   The list of databases is filtered by the nodes selected.

Note:

Nodes multi-select dropdown is not available on Standard or Basic clusters.

### Refresh data

The `system.table_metadata` table caches the necessary metadata of a table that populates the following:

- Databases List Page
- Tables List Tab of the Database Details Page
- Overview Tab of the Table Details Page

The cluster settings that control the refresh behavior for the cached data in the `system.table_metadata` table are:

- `obs.tablemetadata.automatic_updates.enabled` (default: `false`) enables automatic updates of the table metadata cache `system.table_metadata`.
- `obs.tablemetadata.data_valid_duration` (default: `20m0s`) is the duration for which the data in `system.table_metadata` is considered valid.

On the Databases List Page and the Tables List Tab of the Database Details Page, above the table of information, the Last refreshed indicator displays how long ago the cached data was last refreshed automatically (per the cluster settings) or manually.

The cached data can be refreshed in the following ways:

- Automatically: Set the cluster setting `obs.tablemetadata.automatic_updates.enabled` to `true`. The frequency of refreshes is controlled by the cluster setting `obs.tablemetadata.data_valid_duration`. Automatic updates is disabled by default to avoid unnecessarily consuming resources. However, if you want databases and tables information immediately when you visit the corresponding page, then enable automatic updates.
- Manually: When any user clicks the Refresh data icon button to the right of the Last refreshed indicator. If a refresh job has been triggered, the Refresh data icon button will be disabled and the hover text will display the time the job was started and the progress of the job.
- Additionally, when any user visits the Databases List Page, the Last refreshed timestamp is compared to the cluster setting `obs.tablemetadata.data_valid_duration`. If the time since the Last refreshed timestamp exceeds the `obs.tablemetadata.data_valid_duration`, then a cache refresh is triggered.

## Database Details Page

To view this page, click on a database name on the Databases List Page. The Database Details Page has two tabs: Tables and Grants.

### Tables List Tab

Click on the Tables tab of the Database Details Page to view a list of the tables in the selected database.

You can refresh the data that is displayed on this page. Note: This also refreshes data on the Databases List Page.

The following information is displayed for each table:

Column | Description
--- | ---
Name | The name of the table. Click a table name to view the Table Details Page for the selected table.
Replication Size | The approximate compressed total disk size across all replicas of the table.
Ranges | The number of ranges in the table.
Columns | The number of columns in the table.
Indexes | The number of indexes in the table.
Regions/Nodes | Regions/Nodes on which the table's data is stored. Note: Not available on Standard or Basic clusters.
% of Live Data | The percentage of total uncompressed logical data that has not been modified (updated or deleted).
Table auto stats enabled | Whether automatic table statistics is enabled. Automatic statistics can help improve query performance.
Stats last updated | The last time table statistics used by the SQL optimizer were updated.

By default, the Tables List Tab displays up to 10 tables of the selected database on page 1. If more than 10 tables exist, use the page navigation at the bottom of the page to display the additional tables.

To search for specific tables, use the search field above the list table:

1. Enter a string in the search box.

2. Press `Enter`.

   The list of tables is filtered by the string.

To filter databases based on the nodes on which the database tables are located, use the nodes multi-select dropdown above the list table:

1. Click the dropdown arrow.

2. Select one or more nodes. You may need to scroll down for nodes in different regions. You may also type in the beginning of the node name to narrow the list.

   The list of tables is filtered by the nodes selected.

Note:

Nodes multi-select dropdown is not available on Standard or Basic clusters.

### Database Grants Tab

Click on the Grants tab Database Details Page to show the privileges granted to users and roles on the database.

The following information is displayed for each grantee:

Column | Description
--- | ---
Grantee | The role or user.
Privileges | The list of privileges for the role or user on the database.

For more details about grants and privileges, refer to `GRANT`.

## Table Details Page

To view this page, click on a table name on the Tables List Tab of the Database Details Page.

### Overview Tab

Click on the Overview tab of the Table Details Page to view the SQL statement used to create the table and other table details.

The following information is displayed for the table:

Detail | Description
--- | ---
Size | The approximate compressed total disk size across all replicas of the table.
Ranges | The number of ranges in the table.
Replicas | The number of replicas in the table.
Regions/Nodes | Regions/Nodes on which the table's data is stored. NOTE: Not available on Standard or Basic clusters.
% of Live Data | The percentage of total uncompressed logical data that has not been modified (updated or deleted).
Auto stats collections | Whether automatic table statistics is enabled. Automatic statistics can help improve query performance.
Stats last updated | The last time table statistics used by the SQL optimizer were updated.

#### Last updated

The Last updated timestamp is the actual time the metadata was last refreshed for the selected table.

When the refresh job runs to update the `system.table_metadata` table, if it encounters an error retrieving the metadata for the selected table, an error indicator will appear next to the Last updated timestamp and the error message will appear in the hover text.

### Table Grants Tab

Click on the Grants tab of the Table Details Page to view the privileges granted to users and roles on the table.

The following information is displayed for each grantee:

Column | Description
--- | ---
Grantee | The role or user.
Privileges | The list of privileges for the role or user on the table.

For more details about grants and privileges, refer to `GRANT`.

### Indexes List Tab

Click on the Indexes tab of the Table Details Page to view a list of indexes on a table with index recommendations and actions.

The following information is displayed for each index:

Column | Description
--- | ---
Index Name | The name of the index. Click an index name to view the Index Details Page for the selected index.
Last Read | The time the index was created, last read, or index statistics were reset.
Total Reads | The number of times the index was read since index statistics were reset.
Recommendations | A recommendation to drop the index if it is unused.
Action | If recommended, a Drop index button is displayed. Admin users can click this to drop an unused index.

#### Reset all index statistics

Index statistics accumulate from the time an index was created or when statistics were reset. If desired, admin users may reset index statistics for the entire cluster by clicking Reset all index stats. This link does not appear for non-admin users. Last reset is the timestamp at which the last reset started.

## Index Details Page

To view this page, click on a index name on the Indexes List Tab of the Table Details Page.

The Index page displays the SQL statement used to create the index and an index’s details. The page also allows admin users to Reset all index stats.

The following information is displayed for the index:

Detail | Description
--- | ---
Total Reads | The number of times the index was read since index statistics were reset.
Last Read | The time the index was created, last read, or index statistics were reset.
Index Recommendations | A recommendation to drop the index if it is unused.

### Index Usage

The Index Usage table displays a list of the most executed statement fingerprints using this index. This table is only visible to Admin users. The information displayed for each statement fingerprint is similar to the table on the Statements page.

## See also

- Statements page
- Assign privileges
- `GRANT`