I have a use case where I need to store many fields per document - around 1000 (let's say 200 date fields, 200 long fields, 200 keyword, 200 string, 200 something)
I want to do on them:
- filtering - equality, range for numeric fields, equals / prefix / contains on strings
- sorting (for string fields - by lowercased value)
- and aggregations - min/max/count on numeric fields, terms, count on strings
Most of those fields in each document are empty - 10 are always filled and additional 5-15 random ones.
Number of documents is in the range of tens-hundreds of millions.
The question is - how well does elasticsearch handle 1k+ fields? Does it make sense to index each such field as nested document with field_id + field_value (separately for each type)?
I'd assume that sorting, filtering and aggregations would work better on fields then on nested documents but if having 1k+ fields is a reasonable approach.
The workload is read-heavy (low latency required) - 10s queries per second but there is a continuous stream of updates/inserts/deletes as well.