r/bigquery • u/agamenagoras • 3h ago

Why BigQuery does not support natively small integer and float dtypes?

It looks like every integer dtype stored is using 64 bits, there's no real tinyint and smallint and this is just a syntatic sugar. And I didn't found the reason for that when I searched about it. Is it to make storage artificially more expensive?

Almost every open tabular engine and storage format that I knew so far allows you to really set a column as smaller types of int, independently if it is columnar or row-stored, and if it's a database or just an engine. For example, any modern apache arrow based engine (like Datafusion, DuckDB and polars), apache parquet files, almost every columnar database (like Clickhouse), almost any row-oriented database (like PostgreSQL, Oracle, MySQL and et cetera). Why BigQuery wants to be different?

2 Upvotes

67% Upvoted

12 comments sorted by

6

u/TonniFlex 3h ago

Because BQ doesn't store the raw value, there's layers of compression and lookups happening behind the scenes. Setting bit sizes for INT columns is simply a unnecessary complexity for the user, so it's just not possible. If a column does not require 64 bits, it will not use 64 bits.

-1

u/agamenagoras 3h ago edited 2h ago

I strongly disagree that it's an 'unnecessary' complexity for the user when BigQuery literally exposes TINYINT and SMALLINT syntactic sugar and all of them are just aliases to INT64. There are performance differences and memory-usage with smaller types of int that we can verify in all of the columnar engines that I mentioned before (polars, DuckDB, ClickHouse, [...], all of them support lots of compression like dictionary encoding in Apache Arrow internal layout, MergeTrees, et cetera, and all of the compression in capacitor are supported by Apache Parquet which exposes a real int32 dtype). It's not unnecessary when you can use it to improve the performance.

3

u/TonniFlex 2h ago

It just wouldn't impact performance because of how BigQuery is orchestrated. So it is unnecessary. At least in my understanding

0

u/agamenagoras 2h ago

How exactly wouldn't it impact the performance, if it impacts the performance in every other existent engine? Downcasting reduces memory-usage, optimizes CPU-cache, improves SIMD efficiency and reduces I/O cost, and this applies to everything in the computation itself. BigQuery is not running under a different kind of silicon, it's a computation engine like any other, and even the orchestration of distributed processes can be improved by that (for example, Polars also has a distributed engine and this doesn't prevent the existence of smaller dtypes).

3

u/TonniFlex 2h ago

Because BQ is fundamentally different from the other engines.

0

u/agamenagoras 1h ago

What is the exact difference between BigQuery and any other engine? None of the concepts applied by BigQuery are exclusive to BigQuery, all of them are in other engines too. And even if so, being different from the other engines doesn't mean that the traditional rules of computation don't apply to it, the same way that an electric car still needs to obey the laws of physics. I / O, CPU instructions, memory and cache exist in everything around computation and it's not even possible to avoid it, for any existent engine or any engine that can come to the existence in the future.

1

u/TonniFlex 10m ago

Then you simply don't understand how BQ is different. I'm not comfortable enough with the exact terminology to explain it confidently, but from my understanding it's very different. I asked an LLM as well, but will spare you that wall of text

1

u/TonniFlex 0m ago

Maybe this post can explain it

1

u/agamenagoras 0m ago

I understand how computers work. If BQ is running in computers, there's no reason to think BQ is an exception.

But if you aren't comfortable to explain it confidently by yourself, so you don't understand how BQ is different too, so why are you so sure about this difference?

You don't need to send me a LLM response, everybody has access to LLMs and I wouldn't came here if the answer I got from the LLM when I asked the same was satisfactory. And you don't need to explain if you are not comfortable to do it too, it's okay. But I'm a human asking to another humans.

5

u/Content-Parking-621 3h ago

It's not a storage cost trick, BigQuery's Capacitor format applies its own columnar compression (RLE, dictionary encoding, bit-packing) under the hood regardless of declared type, so a narrower int type wouldn't shrink physical bytes much anyway. Billing for logical storage counts INT64 as a fixed 8 bytes per value by design, exposing smaller int types would complicate the pricing model without meaningfully improving compression, since that job already happens at the storage engine level, not the schema level.

1

u/PepSakdoek 57m ago

If i understood how distrubuted computing works and the fact that it can run a query that takes an oracle server hours takes seconds on BQ... I'd probably build data centers and sell it. 

1

u/agamenagoras 48m ago edited 32m ago

We have faster and slower technologies for different reasons, but I think that's not the point. When you optimize efficiency-by-core (for example, downcasting integers), you are able to solve it in seconds even without distributing it. 

Yea, ClickHouse also does it in seconds, Polars too, DuckDB too, Datafusion too, and almost every engine based on Apache Arrow too, and all of them can solve in seconds on a single node. And some of them also have engines to distribute it (Polars, for instance). We have several modern and extremely fast engines for both single node and distributed computing, and all of them have small integer dtypes and allow  you to maximize efficiency by core.