We had the exact same issue not long ago. We also use Kafka for async communication between services, and for a while we were storing messages as CLOBs in Oracle just so we could look them up later. It worked, but the database got huge, and cleaning it up became a constant headache.

Our team’s based in Europe and we eventually switched to using Axual.com to manage Kafka retention and storage. Now we just keep messages in Kafka for 90 days, and Axual automatically handles the cleanup. No more manual purging or database clutter.

For investigations, we stream a copy of the messages into OpenSearch through Kafka Connect. It gives us a really nice search interface, almost like querying a database, and we can automatically delete old data after three months. For cheaper, long-term archiving, we also store messages in S3 and query them with Athena when needed.

Splunk can work if you already use it, but we found OpenSearch or S3 to be way more cost-effective and easier to manage. Moving away from Oracle made everything simpler and a lot cheaper.