We are running into a problem when querying and reporting performance is degrading due to having huge number of records on org of some specific custom objects.
Basically in our use case, we are not interested in old records, e.g. from previous years, but at the same time we cannot just remove them - due to some regulatory rules this information should be kept somewhere for a long period of time. So we've decided to try to use Big Objects for archival purposes.
There is no problem with moving old records of some specific custom object into corresponding Big Object. But the challenge for us here is that we need to archive objects which are in master-detail relationship. And unfortunately system does not support BigObject-to-BigObject lookup relationship. To make it tougher, child records also have bunch of attachments, which also should be archived.
So the question here would be - how could we archive such master-detail hierarchy of objects? Should we try to pack them into single big object with some sort of json serialiazation of child records and long text are fields to save this? Or is it better to create two separate big objects, but without configuring any relationships between them? And also how to archive attachments then, since big objects do not support them?
Any suggestions/ideas would be appreciated.