Databases

Elasticsearch as an Analytics Engine: Aggregations, Dashboards, and a ClickHouse Alternative for Mid-Scale Data

Ruslan Ismailov Published 18 min read
E

Elasticsearch has traditionally been seen as a search engine. However, in 2026, more and more teams are considering it as an analytics tool for business intelligence and dashboards at data volumes up to 1 TB. In this article, we'll explore how justified that approach really is, and provide concrete query examples, optimization tips, and an honest comparison with competing solutions.

1. Elasticsearch as an Analytics Tool: Myths vs. Reality

There's a common myth that Elasticsearch is only for full-text search, and that ClickHouse or Druid are required for analytics. The reality is more nuanced. Elasticsearch has a powerful aggregation engine that covers most OLAP scenarios at volumes up to several hundred gigabytes. At the same time, you get a unified infrastructure for both search and analytics, which reduces operational complexity.

Key advantages of Elasticsearch as an analytics engine:

  • Built-in aggregations without an additional transformation layer
  • Native integration with Kibana for building dashboards
  • Horizontal scaling through sharding
  • Flexible mapping for semi-structured data
  • REST API without requiring a SQL dialect (though a SQL interface is also available)

The main limitations: Elasticsearch is not a columnar store in the classical sense, and JOINs between indices are not supported at the query level. For heavy analytical workloads with billions of rows and complex multi-table queries, ClickHouse will win. But for mid-scale volumes and mixed workloads, Elasticsearch is a perfectly legitimate choice.

2. Aggregations in Elasticsearch: terms, date_histogram, nested, pipeline aggregations

Aggregations are the heart of Elasticsearch's analytics engine. They fall into three categories: metric, bucket, and pipeline. Let's look at the key types with examples.

Terms aggregation — grouping by value

The equivalent of GROUP BY in SQL. Used to count unique field values:

POST /orders/_search
{
  "size": 0,
  "aggs": {
    "by_country": {
      "terms": {
        "field": "country.keyword",
        "size": 20
      },
      "aggs": {
        "total_revenue": {
          "sum": {
            "field": "amount"
          }
        },
        "avg_order": {
          "avg": {
            "field": "amount"
          }
        }
      }
    }
  }
}

The size parameter determines the number of buckets returned. For high cardinality fields, use the composite aggregation for pagination.

Date histogram — time series

A key tool for time-based analytics. date_histogram groups documents by time intervals:

POST /events/_search
{
  "size": 0,
  "query": {
    "range": {
      "timestamp": {
        "gte": "2025-01-01",
        "lte": "2025-12-31"
      }
    }
  },
  "aggs": {
    "events_over_time": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day",
        "format": "yyyy-MM-dd",
        "min_doc_count": 0
      },
      "aggs": {
        "unique_users": {
          "cardinality": {
            "field": "user_id",
            "precision_threshold": 1000
          }
        }
      }
    }
  }
}

The min_doc_count: 0 parameter ensures that zero values appear for days with no events — critical for time series charts.

Nested aggregations — working with nested objects

To analyze nested structures (such as line items in an order), use the nested aggregation:

POST /orders/_search
{
  "size": 0,
  "aggs": {
    "items_analysis": {
      "nested": {
        "path": "items"
      },
      "aggs": {
        "by_category": {
          "terms": {
            "field": "items.category.keyword"
          },
          "aggs": {
            "item_revenue": {
              "sum": {
                "field": "items.price"
              }
            }
          }
        }
      }
    }
  }
}

Pipeline aggregations — analytics on top of aggregations

Pipeline aggregations operate on the results of other aggregations. They are a powerful tool for moving averages, derived metrics, and anomaly detection:

POST /sales/_search
{
  "size": 0,
  "aggs": {
    "monthly_sales": {
      "date_histogram": {
        "field": "date",
        "calendar_interval": "month"
      },
      "aggs": {
        "total": {
          "sum": { "field": "amount" }
        },
        "moving_avg": {
          "moving_avg": {
            "buckets_path": "total",
            "window": 3,
            "model": "simple"
          }
        },
        "month_over_month": {
          "derivative": {
            "buckets_path": "total"
          }
        }
      }
    }
  }
}

