Vertical/Horizontal Fragmentation

Hi everyone,

I need to create a database system with different tables. I decided to use CockroachDB to do that.

I have a table user with several information about my users, I would like to fragment into several shards, this user table based on an attribute of the table (in my case, the region of the user)
For example, in my user table, I have 5 users, 3 are from Europe and 2 are from Asia. In this case, I would like to have 2 fragmentation (and maybe, to store those fragments in different nodes).

How can I fragment a table in this way with CockroachDB?

Cordially,

Edai

Hi Edai!

So you have a couple ways of going about this. As I’m sure you are aware, Cockroach does the sharding for you -> for now, we offer this at the table level. So, you could have a user table that was just for your European users, and then use zone configs to control where those ranges live.

The other option is to manually split your table with our ALTER TABLE… SPLIT AT statement. This isn’t really documented since we typically use it more internally, but it would be a way for your to achieve what you want in terms of breaking up your user tables into separate ranges.

Lastly, we are going to be releasing a feature in our next release that will let you define partitions on a table, but that won’t be coming until a couple months from now.

What do you think about those options?

Thank you so much for this answer ! The first option seems to be more adequate :slight_smile:
I saw on the documentation, about Indexes, do you think it can be another option for that?

Edai, do you mind elaborating on indexes a bit? Do you mean that you want indexes to live on a separate range as well?

Nevermind, I misunderstood what is an index :slight_smile:
Thank you for your answer !

1 Like