Caveats

Benchmarks are very difficult and the intention here is look at patterns in response times and specifically look at an ORM query that can be executed against both Postgres and ElasticSearch to identify patterns and conditions for certain responses.

You should ALWAYS try to run your own benchmarks against your own data. There are so many factors (volume, cardinality, relationships, data skew etc) that randomly generated data does not cater to.

Don't focus on the absolute numbers
... with ElasticSearch by default we effectively get an index on every property. We generally can't do that on our OLTP database (have an index on every column in the table).

Setup

Populated customers, products, orders, order details (using a modifications to the example application: example-kotlin-web with:

  • Orders: 500,000
  • Customers: 100,000
  • Products: 30,000
  • Order lines: 5,263,023

The orders are generated with an orderDate spread randomly over 12 months for 2015 and a random spread of orderStatus.

This is loaded into a Postgres database and additionally indexed into ElasticSearch. This is a relatively small database but it is decent enough to play around with various aspects of the query (selectivity of the order date and denormalisation).

For Postgres I did add indexes on order date and status but other wise kept with defaults for both Postgres and ElasticSearch. This is not about getting the best numbers but instead looking at the variations we can see in changing selectivity of predicates and denormalisation.

It is also interesting to note that for ElasticSearch this is a non-scoring query (filter query only) so it's good to see how it compares ballpark wise to Postgres for this simple query.

The query

Run a simple ORM query against both Postgres and ElasticSearch ...

Find Top 100 NEW orders after a given orderDate. We can pass in a boolean to switch between running the query against a Postgres database or ElasticSearch.

// top 100 new orders after (given date)
// ... run against Postgres or ElasticSearch
// ... based on boolean - asDocStore

return Order.find.where()
  .status.in(Order.Status.NEW)
  .orderDate.after(useDate)
  .order()
    .orderDate.desc()

  .setMaxRows(100)
  .setUseDocStore(asDocStore)
  .findList()

SQL query

SELECT
  t0.id                  c0,
  t0.status              c1,
  t0.order_date          c2,
  t0.ship_date           c3,
  t0.version             c4,
  t0.when_created        c5,
  t0.when_modified       c6,
  t0.customer_id         c7,
  t0.shipping_address_id c8
FROM o_order t0
WHERE t0.status IN (?) AND t0.order_date > ?
ORDER BY t0.order_date DESC, t0.id
LIMIT 100; --bind(NEW,2015-12-08)

Elastic query

{
  "size": 100,
  "sort": [ { "orderDate": { "order": "desc" } } ],
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            { "terms": { "status": [ "NEW" ] } },
            { "range": { "orderDate": { "gt": 1450263600000 } } }
          ]
        }
      }
    }
  }
}

Run mode 1

In this first run mode I restrict the order date to be dates in December which makes the query reasonably selective. This means the cost of the sort and filter required for the TOP 100 is not too bad.

DB mean: 10.2    std dev: 2.34    min:6    max:18
ES mean: 8.77    std dev: 1.08    min:7    max:14

ElasticSearch and Postgres are in the same ballpark here - near enough to the same which is great.

Could we give ElasticSearch this query to run in our application? Yes.

Run mode 2 - Denormalisation effect

Denormalisation benefit in action (ElasticSearch as an alternative to a DB materialised view)

ElasticSearch did very well but actually the object it returned contained some additional denormalised information that we didn't have in the Postgres query.

Specifically the ElasticSearch order index contains additional details for the customer (id,name) and order details (*,product(id,sku,name)). If we change the ORM query to match the data the index is returning we need to fetch the customer name and order details (with some additional joins).

// change the ORM query to additionally fetch
// customer and order details to match the index
// which has been denormalised
query
  .fetch("customer", "id,name")
  .fetch("details", "*")
  .fetch("details.product","id,sku,name")

So this is going to make the ORM query against the DB more expensive because now we join to customer, order details and products.

In the ElasticSearch index these 'joins' have been denormalised into the index so that will run as before. In a DB we could create a materialised view to get a similar effect.

DB mean: 56.4    std dev: 5.95    min:48   max:95
ES mean: 8.75    std dev: 1.01    min:7    max:13

So denormalisation in the ElasticSearch index when it is needed is good (which is pretty much stating the obvious but it is always good to check).

Database materialised views would give us a similar effect but we note that using ElasticSearch also has the advantage of taking load off the database.

Run mode 3 - less selective

In this run mode we drop fetching the customer and order details etc (so back to run mode 1) but change the order date used such that it is now randomly selected from the first 100 days of the year. This means the query is much less selective and so to get the TOP 100 there are many more rows to sort and filter out of the result.

