Querying on Multiple Attributes in Amazon DynamoDB

1
votes

I am moving my database from a sql database to Dynamodb. I currently have a table with those values:

  • tenantId (PartitionKey)
  • resourceId (RangeKey)
  • type
  • role
  • name

I have the following query at the moment:

  • get all the resources belonging to a tenant ten that has type t, role r and name contains n. Where type role name may be null values, so in that case those are not used as filters.

Using filters it is possible to make this query in dynamodb, but reading the following article https://aws.amazon.com/blogs/database/querying-on-multiple-attributes-in-amazon-dynamodb/ I realized it may be an expensive query as dynamodb is retrieving those data and then filtering server side. That page suggests to create a GSI with the following value:

  • tenantId-type-role-name

With this index I can easily filter for ten t r n but in case I just have to filter for tenantId type name how should I query the GSI to get all the records that have tenant ten type t, and name contains n but have no restrictions on role (contains statement seems only to be supported on filters).

I am wondering if I need to create a GSI for each combination, something like:

  • tenantId-type
  • tenantId-role
  • tenantId-name
  • tenantId-type-role
  • ...

Thanks in advance for your help

1
amazon-dynamodb

1 Answers

0
votes

Before you build GSIs to make your querying simpler. Think about storing your data in a different format.

For example how many resources do you expect per tenant? Could you store your data as such:

{
    tenant: 123, //(partition)
    resources: [ 
      { type: 'type1', role: 'role1', name: 'somename1'},
      { type: 'type2', role: 'role2', name: 'somename2'},
      { type: 'type3', role: 'role3', name: 'somename3'}
    ]
}

In the format above your read times will be rapid and scale. You can then filter your contains logic in code. Your dynamodb records can be 400kb in size, so you could probably store several thousands resources in the above format per record.

Also note each GSI has its own read/write unit usage that is used up when you insert into the table. If you do the GSI approach and write a lot to that table you'll have a surprisingly high write usage.