4 ms·

If I understand your case, you'd have to do it in multiple steps - you would get the results and paginated them, and record the hashes in a different store (eg

by yehosef 11y ago

If I understand your case, you'd have to do it in multiple steps - you would get the results and paginated them, and record the hashes in a different store (eg redis) - then run the aggregations on a paginated set of hashes. I understand it's a pain, but I think it's just a hard/messy problem. Like I said, if you can find other people that are solving this problem (could be there are..) then you can reference them to the ES people, I'm sure they'd like to find solutions. It's also possible you could restructure your data to make it easier to extract the way you want.. I'm not sure if that's possible in your case, but many times in the NoSQL world, modeling your data in the best way for how you want to extract it is key for success.

LunaSea 11y ago
Like I said I have +20M records so potentially tens of thousands of results per query. This would mean that every time a user triggers this API call we have to insert those tens of thousands of documents into Redis and run an operation at the end. Even without taking into account the JSON serialisation / deserialisation costs, this would take forever where we need something that runs in around one or two seconds at the maximum. The problem is actually pretty simple and can be summed up by this sentence: "I want to group documents by field X, take one document per group of X and return documents from index OFFSET to index OFFSET + LIMIT". In MongoDB for example this would be quite easy with a $group + $first operation in the aggregation framework. Sadly MongoDB lacks the nice full-text search features that ElasticSearch has. It's highly possible though that going forward we'll have to hack a full-text-like search on MongoDB and switch from ElasticSearch to MongoDB since stuff like this doesn't seem to possible.
- lobster_johnson 11y ago
  Sounds like something Postgres would be very good at, using window functions or (probably recursive) CTEs; its query language is much richer than both ElasticSearch and MongoDB. Postgres' text indexing isn't as advanced as ElasticSearch's (and indexing performance is definitely lower), but it's not bad at all. If you only need basic term support and not actual term vectors, you can use plain Postgres arrays, which have all sorts of supported operators, and can be translated into tables and back to perform some really neat in-memory queries. One downside, of course, is that you don't get any sharding for free, and if your dataset is large enough you might have to manually partition your tables, either using Postgres partitions or by explicitly sharding (e.g., using pg_shard).
  - yehosef 11y ago
    Good suggestion. 20M records is not very big so Postgres might be a great choice.
- yehosef 11y ago
  I still don't really understand your use case to suggest how to solve it - it doesn't seem like it should have to work like that but I don't really know. So why don't you use mongo your main data source and for the aggregations and use ES for the search (you just push changes to mongo when they update.) It's hard to beat ES/Solr/Lucene for text searches, but depending on what you need, Postgres' full text search may be fine. Most use-cases where search is the need use some other datastore and just ES for the text searches. Unless a text search is somehow part of the aggregation query..? in any event - good luck.

by yehosef 11y ago

LunaSea 11y ago

Like I said I have +20M records so potentially tens of thousands of results per query. This would mean that every time a user triggers this API call we have to insert those tens of thousands of documents into Redis and run an operation at the end. Even without taking into account the JSON serialisation / deserialisation costs, this would take forever where we need something that runs in around one or two seconds at the maximum. The problem is actually pretty simple and can be summed up by this sentence: "I want to group documents by field X, take one document per group of X and return documents from index OFFSET to index OFFSET + LIMIT". In MongoDB for example this would be quite easy with a $group + $first operation in the aggregation framework. Sadly MongoDB lacks the nice full-text search features that ElasticSearch has. It's highly possible though that going forward we'll have to hack a full-text-like search on MongoDB and switch from ElasticSearch to MongoDB since stuff like this doesn't seem to possible.

lobster_johnson 11y ago
Sounds like something Postgres would be very good at, using window functions or (probably recursive) CTEs; its query language is much richer than both ElasticSearch and MongoDB. Postgres' text indexing isn't as advanced as ElasticSearch's (and indexing performance is definitely lower), but it's not bad at all. If you only need basic term support and not actual term vectors, you can use plain Postgres arrays, which have all sorts of supported operators, and can be translated into tables and back to perform some really neat in-memory queries. One downside, of course, is that you don't get any sharding for free, and if your dataset is large enough you might have to manually partition your tables, either using Postgres partitions or by explicitly sharding (e.g., using pg_shard).
- yehosef 11y ago
  Good suggestion. 20M records is not very big so Postgres might be a great choice.
yehosef 11y ago
I still don't really understand your use case to suggest how to solve it - it doesn't seem like it should have to work like that but I don't really know. So why don't you use mongo your main data source and for the aggregations and use ES for the search (you just push changes to mongo when they update.) It's hard to beat ES/Solr/Lucene for text searches, but depending on what you need, Postgres' full text search may be fine. Most use-cases where search is the need use some other datastore and just ES for the text searches. Unless a text search is somehow part of the aggregation query..? in any event - good luck.