Lorixen.
EN

Searching 30,000 Records in an Internal Tool: Built-In Database Search or a Search Service?

Short answer: For an internal tool with tens of thousands of records and a few dozen users, the search built into your database is usually enough, and adding a separate search service means a second system to pay for, keep in sync and secure. Start with database search plus a trigram index for typos, measure with real queries, and move to a hosted search service only when you hit a specific limit such as heavy typo tolerance across several record types, faceted filtering at speed, or very large volumes. The right answer depends on what staff actually type, so collect examples before deciding.

Start with the owner's questions

Ask these before anyone mentions a product name.

What do staff search for?

A customer name? A phone number? An invoice number? A part code with hyphens? Each behaves differently. Exact identifiers need prefix or exact matching. Names need typo tolerance and handling for accents and apostrophes. Free text in job notes needs word-based matching. Write ten real examples from the people who will use the tool, including their sloppiest ones.

How many records, and how fast do they grow?

Thirty thousand rows is small for any modern database. A plain query with the right index returns in a few milliseconds. The trouble people feel is rarely size, it is a query written as "contains this text anywhere", which cannot use a normal index and scans every row.

What does a failed search cost?

If a dispatcher cannot find a customer while the phone is ringing, speed and forgiveness matter a lot. If a manager occasionally looks for an old job, a slower, simpler search is fine.

What the database can already do

The PostgreSQL full-text search documentation describes word-based matching with stemming, so "installing" finds "installation", plus ranking and highlighting. The pg_trgm extension adds similarity matching on three-letter fragments, which tolerates typos like "Ohara" for "O'Hara" or "Kowalsky" for "Kowalski". Many other databases have comparable features under different names.

A sensible combination for a small tool:

  1. A full-text index over notes, descriptions and titles.
  2. A trigram index over names, company names and addresses.
  3. Exact or prefix matching for codes, with hyphens and spaces stripped on both the stored and the typed side.
  4. A short ranking rule: exact code match first, then name matches, then notes.

When a search service starts to make sense

Need Database search Hosted search service
Find records by name with typos Good with trigram index Excellent, tunable
Search across several record types in one box Doable, takes design Natural
Filters with live counts (facets) Possible, can get slow Strength
Millions of rows Needs tuning Built for it
Synonyms and "did you mean" Manual work Built in
Operating effort One system Second system to sync, secure and pay for
Data location and access control Same as your database Another vendor holding copies of customer data
Keeping results current Automatic Needs indexing on every change

The last three rows deserve weight. A search service holds a copy of your data, which must be updated whenever a record changes, deleted when a record is erased, and filtered per user so staff cannot find records they should not see. That is real engineering, even if the vendor makes the first demo look easy. Pricing and free allowances differ by vendor and change, so check each vendor's current page as of November 2026 instead of relying on a figure from an article.

A cheap experiment before committing

You can learn more from a week of testing than from a long comparison.

  1. Collect 30 to 50 real searches, with the record staff expected to find for each.
  2. Run them against a prototype using the database features above.
  3. Score each as: found first, found in top five, not found.
  4. Fix the misses by adjusting the fields searched, the ranking or the cleaning of input.
  5. Only if a meaningful group of misses cannot be fixed, trial a search service with the same list.

Keep the list. It becomes a regression test whenever you change the search.

Design details that matter more than the engine

  • Normalise input. Lowercase, strip accents, collapse spaces, and treat hyphens and slashes consistently.
  • Search what people say. Add nicknames or trade terms into a searchable field if staff use them.
  • Show why it matched. Highlight the matched part so staff trust unexpected results.
  • Handle zero results kindly. Offer "search all fields" or "include archived" rather than a blank screen.
  • Respect permissions. Apply the same access rules to search results as to the pages they open.
  • Log queries with no results. They are free product research.

FAQ

Is a spreadsheet-style filter box enough?

For under a few thousand rows and a single table, sometimes yes. Once people search across customers, jobs and notes together, a proper search design pays off.

Will database search slow down the rest of the app?

Not at this scale if the indexes exist. Watch query times during busy hours and add an index before adding hardware.

Can we switch later?

Yes, if the search is behind one function in your code. Keep it that way so changing the engine does not touch every screen.

What about AI-powered search?

Meaning-based search can help with long free-text notes. It is a layer on top of solid basics, not a replacement for exact code matching.

If your team keeps saying "I know the record is in there somewhere", describe your tool to us and we will suggest the lightest search setup that fits.

Sources

WhatsAppBook a call