DynamoDB.SQL 2.0.0 is out!

Hi everyone, happy new year!

I was really glad to find a couple of days to work on some of my open source projects and put together a new version of DynamoDB.SQL which brings it inline with the latest version of the .Net AWSSDK amongst other things. You can download and install it from Nuget here.


Breaking Changes

There are two breaking changes:

  1. DynamoDB v1 is no longer supported as they have been deprecated from the AWSSDK, which means the v1 syntax (which uses the special keywords @hashkey and @rangekey to refer to the table’s hash and range keys) is also deprecated and you should use the v2 syntax going forward.
  2. The clumsy and frankly unnecessary DynamoDbV2.SQL.Execution namespace is gone! Instead, the extension methods for AmazonDynamoDBClient and DynamoDBContext now exist in the same namespaces so you no longer have to import another namespace just to use the extension methods.


Bug Fixes

Selecting specific attributes in a Scan now works, please see respective C# and F# examples.

The old InvalidQuery and InvalidScan exceptions (which didn’t play so well with C# since the error message was not very useful at all) have been replaced with C# friendly InvalidQueryException and InvalidScanException types exposes the underlying parsing errors in the error messages.


Global Secondary Index

AWS announced Global Secondary Index support on December 12th, 2013, and it’s supported in DynamoDB.SQL via the existing INDEX query option, for example:


However, global indexes work very differently to local secondary indexes, for starters they require their own throughput rather than use the existing throughput for the table (for more details refer to its documentation).

Also, it does not support consistent reads, so when querying against the index you must add the NoConsistentRead option in your query otherwise you’ll receive an error from the DynamoDB service.

Lastly, when you create the global secondary index you have to choose which attributes are projected into the index and unlike local secondary index, attributes that have not been projected into the index will not be retrieved from the table at extra read units cost, you will receive an error from the service instead. Please refer to the guidelines page for Global Secondary Index.



I’ve also revamped the README document to make it more detailed and useful and added a bunch more examples for both C# and F#, hope you like the new layout.



Adrian Cockcroft on Dystopia-as-a-Service

And finally, a good summation of the talk here.

Amazon ELB – Some caveats around health check pings

We recently found out about an interesting, undocumented behaviour of Amazon’s Elastic Load Balancing (ELB) service – that health check pings are performed by each and every instance running your ELB service at every health check interval.

Intro to ELB

But first, let me fill in some background information for readers who are not familiar with the various services that are part of the AWS ecosystem.

ELB is a managed load balancing service which automatically scales up and down based on traffic. You can really easily setup periodic health check pings against your EC2 instances to ensure that requests are only routed to availability zones and instances that are deemed healthy based on the results of the pings.

In addition, you can use ELB’s health checks in conjunction with Amazon’s Auto Scaling service to ensure that instances that repeatedly fails healthy checks with a fresh, new instance.

Generally speaking, the ELB health check should ping an endpoint on your web service (and not the default IIS page…) so that a successful ping can at least inform you that your service is still running. However, given the number of external services our game servers typically depend on (distributed cache cluster, other AWS services, Facebook, etc.) we made the decision to make our ping handlers do more extensive checks to make sure that it can still communicate with those services (which might not be the case if the instance is experiencing networking/hardware failures).

Some ELB caveats

However, we noticed that at peak times, our cluster of Couchbase nodes are hit with a thousand pings at exactly the same time from our 100+ game servers at every ELB health check interval and the number of hits just didn’t make sense to us! Working with the AWS support team, who were very helpful and shared the ELB logs with us which revealed that 8 healthy check pings were issued against each game server at each interval.

It turns out that, each instance that runs your ELB service (the number of instances varies depending on the current load) will ping each of your instances behind the load balancer once per health check interval at exactly the same time.

A further caveat being that for an instance to be considered unhealthy it needs to fail the required number of consecutive pings from an individual ELB instance’s perspective. Which means, it’s possible for your instance to be considered unhealthy from the ELB’s point of view without it having failed the required number of consecutive pings from the instance’s perspective.

To help us visualize this problem, suppose there are currently 4 instances running the ELB service for your environment, labelled ELB inst 1-4 below. Let’s assume that the instance behind the ELB always receive pings from the ELB instances in the same order, from ELB inst 1 to 4. So from our instance’s point of view, it receives pings from ELB inst 1, then ELB inst 2, then ELB inst 3 and so on.

Each of these instances will ping your instances once per health check interval. In the last 2 intervals, the instance failed to respond in a timely fashion to the ping by ELB inst 3, but responded successfully to all other pings. So from our instance’s point of view it has failed 2 out of 8 pings, but not consecutively, however, from ELB inst 3’s perspective the instance has failed 2 consecutive pings and should therefore be considered as unhealthy and stop receiving requests until it passes the required number of consecutive pings.


Since the implementation details of the ELB is abstracted away from us (and rightly so!) it’s difficult for us to test what happens when this happens – whether or not all other ELB instances will straight away stop routing traffic to that instance; or if it’ll only impact the routing choices made by ELB inst 3.

From an implementation point of view, I can understand why it was implemented this way, with simplicity being the likely answer and it does cover all but the most unlikely of events. However, from the end-user’s point of view of a service that’s essentially a black-box, the promised (or at the very least the expected) behaviour of the service is different from what happens in reality, albeit subtly, and that some implicit assumptions were made about what we will be doing in the ping handler.

The behaviours we expected from the ELB were:

  • ELB will ping our servers at every health check interval
  • ELB will mark instance as unhealthy if it fails x number of consecutive health checks

What actually happens is:

  • ELB will ping our servers numerous times at every health check interval depending on the number of ELB instances
  • ELB will mark instance as unhealthy if it fails x number of consecutive health checks by a particular ELB instance


If there are expensive health checks that you would like to perform on your service but you still like to use the ELB health check mechanism to stop traffic from being routed to bad instances and have the Auto Scaling service replace them instead. One simple workaround would be to perform the expensive operations in a timer event which you can control, and let your ping handler simply respond with HTTP 200 or 500 status code depending on the result of the last internal health check.

S3 – Masterclass Webinar slides

I stumbled across a set of slides with a rather comprehensive overview of the different aspects of S3, worthwhile reading for anyone who works with Amazon S3 regularly. Enjoy!

DynamoDB.SQL 1.2.1 – now supports Local Secondary Index

A couple of weeks earlier, Amazon announced support for Local Secondary Indexes (LSI) for DynamoDB. You can now perform fast, efficient queries against DynamoDB tables using attributes that are not part of the existing Hash and Range key model without resorting to the use of scans.

As a result to the new feature the DynamoDB query API has also gone through some changes, as did the AWSSDK for .Net. From version onwards, there’s a new top level namespace Amazon.DynamoDBv2 which contains a mirror set of types to those under the original Amazon.DynamoDB namespace, albeit with minor changes to support the new LSI feature.

Query syntax change

Due to the changes in the underlying AWSSDK, I have decided to make some changes to the query syntax supported by DynamoDB.SQL too – namely, to remove the need for the special keywords @HashKey and @RangeKey and instead allow you to use the attributes names for your hash and range keys.

For example, given a table like the one outlined in the DynamoDB docs:


To write a query to find all subjects starting with “a” in the “S3” forum, you would previously write:

SELECT * FROM Thread WHERE @HashKey = \”S3\” AND @RangeKey BEGINS WITH \”a\”

In the new version of DynamoDB.SQL, you would write the following instead:

SELECT * FROM Thread WHERE ForumName = \”S3\” AND Subject BEGINS WITH \”a\”

This syntax change only applies to the extension methods for the AmazonDynamoDBClient and DynamoDBContext types under the new Amazon.DynamoDBv2 namespace. The extension methods themselves are only available under a new namespace DynamoDbV2.SQL.Execution in the DynamoDb.SQL.dll.

The syntax for scans on the other hand, has remained the same in both the new and the old API.

Local Secondary Index support

You can specify that a query should use a Local Secondary Index (LSI) by using the Index option in the WITH clause.

For example, given a Thread table and an index LastPostIndex, as outlined in the DynamoDB docs:


To find all the posts in the “S3” forum since the 1st May 2013, you can write the query as following:

SELECT * FROM Thread WHERE ForumName = \”S3\” AND LastPostDateTime >= \”2013-05-01\”

WITH (Index(LastPostIndex, true))

The WITH clause is where you specify optional query parameters, such as NoConsistentRead, and PageSize. (please refer to the Getting Started guide on available query parameters).

The Index option allows you to specify the name of the index, in this case that’s “LastPostIndex”, and a boolean flag to specify whether or not all attributes should be returned.

For the above query, because we’re asking for all attributes to be sent back with *, and that attributes such as Replies are not projected into the index, they will be fetched (automatically performed by DynamoDB) from the main table at additional consumed capacity units.


On the other hand, if you want only the projected attributes back from the index, we can tweak the query slightly:

SELECT * FROM Thread WHERE ForumName = \”S3\” AND LastPostDateTime >= \”2013-05-01\”

WITH (Index(LastPostIndex, false))

In which case, only ForumName, LastPostDateTime and Subject will be returned by the query.


Finally, if you are interested in a specific set of attributes, you can also specify them in the SELECT clause:

SELECT ForumName, Subject FROM Thread

WHERE ForumName = \”S3\” AND LastPostDateTime >= \“2013-05-01\”

WITH     (Index(LastPostIndex, false))


Some reference links:

AWS announces Local Secondary Index support for DynamoDB

DynamoDB docs on Local Secondary Indexes

DynamoDB docs on Query

Querying with an Index attribute in DynamoDB.SQL

Getting started with DynamoDB.SQL