Multi-Tenant custom pre-defined fields and unknown filters

[mehrdd (Mehrdad Nassiri)]

Hi,

My index:

"mappings": {
	"properties": { 
		"namespace":  { "type":   "text" },
		"uid":  { "type":   "long" },
		"id":  { "type":   "long" },
		"flags": { "type": "text" },
		"s01": { "type": "text" },
		"s02": { "type": "text" },
		"s03": { "type": "text" },
		"n01": { "type": "long" },
		"n02": { "type": "long" },
		"n03": { "type": "long" }
	}
}

And my very simple query:

GET /d3/_doc/_search
{
    "query": {
        "bool": {
            "filter": {
                "term": {
                    "namespace": "1_2_3"
                }
            }
        }
    }
}

Questions:

• My simple query will took about 70ms to 120ms
  But when i try URI query it is much faster (about 2-3ms):
  http://localhost:9200/d3/_doc/_search?q=namespace:1_2_3
  Is it because of caching or something?

• I'm building a SaaS service and every tenants (which is isolated) can have their modules and their custom fields. i thought the best approach is having a set of pre-defined fields like s01, s02, s(n) for strings and n01, n02, n(n) for numbers. with a middleware i can map the target field of a module to it's specific data. And namespace is a 3 part codename for "TenantID_ApplicationID_ModuleID".
  So let's say, if we want to get latest news (ApplicationID: 1 , ModuleID: 4) from a tenant (TenantID: 20) we would do a query like this:
  SELECT * FROM d3 WHERE namespace = '20_1_4' ORDER BY id DESC LIMIT 10
  We can also filter the module with all fields.
  Is it a good approach todo with Elasticsearch? We may have 200 Requests Per Second.

• Due to fact i told here, filters and queries will be very different and completely unknown. is there any suggestion for this model?

• Results of namespace: 1_2_3 are the slowest. I think that's because it has millions of results, but don't know why. On the other hand namespace:1_2_2 is super fast.

---

Some information:
Records with namespace:1_2_1 = 3 docs
Records with namespace:1_2_2 = 17 docs
Records with namespace:1_2_3 = 3000000 docs

I'm using Elasticsearch 6.6 with Docker for macOS.
1 Node, 1 Shard, 1 Replica

Thank you so much
Mehrdad