Can I emulate hash-sharded indices in region partitioned tables?

[JulianRooze]

It's currently not possible to use hash-sharded indices on partitioned tables (results in an error sharded indexes don't support partitioning) but this is something we'd like to use as we want to page through a large table (that is partitioned by region) by a sequential value (a timestamp) and creating a normal index is not recommended on sequential data as it would result in write hotspots as all new data falls into the same range.

I was wondering if I could emulate the behavior of hash-sharded indices by adding a new column to the table (bucket) and giving each row a value for bucket by choosing a number from 1-N (where N is something low like 8). That choice would be either based on randomness, a hash or most likely just a simple round-robin fashion. That bucket column would then be part of the index key and would be filtered on in all select queries (this would be the main downside of this approach).

Am I correct in thinking that this would eliminate write hotspots to a single range?

To clarify, what I'm proposing:

create index on orders (region, bucket, timestamp) partition by list (region) ( ... );

insert into orders (region, customer, bucket, timestamp) values (1, 768, 1, '2022-01-01 12:00'), (1, 768, 2, '2022-01-01 12:01')

select * from orders where region = 'eu' and customer = 768 and bucket between 1 and 8
order by timestamp desc

[ajwerner]

We should definitely do something about this. We're working on making hash sharded indexes no longer experimental. Supporting partitioning seems important. It will require allowing the shard column to follow after the region column in the primary index.

@chengxiong-ruan can you see if we've got an issue for this, add it to the epic, and potentially give it some thought?

[JulianRooze]

Thank you for your reply.

Ah, so it's not a fundamental limitation that hash-sharded indices don't support partitioning, but more of a "hasn't been implemented yet"? Good to know.

That said, until it is implemented, is my approach a good substitute?

[ajwerner]

I was wondering if I could emulate the behavior of hash-sharded indices by adding a new column to the table (bucket) and giving each row a value for bucket by choosing a number from 1-N (where N is something low like 8). That choice would be either based on randomness, a hash or most likely just a simple round-robin fashion. That bucket column would then be part of the index key and would be filtered on in all select queries (this would be the main downside of this approach).

You can do much better than that. You can effectively create the shard column and check constraint just like we do internally. Hash sharded indexes are just syntactic sugar. Here's an example of what will work using the exact same machinery as hash sharded indexes:

CREATE TABLE orders (region STRING, id INT8, ts TIMESTAMP, PRIMARY KEY (region, id))
    PARTITION BY LIST (region)
        (PARTITION us VALUES IN ('us'), PARTITION eu VALUES IN ('eu'));

ALTER TABLE orders
    ADD COLUMN
        shard INT4 AS (fnv32(crdb_internal.datums_to_bytes(region, id)) % 8) VIRTUAL NOT VISIBLE;

ALTER TABLE orders
    ADD CONSTRAINT orders_values CHECK (shard IN (0, 1, 2, 3, 4, 5, 6, 7));
    
CREATE INDEX
    ON orders (region, shard, ts)
    PARTITION BY LIST (region)
        (PARTITION us VALUES IN ('us'), PARTITION eu VALUES IN ('eu'));

It's worth noting that we have new primitives which might make this simpler. Not sure if you've seen the REGIONAL BY ROW hotness.

[JulianRooze]

Awesome, thanks! Is the column virtual so that it doesn't take up any space in the primary key, only in the secondary indices in which you use it? Will a select * from orders trigger a compute of that column or does the not visible prevent that?

[chengxiong-ruan]

I'll create an issue for this. This seems a bit tricky with implicit cases with REGIONAL BY ROW, will think about it.

[chengxiong-ruan]

@JulianRooze (1) you're right that virtual computed column are not stored in PK but can be part of secondary index key, you may take a look at the doc here, but note that virtual column will be allowed in primary key in later release. (2) you're also correct that not visible hides columns from select *.