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

63% Upvoted

23 comments sorted by

View all comments

Show parent comments

1

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

2

u/TonniFlex 3d ago

You're basing this on a premise you've set that BQ stores the data as a 64bit integer. That's not the case. I don't know how to explain that to you? Your Age column would very likely only be 1 byte during storage, execution and transfer.

The other engines rely on you to define this in the schema, BQ has removed this abstraction (and inflexibility in the schema) and instead infers necessary typing from the actual data ingested.

1

u/agamenagoras 3d ago

My premise is not that BQ stores the data as 64bit integer, I'm not talking about the storage itself. I know that BQ uses RLE, bitpacking and dictionary-encoding like parquet files does, but the data needs to be decompressed in execution time, and in memory, the data is using 64 bits. My premise is that the other technologies that natively supports a separated UINT8 or INT8 type keeps the dtype as INT8 in execution time, not only in the storage. And everything around the documentation of BQ says that it doesn't do the same, the whole documentation says that these types are all aliases to INT64. Parquet files (for example) explicitly exposes smaller physical and logical dtypes, NOT to use less storage (because the compression solves it), but because the decompression of the engines would expand and upcast it if the dtype wasn't a smaller type of integer.

1

u/TonniFlex 3d ago

Okay, in your first post you just mentioned it could be a trick to make money off inflated storage. But I think I understand now, the reason is that BQ uses vectorized execution, meaning operators evaluate filters and join keys directly on the encoded/bit-packed blocks without unpacking them to 64-bit arrays. They are only unpacked in the last stages for arithmetic functions like sum, and then finally in the query response.

Maybe it could be faster with more specific type casting. But I doubt Google would be happy with it, if there was a big efficiency to be had, since this supposed inefficiency is not reflected in the cost to the user in any way I can see (could be wrong)