Investigate storing payload outside of RDBMS

[punktilious]

The FHIR resource payload is currently stored as a (potentially) large compressed object in the FHIR RDBMS (Db2, Derby, PostgreSQL).

Investigate options for storing the payload separately using another service.

[lmsurpre]

High estimate due to testing concerns around edge cases...especially cases where COS gets out-of-sync due to network or other issues.

[punktilious]

There are two main challenges that must be addressed for this to be successful:

1. Payloads written to an external system must be removed if the (RDBMS) transaction is rolled back. This removal does not have to be synchronous with the rollback, it just needs to be performed in a reasonable time in order not to leave orphaned PHI records.
2. To maintain reasonable response times when processing ingestion-heavy bundles, the extra service calls (e.g. save to Cassandra) should be done asynchronously

Current thinking is to use a Kafka service to publish changes and use a consumer thread to perform reconciliation with the RDBMS. Any records read from Kafka but not in the RDBMS must be related to a failed transaction and so the payload content can be deleted (e.g. from Cassandra).

More to come.

[lmsurpre]

Be sure that the new config prop fhirServer/persistence/payload gets documented (or ensure this is included in follow-up issue) before closing this one

[lmsurpre]

Be sure that the new config prop fhirServer/persistence/payload gets documented (or ensure this is included in follow-up issue) before closing this one

I moved this part to #2899

[lmsurpre]

We exercised the touchstone basic server tests against the latest in main and, after doing #2906 to appease touchstone, it passed with flying colors.
Further analysis can be made as part of our performance testing for the upcoming 4.10 release.