Load balancing across read replicas is usually handled by your connection bouncer, say pgBouncer/pgPool/etc though you may also do some amount of more complex both L3 and L7 balancing if you get really big.

Sharding is usually a matter of actually splitting the masters. There are many techniques for achieving this. If you want the database to do all the work you will probably want to use something like Citus for PostgreSQL or Vitess for MySQL.

You can also build bespoke topologies using PostgreSQL logical or MySQL binlog replication.

Failing that you can do application level sharding if you don't want the database doing anything fancy for you and manage each shard as an independent database cluster.

By the time you actually need to do this you will be able to afford one of these options. :)

In the meantime you will save a ton of CPU, storage and development time vs a "NoSQL" store as databases like PostgreSQL are inherently more efficient for all but the simplest of KV access patterns.