To do this, we'll have a simple Follow table using the entire relation with userFollowing as the partition key and userFollowed as the sort key. We can also create a Global Secondary Index (GSI) with the reverse relationship (e.g. partition key of userFollowed and sort key of userFollowing) to allow us to look up all the followers of a given user. This allows us to query for the important pieces: If we want to check if the user is following another user, we query with both the partition key and the sort key (e.g. userFollowing:userFollowed). This is a simple lookup! If we want to get all the users a given user is following, we query with the partition key (e.g. userFollowing). This is a range query. If we want to get all the users who are following a given user, we query the GSI with its partition key (e.g. userFollowed). This is a range query.
We’ll have a simple Follow table that models the relationship. The primary key will be a composite (userFollowing, userFollowed), ensuring uniqueness and allowing efficient lookups of who a given user is following. We’ll also create a secondary index on (userFollowed, userFollowing) to efficiently support the reverse lookup (finding all followers of a given user).
CREATE TABLE Follow ( userFollowing BIGINT NOT NULL, userFollowed BIGINT NOT NULL, followed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (userFollowing, userFollowed), INDEX (or KEY) (userFollowed, userFollowing) -- inlin secondary index );
This gives us the following query patterns: Check if one user follows another → query with both columns in the primary key:
SELECT 1 FROM Follow WHERE userFollowing = ? AND userFollowed = ?;
This is a direct index lookup. Get all users a given user is following → filter by the first part of the primary key:
SELECT userFollowed FROM Follow WHERE userFollowing = ?;
This uses the clustered primary index for a range scan. Get all followers of a given user → filter by the secondary index:
SELECT userFollowing FROM Follow WHERE userFollowed = ?;
This uses the secondary index efficiently.