In modern relational databases, what combinations of replication and sharding are possible?

Actually, I'm interested in the following strategy. Is it possible to replicate a small table in order to avoid cross-shard joins whereas a large table is sharded? Like this:

  • Node 1

    • Table orders_male
    • Table genders
  • Node 2

    • Table orders_female
    • Table genders

Both order_male and order_female contain foreign key that references the genders table.

  • 1
    This sounds like a 'do my research for me' question. Replication and shading are common and involve tradeoffs. Care to more narrowly define the question with less artificial example. – danblack Feb 24 '19 at 22:19
  • Since you have not specified a particular RDBMS, you may not get as many answers as you like. The question is in an area where the differences could be significant. – Rick James Mar 4 '19 at 19:07

Db2 for LUW DPF (Data Partitioning Feature) allows that transparently (see CREATE TABLE ... DISTRIBUTE BY REPLICATION in the manual). You can probably roll your own with other popular RDBMSes using a combination of federation/dblinks/linked servers, materialized views and/or engine-specific replication solutions.

So, to answer your questions:

what combinations of replication and sharding are possible?

Too many to list here.

is it possible to replicate a small table in order to avoid cross-shard joins?



Interesting puzzle.

MySQL leaves most of the work to the user when it comes to sharding.

In your tiny example, gender should be identical on both shards, but the burden of maintaining such is left to the DBA.

FOREIGN KEYs are generally not viable in any PARTITIONing or sharding setup. (Your simplified example will probably work.)

The typical shard+repl setup is each shard is composed of several servers. Those servers are configured in some replication (M-S, Galera, Group Replication, etc) for HA and/or read scaling.

It is theoretically possible for gender to be on some master that is separate from all the shards. I have not heard of such, but I don't see why it would not work.

Note. You may need "multi-source" replication, which is a relatively new MySQL/MariaDB feature, especially if each shard is some form of multi-Master. This may lead to a problem if the Master that is the 'slave' to the gender source dies. I can't envision (off the top of my head) how to heal the replication topology without some messy manual effort.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy