Query custom data from childworkflows

My use case could potentially start thousands or even millions of childworkflows. Each childworkflow will make several heavy activity requests. I would like to store some small custom results information for each workflow such as {objectID:string, error_code:int}.
Next I would need a way to query this information with paging. I am pretty sure that I could setup elastic search to query by ParentWorkflow. There might also be a way to do this with a QueryMethod and the object datastore but I do not want to put a list of 1 million into memory right before only returning a page of 500. I am trying to limit as much overhead as I can.
Does anyone have any other ideas on how to accomplish this?

I am also aware that I should not make a million childworkflows. Can a childworkflow create more childworkflows?

You can add objectID and error_code as custom search attributes. Then you would be able to use them as predicates in your search. The list function is already paginated. So no need to load all of them in memory. See relevant docs.

I am also aware that I should not make a million childworkflows. Can a childworkflow create more childworkflows?

Yes, make a 1k children which of them starting 1k children to get to the million.

Thank you for your reply. I think that the custom search attributes are only supported with elastic search. I am not sure if I want to setup elastic search if I don’t have to; however, I see that this may be the only way

In this case, you can create an index in a different DB and update it using workflow activities.

We are considering that as well. Could you explain why we should avoid more than 1000 childworkflows per parent?

Could you explain why we should avoid more than 1000 childworkflows per parent?

Because each workflow maintains event history which is used for recovery. Very large workflows end up having a very large history which slows things down. The current hard limit of the number of events in a history 50k.