Comment by LunaSea | Hacker News Reader

Comment by LunaSea | Hacker News Reader

Parent Full thread

I read the GitHub issue but I don't think it's practical or even possible in some cases to keep all the previous pages in memory, especially if you have a web service.

My use case is that I have around +20M documents with non-unique hashes and each query should return an arbitrary amount of documents matching the query / filter as well as calculated meta-data based on the results in the aggregation field.

Now the issues is that if you want to have only one document per hash, you need to use a TermAggregation on the hash field followed by a TopHitsAggregation of size 1 to obtain the actual document rather than the hash field.

At this point you have many many buckets containing a single document but:

- you can't paginate them since TermAggregation doesn't let you do pagination for the reasons you explained and linked to above

- you can't calculate an aggregation on all the returned documents since all of them are in their own separate bucket (by hash)

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.

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.

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).

Good suggestion. 20M records is not very big so Postgres might be a great choice.

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.

Reply on news.ycombinator.com