Amazon RDS and Amazon Redshift are both packaged database services delivered in a PaaS model.

RDS is available for a number of different DBMS (Oracle, SQL Server, etc), and provides a specific set of features for each type of database. If you are happy with the RDS feature set then this could be for you. If you want more features of a given DBMS than is provided by RDS then you need to install your DBMS on a EC2 instance.

If you are new to AWS and are coming from a traditional data centre environment, then starting with the IaaS model is less of a learning curve than going directly to PaaS. With IaaS Amazon takes care of the racking and stacking and connecting the network cables, you are responsible for the operating system onwards.

My organisation moved all its production servers to AWS last year using the IaaS model and are pleased with the result, both in improved manageability and cost reduction. We are now planning our first Redshift exploitation as part of reworking our BI environment.

Redshift is a column-orientated database with a high degree of in-memory data storage. This can give much faster response to SELECT queries than a row-orientated database, but with current technology Redshift cannot match the INSERT performance of the best row-orientated databases. Redshift does have a fast bulk load capability, and part of designing a Redshift database is working out how you can get your data into Redshift using the fast load rather than using INSERT. In general, Redshift can be easier to exploit for BI workloads than for OLTP workloads, but I do know that some organisations are using Redshift for both workload types.

If you have a major project where you are considering using AWS, then the best advice I can give you is to contact AWS and arrange some (free) pre-sales support to showcase AWS capabilities to both technical and management staff. You should also arrange similar showcases for the major alternative vendors (Rackspace and Microsoft Azure).