Boost Query Speed from 20 s to 0.5 s with MQ‑Driven Elasticsearch Separation (Java)

In a system where a massive table (tens of millions of rows) was joined with dozens of sub‑tables containing billions of rows, each query took about 20 seconds, even after adding indexes and SQL tweaks.

The proposed solution is query separation : when writing to the primary database, also publish the same data to a message queue (RabbitMQ). A separate consumer reads the messages and writes them into Elasticsearch, so subsequent reads query Elasticsearch instead of the heavy relational table.

Producer example (Java, RabbitMQ) :

import com.rabbitmq.client.ConnectionFactory;
import com.rabbitmq.client.Connection;
import com.rabbitmq.client.Channel;

public class MessageSender {
    private static final String QUEUE_NAME = "data_queue";

    public void send(String message) throws Exception {
        ConnectionFactory factory = new ConnectionFactory();
        factory.setHost("localhost");
        try (Connection connection = factory.newConnection();
             Channel channel = connection.createChannel()) {
            channel.queueDeclare(QUEUE_NAME, false, false, false, null);
            channel.basicPublish("", QUEUE_NAME, null, message.getBytes());
            System.out.println("[x] Sent '" + message + "'");
        }
    }
}

The send method is called during normal DB writes to push the same payload into the queue.

Consumer example (Java, Elasticsearch High‑Level REST client) :

import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.action.index.IndexRequest;
import org.elasticsearch.action.index.IndexResponse;
import org.elasticsearch.common.xcontent.XContentType;

public class EsDataWriter {
    private RestHighLevelClient client;

    public EsDataWriter(RestHighLevelClient client) {
        this.client = client;
    }

    public void writeToEs(String indexName, String jsonData) throws Exception {
        IndexRequest indexRequest = new IndexRequest(indexName);
        indexRequest.source(jsonData, XContentType.JSON);
        IndexResponse indexResponse = client.index(indexRequest, RequestOptions.DEFAULT);
        System.out.println("Data written to ES with id: " + indexResponse.getId());
    }
}

The consumer reads messages from the queue and calls writeToEs to store the JSON document in Elasticsearch, enabling fast read‑only queries.

To avoid stale reads before data is indexed, a boolean column es_synced can be added to the relational table. After the consumer successfully writes to ES, it updates this flag. Query logic first checks the flag; if true, it reads from ES, otherwise it falls back to the primary table or retries after a short delay.

For bulk migration of historical data, a script can paginate through the existing rows, publish each row to the same RabbitMQ queue, and let the consumer populate Elasticsearch, effectively back‑filling the search index.

Limitations include the added write latency caused by the extra MQ step and the need to handle eventual consistency. Nevertheless, for most read‑heavy scenarios with massive tables, this pattern dramatically reduces query latency.