QLDB - query - Getting all documents which were inserted after some document of given UUID

Question

Hi. I try to build simple transaction system with transactionRequest registry. I store all transactionRequests in separate table. Some other module processes this transactions in batch. Batch process registers all id's of transactionRequest that has been processed. In each subsequent runs, I need to query for all new transactionRequests, registered (inserted) after last processed one (I know id of that request). Currently I query ledger for metadata of transactionRequest with given ID and use `metadata.txTime` timestamp to query all transactionRequests registered after.
That's my approach to get all newest documents starting from some timestamp or documentId but, to be honest, I don't like it.  I've red in documentation that it's not good approach to query with *inequality *conditions like:
`SELECT data.* FROM _ql_committed_transaction_requests WHERE metadata.txTime > 'some_timestamp'`

Is there some better solution for that? How can I query for all documents that appeared after some document?

Answer

Yes, queries with with inequality conditions are not recommended because they [don't scale well ](https://docs.aws.amazon.com/qldb/latest/developerguide/working.optimize.html#working.optimize.avoid) when the amount of data in table grows. There are currently two workarounds to overcome this problem:

1. Depending on your application architecture, you may consider using [Streaming feature of Amazon QLDB](https://docs.aws.amazon.com/qldb/latest/developerguide/streams.html) to process transactionRegistry records once they created or updated in the table. Steaming sends journal updates to Amazon Kinesis, which can trigger AWS Lambda function to filter out the document and call API of another component or just store it somewhere else for further processing.
2. Instead of an id, you may capture the timestamp of the last processed transactionRequest record. Then the batch process can trigger [Export Journal feature](https://docs.aws.amazon.com/qldb/latest/developerguide/export-journal.html) to export Amazon QLDB journal to Amazon S3 starting from that timestamp and then process exported files.

QLDB - query - Getting all documents which were inserted after some document of given UUID

Relevant content