Putting a load balancer up in front of a few servers isn’t going to do anything to their database
Yes it is. Suddenly your database exists in more than one location, which is extremely difficult to do with reasonable performance.
load balancing doest automatically mean “do something stupid like spin up 100 app servers when we normally use 3”
Going from 3 to 100 is trivial. Going from one to any number greater than one is the challenge.
All you’ve described is a need for a db proxy in the off chance that Lemmy code has horrible access patterns for db transactions.
Define "horrible"?
When Lemmy, or any server side software is running on a single server, you generally upgrade the hardware before moving to multiple servers (because upgrading is cheaper). When that stops working, and you need to move to another server, it's possible everything in the database that matters (possibly the entire database) will be in L4 cache in the CPU - not even in RAM a lot of it will be in the CPU.
When you move to multiple servers, suddenly a lot of frequent database operations are on another server, which you can only reach over a network connection. Even the fastest network connection is dog slow compared to L4 cache and it doesn't really matter how well written your code is, if you haven't done extensive testing in production with real world users (and actively malicious bots) placing your systems under high load, you will have to make substantial changes to deal with a database that is suddenly hundreds of millions of times slower.
The database might still be able to handle the same number of queries per second, but each individual query will take a lot longer, which will have unpredictable results.
The other problem is you need to make sure all of your servers have the same content. Being part of the Fediverse though, Lemmy probably already has a pretty good architecture for that.