Pipeline aggregation types like derivative, cumulative_sum, and bucket_script allow you to build complex analytical expressions without any post-processing on the application side.

3. Designing Indices for Analytics

Proper index design is critical for the performance of Elasticsearch as an analytics engine. Key principles:

Mapping for analytics

For string fields used in aggregations, use the keyword type rather than text. Numeric fields should have the correct numeric type:

PUT /analytics_events
{
  "mappings": {
    "properties": {
      "timestamp": { "type": "date", "format": "strict_date_time" },
      "user_id": { "type": "keyword" },
      "session_id": { "type": "keyword" },
      "event_type": { "type": "keyword" },
      "page": { "type": "keyword" },
      "duration_ms": { "type": "integer" },
      "revenue": { "type": "scaled_float", "scaling_factor": 100 },
      "properties": {
        "type": "object",
        "dynamic": false
      }
    }
  },
  "settings": {
    "number_of_shards": 3,
    "number_of_replicas": 1,
    "index.codec": "best_compression",
    "index.refresh_interval": "30s"
  }
}

The scaled_float type saves storage compared to double and is well suited for financial data. Setting dynamic: false for open-ended objects prevents unbounded mapping growth.

Dynamic templates

For logs and events with unpredictable structure, use dynamic_templates:

PUT /logs
{
  "mappings": {
    "dynamic_templates": [
      {
        "strings_as_keywords": {
          "match_mapping_type": "string",
          "mapping": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      {
        "longs_as_integers": {
          "match_mapping_type": "long",
          "mapping": { "type": "integer" }
        }
      }
    ]
  }
}

Storage optimization

  • Use ILM (Index Lifecycle Management) to automatically transition from hot to cold indices
  • Apply force_merge to read-only indices: reduces segment count and speeds up aggregations
  • Disable _source for indices where access to original documents is not needed (aggregations only)
  • Use best_compression codec for analytics indices

4. Building Analytical Queries: Session Analysis, Funnels, Cohort Analysis

Session analysis

Analyzing user sessions is a typical use case for Elasticsearch as an analytics engine. A query to calculate average session length and event counts:

POST /events/_search
{
  "size": 0,
  "aggs": {
    "by_session": {
      "terms": {
        "field": "session_id",
        "size": 10000
      },
      "aggs": {
        "session_duration": {
          "max": { "field": "timestamp" }
        },
        "session_start": {
          "min": { "field": "timestamp" }
        },
        "event_count": {
          "value_count": { "field": "event_type" }
        }
      }
    },
    "avg_events_per_session": {
      "avg_bucket": {
        "buckets_path": "by_session>event_count"
      }
    }
  }
}

Funnel analysis

Funnels are implemented using filter aggregations. A separate filter is applied for each funnel step:

POST /events/_search
{
  "size": 0,
  "aggs": {
    "funnel_step_1": {
      "filter": { "term": { "event_type": "page_view" } },
      "aggs": {
        "unique_users": { "cardinality": { "field": "user_id" } }
      }
    },
    "funnel_step_2": {
      "filter": { "term": { "event_type": "add_to_cart" } },
      "aggs": {
        "unique_users": { "cardinality": { "field": "user_id" } }
      }
    },
    "funnel_step_3": {
      "filter": { "term": { "event_type": "purchase" } },
      "aggs": {
        "unique_users": { "cardinality": { "field": "user_id" } },
        "total_revenue": { "sum": { "field": "revenue" } }
      }
    }
  }
}

Cohort analysis

Cohort analysis requires a two-step approach: first define the cohort by the date of the first event, then analyze behavior. In Elasticsearch, this is implemented through enrichment or by pre-computing the cohort at indexing time.

If the cohort_month field has been computed in advance and stored in the document, the query becomes straightforward:

POST /user_events/_search
{
  "size": 0,
  "aggs": {
    "by_cohort": {
      "terms": { "field": "cohort_month" },
      "aggs": {
        "by_month_offset": {
          "terms": { "field": "months_since_registration" },
          "aggs": {
            "retained_users": { "cardinality": { "field": "user_id" } }
          }
        }
      }
    }
  }
}

5. Aggregation Performance: doc_values, fielddata, and Memory Optimization

Aggregation performance in Elasticsearch depends directly on how data is stored on disk and in memory.

Doc values vs Fielddata

doc_values is an on-disk columnar store enabled by default for all types except text. Aggregations rely on doc_values. Never disable doc_values for fields involved in aggregations.

fielddata is a legacy in-memory storage mechanism for text fields. Using it in aggregations is an anti-pattern: it leads to OutOfMemoryError. Always use the keyword subfield for strings in aggregations:

"event_name": {
  "type": "text",
  "fields": {
    "keyword": {
      "type": "keyword",
      "ignore_above": 256
    }
  }
}

Memory optimization

  • Set the JVM heap to no more than 50% of the node's RAM and no more than 32 GB (the compressed oops boundary)
  • Use circuit breakers to limit memory consumption by aggregations
  • For high-cardinality terms aggregations, use the composite aggregation with pagination
  • The execution_hint: map parameter in terms aggregations reduces memory usage at the cost of speed
  • Apply a filter to narrow the dataset before aggregating — this is critical for performance

Request cache

Elasticsearch automatically caches aggregation results for queries with size: 0 and fixed ranges. For dashboards, this significantly reduces cluster load. Manage the cache via indices.requests.cache.size (default: 1% of heap).

6. Integration with Kibana and External Dashboard Tools

Kibana remains the primary visualization tool for Elasticsearch dashboards. In 2026, Kibana offers:

  • Lens — a drag-and-drop interface for creating visualizations without knowledge of the DSL
  • ESQL — a new SQL-like query language for analytics (introduced in Elasticsearch 8.x)
  • Alerting — threshold-based alerts driven by aggregations
  • Canvas — pixel-perfect custom dashboards

For external visualization, Elasticsearch integrates with Grafana via the grafana-elasticsearch-datasource plugin. Grafana lets you build dashboards on top of Elasticsearch aggregations alongside Prometheus and other data sources.

Apache Superset supports connecting to Elasticsearch via its SQL interface (elasticsearch-dbapi). This allows you to use familiar SQL syntax for analytical queries:

SELECT
  DATE_TRUNC('day', timestamp) AS day,
  event_type,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS unique_users
FROM events
WHERE timestamp >= '2025-01-01'
GROUP BY 1, 2
ORDER BY 1

7. Comparison with ClickHouse and PostgreSQL for Analytics at Volumes up to 1 TB

Choosing an analytics engine is a strategic decision. Let's take an honest look at three options.

Elasticsearch

  • Strengths: full-text search + analytics in one system, flexible mapping, native Kibana integration, horizontal scaling
  • Weaknesses: no JOINs, high memory consumption, not optimal for heavy OLAP queries, expensive cluster to maintain
  • Best for: mixed search-and-analytics workloads, log analytics, event data up to ~500 GB

ClickHouse

  • Strengths: columnar storage, exceptional performance on analytical queries, SQL compatibility, low memory usage during aggregations
  • Weaknesses: weak full-text search, complex UPDATE/DELETE operations, less schema flexibility
  • Best for: pure analytical workloads, time series, data volumes from 100 GB to petabytes

PostgreSQL

  • Strengths: ACID compliance, full SQL with JOINs, extensions (TimescaleDB, pg_analytics), familiar tooling
  • Weaknesses: row-based storage is poorly suited for OLAP, vertical scaling
  • Best for: volumes up to 50–100 GB, mixed OLTP+OLAP workloads

Bottom line: for pure analytics at volumes between 100 GB and 1 TB, ClickHouse outperforms Elasticsearch by 3–10x on typical OLAP queries. Elasticsearch wins when you need a single system for both search and analytics, or when the data is semi-structured.

8. Loading Data via Go and PHP Pipelines

Efficient data ingestion is critical for Elasticsearch as an analytics engine. Always use the Bulk API.

Go pipeline with the official client

package main

import (
    "bytes"
    "context"
    "encoding/json"
    "log"
    "strings"
    "time"

    "github.com/elastic/go-elasticsearch/v8"
    "github.com/elastic/go-elasticsearch/v8/esutil"
)

type AnalyticsEvent struct {
    Timestamp time.Time `json:"timestamp"`
    UserID    string    `json:"user_id"`
    EventType string    `json:"event_type"`
    Revenue   float64   `json:"revenue,omitempty"`
}

func main() {
    es, err := elasticsearch.NewDefaultClient()
    if err != nil {
        log.Fatalf("Error creating client: %s", err)
    }

    bi, err := esutil.NewBulkIndexer(esutil.BulkIndexerConfig{
        Index:         "analytics_events",
        Client:        es,
        NumWorkers:    4,
        FlushBytes:    5e6,  // 5MB
        FlushInterval: 10 * time.Second,
    })
    if err != nil {
        log.Fatalf("Error creating indexer: %s", err)
    }

    events := generateEvents(10000)
    for _, event := range events {
        data, _ := json.Marshal(event)
        bi.Add(context.Background(), esutil.BulkIndexerItem{
            Action: "index",
            Body:   bytes.NewReader(data),
            OnFailure: func(ctx context.Context, item esutil.BulkIndexerItem,
                res esutil.BulkIndexerResponseItem, err error) {
                log.Printf("Indexing failed: %s", err)
            },
        })
    }

    bi.Close(context.Background())
    stats := bi.Stats()
    log.Printf("Indexed %d documents, failed: %d",
        stats.NumIndexed, stats.NumFailed)
}

func generateEvents(n int) []AnalyticsEvent {
    events := make([]AnalyticsEvent, n)
    for i := range events {
        events[i] = AnalyticsEvent{
            Timestamp: time.Now().Add(-time.Duration(i) * time.Minute),
            UserID:    "user_" + strings.Repeat("x", i%10),
            EventType: []string{"page_view", "click", "purchase"}[i%3],
        }
    }
    return events
}

PHP pipeline using elasticsearch-php

setHosts(['localhost:9200'])
    ->build();

function indexEventsBatch(array $events, $client, string $index): void
{
    $params = ['body' => []];

    foreach ($events as $event) {
        $params['body'][] = [
            'index' => [
                '_index' => $index,
            ]
        ];
        $params['body'][] = [
            'timestamp'  => $event['timestamp'],
            'user_id'    => $event['user_id'],
            'event_type' => $event['event_type'],
            'revenue'    => $event['revenue'] ?? null,
        ];

        if (count($params['body']) >= 2000) {
            $response = $client->bulk($params);
            if ($response['errors']) {
                error_log('Bulk indexing errors detected');
            }
            $params['body'] = [];
        }
    }

    if (!empty($params['body'])) {
        $client->bulk($params);
    }
}

$events = array_map(fn($i) => [
    'timestamp'  => date('c', strtotime("-{$i} minutes")),
    'user_id'    => 'user_' . ($i % 100),
    'event_type' => ['page_view', 'click', 'purchase'][$i % 3],
    'revenue'    => $i % 3 === 2 ? rand(10, 500) : null,
], range(0, 9999));

indexEventsBatch($events, $client, 'analytics_events');
echo "Indexing complete\n";

For PHP, it is recommended to use queues (Laravel Queue, RabbitMQ) to buffer events before writing to Elasticsearch, to avoid hammering the cluster with individual requests.

9. Monitoring and Managing an Elasticsearch Cluster in Kubernetes

Deploying Elasticsearch in Kubernetes has become standard practice in 2026. Use the official Elastic Cloud on Kubernetes (ECK) operator:

apiVersion: elasticsearch.k8s.elastic.co/v1
kind: Elasticsearch
metadata:
  name: analytics-cluster
spec:
  version: 8.12.0
  nodeSets:
  - name: masters
    count: 3
    config:
      node.roles: [master]
    podTemplate:
      spec:
        containers:
        - name: elasticsearch
          resources:
            requests:
              memory: 4Gi
              cpu: 1
            limits:
              memory: 4Gi
  - name: data-hot
    count: 3
    config:
      node.roles: [data_hot, data_content]
      node.attr.data: hot
    podTemplate:
      spec:
        containers:
        - name: elasticsearch
          env:
          - name: ES_JAVA_OPTS
            value: "-Xms16g -Xmx16g"
          resources:
            requests:
              memory: 32Gi
              cpu: 4
            limits:
              memory: 32Gi
        initContainers:
        - name: sysctl
          securityContext:
            privileged: true
          command: ['sh', '-c', 'sysctl -w vm.max_map_count=262144']
    volumeClaimTemplates:
    - metadata:
        name: elasticsearch-data
      spec:
        accessModes: [ReadWriteOnce]
        storageClassName: fast-ssd
        resources:
          requests:
            storage: 500Gi

Key metrics for monitoring an Elasticsearch cluster via Prometheus + Grafana:

  • elasticsearch_cluster_health_status — cluster health status (green/yellow/red)
  • elasticsearch_jvm_memory_used_bytes — heap memory usage
  • elasticsearch_indices_search_query_time_seconds — query execution time
  • elasticsearch_indices_segments_count — segment count (too many segments = slow aggregations)
  • elasticsearch_thread_pool_search_rejected_count — rejected requests

For metrics export, use elasticsearch-exporter (prometheus-community/elasticsearch-exporter). In Kubernetes, it is deployed as a separate Deployment with a ServiceMonitor for the Prometheus Operator.

Important operational practices for Elasticsearch in Kubernetes:

  • Use PodDisruptionBudget to prevent simultaneous draining of multiple data nodes
  • Configure vm.max_map_count=262144 via an initContainer or DaemonSet
  • Separate master, data, and coordinating nodes for production clusters
  • Use a dedicated SSD storageClass for hot-tier data nodes
  • Configure ILM to automatically move old indices to cold/frozen nodes

The Elasticsearch Docker image requires specific resource configuration. Never run Elasticsearch in Docker or Kubernetes without explicitly setting the heap via ES_JAVA_OPTS and resource limits — this is the leading cause of OOM kills in production.

10. Conclusion

Elasticsearch as an analytics engine is a well-justified choice for teams that need a unified system for full-text search and business intelligence at data volumes up to 500 GB – 1 TB. Powerful aggregations — including date_histogram, terms, nested, and pipeline aggregations — enable complex analytical queries without an additional processing layer.

Key takeaways on when to use Elasticsearch for analytics:

  • Choose Elasticsearch if you have a mixed search-and-analytics workload, semi-structured data, or an existing Elastic infrastructure
  • Choose ClickHouse if you need pure analytics at large volumes, with complex GROUP BY queries and a predictable schema
  • Choose PostgreSQL with extensions if your data volumes are modest and you need full SQL compatibility with ACID guarantees

For maximum Elasticsearch aggregation performance: design your mappings with keyword fields for grouping, use doc_values, apply filters before aggregating, manage JVM heap carefully, and use ILM for index lifecycle management. Go pipelines will deliver high-throughput data ingestion via the Bulk API, while PHP solutions integrate well with existing web applications.

Deploying on Kubernetes via the ECK operator with properly configured resources and monitoring through Prometheus and Grafana makes Elasticsearch a production-ready analytics platform for mid-sized businesses in 2026.

Technologies

Tags

Ruslan Ismailov

Senior Web / Backend Developer. Senior web/backend developer with 9 years of experience. Stack: PHP, Laravel, PostgreSQL, Redis, Docker, Kubernetes, REST, microservices, CI/CD. More about me →