Yeah 10 years ago you could support millions of users on a high traffic site on a single box. (This was on postgres in my case.) Today, I'd guess at least a 10x increase due to both software optimizations and increased hardware capabilities, if not significantly more.
Truthfully, unless you're working on some kind of non-transactional problem like analytics, even assuming you will need to shard the data or scale out reads ever due to user activity is borderline irrational unless you have extremely robust projections. The database will be the last domino to fall after you've added sufficient caching and software optimization. It's so far down field for most projects (and the incidental complexity cost so high) that my personal bias is that even having the conversation about such things on most projects isn't even worth the opportunity cost vs talking about something else.
Even then, the first thing to fall over will probably be write heavy analytics-like tables that are usually append only due to index write load. Out of the box, you can often 'solve' this by partitioning the table (instead of sharding.) In modern DBs, this is a simple schema change.
Truthfully, unless you're working on some kind of non-transactional problem like analytics, even assuming you will need to shard the data or scale out reads ever due to user activity is borderline irrational unless you have extremely robust projections. The database will be the last domino to fall after you've added sufficient caching and software optimization. It's so far down field for most projects (and the incidental complexity cost so high) that my personal bias is that even having the conversation about such things on most projects isn't even worth the opportunity cost vs talking about something else.
Even then, the first thing to fall over will probably be write heavy analytics-like tables that are usually append only due to index write load. Out of the box, you can often 'solve' this by partitioning the table (instead of sharding.) In modern DBs, this is a simple schema change.