I have a Big Object where the index is built as String, String, Datetime.
I'm now faced with truncating records so I don't create more records than my current limit, 1 million without additional purchased capacity. (https://developer.salesforce.com/docs/atlas.en-us.bigobjects.meta/bigobjects/big_object_considerations.htm)
My goal is to limit records to 'X' number of days in age so I want to discover all of the records older than my threshold and delete them.
The specific problem I'm running into now is how do I query for only the records that I want to delete? I can't just filter on the Datetime field because Big Object sync queries must filter on indexed fields in order and I can't just skip to the last field in the index. Trying to filter on the CreatedDate returns
FATAL_ERROR System.QueryException: field 'CreatedDate' can not be filtered in a query call and I don't pay for additional capacity so I don't have access to Async SOQL to determine if that would enable a solution. (How to do I enable asyncquery for developer salesforce org)
Are there any direct options to query only old records so I can delete them or some other truncation capability? Without this I have a hard time justifying using Big Objects because there seems to be no reasonable way to limit their growth over time.
I'm trying to avoid the worst-case scenario, query all of the records in batch apex (which also can't use Query Locator, Does QueryLocator work with Big Objects?) and having the Apex parse the timestamp and decide if they should be deleted.