Bonsai Blog | Fully Managed Elasticsearch & OpenSearch • How to Really Design Search for a Database

Contact

Login

Sign up

June 24, 2026

How to Really Design Search for a Database

Max Irwin<br>Architecture,

Saas<br>21 min read

Ah, the birth of a new search. It is a glorious thing. I recently had the pleasure of building out our internal admin search for Bonsai's new SaaS application with OpenSearch, and wanted to share my insights.

The problem we're solving here is that we've got lots of data spread around in various relational tables, and tuning relevance across all these structures is quite tricky. The techniques in this post work for Postgres, SQLite, MSSQL, MySQL, MariaDB, Oracle, DB2, and any other RDMBS. In this post, you'll learn why search is a problem best solved by a search engine, and not just tacked on to your tables in the database using some full text feature or plugin. You'll also learn why vector search may be a poor choice for highly structured relational data.

I've recently done lots of big texty content and vectors and hybrid, but SaaS search is a horse of a different color. It's been a while since I've designed search for an application like this, and in my research I once again had some eyebrow raising revelations that there just isn't a good guide out there for this use case. In this article, I'm going to tell you how to do it right (and how to do it wrong).

For our example, we're going to use an open music database to search. Let's see a quick demo:

This demo is actually usable in this page. Try it out by searching for some music! 👇

Try the interactive search above, or go to a full demo!

Before we get into how to do it right, I'll tell you how to do it wrong. Most people make two design mistakes when implementing search for their database:

They make a different index per table (or use in-database indexing on a table)

They use nested fields, to mimic hierarchical structure in their database

I blame these common mistakes on actual recommendations that Elasticsearch was espousing 15 or so years ago, before we knew better. Back when doc types were still a thing, that was all the rage - a new doc type for each table! After doc types finally got removed, for some reason, people switched to assuming a separate index was the thing to do. As for nested fields, well, if your data is hierarchical, then why not? But little do most people know - nested fields are terrible. They are slow and make things over-complicated.

These mistakes are easy to make if you're not a battle hardened search nerd like yours truly, because it's natural to think relationally about data if that's how your data is structured. But search is not relational! It's a relevance bucket that works best when you design for discovery and not relationships. And, if you put different tables in different indexes and nest fields, tuning for relevance and experience becomes very difficult, especially as your database grows and evolves.

One Index to Rule Them All

Having a single index for your entire database is not only good design for search, but it also follows good software architecture principles: separation of concerns. We don't couple the design of the search index to the design of the database. Instead we focus on the information needs of the person who needs to find things, design the index appropriately, and add data and tune the query.

But weirdly, SaaS info needs are pretty consistent in most cases at a base level - people need to find records of information! When you think about it, you can design a search index that hits most of these information needs without even knowing much about the application, and then grow from there.

So I'm going to do something now that I usually don't do. I'm going to hit you with a schema right off the bat. I don't even know what your database looks like, but this will get you most of the way there, and seeing an end result will make the above point crystal clear. After we review the schema, we'll look at a database example and show how the design really clicks.

Baseline Search Schema for any Database

FieldTypeDescription<br>id keywordThe unique identifier to look stuff up<br>type keywordThe name of the table the record came from<br>permissions keywordWho has access to see it?<br>url urlAn easy url to point to the actual record page from results<br>names entityEntities (column values) that people want to find<br>emails emailIf your table has an email address, it goes here<br>notes textIf your table has long text, it goes here<br>aka entity"Also Known As" - alternate names and recall for the record<br>address entityIf your table has an address, it goes here<br>amount numericThe most important number in your table - i.e. prices, stock, downloads, etc.<br>created dateMatches your table's created timestamp<br>updated dateMatches your table's updated timestamp<br>deleted dateMatches your table's soft-deleted timestamp<br>details objectA json object that keeps unsearchable details that you want to show in results

Yup,...