Simple Elasticsearch Aggregation vs Postgres Counts Benchmark

01 February 2017 on . 3 minutes to read

Aggregations for Elastisearch: Quick Like F1

In my last post, I dove into sub aggregations. This time, we’ll look at aggregations, which are the elasticsearch equivalent of SQL’s count group. If you’ve seen ecommerce sites where they say how many products belong to each category or brand, this is most likely how they accomplish that quickly and at scale.

Up to date list of articles diving into my ecommerce performance investigations:



Elasticsearch and Postgres Rails Implementation

The needed code to fetch and create the {:name, :id} hash from elasticsearch.

#app/models/product.rb
  def self.brand_id_count_from_es
    search "*", aggs: [:brand_id], limit: 0
  end

And the corresponding code to fetch what was needed from the database.

#app/models/product.rb
  def self.brand_id_count_from_db
    group(:brand_id).count
  end

Benchmarks: Unleash the Numbers

Before the official benchmark, let’s checkout an initial, manual run to see how these two compare:

Product.brand_id_count_from_es
  Product Search (299.0ms)  curl http://localhost:9200/products_development/_search?pretty -d '{"query":{"match_all":{}},"size":0,"from":0,"aggs":{"brand_id":{"terms":{"field":"brand_id","size":1000}}},"timeout":"11s","_source":false}'

Product.brand_id_count_from_db
   (232.9ms)  SELECT COUNT(*) AS count_all, "products"."brand_id" AS products_brand_id FROM "products" GROUP BY "products"."brand_id"

Rails.logger.level = :info

Benchmark.ips do |x|
  x.report("Products per Brand from Elasticsearch Aggregation") {Product.brand_id_count_from_es}
  x.report("Products per Brand from PG Distinct Pluck") {Product.brand_id_count_from_db}
  x.compare!
end
Warming up --------------------------------------
Products per Brand from Elasticsearch Aggregation
                        11.000  i/100ms
Products per Brand from PG Distinct Pluck
                         1.000  i/100ms
Calculating -------------------------------------
Products per Brand from Elasticsearch Aggregation
                        150.823  (±21.2%) i/s -    704.000  in   5.011286s
Products per Brand from PG Distinct Pluck
                          4.097  (± 0.0%) i/s -     21.000  in   5.203527s

Comparison:
Products per Brand from Elasticsearch Aggregation:      150.8 i/s
Products per Brand from PG Distinct Pluck:        4.1 i/s - 36.81x  slower

Win: Elasticsearch again. If you noticed, a very similar caching speedup came into play, just like last time:

  • Elasticsearch: 299ms
  • Postgres: 233ms

Sharp eyes if you spotted that. If you wonder why, it has to do with elasticsearch’s aggregations caching which is automatically enabled for any size: 0 queries, and all aggregations. Another manual run reveals this:

Product.brand_id_count_from_es
  Product Search (42.0ms)  curl http://localhost:9200/products_development/_search?pretty -d '{"query":{"match_all":{}},"size":0,"from":0,"aggs":{"brand_id":{"terms":{"field":"brand_id","size":1000}}},"timeout":"11s","_source":false}'

Product.brand_id_count_from_db
   (233.9ms)  SELECT COUNT(*) AS count_all, "products"."brand_id" AS products_brand_id FROM "products" GROUP BY "products"."brand_id"

Wrap Up: Elasticsearch Delivers Again for Stats

What would be great here is to have a load tester running various scenarios simultaneously:

  • 1 to simulate an admin creating new products
  • 1 to simulate requests to this group count functionality

I have a feeling that would drastically reduce elasticsearch’s lead, but I’m also sure that elasticsearch would still be quicker. I’ll add that to my todo list for the future. Got any benchmarks, load tests or complext scenarios you’d like to be sure your application can handle? Contact me and have the peace of mind knowing your site will be performant into the future.


If you enjoy having free time and the peace of mind that a professional is on your side, then you’d love to have me work on your project.

Contact or view a list of available services to see how I’ll make your life better, easier and bring satisfaction back into you running your business.