On search, part 2 of 4

Building Allē’s Search

Leadership wanted Postgres for provider search. A treatment tree and a distance search in every query said otherwise. How we built AbbVie’s first Elasticsearch service, kept it fresh with ID-only messages, and tested the query builder well enough to rewrite it in another language.

Allē is AbbVie’s loyalty and booking platform for aesthetic treatments, the place someone goes to find a provider who offers Botox, Juvéderm or CoolSculpting near them. When I joined in 2020 it had no real search. The search we built became the primary search on alle.com, used by more than two million people a month, and it was the first Elasticsearch service at AbbVie.

It almost wasn’t Elasticsearch. Leadership wanted Postgres, and I understood why. Postgres is great, and the old adage holds: use Postgres until you can’t. Our Provider service already ran on Postgres with PostGIS. Adding a search engine means another system to run, another place for data to drift and another thing for the team to learn.

But that adage is advice for a particular situation. When you’re small, straining Postgres makes sense. You find its limits as you grow into them, and hitting one costs you a slow week, not your users. Allē wasn’t that. It replaced Allergan’s Brilliant Distinctions program, which had grown “to six million members” and was in 25,000 doctor’s offices by 2019, according to Allergan’s head of U.S. medical aesthetics (Modern Aesthetics). This was an enterprise platform launching to an established user base. There was no growing into the limit. Search would carry the full load on day one, and finding out where Postgres broke would have meant finding out in front of millions of people.

Why Not Postgres

The problem was the shape of the question. Every search on Allē worked in three tiers. We searched by closeness, ranked by quality and filtered by what a provider offered. Closeness meant a zip code or a city, turned into a point, and the providers near it. Quality meant reviews, so the best-reviewed providers nearby came first. And what they offered meant the treatment the user selected, and treatments came in a hierarchy.

Taken one at a time, Postgres handles both. PostGIS is excellent at distance, and a recursive query can walk a tree. Together, at the scale we expected, they were a different story. Every search would walk the treatment tree, join providers to every treatment under the node the user picked, compute distance to each of them and sort by reviews, for millions of visitors a month, with the same handful of popular treatments and cities searched over and over. That’s a lot of work to redo on every request.

Elasticsearch is built for that kind of query. A provider becomes one document with its location and everything it offers already on it, so there’s nothing to join at search time. The treatment and distance checks run as filters, which Elasticsearch evaluates as yes-or-no matches “without score calculation,” and it “automatically caches frequently used filters, speeding up subsequent search performance” (Elastic). And because a zip code or a city always geocodes to the same point, every search from the same place produces the same distance filter. Popular metros, the Los Angeles and New York and Miami searches for the same few treatments, would sit mostly in cache. That was the case I made to leadership. Postgres would have to work its way through the taxonomies and the PostGIS math on every query. Elasticsearch was designed to make that query cheap.

System of Record, System of Search

The deeper difference is that Postgres and Elasticsearch are built for different jobs, and the job decides the data model.

Postgres is a system of record. It stores data normalized, one fact in one place, with transactions and constraints to keep it right. A provider is a row, each treatment they offer is a row in a join table, and the treatment tree is a table that points at itself. That’s the right shape for writing data, and it’s why the Provider service stayed on Postgres. Reading it for search means putting it back together on every request. Here’s roughly what our search would have looked like in SQL, simplified:

WITH RECURSIVE subtree AS (
  SELECT id FROM treatments WHERE slug = 'fillers'
  UNION ALL
  SELECT t.id FROM treatments t JOIN subtree s ON t.parent_id = s.id
)
SELECT DISTINCT p.id, p.review_score
FROM providers p
JOIN provider_treatments pt ON pt.provider_id = p.id
JOIN subtree st ON st.id = pt.treatment_id
WHERE ST_DWithin(p.location, :point, 40234)  -- 25 miles
ORDER BY p.review_score DESC;

Every search walks the tree, joins two tables, filters by distance and sorts, and none of it can be reused by the next search, even an identical one.

Elasticsearch is a system of search. It stores data denormalized, shaped for the question, with everything a search needs already on the document. It can’t join at query time the way Postgres can, and its docs say so directly: “Performing full SQL-style joins in a distributed system like Elasticsearch is prohibitively expensive” (Elastic). So you do the joining once, when you write the document, and the search is a pair of filters against an index built for exactly those lookups, with the results cached.

The fair counterargument is that we could have made Postgres do it. We could have added a materialized path or ltree column for the tree, precomputed a provider-treatment table with every ancestor in it, and put a cache in front. At that point we’d have built a worse search index inside our database, and still owned the cache.

