Storage calculations and VARCHAR
Concepts covered: Storage Optimization
An estimate needs 2 numbers: how many rows, and how many bytes per row. Bytes per row is the sum of the fixed widths from the type table plus the average size of each VARCHAR. The product is the decoded size, the bytes a query works through after it reads the files. Compression makes files smaller, so treat the figure as a ceiling for storage and as the working size whenever a query scans or sorts the data. Rows sent between machines travel at that size too.
Price one row of the Reels view log
| Column | Type | Bytes per value |
|---|---|---|
| view_id | BIGINT | 8 |
| video_id | BIGINT | 8 |
| user_id | BIGINT | 8 |
| viewed_at | TIMESTAMP | 8 |
| watch_ms | INTEGER | 4 |
| is_autoplay | BOOLEAN | 1 |
| country | VARCHAR | 6 |
| Row total | 43 |
The country value is a 2-letter code: 2 bytes of text plus 4 bytes recording where the value ends. Variable-width data is commonly laid out with a 4-byte length or offset per value, so 4 bytes is a sound planning figure. Now scale it: suppose the table takes 10 billion rows a day, and run the arithmetic.
SELECT
43 * 10000000000 AS bytes_per_day,
43 * 10000000000 / 1e9 AS gb_per_day,
43 * 10000000000 * 365 / 1e12 AS tb_per_year| bytes_per_day | gb_per_day | tb_per_year |
|---|---|---|
| 430000000000 | 430 | 156.95 |
Narrow widths pay off because they multiply. Here is what the 4 bytes INTEGER saves over BIGINT on watch_ms come to at that daily volume.
- 40 GB
- 14.6 TB
What the decoded estimate leaves out
- Compression, which usually brings files well under the estimate
- Null markers, typically a bit or a byte per value
- Per-file metadata such as column statistics, small beside the data
A VARCHAR is priced in bytes
VARCHAR text is stored as UTF-8, which spends 1 to 4 bytes per character: 1 for ASCII letters and digits, 2 for accented Latin letters such as a with a tilde, 3 for most Chinese and Japanese characters, 4 for emoji. A column's byte cost follows the characters it holds.
| Value | Characters | UTF-8 bytes |
|---|---|---|
| US | 2 | 2 |
| Lisbon | 6 | 6 |
| São Paulo | 9 | 10 |
| 東京 | 2 | 6 |
| 🎬 | 1 | 4 |
To price a VARCHAR column, sample it, find the average bytes per value and add the 4-byte length. A city column averaging 9 ASCII characters costs roughly 13 bytes a value; the same length in a script needing 3 bytes per character costs about 31. Storing country as 'United States' rather than 'US' takes it from 6 bytes to 17.
VARCHAR(n) caps length and truncates
A declared length reserves nothing: VARCHAR(255) holding 'US' costs what VARCHAR holding 'US' costs. The limit acts when text is converted. Casting longer text to a bounded VARCHAR keeps the first n characters and raises no error, so a limit set too low corrupts data quietly.
SELECT
CAST('Brazil' AS VARCHAR(2)) AS country_code,
CAST('São Paulo' AS VARCHAR(3)) AS city_prefix| country_code | city_prefix |
|---|---|
| Br | São |
Both results fit their limits. The limit counts characters, so 'São' keeps 3 characters while taking 4 bytes. A pipeline that casts incoming text to VARCHAR(2) would turn a source's 'Brazil' into 'Br', which matches none of the stored codes, without a single error.
A device_model column is declared VARCHAR(255). Its values average 12 ASCII characters across 2 billion rows. Using this section's model, about how large is the decoded column?
- About 24 GB: 12 bytes of text for each value
- About 32 GB: 12 bytes of text plus a 4-byte length
- About 80 GB: 3 bytes per character plus a 4-byte length
- About 518 GB: the 255 declared characters plus a length
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.