Comments - Can we extend the max length of the data types varchar and varbinary to 65535?

We already have improvements to support the TEXT and BLOB types but these are currently lower priority: https://jira.mariadb.org/browse/MCOL-267 https://jira.mariadb.org/browse/MCOL-356

Does this satisfy your use case, if so please vote on it to help prioritization?

Thanks for your feedback. Could you set high priority for these two data types? You know, it will impact on our decision to use the MariaDB ColumnStore in our environment.

In addition, the column store should not add limitation to the number of columns or fields for table definition. Right now, the number of columns is limited by the size of 65535. You know, we may create big tables to support data analytics. Please help fix it in the following releases. Thank you very much.

It would be helpful to understand your use case more, generally speaking column stores are more typically used for structured data. Are you storing structured or semi structured data (eg. json etc) in your text and blob columns currently?

Also remember that it is possible to utilize a mixed storage engine approach so depending on your use case it may be ok to leave that text / blob data in innodb and use cross engine join support.

I believe the big problem of not having TEXT/BLOBs data types is that the amount of columns supported by the engine columnstore is very limited, around 400 columns if most of them are structured data. Although that is reasonable in OLTP environments, it is not acceptable in OLAP .

I think my use case is very common. We have a 1500 columns table where each row stores all the data of a single transaction (IP addresses, messages sent/received, subscriber data,...). Many of the columns of the table are TINYTEXT. In order to create the table in the columnstore engine, we changed all those columns to varchar(256), but it wasn't possible because we kept getting the error:

"Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535"

The only way to create the table was to reduce the amount of columns to less than 350. We can't loose that many columns in production environments.

That's the reason why I believe MCOL-267 and MCOL-356 should have a higher priority.

Thanks for the feedback. The row size (and max number of columns of 4096) is a limit enforced by the mariadb server derived from the original mysql code base.

I understand, but let's agree than the TEXT and BLOB columns are no limited by the row limit size of 65536 bytes and to have a limit of 300 max columns is very different than 4096

Also the best way for you and the community to help us in prioritizing improvements and bugs is to use the jira 'vote for this issue' function on the improvements to indicate this would be something that would be beneficial to your use case.

We only use the ColumnStore for structured data. But we need create more columns without the limitation of the total column number * length less than 65535.

Our case is described as follows: We have a very large table required to support many years' transactions. We don't want to partition the table according to specific columns. So we plan to keep 6 months' records in row store and migrate older history data to column store. However, we are blocked by the data type issues, such as the length limitation of varchar and varbinary, not supporting text and blob.

We don't want to keep the text or blob data in row store for the count of records is very large, such as 1 billion. In addition the join speed is not acceptable for complex queries. Actually, we only need get the value of text or blob after queries.

By the way, can you provide such a feature to support mixed storage for the same table? In other words, the system can migrate older data to column store automatically. Right now, I have to migrate the old records from row store to column store online. Please give me ideas for my case. Thank you very much.

Ok. Thanks for your feedback.