Streaming Consumer Duplicates and Poison Records After Scaling Out

What happened. Adding consumers triggers a group rebalance. During rebalance, partition ownership moves; records that were fetched but not yet committed on the old owner are redelivered to the new owner. Because the code commits once per 500-record batch, the exposure window is a whole batch, and the pod restart discarded whatever had been processed but not committed. OCI Streaming provides at-least-once delivery for a consumer group with committed offsets - it does not deduplicate for you. Duplicates after a scale-out are therefore expected behaviour, not a defect.

Second-order cause of the long lag. Once ORA-00001 fired, the retry logic retried the entire batch. Retrying the whole batch re-inserts the records that had already succeeded, regenerating the same error - a livelock. Combined with a consumer that holds the partition while retrying, lag on the affected partitions grew monotonically.

Judgement. The naive "commit after processing" is not wrong; treating a batch as an atomic unit when the sink is not atomic is wrong. Commit granularity must match the unit of work the sink can make idempotent, and the batch must not be the retry unit.

Make the sink idempotent rather than trying to prevent redelivery.

1. Replace INSERT with an upsert keyed on event_id (MERGE INTO ... WHEN NOT MATCHED THEN INSERT), or INSERT ... with a duplicate-key handler that treats ORA-00001 for the same event_id as success. The primary key already gives you the dedupe anchor - use it as the correctness mechanism, not as an error to suppress blindly.
2. Keep an offset/event-id watermark per partition if you need stronger guarantees (e.g. an processed_events(partition, offset) table written in the same transaction), so replays are provably no-ops.
3. Shrink the commit and retry unit: commit after each successfully applied sub-batch (e.g. 50 records) or per record for high-value events. This bounds duplicates after a crash from 500 records to the sub-batch size, at the cost of more commits.
4. On failure, retry only the failing sub-batch with bounded exponential backoff and jitter; after N attempts, route the record to a dead-letter stream/table and advance. Never let one record hold a partition indefinitely.
5. Rebalance handling: register a ConsumerRebalanceListener that stops fetching, finishes or abandons the in-flight sub-batch, and commits the last fully applied offset before partitions are revoked.

Trade-off to state explicitly. Per-record commits maximise dedupe safety but add round trips; the right point is the largest sub-batch your sink can apply atomically. Also note the partition count is effectively fixed once keys are hashed - increasing 5 to 8 remaps customerId to partitions and changes ordering guarantees per customer, so scale consumers first and repartition only deliberately.

The 1.4 MB record. OCI Streaming has a per-message size limit of 1 MB, so this record can never be published; the producer's retry loop is pure waste. Two fixes:

• Claim check (preferred): write the payload to Object Storage, publish an event containing the object name, size and checksum, and have consumers fetch it. This keeps messages small and preserves the stream as an ordering/index channel.
• Chunking: split into ordered chunks with (event_id, part_no, part_count) and reassemble in the consumer. This works but adds reassembly state, timeout and partial-chunk cleanup - only worth it if consumers must not call Object Storage.

In both cases, validate payload size in the producer before publishing and make MessageTooLarge a non-retryable, alertable error with the offending business key logged. Retrying a deterministic rejection forever is the actual bug.

The malformed record. It is poison: it will fail on every delivery. Classify errors as transient (network, DB contention, throttling - retry with backoff) versus deterministic (deserialization, schema violation, business rule - do not retry). For deterministic failures, publish the original payload plus failure metadata to a dead-letter stream after one or two attempts and commit past it. The dead-letter consumer can be a human queue or an automated repair job.

Guardrails. Alert on consumer lag per partition, on dead-letter rate, and on MessageTooLarge producer errors. A schema change that increases payload size should be caught by producer-side validation and contract tests, not by a three-hour partition stall in production.