sql: add support to group all unique variants of a tuple in a range

[ddorian]

Like in bigtable, where all key-values with the same ROW-KEY are in the same range. Something specifiable, like an index, that should always keep (col1,col2, etc) unique values in 1 range. This can and will be misused (like ~every feature). This is done so the app-developer can make queries where all the data will be guaranteed in 1 range/node.

This will(?) help with the performance since it will be used in cases where I know the data won't grow alot to be bigger than a range.

Of course, this combination of columns can have only the columns of the primary-key and in the same order.

Makes sense ?

Jira issue: CRDB-6178

[ddorian]

You probably already do this to keep a sql-row (which has many key-values on rocksdb) in 1 range (you should if not ?).

[petermattis]

@ddorian All of the columns for a row are kept within a single range. Please reopen if there is something additional to do here.

[knz]

@petermattis I think the request is that if a k/v table has a non-unique constraint on k, and there are multiple rows with the same value for k, the user should be able to request that all rows with the same k value reside on the same range.

[petermattis]

@ddorian Care to clarify?

[ddorian]

@petermattis
I meant what knz explained.

Example: I'm storing the friends of a user. Primary-key = (user_id, friend_id), and I want all key-values where user_id is the same to reside on 1 range, so I can store/query as efficiently as possible all friends of a user.

Or I'm storing stuff as primary_key=(a,b,c,d,e) and I want all values of (a,b,c) to reside in 1 range since I will ~ALWAYS query for the same a,b,c rows.

Basically certain cases when:

1. the row may be too big if I would store everything in 1 blob/json/array column
2. the rows won't be too big for a range

[petermattis]

@ddorian Thanks for the clarification.

There is danger here in that a user might accidentally create very large ranges which are currently problematic. What should the system do if a range grows to multiple gigabytes because it can't be split? My instinct is that it would be better to figure out how to model the data as a single row and rely on rows being contained on a single range along with the (currently non-existent) row size limits.

And I'm curious how much of a benefit this feature would provide. If your rows are contiguous, they are very likely on the same range. Multi-range operations are certainly slower than single-range operations, but not that much slower and if they are rare for your data set then they'll be lost in the noise.

[ddorian]

@petermattis I remember hypertable returned an error when you tried to write to rows that grew too big.
This can maybe be an "advanced" feature.

I actually prefer the single-row option but there's no json/flatbuffers/blob column that can make modifications and checks. Since most cases are "get friendship, get all friendships, delete friendship, check friendship exists".

[petermattis]

@ddorian At some point we'd like to add support for CQL-style set/map types. Would that satisfy your use case?

[ddorian]

@petermattis
Yes, but:
try to make them as blobs on the low-level (or some other fancy thing), since the current cql version has ~crazy overhead.

[petermattis]

@ddorian Thanks for the pointer. I'll have to read that article more closely and or dive into the CQL implementation. I was imagining each element of a {SET,MAP} would have similar storage overhead to a column. I can't imagine how there would be a 30x storage overhead.

[ddorian]

@petermattis Cassandra goes to great length to increase overhead (ex: repeating full-column-names on every value until recently etc)

I assume in their case it was that much because they probably have a compound primary-key (which in this case all primary-key-columns are merged into 1 "string", while cassandra stores them differently in a sstable explained here: http://blog.librato.com/posts/cassandra-compact-storage and also short values.

I understand you want to implement them like the normal hbase/bigtable/hypertable way (they do column qualifier, you implement something similar on the key). This will be better for big/very-big sets/maps (you don't have to write big blobs on each update) and will suck for small sets/maps.

I think, in your case, why don't you use https://github.com/facebook/rocksdb/wiki/Merge-Operator . This is the same feature-request that I mention in this issue scylladb/scylladb#1321.

Assuming 1 value for a map/set:
The merge operator should allow you to only write to a map/set column for insert/update/delete and merge all the versions of it on read/compaction/merge etc.

Assuming you need to only replicate the "change" instead of the "final value" (slower since values can be big maps/sets/etc).

I think the way you want to implement it (each element as unique kv), can also be done by interleaved tables (even better, since you can also alter the interleaved table, while on a set you only have 1 value for each element) #2972

So if this has to be done, it should be on 1 column-value (for minimum overhead) and for 'normal' overhead you can use interleaved tables.

Makes sense ?

[knz]

@ddorian since you have filed this issue, CockroachDB has started supporting JSON columns. Would this be a suitable alternative?

[ddorian]

json may work in some cases but it's still limited to maximum row size, while this one offers more flexibility.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!