Type optimization at scale

Concepts covered: Storage Optimization

A column's type fixes how many bytes each value occupies once the engine reads it, and that width is paid on every row. At 10 billion rows, 1 extra byte per row is 10 GB more that the engine scans and holds in memory on every query that touches the column, and that data also travels between machines. The table below lists the widths you are choosing between.

These are the widths the engine uses for each value in memory. Files on disk usually hold less, which the compression section covers.

Size each column to its worst case

Pick the narrowest type that holds the largest value the column could ever take, judged by what the value measures. watch_ms in the Reels view log is a single view's watch time in milliseconds. SMALLINT stops at 32,767, under 33 seconds, so the first long view breaks it. INTEGER reaches 2,147,483,647 ms, almost 25 days, far past any single view, so it fits with room to spare, while BIGINT would spend 4 more bytes per row on range no view will use. IDs are the exception: generators at this scale hand out 64-bit values, and a narrower column fails the first time an ID passes its ceiling, so IDs get BIGINT.

Here is the first of those traps on a realistic video ID. Predict the last column, then run it.

SELECT
  9007199254740993 AS video_id,
  CAST(9007199254740993 AS DOUBLE) AS as_double,
  CAST(CAST(9007199254740993 AS DOUBLE) AS BIGINT) AS back_to_bigint

A DOUBLE keeps 53 bits for its digits, so above 2 to the 53rd power it can only land on even numbers. The ID ending in 993 comes back ending in 992, and 2 different videos now share an ID: views of one get credited to the other, with no error anywhere.

Text that holds numbers sorts as text

Raw logs often land with every field as text. Keeping watch_ms as VARCHAR costs more bytes than INTEGER and changes how values compare: text is compared character by character, so '15000' sorts ahead of '800' because '1' comes before '8'. Predict the order in each tab before you run it.

As VARCHAR

Orders by the VARCHAR column, comparing the text a character at a time.

SELECT
  view_id,
  watch_ms_text
FROM raw_views
ORDER BY watch_ms_text

As INTEGER

Casts to INTEGER first, so ORDER BY compares numeric values.

SELECT
  view_id,
  CAST(watch_ms_text AS INTEGER) AS watch_ms
FROM raw_views
ORDER BY watch_ms

The VARCHAR tab returns 15000, 31000, 4200, 800, 900, the order a word list would file them in. The INTEGER tab returns 800, 900, 4200, 15000, 31000. The cast fixes the order, and the worst-case rule from earlier decides which integer type it targets. A single view's milliseconds stay under INTEGER's ceiling. A column that adds views together keeps growing, so its worst case is its largest future total.

A video_stats table keeps a row per reel with lifetime_watch_ms, the total milliseconds all viewers have spent on that reel. Which type should the column use?

• INTEGER, since 4 bytes per row halves the cost of BIGINT
• DOUBLE, since it reaches far larger totals in 8 bytes
• BIGINT, since a lifetime total can outgrow INTEGER's ceiling
• DECIMAL(38,0), since an exact decimal never overflows

About this lesson

This section is part of the Data Types: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.