How to check table size in BigQuery

Galaxy Glossary

How do I quickly check the size of a table in BigQuery?

Use INFORMATION_SCHEMA views or __TABLES__ meta-tables to find the on-disk size of a BigQuery table and its partitions.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is the fastest way to see a table’s size?

Run a SELECT against region-us.INFORMATION_SCHEMA.TABLE_STORAGE or the legacy __TABLES__ meta-table. Both return bytes stored without scanning the table, so the query is free.

Which system views store size metrics?

Standard SQL: PROJECT_ID.DATASET.INFORMATION_SCHEMA.TABLE_STORAGE and TABLE_OPTIONS. Legacy SQL: [PROJECT:DATASET.__TABLES__]. Each row includes total_rows, active_storage_bytes, and long_term_storage_bytes.

How do I inspect a single table?

Filter by table_name in INFORMATION_SCHEMA, or by table_id in __TABLES__. Include partition_id when the table is partitioned to see per-partition footprints.

How can I list the top 10 largest tables?

Order by active_storage_bytes descending and limit the result set. This helps prioritize cleanup or clustering work.

Does table size match query scan size?

No. Query scan size equals columns read × filtered partitions. Table size is the full stored data. Use size stats to forecast storage cost, not query cost.

Best practices for managing large tables

Partition by date or integer ranges, cluster by commonly filtered columns, and configure partition_expiration_days. Regularly archive old partitions to long-term storage or delete them.

Why How to check table size in BigQuery is important

How to check table size in BigQuery Example Usage


-- Show size of each partition in a daily-partitioned Orders table
SELECT
  partition_id,
  active_storage_bytes/1024/1024 AS size_mb
FROM `my-project.my_dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'Orders'
ORDER BY size_mb DESC;

How to check table size in BigQuery Syntax


-- INFORMATION_SCHEMA (recommended)
SELECT
  table_name,
  active_storage_bytes AS table_size_bytes
FROM `my-project.my_dataset.INFORMATION_SCHEMA.TABLE_STORAGE`
WHERE table_name = 'Orders';

-- Legacy meta-table
SELECT
  table_id AS table_name,
  size_bytes
FROM `my-project:my_dataset.__TABLES__`
WHERE table_id = 'Orders';

-- Top 10 largest tables in a dataset
SELECT table_name,
       active_storage_bytes/1024/1024/1024 AS size_gb
FROM `my-project.my_dataset.INFORMATION_SCHEMA.TABLE_STORAGE`
ORDER BY active_storage_bytes DESC
LIMIT 10;

Common Mistakes

Relying on query bytes processed as a proxy for storage size. Scan bytes reflect columns read and filters applied, not the full stored footprint. Always query INFORMATION_SCHEMA instead.
Using wildcard tables like <code>dataset.Orders*</code> to estimate size. Wildcards trigger a full scan and incur cost. Meta-tables return size instantly and free.