"Skip value must 25000 or less" prevents access to adverse events data

[beckyconning]

Why should the skip value be less than 25000? If I have the required requests remaining this minute / day why can't I do this from a single query rather than having to split them up by receiveddate?

[beckyconning]

For example say I want all 83622 records about adverse events in cats. This is not possible without arbitrary windowing of the data to enable a secondary form of pagination as that is more than 25000 results.

API usage limits (240 requests per minute 120000 a day) make complete sense however the restriction on skip amount is redundant and prevents valid use cases.

[dkrylovsb]

@beckyconning The thing is, we are using Elasticsearch in the backend. The skip parameter cannot exceed the index.max_result_window index setting, which we have already upped to 25100 from the default of 10K. Search requests take heap memory and time proportional to skip + limit and index.max_result_window limits that memory. Setting index.max_result_window too high can increase strain on the infrastructure.

[beckyconning]

Thanks for the informative response : ) @dkrylovsb Would you be able to suggest an approach to retrieving all the adverse reactions for cats?

[beckyconning]

is there a way to leverage search-after for pagination instead of from?https://www.elastic.co/guide/en/elasticsearch/reference/6.4/search-request-search-after.html

[beckyconning]

this might improve performance of > 10k < 25k paginated queries whilst also enabling > 25k queries.

[dkrylovsb]

@beckyconning Unfortunately, the version of Elasticsearch currently being used in openFDA does not support the Search After feature.

Have you been able to work around the current skip limit of 25K?

[beckyconning]

Not in a reasonable way. I've been contacted by other multiple people (researchers, journalists, data scientists) who have the same issue as well.

[beckyconning]

@dkrylovsb any chance that search after will be supported? there isn't a way to get the data reliably and each page is very slow to retrieve.

[dkrylovsb]

@beckyconning Do you have any specific max skip limit value in mind that would help you achieve your research goals?

We will look into supporting a Search After parameter. That will require an Elasticsearch upgrade across major versions, which by itself is a significant undertaking.

[beckyconning]

Awesome thanks for pushing this forwards @dkrylovsb. For us it would be nice to retrieve any dataset from the OpenFDA API that our users ask for. Sometimes it just falls within the limits, sometimes it doesn't.

For example adverse event reports which include a reference to drugs indicated for osteoarthritis just fits under 25k. At the same time one researchers query for reports involving a drug that's commonly prescribed for autism was over 80k.

Additionally load times are slow due to the data retrieval method and of course that can get worse if the limit is relaxed without switching to search after.

Thanks so much!

[beckyconning]

Happy to donate my weekends if I can be of any use. I'm an experienced programmer so I'm happy to take on any aspects of this which would be useful. I can meet during the week to discuss tasks etc.

[beckyconning]

Hi! Can I be of any use? Just need a dev env with sample data to get going. Thanks!

[dkrylovsb]

Thank you for your generous offer @beckyconning ; unfortunately, Elasticsearch upgrade is something that would have to be done by our team according to priorities. I will post back once I have news to report.

[beckyconning]

Thanks so much @dkrylovsb, looking forward to this upgrade : )

[dkrylovsb]

This has been implemented: https://open.fda.gov/apis/paging/

[beckyconning]

Whoa

[beckyconning]

🎉 thank you so much!