search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.19k stars 3.82k forks source link

sql: re-home multi-region rows based on read access #83865

Open awoods187 opened 2 years ago

awoods187 commented 2 years ago

We can currently re-home rows based on writes.

However, in some cases, users would like to automatically re-home rows based on reads/access patterns from other regions. We could create a session variable/cluster setting that after N reads from the non-home region, we update the record to move accordingly.

cc @ajstorm @vy-ton

Jira issue: CRDB-17320

ajstorm commented 2 years ago

@awoods187 this issue is something we've been debating for some time, albeit without an accompanying issue. The problem here is that it's not clear when we'd want to rehome, or even, how we'd efficiently collect statistics which would guide such a decision. For example, rehoming after N reads from the non-home region ignores the fact that there could have been thousands of reads from the home region in that same time period. In cases like that, rehoming would be a bad idea.

I was also thinking about this a bit more last night, based on the call we had yesterday, and may have a solution now that could work. I'm going to run it past @nvanbenschoten later today for his thoughts.