Crate Column Limit & Performance Impact


#1

Is there a column limit in crate?
If not - does it have negative impact to have a huge amount of columns?


#2

Hi Phil,
Thanks for your question. As you can see in the link to the documentation I posted below, the default limit is 1.000 columns. If you stay within that, you should experience no negative impact. Theoretically the amount of columns is unlimited, but you will for sure start to experience performance issues if you overdo it.

https://crate.io/docs/crate/reference/en/latest/sql/statements/create-table.html#mapping-total-fields-limit


#3

Thank you for your feedback. One more question:
If i increase the amount of columns to lets say 10.000. And each row has values for about 100 columns (rest is null). Does it still have a bad impact on performance like if i would have values in every column?

My idea is to have rows with lots of columns (millions) but only some columns (~100) of them contain values. As i dont have any experience with lucene indices maybe you can help me out?


#4

Hi Phil,

As already mentioned above, it is in theory allowed to have unlimited set of columns but there is a certain performance impact for even having >10K columns in real world scenarios. Every column is basically an extra index and too many would not be ideal even though we store data in a few of these fields.

Kindly reach out incase of further queries.

Thanks.