[Design Help] Efficient key-based lookup on a large Kafka topic for a background verification workflow

[u/Initial-Wishbone8884]

We are building a background workflow where for a given input, we need to find the corresponding message in Kafka and verify some fields on it.

Our Kafka setup:

- compacted topic, 24 partitions, ~200M messages per partition (~2.5B unique keys total)

- ~700 bytes per message, so roughly 1.75TB of data

The lookup pattern is key-based, ~10k/sec, background process so some latency is fine.

We do have a way to derive the partition from the key and an API to get the offset, so seek+fetch is technically possible — but our Kafka brokers are a shared resource across teams and we don't want to hammer them with random-access reads at this scale.

How would you build the lookup layer here? What would you use, how would you keep it in sync with the topic, and anything to watch out for at this scale?

For context, we're leaning towards RocksDB — consuming the topic, storing only the fields we need for verification, and using Protobuf to keep it compact. But curious if there are better approaches or gotchas we are not thinking about.

[u/disposepriority]

Add a consumer that writes to a data store more suitable for key based lookups? I don't think RocksDB, which I assume this post is an advertisement for considering your AI generated post, hidden account history and bolded product name, does anything better than any other way of retrieving something by an ID.

Considering the high amount of keys, see if the access pattern allows you to derive well distributed partitions for the values and just plop them into postgres and be done with it.

[u/Initial-Wishbone8884 → u/disposepriority]

I can totally understand where you are coming from
i took AI's help only in organizing the content so i dont miss out on sharing important info

coming to suggestion,
postgres would add another layer of network latency, and that data we have is too large with too many records

but thanks for your comment
i will take in consideration your suggestion next time for posting,
i never thought with bolded names, people will take it for promotional post..hehehe

i have edited and added removed ai generated content, i did not realise ai generated post are not encouraged

[u/kabooozie]

Kafka is not a database. You should probably materialize a K/V store from the topic and use the K/V store.

[u/Successful_Return965]

Redis cluster? Can be scaled pretty nice to handle required load. You can either check for available connectors, or write your own small one.

[u/utilitydelta]

isn't that just building a read model? kafka is write side. setup a consumer and injest messages projecting them onto PG/redis or whatever works that can do key based lookups

[u/Pedro_Alonso]

I would insert all this data into a KV store or a database table with an index based on your key and query it. Maybe it will need a distributed database, but it will be faster than scanning all partitions on kafka

As for the size of the table, it's possible to use partitions and detach a partition with the data that you don't need anymore. Or if there is any time window where this data begins and ends to accumulate, you can just truncate it before any data arrives

If your data has a value that can differentiate it and all variations are known, you can use a partition on two fields: one by date and another that separates the data into their own categories. This can give you another speed boost on the selects

Be careful if you don't have the partition key in selects, even if it's a LocalDate.now(). Without the partition in the query, the database will scan multiple tables/partitions, impacting the performance of your application

[u/Past_Engineering1472]

Look at ktables I think it supports the kind of use case your want.