Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

sys.table_storage ​

This view shows the current storage statistics for each table in the database. See also sys.table_info.

Column NameData TypeDescription
table_idbigintUnique table ID; join key to sys.table. Some internal yb_query* tables with four-digit IDs are tracked by this view; you can filter them out in queries: where table_id>10000;
worker_iduuidUnique ID of the worker node where rows are stored. (The worker_id may be null when a table has no data, or when the data is temporarily stored in the row store.)
rows_columnstorebigintNumber of rows in the column store (flushed to the worker nodes). See also the rows_rowstore column in the sys.table_info view.
compressed_bytesbigintAmount of table data stored (in compressed bytes). This number may be greater than uncompressed_bytes, especially for smaller tables, given the overhead of the size of a shard (unit of data storage).
uncompressed_bytesbigintEstimated amount of table data received (in uncompressed bytes).

Calculation of Uncompressed Bytes ​

The uncompressed_bytes is calculated based on the number of columns in the table, their data type, whether they are nullable, and the number of table rows. The computed metric is typically an estimate because an average row size is subtracted for rows that were deleted from the table, not the actual row size.

The following is a breakdown of how to calculate the number of uncompressed bytes in a table:

  • Nullable columns: 1 bit per row for each column, across all rows, rounded up to the nearest byte. For example, if a table has 8 columns that allow NULL values, each row requires 1 byte for NULL values.
  • Bytes per column type for each row:
  • Fixed field types:
    • 1 byte: BOOLEAN
    • 2 bytes: SMALLINT
    • 4 bytes: INTEGER, REAL, DATE
    • 8 bytes: BIGINT, DOUBLE PRECISION, TIME, TIMESTAMP, TIMESTAMPTZ
    • 16 bytes: UUID
  • Variable field types:
    • DECIMAL
      • 4 bytes: Precision values 1 to 9
      • 8 bytes: Precision values 10 to 18
      • 16 bytes: Precision values 19 to 38
    • VARCHAR: 2 bytes to store string length, plus the length of the string in bytes
    • CHAR: Same as VARCHAR (The CHAR data type is only conceptually a fixed-width character string; internally it is handled as a variable-width character string.)

Here is a specific example of the calculated number of uncompressed bytes for a very small table named hometeam, which has 2 nullable columns and 50 rows:

CalculationUncompressed bytes
Calculation for htid (smallint data type)2 * 50 = 100
Calculation for name(varchar(30) data type)(2 * 50) + 593 (sum of actual length of strings) = 693
Calculation for both nullable columns2 bits * 50 = 100 bits = approx. 13 bytes
Total100 + 693 + 13 = approx. 806 bytes

Deleted Rows ​

When rows are deleted from a table, they are only logically deleted. They persist in the table’s storage structures until space is reclaimed by a periodic background operation. As a result, deleted rows consume space and are included in both the compressed_bytes and uncompressed_bytes metrics until space is reclaimed. If required, the number of logically deleted but not yet reclaimed rows can be used to estimate the uncompressed bytes of current rows.

Inserted Rows ​

Rows are included in the compressed_bytes and uncompressed_bytes metrics only when they are stored in the column store of the database. Rows inserted via the ybload bulk loader are accounted for immediately because they are loaded directly into the column store. However, because rows inserted via INSERT statements, or via tools using OBDC or JDBC drivers, are initially stored in the row store, they are not accounted for until they are flushed into the column store. This process typically takes only a few minutes; therefore, these rows appear in the storage calculations after a short delay.

Examples ​

Return storage information for a specific table:

yellowbrick_test=# select sum(rows_columnstore) from sys.table_storage where table_id=16606;
   sum    
-----------
 600037902
(1 row)
yellowbrick_test=# select * from sys.table_storage where table_id=16606;
 table_id |              worker_id               | rows_columnstore | compressed_bytes | uncompressed_bytes
----------+--------------------------------------+------------------+------------------+--------------------
   16606 | 00000000-0000-0000-0000-38b8ebd00023 |         40012122 |       1692401664 |         6040322048 
   16606 | 00000000-0000-0000-0000-38b8ebd00087 |         40013865 |       1692401664 |         6042157056 
   16606 | 00000000-0000-0000-0000-38b8ebd000b4 |         39998238 |       1690304512 |         6038487040 
   16606 | 00000000-0000-0000-0000-38b8ebd00163 |         39990064 |       1692401664 |         6038749184 
   16606 | 00000000-0000-0000-0000-38b8ebd001a9 |         40007412 |       1692401664 |         6041108480 
   16606 | 00000000-0000-0000-0000-38b8ebd001f9 |         39983517 |       1690304512 |         6035603456 
   16606 | 00000000-0000-0000-0000-38b8ebd00235 |         40008626 |       1692401664 |         6041108480 
   16606 | 00000000-0000-0000-0000-38b8ebd0023f |         40006680 |       1692401664 |         6038749184 
   16606 | 00000000-0000-0000-0000-38b8ebd0024e |         39975472 |       1692401664 |         6034292736 
   16606 | 00000000-0000-0000-0000-38b8ebd0082f |         40015874 |       1692401664 |         6043729920 
   16606 | 00000000-0000-0000-0000-38b8ebd00866 |         39993230 |       1690304512 |         6040846336 
   16606 | 00000000-0000-0000-0000-38b8ebd0086b |         40008190 |       1694498816 |         6038487040 
   16606 | 00000000-0000-0000-0000-38b8ebd009a6 |         40010891 |       1692401664 |         6039273472 
   16606 | 00000000-0000-0000-0000-38b8ebd009bf |         40017426 |       1692401664 |         6040846336 
   16606 | 00000000-0000-0000-0000-38b8ebd009f1 |         39996295 |       1692401664 |         6037700608 
(15 rows)

The following example joins the sys.table_storage view to the sys.table and sys.schema views. The query returns an aggregated count of bytes stored per table across the cluster.

yellowbrick=# select t.name table_name, sc.name schema_name, sum(t.compressed_bytes) bytes_stored
yellowbrick-# from sys.table t, sys.schema sc, sys.table_storage s
yellowbrick-# where (t.table_id = s.table_id) and  (t.schema_id = sc.schema_id) and (t.database_id = sc.database_id)
yellowbrick-# group by 1, 2
yellowbrick-# order by 2, 1;
  table_name   | schema_name | bytes_stored  
---------------+-------------+---------------
 newt          | premdb_bobr |             0
 awayteam      | public      |    1887436800
 hometeam      | public      |    1887436800
 match         | public      |    1887436800
 newmatchstats | public      | 2656881868800
 season        | public      |    1887436800
 t1            | public      |             0
 team          | public      |    1887436800
(8 rows)

Parent topic:System Views