Support having multiple head nodes about pg_shard HOT 1 OPEN

Hey @jberkus,

We have been thinking of ways to properly support multiple master nodes for a while. We're looking at this issue in two dimensions.

The first dimension involves a side-effect of how we currently handle Insert failures. Today, if we fail to write to a shard, we have to update its corresponding metadata. When the node that routes the write request and the one that holds the metadata are the same, that's easy. When they are different, we need to think more. On this one, we'll investigate the approach BDR takes more, and see if we can guarantee propagating changes to metadata.

Of course, if the previous requirement ends up being too burdensome, we can also look into relaxing it.

The second dimension relates more to usability. We have a small amount of metadata that changes infrequently. Do we want to have one node, multiple nodes, or all nodes to have a copy of that metadata? A few rough architectural ideas there are (more to brainstorm on):

• Can we model each metadata change as a separate event? Each node then reconciles all metadata change events that it has access to, and reconstructs its metadata. This approach has the drawback that end users may observe "eventually consistent" results.
• We remove the "shard state" field from authoritative metadata. If a shard is replicated to node A, B, and C, we then say these three nodes form a replica group. Each replica group chooses its own leader and follower through a distributed consensus algorithm (Raft), and ensures that writes coming to a replica group are correctly propagated. This approach has the drawback that it requires a fair amount of coding.
• Each worker node "leases" metadata from the master node. When the worker receives a write, it tries to forward it to the correct replicas. If that fails, it asks the master node to update the metadata. This approach has the drawback that there is a window where the first time the master node's metadata needs to be updated, if the worker loses the connection to the master, the metadata goes out of sync. We may introduce pings in here to dramatically reduce this window, but it's still theoretically not safe.

Just a few brainstorming ideas over the weekend for now.

from pg_shard.