Indexing large tables has very poor throughput

[rvause]

I have a table to index with over 20m rows. The chunked row iteration uses limit/offset queries which gradually take longer to execute as the offset increases.

Indexing these rows using the current get_indexing_queryset took over 15 hours to complete.

Working around the issue, using cursor pagination helped a lot with the bulk indexing job taking about ~65 minutes. The downside to this is that Django's prefetch queryset API would not apply.

Here is the implementation of the get_indexing_queryset method using QuerySet.iterator():

class Document(DODocument):
    def get_indexing_queryset(
        self,
        verbose: bool = False,
        filter_: Optional[Q] = None,
        exclude: Optional[Q] = None,
        count: Optional[int] = None,
        action: OpensearchAction = OpensearchAction.INDEX,
        stdout: TextIO = sys.stdout,
    ) -> Iterable[Any]:
        chunk_size = cast(int, self.django.queryset_pagination)
        qs = self.get_queryset(filter_=filter_, exclude=exclude, count=count)
        qs = qs.order_by("pk") if not qs.query.is_sliced else qs
        count = qs.count()
        model = self.django.model.__name__
        action = action.present_participle.title()

        i = 0
        done = 0
        start = time.time()
        if verbose:
            stdout.write(f"{action} {model}: 0% ({self._eta(start, done, count)})\r")

        iterqs = qs.iterator()
        while done < count:
            if verbose:
                eta = self._eta(start, done, count)
                stdout.write(f"{action} {model}: {round(i / count * 100)}% ({eta})   \r")

            for _ in range(chunk_size):
                done += 1
                try:
                    yield next(iterqs)
                except StopIteration:
                    # Ensure we break out of the outer loop
                    done = count + 1
                    break

            i = min(i + chunk_size, count)
        if verbose:
            stdout.write(f"{action} {count} {model}: OK          \n")

[David-Guillot]

For reference, a previous implementation #6 was proposed (and rejected) to address this issue. It worked with prefetch_related() but implied a larger number of DB queries.

[rvause]

Yeah, I think that is a trade-off that would need to be made here. Making more queries is not necessarily bad depending on an individual use-case (in the case I am describing above it is totally acceptable).

It might make more sense if such implementation details are handled downstream but perhaps the Document class could provide better ways to hook into the queryset usage so that the entire get_indexing_queryset method does not need a complete override.

[kwood]

It's probably time to revisit this. As of Django 4.1, iterator() with a chunksize does support prefetch_related
https://docs.djangoproject.com/en/4.2/ref/models/querysets/

[qcoumes]

Easier customization of the indexation process

• Provide different mixin of get_indexing_queryset() with different indexing strategies.

  • Manually chunked

    More RAM on the server, but fewer resources on the database.

  • iterator()

    Almost no RAM consumption on the server, but either high RAM or disk consumption on the database, depending on the driver (e.g. Oracle stores everything in a temporary table at the start of the iteration).

    Also quite faster.

• Document more lower-level arguments so that overriding method is easier.

  Most of the functions calling lower-level opensearchpy functions rely a lot on *args and **kwargs. For instance, specifying arguments such as elasticsearch.helpers.streaming_bulk's chunk_size and max_chunk_bytes goes a long way in saving time finding relevant documentation in lower-level libraries.

• Make more things configurable in the indexation commands:

  • filters
  • exclude
  • os_filters
  • os_exclude
  • order_by
  • offset
  • limit
  • db_chunk_size
  • os_chunk_size
  • max_chunk_bytes
  • max_retries
  • initial_backoff
  • max_backoff

Resources:

• https://elasticsearch-py.readthedocs.io/en/7.x/helpers.html#elasticsearch.helpers.streaming_bulk