How BigQuery works

BigQuery keeps storage and compute separate, stores each column on its own, and runs a query as parallel stages on slots. What that means for what you pay, why there are no indexes to tune, and what a slot is.

BigQuery keeps your data in one managed storage system and runs queries on a separate pool of compute. The two scale independently and are billed separately. Tables are stored by column, so a query reads only the columns it names. A query is split into stages, and each stage runs as many parallel pieces of work on units of compute called slots.

Storage and compute are separate

When you load data, BigQuery allocates the storage for you. There is no disk to provision and no cluster to size. Google describes the storage as designed for 99.999999999% annual durability, and it replicates data across multiple availability zones. All data is encrypted before it is written to disk.

When you run a query, a query engine hands the work to many workers. They scan the tables in storage, process their share, and pass results along. Google says the whole execution happens in memory, over a petabit network that moves data to the workers.

Storage and compute are priced on separate meters: storage pricing charges for bytes kept, and on-demand or capacity pricing charges for the query.

Data is stored by column

A row-oriented database keeps each row together, which is fast for reading one record. BigQuery stores each column separately. A query that sums one column of a million-row table reads that column and leaves the rest of the table alone. Data in one column also tends to repeat (the same country code a million times), so it compresses well. Google’s own name for the storage format is Capacitor.

You can see this in the byte estimate. The table bigquery-public-data.samples.shakespeare has 164,656 rows. Reading its integer column word_count:

SELECT word_count
FROM `bigquery-public-data.samples.shakespeare`

The estimate is 1,317,248 bytes, which is 164,656 rows times 8 bytes. Reading the string column word instead:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`

costs 1,332,943 bytes, because a string is counted as 2 bytes plus its length in UTF-8. Reading both:

SELECT word, word_count
FROM `bigquery-public-data.samples.shakespeare`

costs 2,650,191 bytes, exactly the sum of the two. The estimate follows the columns you name, nothing else. Filtering rows does not make a column cheaper, because the filter still reads its column:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = 'hamlet'

This reads word and corpus, 3,797,568 bytes, more than word alone. The rules for what counts, including LIMIT, are in how queries are billed.

How a query runs

BigQuery turns the SQL into an execution plan made of stages. A stage is a set of steps that can run in parallel, such as reading a table, filtering, or aggregating. Stages pass data to the next stage through shuffle, a fast distributed memory layer that holds the intermediate results. If a query moves a lot of data between stages, shuffle can spill to disk, which the plan reports as shuffleOutputBytesSpilled.

The plan is not fixed. While a query runs, BigQuery can change it based on what earlier stages produced. When the query finishes, the result is written to persistent storage before it is returned, which is what lets BigQuery serve cached results to the same query later. The stages, steps and shuffle statistics of a finished query are in the execution graph and in the job’s metadata. See how a query runs. Querylab.io’s execution plan draws a finished query’s stages as a data-flow chart, with the rows each stage read and wrote, its shuffle bytes and slot time, and a BOTTLENECK tag on the slowest stage.

What a slot is

A slot is a virtual compute unit that BigQuery uses to run a query. Google’s definition abstracts compute, memory and I/O. Slots run the individual units of work of each stage in parallel. BigQuery decides how many slots a query uses, based on the amount of data, the complexity of the query and the slots available. You can’t set it per query.

What you do control is how many slots are available and how you pay for them. On-demand pricing bills by bytes scanned, and each project is subject to a maximum concurrent slots limit. Capacity pricing (editions) lets you buy slots. See slots and reservations.

Each finished job records how many slot milliseconds it used; the Info view of a result in Querylab.io shows it as slot time. This is real metadata from your own project, so the query is shown here and not run:

-- Not run: reads your project's job history
SELECT
  job_id,
  total_bytes_processed,
  total_slot_ms,
  TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS elapsed_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_type = 'QUERY'
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY creation_time DESC
LIMIT 10;

total_slot_ms is the slot time the job used, summed over all its workers, so it can be larger than elapsed_ms. Change region-us to your data’s location; see locations.

Why there are no indexes to tune

You don’t create indexes to make ordinary queries fast. BigQuery doesn’t index primary keys, and Google’s join advice follows from that: the wider the join column, the longer the comparison takes, so join on INT64 where you can. Query speed comes from reading fewer columns, reading fewer partitions with partitioning and clustering, and having enough slots.

BigQuery does have two optional index types: search indexes for finding a value in text, and vector indexes for nearest-neighbor search.

What this means for how you write queries

• Name the columns you need. SELECT * reads everything.
• Narrow the data before you join and aggregate, since both move data through shuffle.

In Querylab.io the editor shows BigQuery’s dry-run estimate for the query you are writing, the same number as the dry runs above. See cost limits.