A high-throughput consumer group processes millions of events per hour and checks a shared relational 'processed_messages' table before applying each one. What architectural problems does this single shared table cause at that scale, and what alternative designs would you consider?

## Why one shared table caps the whole consumer group A single shared relational `processed_messages` table, checked by every consumer instance before applying a message, works fine at modest volume but becomes a **structural bottleneck** once throughput reaches millions of events per hour. Every message, regardless of which partition, shard, or logical stream it belongs to, funnels through the same table and typically the same unique index on `message_id`, so the table's write path becomes a shared serialization point: - concurrent inserts from many consumer instances contend for index-page locks, - and the read-before-write check adds a network round trip to a single database for every single message, which caps the whole consumer group's throughput at whatever that one table can sustain, however many parallel consumer instances you add. It's the classic mistake of introducing a distributed system's worth of parallel consumers and then routing all of them through one non-partitioned, non-scaled piece of shared state. ## Growth that never stops The second structural problem is unbounded growth: a table that records every processed message and is never pruned grows linearly with total message volume forever, which at millions-per-hour translates to billions of rows within months. As it grows: - index lookup latency degrades as the index grows past what fits comfortably in memory/cache, - storage costs climb, - and routine operations like backups or schema migrations on that table become progressively more painful, all for a table whose actual useful lifespan per entry is only as long as the maximum plausible redelivery window, typically minutes to low hours, not months or years. ## Alternative one: a low-latency store with native expiry The first alternative design is to move the dedup store to a purpose-built low-latency key-value system with native TTL support, **Redis** being the most common choice, where a set-if-not-exists operation on the message key serves the same claim role the unique-constraint INSERT played before, but at far higher throughput than a general relational database, and where entries can be given a TTL matching the redelivery window, say twenty-four hours, so old entries expire automatically instead of needing an explicit cleanup job. The trade-off is durability and consistency semantics: Redis, without careful configuration, can lose recently written keys on a crash/failover, which reopens a small window where a duplicate could slip through right after an outage — an acceptable trade for many systems, but a real one to weigh against the relational table's stronger durability guarantees. ## Alternative two: shard along the messages' own partition key The second alternative is to shard the dedup keyspace along the same partition key the messages are already naturally partitioned by. For a Kafka-based system that typically means: - a dedup store, or table, per partition, - or a store keyed such that lookups for messages from one partition never need to touch data from another. Because a partition is already the unit of ordering and single-consumer-at-a-time processing in Kafka's model, colocating the dedup check with the partition eliminates cross-partition contention entirely and lets the dedup store scale linearly with the number of partitions, the same way the rest of the pipeline already does. ## Alternative three: push the guarantee into the sink The third, often more elegant alternative for a subset of use cases is to avoid a general-purpose dedup store altogether and push the exactly-once guarantee down into the specific sink the consumer writes to. - If the downstream write is a naturally idempotent upsert, no dedup store is needed at all. - If the consumer is entirely inside the Kafka ecosystem, consuming from and producing back to Kafka, or consuming and writing via Kafka Connect, Kafka's own exactly-once semantics, idempotent producer plus read-process-write transactions, can supply the guarantee natively without any application-level dedup table, at the cost of being locked into that narrower transactional boundary and only covering writes that go back through Kafka. ## What teams actually run at that scale **A concrete real-world scenario:** a large e-commerce platform's order-event pipeline processing on the order of ten million events per hour would typically not survive on a single Postgres `processed_messages` table checked synchronously by every consumer instance. Teams at that scale commonly: 1. move to a sharded Redis cluster keyed by `(partition, message_id)` with a TTL of a few hours, sized to the platform's own worst-case redelivery lag, 2. explicitly accepting that a Redis failover in the rare case could theoretically let a duplicate through, 3. and mitigating that residual risk by making the terminal database write itself an idempotent upsert as a **second line of defense**, rather than relying on the dedup store as the sole guarantee.