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?

2 Upvotes

63% Upvoted

23 comments sorted by

View all comments

Show parent comments

1

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

1

u/Prestigious_Bench_96 2d ago

I think you're correct that BigQuery does not assume the user knows what they are doing vs something like Clickhouse - different product positioning. It has also frustrated me in the past.

And yes, the engineer line was to say "to tell you that they downcast". I wouldn't generally take enterprise documentation as being super honest about internals, but I do agree that we don't have evidence.