GAE DataStore vs Google Cloud SQL for Enterprise M

Question

I am building an application that is an enterprise management system using gae. I have built several applications using gae and the datastore, but never one that will require a high volume of users entering transactions along with the need for administrative and management reporting. My biggest fear is that when I need to create cross-tab and other detailed reports (or business intelligence reporting and data manipulation) I will be facing a mountain of problems with gae's datastore querying and data pull limits. Is it really just architectural preference or are there quantitative concerns here?

In the past I have built systems using C++/c#/Java against an Oracle/MySql/MSSql (with a caching layer sprinkled in for some added performance on complex or frequently accessed db results).

I keep reading that we are to throw away the old mentality of relational data and move to the new world of the big McHashTable in the sky... but new isnt always better... Any insight or experience on the above would be helpful.

Answer 1

From the Cloud SQL FAQ:

Should I use Google Cloud SQL or the App Engine Datastore?

This depends on the requirements of the application. Datastore provides NoSQL key-value > storage that is highly scalable, but does not support the complex queries offered by a SQL database. Cloud SQL supports complex queries and ACID transactions, but this means the database acts as a ‘fixed pipe’ and performance is less scalable. Many applications use both types of storage.

If you need a lot of writes (~XXX per/s) to db entity w/ distributed keys, that's where the Google App Engine datastore really shine.

If you need support for complex and random user crafted queries, that's where Google Cloud SQL is more convenient.

Answer 2

What is scare me more in GAE datastore is index number limitation. For example if you need search by some field or sorting - you need +1 index. Totally you can have 200 indexes. If you have entity with 10 searchable fields and you can sort by any field - there will be about 100 combunations. So you need 100 indexes. I have developed few small projects for gae - and this is success stories. But when big one come - this is not for gae.

About cache - you can do it with gae, but they distributed cache works very slow. I prefer to create private single instance of permanent backend with RESTfull API that holds cached values in memory. Frontend instances call this API to get/set values.

Maybe it is posible to build complex system with gae, but this will be a set of small applications/services.