Fulltext support for rowstore tables

(Just reposting this from the Slack channel as suggested by Nikita.)

When is fulltext support for rowstore tables projected? Is it on the roadmap?


@Alex Gunnarson columnstore tables are getting a lot more powerful. We will have secondary indexes in 7.0 so it may work for you. What’s your use case ?

Alex Gunnarson

Exciting @nikita! So it’s more of a theoretical question for us at this time. We’re building an EAVT (entity/attribute/value/“time” — i.e. temporally-based transaction ID) store on top of MemSQL and we were wanting to keep everything in memory for now for speed and simplicity. We’re using one table for each built-in datatype that the “V” in EAVT can be — 1) tinyints/booleans, 2) smallints, 3) mediumints, 4) ints, 5) longs, 6) floats, 7) references/UUIDs, 8)opaque binary data, 9) strings, and 10) fulltext-indexed strings — rather than e.g. having one varbinary EAVT table. This makes it so can make proper comparisons/aggregations among values. Currently, the fulltext-indexed-strings EAVT table is a columnstore and this seems to be working really well.
The fact that columnstore tables are getting secondary indexes will be really helpful to us! This is because at some point we’d like to test a hypothesis. We’re thinking that rather than storing all rows in memory, including non-current states of entities, we might want to offload historical data to columnstore tables. This would significantly reduce the size of the in-memory tables, which would make it cheaper for us, and the reduced table size would speed up queries on current entity-states.


Fulltext for row store columns is not in the short term roadmap right now. Currently most customers using full text have large enough amounts of data that it isn’t practical to store it all in memory which is why we chose to support it for columnstore only to start.

The current recommended pattern for cases where the primary table is a row store table is to have a single column in a columnstore table for the data you want to full text index and use the same shard key for the columnstore table and the main row store table (so you get local joins). You can simplify the queries against the two tables by creating a view over both of them.

Given your pattern of a separate table for each data type, using a columnstore table for that type is the best thing you can do.

Rick Negrin
VP of Product Management

1 Like

That makes sense. Thanks for your input @rick!