elasticsearch - routing VS. indexing for query performance

elasticsearch

Solution

One of the design patterns that Shay Banon @ Elasticsearch recommends is: index by time range, route by user and use aliasing.

Create an index for each day (or a date range) and route documents on `user` field, so you could 'retire' older logs and you don't need queries to execute on all shards:

$ curl -XPOST localhost:9200/user_logs_20140418 -d '{
    "mappings" : {
        "user_log" : {
            "_routing": {
              "required": true,
              "path": "user"
            },
            "properties" : {
              "user" : { "type" : "string" },
              "log_time": { "type": "date" }
            }
        }
    }
}'

Create an alias to filter and route on users, so you could query for documents of `user_foo`:

$ curl -XPOST localhost:9200/_aliases -d '{
  "actions": [{
    "add": {
      "alias": "user_foo",
      "filter": {"term": {"user": "foo"}},
      "routing": "foo"
    }
  }]
}'

Create aliases for time windows, so you could query for documents `this_week`:

$ curl -XPOST localhost:9200/_aliases -d '{
  "actions": [{
    "add": {
      "index": ["user_logs_20140418", "user_logs_20140417", "user_logs_20140416", "user_logs_20140415", "user_logs_20140414"],
      "alias": "this_week"
    }, 
    "remove": {
      "index": ["user_logs_20140413", "user_logs_20140412", "user_logs_20140411", "user_logs_20140410", "user_logs_20140409", "user_logs_20140408", "user_logs_20140407"],
      "alias": "this_week"
    }
  }]
}'

Some of the advantages of this approach:

- if you search using aliases for users, you hit only shards where the users' data resides

- if a user's data grows, you could consider creating a separate index for that user (all you need is to point that user's alias to the new index)

- no performance implications over allocation of shards

- you could 'retire' older logs by simply closing (when you close indices, they consume practically no resources) or deleting an entire index (deleting an index is simpler than deleting documents within an index)

Problem

I'm planning a strategy for querying millions of docs in date and user directions. - Option 1 - indexing by user. routing by date. - Option 2 - indexing by date. routing by user. What are the differences or advantages when using routing or indexing?

Original source