Catalog Postgres Exploration Effort

On May 22nd at 1500 UTC several OpenverseOpenverse Openverse is a search engine for openly-licensed media, including photos, audio, and video. Openverse is also the name for the collection of related code repositories that make up the project. find Openverse at members will be gathering for a meeting to discuss and engage in some exploratory operations on a copy of the catalog database to get a better sense of how Postgres performs given our current scale & purpose. This was spurred on by this discussion on the decoupling popularity calculations IP; I personally had a feeling regarding how certain Postgres changes would affect our query time, performance, etc., but based on the level of unpredictable behavior we’ve seen with the matview refresh, few of us feel confident about being able to predict outcomes with Postgres. We thought it might be useful to spend some time performing some exploratory operations & optimizations with Postgres!

Some things I think might be useful to try:

  • Reducing the width of the coreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress. media table
  • Performing some normalization steps (e.g. pulling provider out into a separate table)
  • Adding an index on the provider column

Some ways that we could assess the performance changes of the above:

  • Have a sample set of data we use for upsert tests, one small (~1k rows) and another large (~100k rows)
  • Develop some sample updates we might perform (e.g. the thumbnail column update we tried to perform for Flickr which was projected to take an extremely long time)
  • A matview refresh or recreation

Our plan will be to create a new catalog DB instance based on a snapshot, and perform all the above against that instance. @olgabulat and @stacimc have confirmed to join the meeting, and Staci and I will continue to work on things together throughout the day while we’re co-located.

If folks have any suggestions for other operations or queries for us to test, we’d love to hear them! Hopefully we can come away from this with a better sense of “what will happen” when we change the catalog schema in certain ways 😁

#catalog #postgres

Concurrency/resource issues on monthly runs

Today (or yesterday, depending on your timezone) was the start of March 1st, UTC 00:00. For our Airflow instance, this meant that the scheduler kicked off all @hourly, @daily, @weekly, and @monthly DAG runs simultaneously.

While this has not historically been a problem, the iNaturalist workflow is set to run @monthly. iNaturalist is a particularly resource intensive DAG, and the massive amount of data it processes has required some other adjustments to our existing DAGs. The iNaturalist DAG does run a check for new data before proceeding for any run, but when it does identify that there is new data to process, it must reprocess the entire dataset (since there is no way to detect which records have changed from the last run).

For our Airflow cluster, this meant that iNaturalist was running alongside almost all of our other scheduled DAGs, and this caused some interruptions with other DAGs. We saw a myriad of seemingly-inexplicable issues on the cluster, ranging from log files missing to TSVs failing to exist when they should. This seemed to point to a disk space issue, but when I checked the instance itself it had plenty of disk space. I suspect I wasn’t able to catch it, but the iNaturalist DAG initially loads the Catalog of Life data as its first step which could have pushed it over the edge. Alongside other ingestion processes, it’s totally plausible that the disk ran out of space with everything going on.

@stacimc and I monitored the instance throughout the afternoon, pausing the iNaturalist DAG and waiting for the other DAGs to finish processing. Everything after that point ran successfully, and we re-enabled the iNaturalist DAG (and the data refresh DAG). Everything seems to have returned to normal at this point, though we plan on restarting the Docker stack on the instance once iNaturalist is complete and prior to the data refresh run this weekend.

Moving forward, we’ve identified a number of ways to improve our workflows and infrastructure:

#airflow #catalog #database

Preparing for iNaturalist

Today we were able to merge some massive and significant changes contributed by @beccawidom to the iNaturalist DAG! This PR includes a number of changes, namely:

  • The transformation steps have changed from “CSV -> Postgres -> TSV -> Postgres” now to “CSV -> Postgres -> Postgres”. This significantly reduces disk space, time, and processing overhead, and was a necessary change in order to process all of the iNaturalist data in a reasonable timeframe. It also serves as a proof-of-concept for future bulk data imports, since the transformation & data cleaning steps are happening entirely in SQL (an OpenverseOpenverse Openverse is a search engine for openly-licensed media, including photos, audio, and video. Openverse is also the name for the collection of related code repositories that make up the project. find Openverse at first!).
  • Images are now connected with the Catalog of Life, which provides English vernacular names. This should help improve search relevancy over the current scientific names.

I want to take a moment to celebrate this huge accomplishment, and the tremendous effort @beccawidom poured into this effort. Thank you!

Now that this DAG is ready to be run once again, we’re faced with the impressive and daunting notion that we could, in a matter of days, increase the size of the image catalog by ~137 million (a roughly 23.3% increase in size). With that information, it’s important to consider the implications of including this data.

We have a weekly image data refresh process which transfers images from the catalog into our APIAPI An API or Application Programming Interface is a software intermediary that allows programs to interact with each other and share data in limited, clearly defined ways. for public use. Presently, this data refresh takes around 47 hours without the popularity recalculation and 60 hours with the popularity recalculation. If we are to assume these times are linear, we can expect those times to become 58 hours and 74 hours respectively. Since these are run weekly, this still gives us about 100 hours left in the week before we start having data refreshes queued while previous ones are running.

Here are some steps we can take to monitor the process:

  1. Take a manual database snapshot of the catalog prior to enabling the iNaturalist DAG.
  2. Enable the DAG shortly after the weekly data refresh has completed. This will allow iNaturalist to run without other significant database operations occurring.
  3. Disable the DAG after the run while we verify the following steps.
  4. Monitor the next scheduled image data refresh closely for significant aberrations in step duration.
  5. Make a number of searches after the data refresh is complete to see how results are affected. We can make a number of searches which we would expect to return iNaturalist data (e.g. cat, mushroom, alligator) and some we expect should not (e.g. computer, transistor, book).
  6. Re-enable the iNaturalist DAG.

One of our big-picture goals for 2023 is search relevancy, and a key piece required for making improvements in that area is understanding how our existing document scoring works. I’m not sure that we can predict how adding this much data will affect our result relevancy. In the case where we notice result relevancy is negatively impacted (e.g. unrelated queries are flooded with iNaturalist results), there are a few actions we can take to mitigate this:

  • Alter the weight of the provider in the API (@sarayourfriend had mentioned this as an option).
  • Set the authority boost of the provider in the ingestion server and reindex the images.
  • Disable the iNaturalist provider in the API.

We would like to do all we can to avoid the last option. I don’t presume that the iNaturalist data will require taking the above actions, but I wanted to outline them and open up space in case other folks have mitigation ideas.

We’re incredibly excited for the addition of this data!

#catalog #database