Hey. I’m writing a database that contains guilds and channels. Guilds can have multiple channels, but channel IDs are unique and each channel can only belong to a single guild. I want my channel table to be indexable by either channel ID or guild ID, because I will often want to find the guild that a given channel is part of.
If I establish guild ID as the primary key for my table and channel ID as a secondary index, Scylla will handle the creation of a reverse lookup table that maps channel ID → guild ID and then index the ‘channels’ table using its guild ID.
However, I could also create a custom denormalized table that does the same thing (maps channel ID → guild ID). Then, whenever I want to retrieve a channel by channel ID, I’ll query that table, retrieve the guild ID, and then index the original table by guild ID. This is prone to more errors because I will have to manually update the denormalized table whenever a channel is added/removed.
Are there any performance benefits to using a denormalized table vs. the built-in secondary index? I have read that denormalized tables are “more efficient for predefined, specific access patterns” but I don’t see how so. I assume that Scylla’s reverse lookup table and a custom denormalized table will have the same storage overhead.