Elasticsearch has its own cost, and it’s worth naming. It isn’t a source of truth. Documents become searchable in “near real-time—within 1 second,” after a refresh, not the moment they’re written (Elastic), and an index is a copy that can drift from the data it came from. That’s why Postgres kept owning every provider, and why nothing but the indexing worker ever wrote to Elasticsearch. One system was responsible for being right, and the other for being fast. It also split the load. Postgres kept serving what it’s good at, the provider pages and everything else that reads one provider by ID, and the search traffic, millions of visitors a month asking the same broad questions, went to something better suited for the job. The database never had to carry it.

Where

Users searched by zip code or city. We sent that to Google’s Geocoding API to turn it into a latitude and longitude, and cached the result, so popular cities and zips didn’t go back to Google on every search. A request for a zip code looks like this:

GET https://maps.googleapis.com/maps/api/geocode/json
    ?components=postal_code:92618|country:US
    &key=YOUR_API_KEY

And the response, trimmed, with the coordinates rounded:

{
  "results": [
    {
      "formatted_address": "Irvine, CA 92618, USA",
      "geometry": {
        "location": { "lat": 33.66, "lng": -117.73 },
        "location_type": "APPROXIMATE"
      },
      "place_id": "…",
      "types": ["postal_code"]
    }
  ],
  "status": "OK"
}

All we kept was geometry.location, the latitude and longitude, cached against the zip code or city. APPROXIMATE is honest. A zip code isn’t a point, it’s an area, and Google is handing back a point somewhere in the middle of it, which is plenty for “providers near me.”

Then we asked Elasticsearch for providers within a distance of that point. The geo_distance query “matches geo_point and geo_shape values within a given distance of a geopoint” (Elastic), and it runs in filter context like everything else that’s a yes or no.

What

Treatments were a tree. Fillers sat above Juvéderm, and Juvéderm sat above the individual products like Voluma. Someone searching for fillers wanted every provider who offered any filler, not only the ones who had listed the word “fillers.”

The trick with a hierarchy in a search index is to do the walking once, when the document is written, rather than on every search. Here’s an illustrative provider document, not our production mapping, with every ancestor of every treatment written onto it:

{
  "id": "6f1c…",
  "name": "Example Aesthetics",
  "location": { "lat": 33.66, "lon": -117.82 },
  "review_score": 4.8,
  "treatments": ["fillers", "juvederm", "juvederm-voluma", "neurotoxins", "botox"]
}

A search for fillers near Irvine then becomes all three tiers in one request, two filters and a sort:

{
  "query": {
    "bool": {
      "filter": [
        { "term": { "treatments": "fillers" } },
        { "geo_distance": { "distance": "25mi", "location": { "lat": 33.68, "lon": -117.83 } } }
      ]
    }
  },
  "sort": [
    { "review_score": "desc" }
  ]
}

Distance decides who’s in, reviews decide the order, and the treatment decides what counts. There’s no tree walk and no join at search time, and both filters are the kind Elasticsearch caches.

Keeping It Fresh

A search index is a copy, and copies go stale. Providers created their profiles, updated them, added treatments and changed addresses, and search had to reflect that quickly.

When a provider was created or updated, the Provider service put a message on Amazon SQS that looked like { action: 'create', id: <uuid> }. An indexing worker read it, fetched the provider by ID and wrote the document to Elasticsearch.

The message carried only the action and the ID, on purpose. We kept messages light so the worker would always fetch the freshest version of the provider, rather than indexing whatever the provider looked like when the message was sent. That turned out to matter more than it sounds. AWS is clear that standard queues ensure “at-least-once message delivery,” that “more than one copy of a message might be delivered,” and that “messages may occasionally arrive out of order” (AWS). If each message had carried a full copy of the provider, an older update arriving after a newer one would have overwritten good data with stale data. Because each message only said “this provider changed, go look,” a duplicate meant indexing the same current data twice, and an out-of-order message did no harm, because the worker always read the provider as it was right then. It also meant we never had to worry about concurrent updates. When a provider saved their profile twice in quick succession, the first message’s worker usually read the provider after both saves had landed, so it indexed the final version, and the second message ended up as a no-op, rewriting data that was already in the index. No locks, no version numbers and no ordering logic, because the queue was only ever saying “go look.”

The action field helped too. It told the worker what kind of change it was looking at, a new provider, an update or a delete, so each one had its own handler, tested on its own. We dispatched on it with a switch statement, and that was deliberate. An if-else chain leaves implied branches, where the last else quietly catches every case nobody thought about, including actions that didn’t exist yet. A switch names every case it handles, and its default sent anything else to a dead-letter queue, so an action the worker didn’t know about had nowhere to hide. It sat in the dead-letter queue where we could see it, instead of being half-handled by a branch that was written for something else.

Here’s the shape of it, simplified and in TypeScript. The message is a type with one field that says what happened and one that says to whom:

type ProviderEvent = {
  action: 'create' | 'update' | 'delete';
  id: string; // provider UUID, nothing else
};

And the worker dispatches on the action, one handler per case, with everything else going to the dead-letter queue:

