On a secure cluster, you must be an admin user or a SQL user with the CONNECT privilege granted on a database.
The Databases page of the DB 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 user.
- Index recommendations.
To view this information, access the DB Console and click Databases in 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. |
Search and filter databases
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:
- Enter a string in the search box.
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:
- Click the dropdown arrow.
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.
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 cachesystem.table_metadata.obs.tablemetadata.data_valid_duration(default:20m0s) is the duration for which the data insystem.table_metadatais 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.enabledtotrue. The frequency of refreshes is controlled by the cluster settingobs.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 theobs.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. |
| % 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. |
Search and filter tables
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:
- Enter a string in the search box.
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:
- Click the dropdown arrow.
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.
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. |
| % 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
Was this helpful?
