I would like to ask you about optimizing reduce operations (e.g. count) on multiple databases.
Let’s say there is a user table that is stored in various countries and sharding is made based on the country field of the entity row. Think this as a situation where sharding and replication can occur so same record may exist in another database. At this point does “coming up with a solution where all the unique keys of entities of this table stored in a central database with maybe where the real record is” sound stupid and implies that I know nothing about the subject or a good way to solve the issue and there are a lot of examples in enterprise applications?
Thank you for your answers.
The short answer is that if you’ve set things up properly, it’s a waste of both space and time to keep a separate central master mapping of which primary keys exist globally.
Your sharding function tells you which shard has is authoritative for a given primary key. A central master list of which rows are currently in the distributed database would be an aggregation across shards of the list of primary keys for which that shard is authoritative. Keeping this central mapping consistent with the sets of keys present on each shard introduces unnecessary overhead, and quickly becomes a bottleneck for all database modifications on any shard globally. All row insertions or deletions would require querying this central master mapping to see if it’s consistent, possibly followed by modifying the central master mapping.
Even with your proposed master mapping of primary keys to shards, you still need to contact each shard in order to perform your reduction. (Of course, you skip any shards that your sharding function tells you don’t contain any rows relevant to your reduction operation.)
If your proposed master list can answer your query without contacting the shards, you probably wouldn’t want to query it anyway, since it’s already a bottleneck for all write operations. You could of course shard this master list of primary keys for better performance, but then it ceases to be the proposed central list of primary keys. This would be better thought of as a second read-only distributed database that contains a mirror of a subset of the columns in your primary distributed database. Now, a second distributed read-only copy of a subset of your data does make sense for some usage patterns. However, how much sense it makes is highly dependent on your usage patterns and does introduce some replication delay between the primary distributed database and the secondary distributed database.
1