DB mean: 51.3    std dev: 3.71    min:44   max:60
ES mean: 14.0    std dev: 2.00    min:10   max:22

So here both queries go slower than run mode 1 as we expect (as they have to sort and filter out many more results to obtain the top 100). ElasticSearch does do rather well here in this TOP 100 relatively unselective query in the sense that it didn't really get that much slower.

ElasticSearch is orientated to paging query results but this a is pretty impressive result given that this is a non-scoring filter only query.

Appendix - Bean mapping

The mapping on the order entity bean is:

// this is Kotlin
// .. the @DocEmbedded is what you should look at

@DocStore
@Entity
@Table(name = "o_order")
class Order : BaseModel() {

  ...

  var status: Status = Status.NEW;

  @DocEmbedded(doc = "id,name")
  @ManyToOne @NotNull
  var customer: Customer? = null;

  @DocEmbedded(doc = "*,product(id,sku,name)")
  @OneToMany(mappedBy = "order", cascade = arrayOf(CascadeType.PERSIST))
  @OrderBy("id asc")
  var details: MutableList<OrderDetail> = ArrayList();

Appendix: Index mapping

The mapping for the ElasticSearch order index. Note that enums are automatically treated as codes (hence status is not_analysed). The customer and details mapping from the respective @DocEmbedded annotations.

{
  "mappings" : {
    "order" : {
      "properties" : {
        "status": { "type": "string", "index": "not_analyzed" },
        "orderDate": { "type": "date" },
        "shipDate": { "type": "date" },
        "customer" : {
          "properties" : {
            "id": { "type": "long" },
            "name": { "type": "string" }
          }
        },
        "details" : {
          "type" : "nested",
          "properties" : {
            "id": { "type": "long" },
            "orderQty": { "type": "integer" },
            "shipQty": { "type": "integer" },
            "unitPrice": { "type": "double" },
            "product" : {
              "properties" : {
                "id": { "type": "long" },
                "sku": { "type": "string" },
                "name": { "type": "string" }
              }
            },
            "version": { "type": "long" },
            "whenCreated": { "type": "date" },
            "whenModified": { "type": "date" }
          }
        },
        "version": { "type": "long" },
        "whenCreated": { "type": "date" },
        "whenModified": { "type": "date" }
      }
    }
  }
}

Appendix: Example Order

An example from the ElasticSearch index.

{
  "_index": "order_v1",
  "_type": "order",
  "_id": "72033",
  "_version": 1,
  "found": true,
  "_source": {
    "status": "NEW",
    "orderDate": 1446462000000,
    "shipDate": 1446721200000,
    "customer": {
      "id": 31772,
      "name": "big 31775"
    },
    "details": [
      {
        "id": 759300,
        "orderQty": 7,
        "unitPrice": 78,
        "product": {
          "id": 9697,
          "sku": "A1672",
          "name": "A1672"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759301,
        "orderQty": 8,
        "unitPrice": 94,
        "product": {
          "id": 18351,
          "sku": "E2322",
          "name": "E2322"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759302,
        "orderQty": 11,
        "unitPrice": 73,
        "product": {
          "id": 12358,
          "sku": "B2332",
          "name": "B2332"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759303,
        "orderQty": 14,
        "unitPrice": 33,
        "product": {
          "id": 11847,
          "sku": "B1821",
          "name": "B1821"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759304,
        "orderQty": 11,
        "unitPrice": 34,
        "product": {
          "id": 14230,
          "sku": "C2203",
          "name": "C2203"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759305,
        "orderQty": 17,
        "unitPrice": 95,
        "product": {
          "id": 20625,
          "sku": "F2595",
          "name": "F2595"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759306,
        "orderQty": 4,
        "unitPrice": 89,
        "product": {
          "id": 11653,
          "sku": "B1627",
          "name": "B1627"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759307,
        "orderQty": 2,
        "unitPrice": 42,
        "product": {
          "id": 2237,
          "sku": "C450",
          "name": "C450"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759308,
        "orderQty": 1,
        "unitPrice": 60,
        "product": {
          "id": 3404,
          "sku": "D726",
          "name": "D726"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759309,
        "orderQty": 18,
        "unitPrice": 80,
        "product": {
          "id": 19433,
          "sku": "F1403",
          "name": "F1403"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      },
      {
        "id": 759310,
        "orderQty": 9,
        "unitPrice": 53,
        "product": {
          "id": 22608,
          "sku": "G2577",
          "name": "G2577"
        },
        "version": 1,
        "whenCreated": 1460670857630,
        "whenModified": 1460670857630
      }
    ],
    "version": 1,
    "whenCreated": 1460670857630,
    "whenModified": 1460670857630
  }
}