[GitHub] [shardingsphere] dybizqq opened a new issue, #24999: Suggest: binding / synchronize snowflake ID & timestamp sharding column

["dybizqq (via GitHub)" <gi...@apache.org>]

dybizqq opened a new issue, #24999:
URL: https://github.com/apache/shardingsphere/issues/24999

   ## Binding snowflake ID and create_time column for sharding
   
   ### Scenario
   Currently my table uses create_time for yyyyMM sharding purpose, at the same time, the ID is generated with snowflake, 
   program can infer the timestamp too, but I am not sure whether or not these 2 timestamp-based columns are based on exact same timestamp value because they are not generated by same code.
   
   #### I tried following sharding configurations:
   
   1. sharding with create_time, updating record with ID will generate SQL for all shards, so that I must estimate its create_time and append create_time to the where clause (to make things  correct, when estimated timestamp is on month-begin or month-end, I suggest a wide range, etc, let it scan 2 tables via  ' where id =? and create_time >= '2023-03-31 23:59:59' and create_time < '2023-04-01 00:00:05'), this is better than than scanning all 50+ tables.
   2. sharding with snowflake ID based timestamp (implements my own StandardShardingAlgorithm to infer timestamp), this can be used for tables with ID / Key based insert/update/select only, when query data of a range of dates (filter on create_time) it generates SQL for all shards.
   
   ### Question
   Is it possibly to mix them together like following, so that we can use sharding with either create_time or snowflake ID column:
   
   1. Can we bind snowflake ID with create_time, so that when snowflake generated, create_time also updated to same timestamp?
   2. When snowflake ID used for sharding, can routing algorithm just apply / suggest create_time automatically so that also limit to single shard?
    
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org