Search API Solr at 5 million users: custom datasource and tracker

Search API’s entity:user datasource works beautifully — up to a point. Somewhere between one hundred thousand and one million users, the setup that every tutorial shows you stops being slow and starts being unusable: cron runs never finish, a reindex locks the database for an hour, and the tracking table becomes the largest object in your schema. Here is what actually breaks, and the two plugins that fix it.

First: do you even need Solr for this?

The most common version of this request is “let editors search users, filtered by role.” If that is genuinely all you need, an indexed SQL query beats Solr — it is a tenth of the work, has no synchronisation to get wrong, and no second copy of your personal data to secure. Add an index on the columns you filter and sort, use a plain view, move on.

Solr earns its place when you need full-text across profile fields, facets over millions of rows, or multi-criteria queries that no single SQL index can serve — the same reasoning that moved DOGA’s product catalogue off Search API Database. If that is your case, keep reading.

Where the default setup breaks

Do the arithmetic before blaming Solr. Five million users, indexed in batches of 50 through the entity API, is 100,000 cron batches; at one second each — optimistic once field data, hooks and processors are involved — a full reindex is around 28 hours. Meanwhile:

• search_api_item holds one row per item. At 5M rows, “mark all for reindex” is a single enormous UPDATE that locks the table while cron is trying to read it.
• Every cron run scans that table for the next batch of pending items.
• Offset pagination degrades quadratically: fetching page 4,000 makes MySQL walk four million rows to discard 3,999,000 of them.

The split that matters

The instinct is to write a custom tracker that “tracks only users with the role.” That puts the logic in the wrong plugin. Keep the responsibilities clean:

• The datasource decides who belongs in the index. Role filtering lives here, in getItemIds().
• The tracker is bookkeeping only — what is dirty. Replace it when the tracking table itself is the bottleneck, not to filter.

The datasource: keyset pagination, not offsets

Search API hands getItemIds() a page number, so the trick is translating it into a cursor. Core’s own ContentEntity datasource does exactly this — it remembers the last tracked ID in state rather than paging by offset. Yours should too:

public function getItemIds($page = NULL) {
  $state_key = 'acme_user_search.cursor.' . $this->getIndex()->id();

  $query = $this->database->select('users_field_data', 'u');
  $query->fields('u', ['uid']);
  $query->innerJoin('user__roles', 'r', 'r.entity_id = u.uid');
  $query->condition('r.roles_target_id', $this->configuration['role']);
  $query->condition('u.uid', 0, '>');
  $query->condition('u.default_langcode', 1);

  if ($page !== NULL) {
    // Page 0 means "start over" — Search API always walks pages in order.
    $cursor = $page === 0 ? 0 : (int) $this->state->get($state_key, 0);
    $query->condition('u.uid', $cursor, '>')->orderBy('u.uid')->range(0, 1000);
  }

  $uids = $query->execute()->fetchCol();

  if ($page !== NULL) {
    if (!$uids) {
      $this->state->delete($state_key);
      // NULL tells Search API: this page and every following one is empty.
      return NULL;
    }
    $this->state->set($state_key, (int) end($uids));
  }

  return array_map('strval', $uids);
}

One more rule for loadMultiple(): re-check the role at load time. Tracking and indexing are minutes or hours apart, and without that check you cheerfully index someone who no longer qualifies.

The bug nobody handles: role transitions

Neither plugin notices a user entering or leaving the indexable set. A user is granted the role and never gets indexed; a user loses it and stays searchable forever. You have to wire it yourself:

function acme_user_search_user_update(UserInterface $user) {
  $had = in_array(ACME_ROLE, $user->original->getRoles(), TRUE);
  $has = in_array(ACME_ROLE, $user->getRoles(), TRUE);
  if ($had === $has) {
    return;
  }

  $ids = [(string) $user->id()];
  foreach (Index::loadMultiple() as $index) {
    if ($index->isValidDatasource('user_by_role')) {
      // Not "updated" — deleted. An update would re-index them forever.
      $has ? $index->trackItemsInserted('user_by_role', $ids)
           : $index->trackItemsDeleted('user_by_role', $ids);
    }
  }
}

This is the one that shows up as a security finding: former staff, still in the index, still searchable.

The tracker: stop writing five million rows

Now the tracker earns its custom plugin. Instead of a row per item, derive dirty from the source table’s own changed column and store a single watermark. A full reindex becomes one write instead of 5M:

public function trackItemsInserted(array $ids) {}
public function trackItemsUpdated(array $ids) {}

public function trackAllItemsUpdated($datasource_id = NULL) {
  // A full reindex: one row deleted instead of five million updated.
  $this->getState()->delete($this->key());
}

Two traps. First, changed has one-second resolution, so a bulk role grant stamps fifty thousand users with an identical timestamp — compare on the composite (changed, uid) pair or you silently skip everyone after the first tie. Second, a watermark cannot see a row that no longer exists: hook_user_delete() must call trackItemsDeleted() directly, or the index accumulates ghosts.

Also cache your getTotalItemsCount() and getRemainingItemsCount(). They run on every Search API admin page load, and an approximate progress bar beats an exact 900 ms query.

Before you ship: the data protection part

Everything above puts email addresses and profile fields into Solr. Without role tokens written into each document and enforced at query time, your search endpoint is a user-enumeration API — and your Solr backup is a copy of your user table sitting outside whatever governs the database. Treat the index as personal data, because it is.

The short version

Filter in the datasource, paginate by keyset, handle role transitions in hook_user_update(), and only replace the tracker once you have measured that search_api_item is the bottleneck. Most sites that think they need all four actually needed the SQL query in the first section.

Running Search API at a scale where the tutorials stop applying? Architecture and performance work on large Drupal platforms is what I do — including the audit that tells you which of the four you actually need.