IMHO, RDS is a credible replacement for on-instance MySQL process. Leave AWS to figure out the scaling, patching, master slave replication across AZ. And no code change necessary. If I were writing a database connector for Joomla, I'd probably favour DynamoDB or SimpleDB (no upfront cost for database instances).
I think what's truly holding Joomla back is the lack of S3 support for filesystem requirements. For example, I can't run an autoscaling group of EC2 instances behind a load-balancer without a supporting Gluster / NFS setup. Or else an admin could find that a picture he added is only accessible some times, i.e. when the page is served from the instance he uploaded to.
Depending on how you feel scalability is, RDS is not necessarily in its entirety a solution to solve your scalability problem. It solve the fundamental database management, redundancy problem. It also simplified replication for administrators. With read replicas, it is possible for distributed read, but writing is still very much required on the master. Depending on the necessity in data consistency, application may require modification to design or consistency requirement when dealing with replication lag.
I wouldn't exactly call DynamoDB or SimpleDB a simple fix to scalable design. Mainly certain operations in Joomla may require transaction for some components. I don't remember seeing transactional requirement in common Joomla CMS operations yet. However some of the external components may. RDBMS normalized data storage allows only 1 copy of data to be updated when referenced across multiple records. While for document based storage solution, there is still a need to employ application based solution to ensure consistent data update, or using EMR(aka Map-Reduce technique) to efficiently update duplicated records in scalable manner.
Joomla is not exactly an Enterprise CMS so to speak. Having no S3 or object based storage solution is not a setback for CMS. For highly scalable ECMS design, it goes far more than just having which kind of storage. It's about the architecture that Joomla has that inhibit scalable design.
One of the ECMS product I have came across is scalable in architecture, yet doesn't require exceptional storage solution. In this design, it has publishing, versioning design into it's core concept. Having numerous frontend that are serving contents, and have one backend for editorial operations. Another frontend for preview purpose. Data consistency is achieved through versioning control and distributed updating of data from the backend to all the frontends. These frontends can be deployed all over the world and function without the backend to be online. Each of them have their own data storage using a RDBMS solution.
First of all, for CMS, the main bulk of load is normally serving content. Getting content from the public is much less. Of course, there are some web use cases where public submission is high. But that doesn't require everything to be perform inside the CMS. It can be always externally performed in separate solution that are designed for high load receiving data.
For your reason towards requiring a clustered filesystem or a consolidated filesystem to store uploaded data, this is totally up to your decision. You could also choose to have the file data written into the database and replicated across numerous instances, or you could also choose to have it written into the filesystem and having rsync to sync across other systems.
Obviously if you are looking for simple solution, it seems clustered filesystem is a good taste. I agree it is definitely easier to choose this approach and cope with the complexity in managing a clustered filesystem. But nothing is stopping you from shifting the consistency responsibility into the application layer design.
Ultimately it is a design choice. My point there are numerous approach to solve scalability issue. Sometimes the whole idea is knowing which is sacrifice and make up in different manner. Consistency, Availabilty, or Partition tolerance ?
YOU ARE 
