Should that big lookup list live in the browser or in Postgres? A decision guide (and what breaks first)

Every app with autocomplete eventually hits the same fork: ship the whole list to the browser, or ask the database.
Both work on day one. They break in very different places.

The example here is a city picker: type a few letters, get matching cities. A public dataset like GeoNames makes it easy to end up with a list far bigger than you'd want in a bundle. The same reasoning applies to product catalogs, glossaries, or any big lookup table.

Option A: ship the list to the client

Load a JSON file (or a compressed index) and filter in the browser.

What's great:

• Instant results after the first load, no network per keystroke
• Works offline
• No server cost per search
• Simple: a filter over an array is easy to reason about

What breaks first:

1. Bundle size and first load. Fine with a few thousand entries. Painful once the list grows, especially on slow mobile connections.
2. Memory on low-end devices. Parsing a big JSON file and building an index costs memory and main-thread time.
3. Updates need a deploy (or cache busting). Fix a typo in one entry and every client has to re-download the list.
4. Languages multiply it. If entries have names in several languages, the payload grows with each language.

Option B: query the database

Send the typed text to an endpoint that searches in Postgres.

What's great:

• Payload stays tiny no matter how big the list grows
• Updates are instant: change the row, done
• You get real search tools: ranking, fuzzy matching, full-text

What breaks first:

1. Latency per keystroke. Every request costs a network round trip. Without debouncing, you'll also hammer the database.
2. Fuzzy matching isn't free. Typo tolerance needs an index built for it, or queries crawl as the table grows.
3. Accents and case. "Zurich" vs "Zürich", "Koln" vs "Köln". Users type without diacritics; your data has them.
4. Abuse. A public search endpoint is an easy target for scraping and load. It needs rate limits.

Making option B work well

Two Postgres features do most of the work.

pg_trgm gives you trigram similarity and index support for fuzzy matching:

create extension if not exists pg_trgm;
create extension if not exists unaccent;

create index cities_name_trgm on cities using gin (name_search gin_trgm_ops);

select name, country
from cities
where name_search % unaccent(lower('koln'))
order by similarity(name_search, unaccent(lower('koln'))) desc
limit 10;

Here name_search is a precomputed, lowercased, unaccented copy of the name, so the index matches what users actually type. For longer text, full-text search with tsvector is the better tool.

On the client, debounce so you search when the user pauses, not on every key:

function debounce(fn, ms = 200) {
  let id;
  return (...args) => {
    clearTimeout(id);
    id = setTimeout(() => fn(...args), ms);
  };
}

const search = debounce(async (q) => {
  if (q.length < 2) return render([]);
  const res = await fetch('/api/cities?q=' + encodeURIComponent(q));
  render(await res.json());
});

Add a minimum query length, a result limit, and a rate limit on the endpoint, and option B is boringly reliable.

The hybrid most apps end up with

In practice, the best answer is often both:

• Ship a small hot list (the most common entries) for instant results on the first keystrokes.
• Fall back to the database for anything beyond it.
• Cache recent queries in memory on the client.

Users get instant feedback for common cases and complete results for rare ones.

A decision checklist

Answer these before choosing:

• [ ] How large is the list in bytes, compressed, per language?
• [ ] How often does it change, and how fast must changes reach users?
• [ ] Must it work offline?
• [ ] Do you need typo tolerance and ranking, or is prefix match enough?
• [ ] What's the slowest device and connection you care about?
• [ ] Can you protect a public search endpoint from abuse?

Small, stable, offline-friendly: client. Large, changing, fuzzy: database. Somewhere in between: hybrid.

Measuring before you decide

Arguments about client versus server get settled faster with two measurements:

1. The real payload. Build the list the way you'd ship it, compress it the way your server would, and look at the transfer size. Then load it on the slowest phone you can find, with network throttling on, and time how long until the first search works.
2. The real round trip. Deploy a throwaway search endpoint in the region you'd actually use and time requests from where your users are. Include the slow tail, not just the median.

Web performance guidance like the RAIL model is a useful yardstick for what "feels instant" means for input responses. Compare both measurements against it.

If the payload loads quickly on a slow device, the client is probably fine. If round trips are consistently fast from your users' locations, the server is probably fine. If both are borderline, that's the signal for the hybrid.

Takeaways

1. Client-side lists break on size, memory and updates.
2. Database search breaks on latency, fuzzy matching, accents and abuse.
3. pg_trgm plus a precomputed unaccented column covers most fuzzy-search needs.
4. Debounce, minimum length, limits and rate limiting make server search reliable.
5. A small hot list on the client plus the database behind it is often the best of both.

Your turn

Where does your biggest lookup list live today, and what was the moment you realized it was in the wrong place?

I write about practice, feedback and the AI-readable web while building NEXO, a daily learning game.