This is a membership-based site. Each account is capable of:
Making posts (unlimited, no restriction on frequency)
Liking, sharing and commenting (on) the other posts
The goal is to provide the smallest possible resolution for account stats. Instead of bundling
likes into groups of 15-minute intervals (or something similar), we are storing every like as it happens.
Right now, it is a single table tracking likes, for all accounts. Something like...
Table: users (Accounts) user_id (pk) ... remaining data (name, login, etc) ---- Table: posts post_id (pk) user_id (fk) ... remaining data (time, content, etc) ---- Table: post_likes id (ai, pk) post_id (fk) user_id (fk) action timestamp
user_id is that of the user liking the post, and
action is either +1 or -1, for like or dislike, respectively.
Note: A dislike is only undoing a like (likes for a post cannot sum to less than zero). There are valid reasons for tracking dislikes, opposed to just deleting the original like entry
The DB is currently MySQL, and cannot be changed at this moment. All tables are
InnoDB to facilitate row locking.
Playing a little game of Best Case Scenario, let's assume we've got ourselves 10M users.
-> 'users' table has 10M entries
And say, after a year, the average user has made 250 posts
-> 'posts' table has 2.5B entries (250 * 10M)
And say that each post has an average of 15 like-operations (likes and dislikes).
-> 'post_likes' table has 37.5B entries (250 * 10M * 15)
When a user views their metrics, the query would be something like:
Current likes count: SELECT SUM(action) as cur_likes FROM post_likes WHERE post_id=? Comprehensive likes history: SELECT (action, timestamp) FROM post_likes WHERE post_id=? ORDER BY timestamp ACS
Billions of entries in a single table seems like a lot to me. Now, I'm no database guru, but a couple things jump out at me as issues:
What happens when the table exceeds the allotted memory?
According to docs, for InnoDB tables (after extending the
The maximum tablespace size is 64TB.
But is that PER table? For all tables? I've been looking into InnoDB General Tablespaces, but I'm still unclear on the matter. I understand that such high numbers are a pipe-dream at this point, but I'd rather be safe than sorry.
Surely the performance of operations on the table suffer with such high numbers of entries?
Assuming the previous point is moot (it is unlikely that we will ever reach or exceed 64TB of data), there is a lot of inconsistency between posts regarding table-size vs performance. Some will tell you that there is no bound to table size, and others say just a few million is already detrimental to performance.
My specialty is not DB administration, and I cannot comfortably say either way on this matter.
Originally, I had a unique table for each post:
Table: post_(post_id)_likes user_id action timestamp
... and was quickly told, "not do do that" - nothing else, so I cannot be sure what the solution would have been. Similar situation for a table tracking all posts likes for a single user/account:
Table: posts_(user_id)_likes user_id post_id action timestamp
I will always know which account or post to retrieve metrics for, so it made sense to me to create a table specifically for that post or account. Then I know exactly where to look, and the number of records to look through would pale in comparison to the billions described above.
There is a similar post here, but the solutions mimic the gigantic tables I'm worried about.
The stuff I'm doing right now does not have to be perfect, and can always be changed at a later date, but I'd like to get it set-up in a way that makes sense, while maintaining performance and keeping it easy enough to alter/fix further down the road. Even if every like was stored within a single table, we'd likely have plenty of time to the accommodate growth. But if just a few million is enough to slow things down, then I need to look at preventative solutions before digging myself into a hole.
Thanks for making it this far!