30 Jan 2012

Optimizing App Engine datastore reads

Under the new pricing scheme, Google App Engine has suddenly become very expensive for even moderately active apps. For the free quota, you are only allowed 50k datastore reads (that is 50k entities, not 50k queries).

To take an example, I run a todo app on GAE that needs to query the database for last 7 day trends. So if a user has 10 tasks, the app retrieves 10x7 = 70 db entities.

  query = db.GqlQuery ('SELECT * FROM Records WHERE date > DATETIME (:1)', lastweek)

This design will run through the free quota very rapidly. At 7 tasks per user, this app will only get 1k pageviews per day for free.

Remarkably, no other quota comes close to expiry. If only this db read quota was taken care of, the app will comfortably go back to the free quota limits.

So I decided to tweak the design to drastically reduce the db reads per pageview. Instead of one entity per task record, I consolidated all records into one single master entry using JSON.

  data = [{'task':'Walk the dog', 'records':[1,0,0,1,0,0,0]}, {'task':'Water garden', 'records':[0,0,0,1,1,1,1]}]

  json.dumps (data)

With this design, a single pageview will generate just 1 db read, as opposed to 70-100 earlier.

You should note that the TextProperty can be a maximum of 1Mb, so if you need more space, consider using the Blobstore.