High performance mysql book suggests that for sharding a blog application, one may want to put comments data across 2 shards: first, on the shard of a person posting comment, and on the shard where the post is stored.

So this raises the question how to reliably duplicate this data. Which of the following data duplication options across shards is recommended?

Option 1: Make 2 separate inserts from the PHP script.
Pros: a) Logic is in application layer.
Cons: a) User is held for 2 inserts. b) This logic will need to be duplicated in every client trying to insert similar data.
Conclusion: Seems reasonable.

Option 2: Form federated tables and use some trigger to handle the insert of duplicate.
Pros: a) App layer doesn't need to worry about multiple inserts
Cons: a) Every shard need to have federated connection to every other shard; b) Federation will work on machines in LAN, but what about at 2 different sites. c) what if connection to federated server fails.
Conclusion: Doesn't seem like a sound idea.

Option 3: Messaging such as RabbitMQ
Pros: a) Different clients can insert data at one place, and all subscribers can consume the insert.
Cons: a) Complex; b) may impose overhead in order to host messaging server, and clients; c) not sure how will it work with a look-up service to locate appropriate shards
Conclusion: Not sure

Option 4: your suggestion?

I will greatly appreciate your help.

  • 1
    Do they give an explanation for why you'd want to duplicate the data? Personally, I think that manually duplicating data is an incredibly bad idea.
    – kdgregory
    Feb 11, 2012 at 23:32
  • 1
    If I wanted to support the "see all my comments" functionality, I might create a table USER_COMMENTS that's sharded by user and contains references to the actual comments. You'd pay a penalty for to retrieve the comments from a shard, but it's a much less frequent operation than retrieving the comments for a post. And you're not faced with the problem of how to synchronize data across two shards.
    – kdgregory
    Feb 11, 2012 at 23:32
  • @kdgregory as you predicted, to support the "see all my comments" functionality, you will create user_comments that's sharded by user. So now you have comments at two place. The question is in settings such as this one where you may need to duplicate data across two shards to prevent cross-shard queries, how would you implement it?
    – jeff musk
    Feb 11, 2012 at 23:38
  • I would pay the cost of a cross-shard query. I'll develop the reasons why as an answer.
    – kdgregory
    Feb 11, 2012 at 23:43

1 Answer 1


This question can be restated more generally as "when do I denormalize data to improve performance." And the answer is "when the cost of performing a normalized query exceeds your performance threshold, and the cost of maintaining duplicate data is justified."

Let's start with a traditional relational design for a bank. How do you compute the current account balance? In a fully normalized schema, you would perform a query against the transaction table. However, your users will be checking their balances all the time, and you don't want to pay the cost of that query for every balance inquiry. So you make the decision to add a field to your ACCOUNTS table, in which you store the current balance and update it as part of every transaction.

On the other hand, given the requirement "show customers the total amount they've spent, by payee," you'd probably perform the query against transactions, rather than attempt to denormalize. While there may be some users who check their payment breakdown on a daily basis, most users will never do it. So you don't want to waste disk space and coding time on a denormalized table.

I think the same thing applies to "show me all my comments." Yes, there are some users who will do this all the time (and I may be one of them; I often check the "Activity" tag on SO to pay attention to questions that I've answered or commented on). But it's probably not the majority of your users, so it's probably not that expensive in the grand scheme of things to perform an all-shards query to retrieve the data.

Or maybe it is. In that case, you have to answer the question "who is to be master" (or, without the Alice in Wonderland reference, the "authoritative source"). In the case of the bank account, the transaction table is always the master. If, for some reason, the balance as stored on the account is different from that calculated from transactions, you must update the former from the latter.

In the case of blog comments, I believe that the blog entry is the authoritative source. Which means that, when the request completes, I want that table to be updated, regardless of what happens to the comments-by-user table. And I also want the comments-by-user table to have a reference to the comments-by-entry table, so that I can reconstruct it if the two get out of sync.

How you accomplish this is a trade-off between complexity, response time, and how important it is that you keep the two tables in sync.

As you point out, having triggers between the various shards is silly; the whole reason for sharding is independent database operations. So you can throw it out right away.

Updating both tables at the same time is the approach with the fewest moving parts. Over the long term, it will be the most maintainable. And it will be the easiest to debug if something goes wrong.

But if response time is important, then you might think of some sort of messaging approach: update the comments-by-entry table, and queue a message to update the comments-by-user table. If it takes an hour for that message to be processed -- or if it gets lost in a system crash -- no big deal, you can always recover. By no means should you use a messaging approach to update both tables.

So, bottom line is that there are no clear-cut answers; it's all about tradeoffs. And that's why they pay us.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.