Product DocumentationAppearance
sys.table
A system view that returns information about the user-defined tables in the database.
Note: The yellowbrick database stores a set of yb_* tables that are visible to superusers only. These tables have four-digit table IDs and appear in results when superusers query sys.table and other views that have the sys.table_ prefix.
| Column Name | Data Type | Description |
|---|---|---|
| table_id | bigint | The unique ID for the table. Tables in different schemas within the same database may have the same name, so you can use this ID to uniquely identify a table. |
| database_id | bigint | The unique ID for the database. |
| name | text | Name of the table. |
| schema_id | bigint | Unique ID of the database schema where the table was created. |
| owner_id | bigint | Unique ID of the owner of the table, referring to the user who created it. |
| distribution | varchar(256) | Distribution type: replicated, hash, random. See Distribution Options. |
| sort_key | text | Column name if sorted; otherwise null. See Sorted and Clustered Tables |
| distribution_key | text | Column name if distributed by hash; otherwise null. See Distribution Options. |
| cluster_keys | text | Columns used as cluster keys, if any. See Sorted and Clustered Tables |
| partition_keys | text | Columns used as partitioning columns, if any. See Partitioning Options. |
| is_temp | boolean | Whether the table was created as a temporary table (f=persistent; t=temporary). |
| definition | text(4) | Not implemented. |
| is_auto_analyze | varchar | f=table not enabled for auto-analyze; t=table enabled for auto-analyze. |
| auto_analyze_policy | text | Name of the auto-analyze policy enabled for the table, if any (or default_policy). |
| last_analyzed | timestamptz | When the table was last analyzed (either manually or automatically). |
| creation_time | timestamptz | When the table was created. |
| rowstore_bytes | bigint | Number of bytes committed to the row store for the table. |
| rowstore_row_count | bigint | Number of rows committed to the row store for the table. |
| rowstore_disk_usage | bigint | Total size of files currently used in the row store for the table. An entire file (32MB) is reported as in use even if there is only a single row in the table. In general, partially filled files are filled up before new ones are created. |
| rowstore_size_limit | bigint | Total size allowed in the row store for the table. The default is 500GB. See also ALTER TABLE. |
| rowstore_full_action | text | Action taken when the row store size limit is reached for the table: S (Slow), B (Block), or C (Cancel). See Managing the Row Store and ALTER TABLE. |
| compressed_bytes | bigint | Total compressed bytes of actual, live usage on all transactions on the workers. |
| uncompressed_bytes | bigint | Total uncompressed bytes of actual, live usage on all transactions on the workers. This does not include bytes in the rowstore on the manager nodes. |
| snapshot_backup_bytes | bigint | Backup and restore system data, which is stored in internal system tables. Managed by the backups and only decreases when full backups occur. This data is not counted in the quota decision for the current transaction but is counted for subsequent transactions. |
| delete_info_bytes | bigint | Deleted and updated system data on the workers, which is kept in specialized internal data blocks. |
| reclaimable_bytes | bigint | Deleted or updated user data on the workers that will be freed up in the background. |
| max_size_bytes | bigint | Maximum size of the table set by the quota. NULL if no disk usage limit is set. |
| location | name | Storage location of the table (currently always LOCAL). |
Examples
Return distribution information for tables and the timestamp for when they were last analyzed:
premdb=# select table_id, name, distribution, distribution_key, last_analyzed
from sys.table order by table_id;
table_id | name | distribution | distribution_key | last_analyzed
----------+---------------+--------------+------------------+-------------------------------
16882 | season | replicated | | 2017-09-01 11:47:50.685147-07
16885 | team | replicated | | 2017-09-01 11:47:56.03602-07
16888 | hometeam | replicated | | 2017-09-01 11:44:19.424487-07
16891 | awayteam | replicated | | 2017-09-01 11:44:19.032821-07
16894 | match | hash | seasonid | 2017-09-01 11:48:07.712739-07
16898 | newmatchstats | hash | seasonid | 2017-09-01 11:40:18.967244-07
(6 rows)Return row store information for some tables:
premdb_match=# select name, rowstore_bytes, rowstore_row_count, rowstore_disk_usage, rowstore_size_limit, rowstore_full_action
from sys.table where table_id>10000;
name | rowstore_bytes | rowstore_row_count | rowstore_disk_usage | rowstore_size_limit | rowstore_full_action
-------+----------------+--------------------+---------------------+---------------------+----------------------
new | 231 | 3 | 33554432 | 549755813888 | S
match | 33288310 | 6502 | 33554432 | 33554432 | S
(2 rows)Return information for a table named season:
premdb=# select * from sys.table where name = 'season';
-[ RECORD 1 ]---------+---------
table_id | 16635
database_id | 16634
name | season
schema_id | 2200
owner_id | 16007
distribution | replicated
sort_key | [NULL]
distribution_key | [NULL]
cluster_keys | [NULL]
partition_keys | [NULL]
is_temp | f
definition | [NULL]
is_auto_analyze | t
auto_analyze_policy | default_policy
last_analyzed | [NULL]
creation_time | 2020-10-01 15:01:01.747977-07
rowstore_bytes | 1917
rowstore_row_count | 25
rowstore_disk_usage | 33554432
rowstore_size_limit | 549755813888
rowstore_full_action | S
compressed_bytes | 0
uncompressed_bytes | 0
snapshot_backup_bytes | 0
delete_info_bytes | 0
reclaimable_bytes | 0
max_size_bytes | [NULL]
location | LOCAL