For a use-case, my team is using DynamoDB to query a GSI. With increasing number of rows returned, the query latency increases.
Our code looks something like this (Java) (pretty normal)
Map<String, Condition> keyConditions = new HashMap<>();
keyConditions.put(indexHashKeyName, new Condition()
.withAttributeValueList(Collections.singletonList(new AttributeValue(identifier.toString())))
.withComparisonOperator(ComparisonOperator.EQ));
QueryRequest queryRequest = new QueryRequest();
queryRequest.setTableName(tableName);
queryRequest.setIndexName(indexName);
queryRequest.setKeyConditions(keyConditions);
queryRequest.withAttributesToGet(attributesRequired);
QueryResult result = dynamoDB.query(queryRequest);
Our DynamoDB table has about 4-5 attributes of type String. We use AWS KMS for encryption-at-rest, and all 4-5 attributes are present in the GSI.
We see p99 latencies on the GSI as typically 30-40ms (milliseconds) for retrieving ~30 rows of data.
Is there a good way to reduce this latency (apart from caching)? Caching is not a viable solution for the pattern of client traffic our service receives.
We were looking into a couple of options:-
Create a DynamoDB new table which is indexed on the GSI-hash-key, with all data stored as a JSON blob (or compressed blob). This would reduce latency since the Query would now become a GET. But this would get difficult once items grow beyond the 400KB attribute size limit.
Create a new DynamoDB table which is indexed on the GSI-has-key, and has a range key of 1,2,3... and everytime a blob of data exceeds 400KB, create a new range item, and do multiple GETs to retrieve multiple rows of JSON blobs. This would increase latency once too many range keys start getting created for a given hash key.
Using ElasticSearch to do the above (presumably ElasticSearch has less limitations of size of a document), but we're not sure whether latencies would be similar or worse than DynamoDB query latencies.
Is there any other database solution AWS provides that would help for our use-cases?
Note:- Our EC2 hosts are in the same region as our DynamoDB table. The latency of 30-40ms is purely for dynamoDB query.