Table sizes statistics

Spanner provides the following built-in tables:

  • SPANNER_SYS.TABLE_SIZES_STATS_1HOUR: lists the sizes of your tables and indexes within your databases.
  • SPANNER_SYS.TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR: lists the sizes of your tables and indexes within your databases for each locality group.

The table size is in bytes. Table sizes include data versions. You can use theses built-in tables to monitor your table and index sizes over time. You can also monitor the sizes of your indexes as you create, delete, and you modify them (as you insert more rows into the index or when you add new columns to it). Additionally, you can also look at the sizes of your change stream tables.

Database storage can be monitored with the Total database storage metric. You can see the breakdown of the database storage with SPANNER_SYS.TABLE_SIZES_STATS_1HOUR and SPANNER_SYS.TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR.

Availability

SPANNER_SYS data is available only through SQL interfaces. For example:

  • A database's Spanner Studio page in the Google Cloud console

  • The gcloud spanner databases execute-sql command

  • The executeQuery API

Other single read methods that Spanner provides don't support SPANNER_SYS.

TABLE_SIZES_STATS_1HOUR

SPANNER_SYS.TABLE_SIZES_STATS_1HOUR contains the sizes of all the tables in your database, sorted by interval_end. The intervals are based on clock times, ending on the hour. Internally, every 5 minutes, Spanner collects data from all servers and then makes the data available in the TABLE_SIZES_STATS_1HOUR table shortly thereafter. The data is then averaged per every clock hour. For example, at 11:59:30 AM, TABLE_SIZES_STATS_1HOUR shows the average table sizes from the interval of 10:00:00 AM - 10:59:59 AM.

Table schema

Column name Type Description
INTERVAL_END TIMESTAMP End of time interval in which the table sizes were collected.
TABLE_NAME STRING Name of the table or the index.
USED_BYTES FLOAT64 Table size in bytes.
USED_SSD_BYTES FLOAT64 SSD storage used by table in bytes.
USED_HDD_BYTES FLOAT64 HDD storage used by table in bytes.

Example queries

This section includes several example SQL statements that retrieve aggregate table sizes statistics. You can run these SQL statements using the client libraries, the gcloud spanner, or the Google Cloud console.

Query 4 largest tables and indexes for the most recent interval

The following query returns the 4 largest tables and indexes for the most recent interval:

    SELECT interval_end,
          table_name,
          used_bytes
    FROM spanner_sys.table_sizes_stats_1hour
    WHERE interval_end = (
          SELECT MAX(interval_end)
          FROM spanner_sys.table_sizes_stats_1hour)
    ORDER BY used_bytes DESC
    LIMIT 4;

Query output
interval_end table_name used_bytes
2022-11-15 13:00:00-07:00 order_item 60495552
2022-11-15 13:00:00-07:00 orders 13350000
2022-11-15 13:00:00-07:00 item_inventory 2094549
2022-11-15 13:00:00-07:00 customer 870000

Query size trend for a specific table or index for the last 24 hours

The following query returns the size of the table over the last 24 hours:

GoogleSQL 

SELECT interval_end, used_bytes
  FROM spanner_sys.table_sizes_stats_1hour
WHERE interval_end > TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -24 HOUR)
  AND table_name = "table_name"
ORDER BY interval_end DESC;

Where:

  • table_name must be an existing table or index in the database.

PostgreSQL 

SELECT interval_end, used_bytes
  FROM spanner_sys.table_sizes_stats_1hour
WHERE interval_end > spanner.timestamptz_subtract(now(), '24 HOUR')
  AND table_name = 'table_name'
ORDER BY interval_end DESC;

Where:

  • table_name must be an existing table or index in the database.
Query output
interval_end used_bytes
2022-11-15 13:00:00-07:00 13350000
2022-11-15 12:00:00-07:00 13350000
2022-11-15 11:00:00-07:00 13350000
2022-11-15 10:00:00-07:00 13350000
2022-11-15 09:00:00-07:00 13350000
2022-11-15 08:00:00-07:00 12350000
2022-11-15 07:00:00-07:00 12350000
2022-11-15 06:00:00-07:00 12350000
2022-11-15 05:00:00-07:00 11350000
2022-11-15 04:00:00-07:00 11350000
2022-11-15 03:00:00-07:00 11350000
2022-11-15 02:00:00-07:00 11350000
2022-11-15 01:00:00-07:00 11350000
2022-11-15 00:00:00-07:00 10350000
2022-11-14 23:00:00-07:00 10350000
2022-11-14 22:00:00-07:00 10350000
2022-11-14 21:00:00-07:00 10350000
2022-11-14 20:00:00-07:00 10350000
2022-11-14 19:00:00-07:00 10350000
2022-11-14 18:00:00-07:00 10350000
2022-11-14 17:00:00-07:00 10350000
2022-11-14 16:00:00-07:00 10350000
2022-11-14 15:00:00-07:00 10350000
2022-11-14 14:00:00-07:00 10350000
2022-11-14 13:00:00-07:00 10350000

TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR

SPANNER_SYS.TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR contains the sizes of all the tables in your database, sorted by interval_end, for each locality group. The intervals are based on clock times, ending on the hour. Internally, every 5 minutes, Spanner collects data from all servers and then makes the data available in the TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR table shortly thereafter. The data is then averaged per every clock hour. For example, at 11:59:30 AM, TABLE_SIZES_STATS_PER_LOCALITY_GROUP_1HOUR shows the average table sizes for each locality group from the interval of 10:00:00 AM - 10:59:59 AM.

Table schema

Column name Type Description
INTERVAL_END TIMESTAMP End of time interval in which the table sizes were collected.
TABLE_NAME STRING Name of the table or the index.
LOCALITY_GROUP STRING Name of the locality group.
USED_BYTES FLOAT64 Table size in bytes.
USED_SSD_BYTES FLOAT64 SSD storage used by table in bytes.
USED_HDD_BYTES FLOAT64 HDD storage used by table in bytes.

Data retention

At a minimum, Spanner keeps data for SPANNER_SYS.TABLE_SIZES_STATS_1HOUR for intervals covering the previous 30 days.

What's next