Support of ip range as primary key

ScyllaDB
, ,
1

Hi, Community.
I’m currently working on storing a table with IP ranges as the partition key (ip_from and ip_to declared as inet type). Since IP ranges may overlap, I’ve added an additional unique clustering key row_num, to ensure that I can always select the same row from a set of ranges. The create table statement looks like this:

create table my_table ( ip_from inet, ip_to inet, row_num bigint, …, primary key ((ip_from, ip_to), row_num) )

I’m executing queries to find a row with an IP range that includes a provided IP address:

select * from my_table where ip_from <= ? and ip_to >= ? limit 1 allow filtering

In some cases, I manage to find the row I’m looking for (for example, any IP in the range 2607:fb90:e200:8000:: to 2607:fb90:e200:80ff:ffff:ffff:ffff:ffff), but in other cases, nothing is found (for example, any IP in the range 2603:2000:: to 2603:203f:ffff:ffff:ffff:ffff:ffff:ffff).
Could anyone explain this behavior?
(I understand that this might not be the most appropriate task to solve using Scylla, but perhaps there’s a way to tackle it somehow)

UPD: I found workaround hack but I don’t think that it is best solution. I converted all ip ranges to CIDR format and used 2 parts of it as 2 primary keys. For example, ip range 2607:fb90:e200:8000:: - 2607:fb90:e200:80ff:ffff:ffff:ffff:ffff can be presented as 2607:fb90:e200:8000::/56 - 2607:fb90:e200:8000:: as first key and 56 as second key. It is good, if all ranges has the same ip prefix in CIDR, but in my case this is not the true. I have to iterate with passed ip to find needed ip_prefix from 128 to 1 for IPv6 and from 32 to 1 for IPv4.

It looks like the best solution is to use vector search mechanism like Cassandra supports now, but as I know Scylla don’t support it yet.

2

Can you post an example that fails?

3

There is no fail or error. Just nothing found while I know that the record exists. If I execute query like:

select * from my_table where ip_from = ? and ip_to = ?

and pass 2607:fb90:e200:8000:: for ip_from and 2607:fb90:e200:80ff:ffff:ffff:ffff:ffff for ip_to then I will find the record with this range. But any other query that is looking for range by specific ip, that this range includes, like:

select * from my_table where ip_from <= ? and ip_to >= ? limit 1 allow filtering

will return nothing as soon as the table size becomes more than 100 000 records.

4

Please post a script that creates such a table, populates it, and runs the failing query. Best in a new issue.