I have a problem when design Elasticsearch mapping.
I have 2 table in Postgresql: Person table with 1000 records and Variant table with 50M records. Each person has 20 millions variants and each variant belongs to at least 700 person. Two tables join by a mapping table Person_Variant with n-n relationship.
I want to transform it to Elasticsearch for faster query text. But I also want to some aggregate and filter on Web UI like this: when filter one property of Person or Variant, Web UI update number of Person meet the filter and number of variant from matched Person; and Web UI update some aggregate value of properties of Person, Variant. Data does not need to update, if it change I can index again.
I used nested document design: Each record in Person Index has list of nested Variant which contains only filter fields, and each record in Variant Index has list of nested Person which contains only filter fields. It still to large and not efficient.
Do you have meet any problem like this? Or Can you give some advice?