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.

Access table sizes statistics

Spanner provides the table sizes statistics in the SPANNER_SYS schema. You can use the following ways to access SPANNER_SYS data:

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

• The gcloud spanner databases execute-sql command.

• The executeSql or the executeStreamingSql method.

The following single read methods that Spanner provides don't support SPANNER_SYS:

• Performing a strong read from a single row or multiple rows in a table.
• Performing a stale read from a single row or multiple rows in a table.
• Reading from a single row or multiple rows in a secondary index.

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

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

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:

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;

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;

Query output

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

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

• Learn about other Introspection tools.
• Learn about other information Spanner stores for each database in the database's information schema tables.
• Learn more about SQL best practices for Spanner.
• Learn more about Investigating high CPU utilization.