Supercharging Elasticsearch: Practical Index & Search Optimizations for Billion-Row Queries

Preface

The data platform has evolved through three versions, encountering many common challenges; this article documents the refined solutions, focusing on Elasticsearch (ES) optimization.

Requirement Description

Project Background: In a business system, some tables store over a hundred million rows per day. Data is partitioned by day, but queries are limited to daily ranges and only three months of data can be retained in the DB due to hardware constraints, making sharding costly.

Improvement Goals:

Enable cross‑month queries and support exporting more than one year of historical data.

Achieve second‑level response times for conditional data queries.

Elasticsearch Retrieval Principles

3.1 ES and Lucene Basic Architecture

Understanding component fundamentals is essential for pinpointing bottlenecks. The basic ES architecture is illustrated below:

Key concepts:

Cluster: a group of Nodes.

Node: a service unit within the cluster.

Index: a logical namespace that contains one or more physical shards.

Type: a classification within an index (only one type allowed after ES 6.x).

Document: the basic indexable unit, e.g., a JSON string.

Shards: the smallest working unit that stores a subset of data; each shard is a Lucene instance.

Replicas: shard copies for data safety and query load balancing.

ES relies heavily on Lucene; optimizing data structures often means optimizing Lucene.

Lucene separates indexing (tokenizer, filter, character mapper) from searching (query parser). A Lucene index consists of multiple segments; each segment holds many documents, each document contains fields that are tokenized into terms.

The Luke tool shows that ES adds the _id and _source fields to the Lucene files.

3.2 Lucene Index Implementation

Lucene index files include dictionaries, inverted tables, forward files, and DocValues, as shown:

Random disk reads are costly; .fdt files consume space, while .tim and .doc benefit from SSD storage. Scoring also adds overhead and can be disabled when unnecessary.

About DocValues

Inverted indexes quickly locate document IDs, but sorting, grouping, or aggregating requires fast access to field values. DocValues provide a column‑store structure that maps document IDs to field values, enabling efficient sorting and aggregation. If a field does not need sorting, its DocValues can be disabled to save resources.

3.3 ES Index and Search Sharding

An ES index consists of one or more Lucene indexes, each composed of multiple segments; a segment is the smallest searchable unit.

Shard allocation follows the formula:

shard = hash(routing) % number_of_primary_shards

By default, the routing parameter is the document ID (MurmurHash3). Supplying a consistent _routing value groups related documents on the same shard, reducing search work and improving performance.

Optimization Cases

In our case, queries are limited to specific fields (no full‑text search), which is a prerequisite for sub‑second responses on billions of rows.

ES stores only the HBase RowKey, not the actual data.

Actual data resides in HBase and is retrieved via RowKey.

Performance tuning recommendations can be found in the official ES documentation.

Key optimization items include:

4.1 Index Performance Optimizations

Bulk writes with each batch containing hundreds to thousands of records.

Multi‑threaded writes matching the number of machines; monitor performance via Kibana.

Increase segment refresh interval (e.g., set refresh_interval to -1 and manually refresh after bulk loading).

Allocate ~50% of system memory to Lucene file cache; ES nodes benefit from large memory (e.g., 64 GB+).

Use SSDs; mechanical RAID still suffers from random I/O latency.

Use custom IDs that match HBase RowKey to enable efficient updates and deletions.

Configure segment merging thresholds (e.g., indices.store.throttle.max_bytes_per_sec) and thread counts based on storage type.

4.2 Search Performance Optimizations

Disable DocValues for fields that are not needed in queries.

Prefer keyword fields over numeric types for term queries.

Disable _source storage for fields that are never returned.

Use filters or constant_score queries to avoid scoring overhead.

Pagination: from + size retrieves up to from + size results per shard, leading to high cost for deep pages; default index.max_result_window is 10 000.

search_after uses the last hit of the previous page for efficient deep pagination.

scroll is suitable for large result sets but requires maintaining a scroll_id.

Introduce a long field combining timestamp and ID to improve sorting performance.

Allocate CPUs with 16 cores or more for sorting‑intensive workloads.

Set "merge.policy.expunge_deletes_allowed": "0" to force deletion of marked records during merges.

Performance Testing

Benchmarking is essential to verify improvements:

Single‑node test with 50 M–100 M rows to assess point‑load capacity.

Cluster test with 100 M–3 B rows to evaluate disk I/O, memory, CPU, and network consumption.

Random query combinations across data volumes to measure response behavior.

Compare SSD versus HDD performance.

Extensive testing helps identify bottlenecks and guides further tuning.

Production Results

The platform now handles tens of billions of rows, returning 100 records within 3 seconds, with fast pagination; future bottlenecks can be addressed by scaling nodes.