Use alternate method for getting fast subset of rows

[AetherUnbound]

Problem

PR WordPress/openverse-api#474 introduced an approach to creating a pseudo-random subset by ordering the primary query on identifier. Unfortunately, while I thought the index on identifier would help out, it appears that the query still takes an incredibly long time to return results.

Description

We don't really care about true randomness or even an exact number of records selected, so we could potentially use an approach like this involving TABLESAMPLE_SYSTEM to get a fast subset: https://stackoverflow.com/a/8675160/3277713. One thing to consider here is ensuring this is robust during integration testing and copies sufficient data in that case as well. It may be necessary to base the estimate off table count and provide a bare minimum number of rows.

Alternatives

Additional context

Implementation

• 🙋 I would be interested in implementing this feature.

[AetherUnbound]

This relates to milestone 1.2.0 in the catalog repo: https://github.com/WordPress/openverse-catalog/milestone/2

[sarayourfriend]

@AetherUnbound do we still need this? It seems like it might be missing some context. or is detached from issues/documents that would explain what's going on here. If we still need to solve this, can you add an explanation of why we need this and where it should go? I'm wondering if it's actually meant to be part of the ingestion server, rather than API? And if so, maybe the ingestion server removal work obviates the need for this or drastically changes how we would implement it?

[AetherUnbound]

Yes this had to do with the ingestion server's select limit when running a data refresh. This is actually relevant for @krysal's recent work with setting the limit on https://github.com/WordPress/openverse-infrastructure/pull/908. That said, this is if anything an extremely low priority and probably does not need to be addressed in the near future. We'll get data refreshes for staging up soon with #3925 I imagine, and after we start running and testing that we can see if this sort of thing is still necessary. CC @stacimc in case you have other thoughts, but for now we can close this.