Parallelize the ingester by turning each worker into a full ingestion pipeline

[gustavobtflores]

Today multiple workers parse/validate files, but all inserts are funneled through a single process, which becomes a bottleneck. This limits throughput and leaves workers idle during busy ingestion cycles.

We should evaluate shifting to a model where each worker performs the full pipeline, parse, validate, and insert, using its own database connection. This removes the single-process bottleneck and allows inserts to happen in parallel.

[gustavobtflores]

Another point of observation is that, in the current implementation, the flush condition is based on a global item count (e.g. flushing once we exceed 10k total items). In practice, this means that when one table (notably tests) accumulates ~9k+ items, we end up flushing very small batches for other tables.

This results in flushes like:

[2025-12-12 13:36:35] bulk_create issues: n=21 in 0.004s
[2025-12-12 13:36:35] bulk_create checkouts: n=4 in 0.003s
[2025-12-12 13:37:14] bulk_create issues: n=10 in 0.003s
[2025-12-12 13:37:14] bulk_create checkouts: n=5 in 0.003s

While these inserts are usually fast, flushing very small batches increases the frequency of insert operations. Under high concurrency, this can amplify lock contention (e.g. on indexes or tables), causing the process to block even when inserting only a handful of rows.

A more robust approach would be to base flushing primarily on per-table thresholds, with a time-based flush as a safety mechanism. This avoids premature flushes for low-volume tables while still ensuring that infrequently updated tables do not remain buffered indefinitely.

[gustavobtflores]

Another thing to keep in mind when having this setup with high concurrency is that we will probably hit some locks if we keep data being inserted as it is right now. We have a lot of upserts in the tables, so we will have a lot of transactions trying to update the same rows sometimes, what causes some lock contention:

txA is inserting 10000 items and needs to upsert test_id abc
txB is inserting 10000 items and also needs to upsert test_id abc

This would probably cause deadlocks depending on the order that each transaction acquired the lock, but we already mitigated this by sorting the data before inserting. But, we would yet have lock contention because txB needs to wait for txA resolution.

One of the possible solutions for this is to have data being distributed by id to the workers in a way that repeated ids will never be sent to different workers, so it is basically impossible to one transaction lock the other because each worker will be inserting different ids. There's many ways to achieve that, but a simple one is to transform the ids into integers and distribute data to the workers using modulo (%)