Smaller DB setups rarely have the ops/DBA support required to do manual failover. I think having an as-consistent-as-feasible, automatic failover is something of a default expectation for databases these days, at any size.
You need a larger team to do automatic failover because getting it right is a massive PITA. Either that or pay someone to do it right for you, e.g. RDS, managed solutions.
Manual failover is often a lot safer, automatic systems have a nasty habit of not doing what you expect them to and trashing your database / losing data.
I should have clarified that I meant small in the context of less scaled out and more vertically scaled like the traditional rdbms running on big iron.
Manual failover is often a lot safer, automatic systems have a nasty habit of not doing what you expect them to and trashing your database / losing data.