async function handle(event: ProviderEvent) {
  switch (event.action) {
    case 'create':
      return indexProvider(event.id); // fetch the current provider, write it
    case 'update':
      return indexProvider(event.id); // same fetch, so the newest data always wins
    case 'delete':
      return removeProvider(event.id); // nothing to fetch, just remove it
    default:
      return sendToDeadLetterQueue(event); // an action we don't know about
  }
}

Notice that create and update do the same thing. They’re still separate cases, because they’re separate events, and the day one of them needs to behave differently, the change has an obvious place to go. A delete removed the provider from the index instead of fetching anything, and a create or an update fetched and wrote. The message stayed tiny, and the worker still knew exactly what to do with it.

The Query Builder

Between the request and Elasticsearch sat a query builder. A search came in with a location, a treatment and whatever else the page asked for, and the builder composed the Elasticsearch query from those pieces. Each input was optional, and each one added or removed a clause.

In the NestJS version it was a class that built the Elasticsearch DSL one piece at a time. Simplified, it looked something like this:

type Point = { lat: number; lon: number };

export class ProviderQueryBuilder {
  private filters: object[] = [];
  private sort: object[] = [];

  near(point: Point, miles = 25): this {
    this.filters.push({ geo_distance: { distance: `${miles}mi`, location: point } });
    return this;
  }

  rankedByReviews(): this {
    this.sort.push({ review_score: 'desc' });
    return this;
  }

  offering(...treatments: string[]): this {
    this.filters.push({ terms: { treatments } });
    return this;
  }

  build() {
    return { query: { bool: { filter: this.filters } }, sort: this.sort };
  }
}

A search for fillers near a zip code became new ProviderQueryBuilder().near(point).rankedByReviews().offering('fillers').build(), one method per tier, and each method only knew about its own clause.

Most of the builder’s real work was boolean logic: which clauses had to match, which were optional, which only affected the order, and how a should beside a filter quietly stops filtering anything. That’s its own topic, and I wrote it up in Who’s In, and in What Order, along with how a sort on reviews decided the ranking.

That kind of code looks simple and breaks in combination. A filter that works alone and a sort that works alone can produce the wrong query together. So we unit tested it to death. Every combination of inputs we could think of had a test asserting the exact query it should produce, which is easy to do for a builder, because its output is plain data. A test for the builder above is a few lines:

it('searches nearby, ranks by reviews, filters by treatment', () => {
  const point = { lat: 33.68, lon: -117.83 };
  const query = new ProviderQueryBuilder().near(point).rankedByReviews().offering('fillers').build();

  expect(query.query.bool.filter).toEqual([
    { geo_distance: { distance: '25mi', location: point } },
    { terms: { treatments: ['fillers'] } },
  ]);
  expect(query.sort).toEqual([{ review_score: 'desc' }]);
});

We wrote the first version in Go. Later a new lead joined who didn’t work in Go, and the builder moved to NestJS, in TypeScript. That’s the kind of rewrite that usually goes badly, because nobody remembers everything the old code handled. Ours had a written answer, the test suite. Every case the Go builder handled was a test with an input and an expected query, and the TypeScript version had to produce the same queries to pass. The tests were the specification, and the language underneath was an implementation detail.

The move to Node brought one habit I’d push back on. Validation crept in on both ends of the service, at ingest, checking every provider document on the way into the index, and at egress, checking every result on the way out. It existed because the Node code wasn’t trusted to produce the right shapes on its own. I think that’s a bad model. Validating at both ends of your own service means paying for the same check on every write and every search, and it catches mistakes at runtime, in production, that should never have shipped. The fix was the same thing that had made the port safe in the first place. We unit tested every mutation, with mocks standing in for the Provider service and Elasticsearch, so each one was checked against the shape it had to produce, and we used TypeScript’s types for real. A wrong shape failed at compile time or in a test, not in front of a user. Validation belongs at the boundary where untrusted data comes in, not wrapped around code we wrote ourselves.

What It Did

The search became the primary search on alle.com, used by more than two million visitors a month, and it was the first Elasticsearch service at AbbVie. It was also the part of the platform people noticed when it was slow and didn’t think about when it worked, which is the job of search.

Takeaways

If I were building this again, I’d still start by asking whether Postgres can do it, because the adage is right far more often than it’s wrong. What changed the answer here was the combination: a hierarchy, a distance search and a ranking in nearly every query, at millions of visitors a month. Either one alone, Postgres would have handled. I’d flatten the hierarchy into the document at write time, so the search never walks a tree. I’d keep queue messages to an ID and an action, and let the worker read the current state, because a queue that can repeat and reorder messages is only safe when the messages don’t carry data that can go stale. And I’d test the query builder harder than anything else in the service, because the tests are what let you change everything underneath it, even the language. I’d put types and tests in front of runtime validation, and keep validation for the data we didn’t write.

Leadership wanted Postgres, and usually that’s the right call. The shape of the question decides when it isn’t.