Implement tunable batching of queries/inserts

[chadlwilson]

Context / Goal

At time of writing there are no optimizations performed on

• fetch size for data set queries off source/target
• insert batching into recce db for source rows
• update batching into recce db for target rows

After #26 it is likely that we will be in a position to experiment with different strategies from a performance perspective.

Expected Outcome

• If advantageous, add a default query fetchSize which can be overriden at config level, or dataset level
• If advantageous, add a default insert/update batchSize and implementation in the stream processing to allow this via R2DBC
  • transactional handling may be required here, and

Out of Scope

Additional context / implementation notes

[chadlwilson]

Performance with batching implemented

Environment

• on dev machine
• OOTB Postgres containers with no customisation
• docker has 10 cores available
• all DBs inside Docker, Recce inside Docker (local docker image build, no Gradle)

Configuration

batchSize = 1000
batchConcurrency = 5 

"source-only" rows = 50000
"target-only" rows = 50000
"both" mismatched rows = 50000
"all" rows = 150000 (50,000 in source only, 50,0000 in target only, 50,000 in both)

Modifying the datasetId in the below for the big-table-postgres scenario
hey -c 1 -n 10 -t 3600 -m POST -d '{ "datasetId": "all" }' -T 'application/json' http://localhost:8080/runs

Reporting 75th percentile results:

Dataset	Before Batching	After Batching
source rows only	75% in 42s	75% in 8.3s
target rows only	75% in 78s	75% in 16s
both	75% in 112s	75% in 45.5s
all	75% in 192s	75% in 61s

Not done to be looked at later

• fetchSize from the source/tartget DBs seemed to make no effective difference to timings so was not added at this stafe.
  • It is possible that the relevant R2DBC drivers have a different way of interacting with underlying DB, so perhaps this is not so relevant in an R2DBC world where the streaming can offset any roundtrip latency?
  • Alternatively, it is possible this is more relevant with a DB remote to Recce where latency is more of a factor. This should be looked at later when testing in a more representative environment
• batchSize was not specifically tuned to find optimal size since the testing was all done locally. Again, such tuning is better with a real deployment of a DB in a given cloud environment, or something like that. 1000 rows was just a guess. It was a bit faster than 100 rows, but more tuning is necessary for a given "real" environment.
• batchConcurrency was mainly tuned to avoid R2DBC Recce DB connection exhaustion. More testing can/should be done
  • Locally, this tends to push load to the DB during the "target update" stage, with concurrency of 5 leading to 500% CPU usage on the DB container.
  • with parallel recs running (to ensure connection exhaustion can be managed - possibly there needs to be limits on recs that are running in parallel?)
• the slowest part is the "find rows already loaded from source, then update based on target". Since in a "well reconciled" source/tagret combination this should constitute 100% of the rows, it seems focusing on performance here is important. It might be that the Recce DB reconcilation_record design should be re-looked at to avoid actually having to find+update existing rows. Will summarise that idea in another card.