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 | text | Distribution type: replicated, hash, random. |
| sort_key | text | Column name if sorted; otherwise null. |
| distribution_key | text | Column name if distributed by hash; othwerise null. |
| cluster_keys | text | Columns used as cluster keys, if any. |
| partition_keys | text | Columns used as partitioning columns, if any. |
| is_temp | boolean | Whether the table was created as a temporary table (f=persistent; t=temporary). |
| definition | text | Not implemented. |
| is_auto_analyze | boolean | 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 | "char" | 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. |
Examples
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)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.database where name = 'premdb';
-[ 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]Parent topic:System Views