AWS MySQL RDS vs AWS DynamoDB

I've been using MySQL for a fair while now and I'm comfortable with its structure & SQL Queries etc.

Currently building a new system in AWS and I've been looking at DynamoDB. Currently I only know a little about it.

Is one better then the other?

What are the advantage of DynamoDB?

what is the transition like from MySQL queries etc to this flat style DB?

Really DynamoDB and MySQL are apples and oranges. DynamoDB is a NoSQL storage layer while MySQL is used for relational storage. You should pick what to use based on the actual needs of your application. In fact, some applications might be well served by using both.

If, for example, you are storing data that does not lend itself well to a relational schema (tree structures, schema-less JSON representations, etc.) that can be looked up against a single key or a key/range combination then DynamoDB (or some other NoSQL store) would likely be your best bet.

If you have a well-defined schema for your data that can fit well in a relational structure and you need the flexibility to query the data in a number of different ways (adding indexes as necessary of course), then RDS might be a better solution.

The main benefit for using DynamoDB as a NoSQL store is that you get guaranteed read/write throughput at whatever level you require without having to worry about managing a clustered data store. So if your application requires 1000 reads/writes per second, you can just provision your DynamoDB table for that level of throughput and not really have to worry about the underlying infrastructure.

RDS has much of the same benefit of not having to worry about the infrastructure itself, however if you end up needing to do a significant number of writes to the point where the largest instance size will no longer keep up, you are kind of left without options (you can scale horizontally for reads using read replicas).

Updated note: DynamoDb does now support global secondary indexing, so you do now have the capability to perform optimized lookups on data fields other than the hash or combination of hash and range keys.

We have just migrated all of our DynamoDB tables to RDS MySQL.

While using DynamoDB for specific tasks may make sense, building a new system on top of DynamoDB is really a bad idea. Best laid plans etc., you always need that extra flexibility from your DB.

Here are our reasons we moved from DynamoDB:

1. Indexing - Changing or adding keys on-the-fly is impossible without creating a new table.
2. Queries - Querying data is extremely limited. Especially if you want to query non-indexed data. Joins are of course impossible so you have to manage complex data relations on your code/cache layer.
3. Backup - Such a tedious backup procedure is a disappointing surprise compared to the slick backup of RDS
4. GUI - bad UX, limited search, no fun.
5. Speed - Response time is problematic compared to RDS. You find yourself building elaborate caching mechanism to compensate for it in places you would have settled for RDS's internal caching.
6. Data Integrity - While the concept of fluid data structure sounds nice to begin with, some of your data is better "set in stone". Strong typing is a blessing when a little bug tries to destroy your database. With DynamoDB anything is possible and indeed anything that can go wrong does.

We now use DynamoDB as a backup for some systems and I'm sure we'll use it in the future for specific, well defined tasks. It's not a bad DB, it's just not the DB to serve 100% of your core system.

As far as advantages go, I'd say Scalability and Durability. It scales incredibly and transparently and it's (sort of) always up. These are really great features, but they do not compensate in any way for the downside aspects.

You can read AWS explanation about it here.

In short, if you have mainly Lookup queries (and not Join queries), DynamoDB (and other NoSQL DB) is better. If you need to handle a lot of data, you will be limited when using MySQL (and other RDBMS).

You can't reuse your MySQL queries nor your data schema, but if you spend the effort to learn NoSQL, you will add an important tool to your tool box. There are many cases where DynamoDB is giving the simplest solution.

When using DynamoDB you should also know that the items/records in DynamoDB are limited to 400KB (See DynamoDB Limits). For many use cases this will not work. So DynamoDB will be good for few things but not all. Same goes for many of the other NoSQL database.