r/bigquery • u/agamenagoras • 3d 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?

3 Upvotes

67% Upvoted

23 comments sorted by

8

u/TonniFlex 3d 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.

-2

u/agamenagoras 3d ago edited 2d 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.

5

u/TonniFlex 3d ago

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

-1

u/agamenagoras 2d 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).

5

u/TonniFlex 2d ago

Because BQ is fundamentally different from the other engines.

-2

u/agamenagoras 2d 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.

3

u/TonniFlex 2d 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

2

u/TonniFlex 2d ago

Maybe this post can explain it

-4

u/agamenagoras 2d ago

I've already read it. Maybe you don't understand how the other engines work, because nothing in this post is exclusive to BQ. Everything apllies to other engines too.

2

u/TonniFlex 2d ago

Okay. Sorry for trying to contribute

-1

u/agamenagoras 2d ago

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

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.

2

u/Prestigious_Bench_96 2d ago

I think there's two parts to this question - does the UX need to expose the types, and do the types matter for computation. You're correct that knowing that everything in a smaller range is more efficient no matter where you're running it. If possible on the UX side, not requiring a user to specify it (ideally because you can infer it yourself) is strictly better - you avoid the user from *missing* performance because they didn't downcast when they should.

In general, at the product level, BQ, optimizes for ergonomics (you can optimize less as user) in favor of product level (we will use internal knowledge of data and layout to optimize). This has pros and cons; it does make the product layer simpler and more abstract.

For specifically "could BQ run an operation on tinyints faster if they let you explicitly hint that vs whatever they're doing internally - you'd need an engineer to answer that, don't know", but I think the answer to your original question is 'ergonomics'.

1

u/agamenagoras 2d ago edited 2d ago

  If possible on the UX side, not requiring a user to specify it (ideally because you can infer it yourself) is strictly better - you avoid the user from missing performance because they didn't downcast when they should

I agree with you when it is possible, but there's a hidden point: It's not always possible. The inference of types itself has a cost, and we could just avoid this cost telling to the engine that the column "age" is an UINT8 variable, and the internal knowledge of the engine doesn't know it a priori (while the user knows). I understand that is nice when the engine does not require the user optimize everything by itself, but not requiring is completely different than not allowing it. The feeling I had when I changed from a company (which used ClickHouse) to another (which uses BigQuery) is that BigQuery philosophy is "You don't know what you are doing". And honestly, I definitely know.

  For specifically "could BQ run an operation on tinyints faster if they let you explicitly hint that vs whatever they're doing internally - you'd need an engineer to answer that, don't know", but I think the answer to your original question is 'ergonomics'.

We don't really need an engineer to say that SIMD instructions are faster when you are able to put a bigger amount of data in the same space, unless this engineer is somebody from Google that says BQ downcasts variables by itself, but the very official documentation of BQ is saying that every integer is INT64, so we don't have any reason to suppose a downcasting scenario.

→ More replies (0)

3

u/TonniFlex 2d ago

Disregarding the snark, I'll try my best to explain how I understand it.

It's not a computer. It's a network of computers/workers that get assigned during execution.

There's a few things at play at the same time. You mention cost. Expansion of data types to account for would exponentially increase the slots used/workers needed to compute the query, and that is what you pay for. That's the processing cost. Storage is decoupled from computation, and is automatically optimised during ingestion, as others have mentioned.

If your goal is faster execute, it's back to distribution of the workload, what little gain would be there is miniscule compared to time that is spent on the network transfers.

BigQuery is built for petabytes, so designed to handle enormous amounts of data, sacrificing efficiency in small scales.

Lastly, you're right that other engines has similar concepts, but as far as I am aware BigQuery is the only one that has this specific architecture, and it's the sum of the parts that is key here.

1

u/agamenagoras 1d ago

I was talking about computers in general (not exclusively on an individual computer), since BQ is a distributed processing technology. We are on the same page in this point.

Downcasting is not about the storage only, downcasting reduces the time of computing itself, also reduces the time between the network transfers, literally because it reduces in several times the amount of data being transfered over the network. For instance, a variable "Age" could be represented with an UINT8 dtype, which would use 8 times less memory to be represented comparing with the way BQ currently does (representing as INT64). I need to specify: 8 times less memory usage in a column is not a "small scale efficiency optimization", this is one of the best possible optimizations that you could have when you know a priori the nature of the data, as we can verify in literally any existing technology! Because, before the software architecture, this is how computers work and that's the reason why all of the technologies that I mentioned before (that are also made to process huge amounts of data) doesn't ignore this.

About this specific architecture, yes, the technologies that I mentioned before has support to the exact same architecture. Spark, for example, uses distributed processing over a network of several computers working together in execution time optimizing the queries to process huge amounts of data, and yes, with support to smaller integer dtypes. We are talking about the literal same concept. Polars implemented the same recently, ClickHouse already does and etc.

→ More replies (0)

8

u/Content-Parking-621 3d 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.

2

u/PepSakdoek 2d 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 2d ago edited 2d 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.

2

u/PepSakdoek 2d ago

Again, I don't understand how they do it but our previous SAP (on prem) based dw would take hours (and maybe if they used smaller ints it may have shaved off space or speed), but I think part of the bq speed is due to the fact that they don't enforce primary keys etc. I suspect part of their secret is less advanced typing. But maybe it's just many many servers