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.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.

Description

Table of Contents

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

Frequently Asked Questions (FAQs)

Is INFORMATION_SCHEMA free to query?

Yes. Metadata queries don’t incur data-processing charges.

How often are size statistics updated?

Storage stats are eventually consistent and usually update within a few minutes of data changes.

Can I view size in the BigQuery UI?

Yes. Click the table in the left panel; size appears under Details. The SQL methods are better for automation.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.