The best way to move a row of data to another shard?
This question says it all.
Example. I am planning to outline a database table. The table contains customer orders that are marked as "active", "made" and "deleted". I also have three shards, one for each flag.
As I understand it, the line should be moved to the right shard when the flag is changed.
I'm right? What's the best way to do this? Can triggers be used?
I was thinking of moving the line immediately, but only at the end of the day / week / month, but then it is undefined, in which the shard builds lines with a specific flag and the search should always be done on all shards.
EDIT: Some clarification:
In general, I have to choose a criterion to decide which shard the string is in. In this case, I want it to be the flag described above, because this is the most natural way to cheat such data. (In my opinion) There are only a limited number of active orders that are accessed very often. There are a large number of out-of-the-box orders that are rarely accessed, and there are a huge number of rows of data that are almost never available.
If I want a specific row of data to exist now, I don't have to search for all the shards. If a user wants to download an active order, I already know which database I should look at.
Now the flag, which is my critical criterion, is changing and I want to know how best to handle this case. If I were to simply store the record in my original database, eventually all the data would accumulate in one table.
source to share
Mooring usually refers to separating them in different databases on different servers. Oracle can do what you want using a feature called partitioned tables.
If you use triggers (after / before_update / insert) it will move immediately, other methods will cause the first shard (active) to have different data types until they are cleared.
I also suggest doing it by date (eg a monthly job that moves everything that is inactive and older than a month to another Archive database).
I would like to ask you to revisit this if you are doing it for performance reasons (unless you have terabytes of data in this table). Please tell us why you want to fine, and we will all think about ways to solve your problem.
source to share
In my opinion, keeping the entire active recording in a single shard might not be a good idea. In this sharding strategy, all IO will be done on a single instance of the database, leaving all others highly reliable.
An alternative shaping strategy could be to distribute the newly created rows among the shards using some sort of hash function. This will allow
- Fast string search
- Distribute IO across all instances of shards.
- You don't need to move data from one shard to another (unless you want to increase the number of shards).
source to share