I am trying to design a posts table, where a user can react to the post with any emoji. I need to keep a count of each unique emoji that is used.
If I use a separate Counter table, for each post I receive, I have to make a query for the counts. So pulling 12 messages means 1 query for the messages, + 12 for the counts if I use a separate table.
Is there another more common approach to this? I can't bake the counter column directly into the posts table because the emoji/reaction's would be random.
I am not sure what you meant by "pulling 12 messages means 1 query for the messages, + 12 for the counts". If you can really read these 12 messages in one query, it means you have some sort of partition key or something that allows you to SELECT all 12 messages together. If that's the case, you can have the same partition key in the counter table as well.
If you want to use just one table, or using materialized views, you shouldn't be using "counters" and can instead use a regular integer column which you can increment using lightweight transactions (LWT). This will make the read very efficient (you just read the post record, and it includes the reaction counter in them) but the more infrequent reactions will be less efficient.
For a relatively few very popular posts that will receive a huge amount of interactions, these LWT transactions can be expensive and you can perhaps consider an alternative, perhaps even an additional counter column (i.e., have both an integer column and a counter on a separate table, and when the regular column reaches 1000, you will also read from the separate counter column, and increment it - not the regular column). However, arguably even for a very popular post it is read much more frequently than it is interacted with - imagine the post being likes a million times - but read 10 million times - so even LWT transactions might be fine as they are still the minority of the activity of the database.