Create a buffer DB inbetween the client and the database server that kept common objects in memory (like the whole page and comments section) allowing fast edits (and appends) and only syncing it all to the DB every X seconds or after the object has fallen off the front page and things have slowed down for it.
A incrementing counter would be needed as we would need to sync stuff before it has a DB id? Or we could get a database ID on first save, but still use the buffer DB until things died back down.
Redis could be the sync store since multiple servers could read-write to it in an ATOMIC / ACID way without problems. You cannot ever prevent race conditions (two people making changes to a comment); only race conditions that clober previous updates (two people saving a comment at the same time resulting in a lost comment).
Each element of the object which needs CRUD support would need to be a new HASH with the ID stored in a SET (except for the root object). This way we can work each object, and all it's sub-objects multiple clients without clobering other elements. Each comment could be updated without the chance of losing sub-comments. Same would be true if we were tracking "votes" on each comment; the votes would be a separate SET. (We could handle hierarchical comments using a SORTED SET where the value was the parent comment ID + new id, not sure how to handle it in the RDBMS though)
After each append to the object (i.e. a new comment) then we would increase the TTL on the object. Actually, we don't want the object to disapear from the cache so we would need a saparate sorted-set storing the id -> last_modified of each object so that after the last_modfied got to a certain point we could pull the whole object out of the buffer DB, save it back to MySQL, and purge the buffer DB to make room for new items. - https://redis.io/commands/zadd
Each client will always check the buffer DB before fetching the records from the RDBMS. If the buffer doesn't have the object it wants, it could increment a counter for that root object with a TTL. If the counter got high enough (an old post getting active again?) the buffer could pull it back out of the database and we could use the buffer DB until things died back down. This part needs more thought as it adds complexity for a much smaller use-case (old posts) which probably isn't worth it.
This would require a continous single-process which keep checking the buffer for old items and setting a lock on the cache (which the clients would check) and then pulled everything out and stored it in the RDBMS.
When an item is old enough to expire, then the process would:
- Fetch the object (i.e. post) and sub-objects (i.e. comments)
- Store in the RDBMS
- mark the cache item invalid (so clients should switch to the DB for all future reads and writes), wait X seconds (for any clients to finish writing to the cache)
- Re-fetch the object looking for changes
- update the database if found
- Purge the cache (redis) of this object and all keys
Most of the time 4 should show 0 changes since the object is no longer trending anymore.