How to handle a change in denormalized data

Question

What is the best approach for updating an un-indexed regular column (not a primary key related) throughout the tables containing it as a duplicate ?

i.e the user posts something and that post is duplicated in many tables for fast retrieval. But when that post changes (with an edit) it needs to be updated throughout the database, in all tables that contain that post (in tables that have different and unknown primary keys).

Solutions I'm thinking of:

1. Have a mapper table to track down the primary keys in all those tables, but it seems to lead to tables explosion (post is not the only property that might change).
2. Use Solr to do the mapping, but I fear I would be using it for the wrong purpose.

Any enlightenments would be appreciated.

EDIT (fictional schema).

What if the post changes? or even the user's display_name?

CREATE TABLE users (
    id uuid,

    display_name text,

    PRIMARY KEY ((id))
);

CREATE TABLE posts (
    id uuid,

    post text,
    poster_id uuid,
    poster_display_name text
    tags set<text>,
    statistics map<int, bigint>,

    PRIMARY KEY ((id))
);

CREATE TABLE posts_by_user (
    user_id uuid,
    created timeuuid,

    post text,
    post_id uuid,
    tags set<text>,
    statistics map<int, bigint>,

    PRIMARY KEY ((id), created)
);

Answer 1

It depends on the frequency of the updates. For instance, if users only update their names infrequently (a handful of time per user account), then it may be ok to use a secondary index. Just know that using a 2i is a scatter gather, so you'll see performance issues if it's a common operation. In those cases, you'll want to use a materialized view (either the ones in 3.0 or manage it yourself) to be able to get the list of all the posts for a given user, then update the user's display name.

I recommend doing this in a background job, and giving the user a message like "it may take [some unit of time] for the change in your name to be reflected everywhere".