It began, as most ambitious projects do, with a meeting. The stakeholders had seen the competition. They had seen autocomplete — fast, sleek, and seemingly magical. Naturally, they wanted one too.

“Can we do this?” they asked, wide-eyed with enthusiasm.

The dev team exchanged wary glances. Sure, autocomplete is great, but the devil’s in the details. And, in this case, the detail was our infrastructure — or lack thereof. We didn’t have Elasticsearch or any fancy distributed search engine. What we had was a SQL database with a million records and a mandate to keep costs low. Challenge accepted.

The Challenge: Big Ideas, Small Budgets

The stakeholders wanted autocomplete, but they also wanted it cheap. That ruled out solutions like Elasticsearch, which — let’s be honest — was our dream solution. As developers, we’d used Elasticsearch before on another project, and it was a game-changer. Fast, scalable, and reliable. We knew it could handle a growing dataset like ours with ease, and we were eager to bring that level of sophistication to this project.

We also had a bit of a competitive streak. It wasn’t just about solving the problem; it was about proving to ourselves (and, let’s admit, our stakeholders) that we could build something better than the competition. Their autocomplete might be good, but ours? Ours would be faster, smarter, and, frankly, cooler.

Even though we knew the company didn’t have the budget, we couldn’t resist the urge to crunch the numbers and pitch Elasticsearch as the solution. After all, we wanted to show our stakeholders that we weren’t just talented — we were visionary. We did our homework and calculated the cost of the minimum Elasticsearch instance we’d need: 240 GB of storage, 8 GB of RAM, and up to 4.4 vCPUs spread across two zones. The hourly rate came out to $0.5878, which added up to about $500 per month for infrastructure alone.

But that wasn’t the full picture. On top of the infrastructure costs, there were development and integration costs, which we estimated at $XXXX (let’s just say it wasn’t pocket change). When we presented the numbers to the stakeholders, their excitement faded. As much as they wanted an Elasticsearch-powered search bar, the price tag was too steep.

But while the stakeholders appreciated our enthusiasm, reality hit hard: there just wasn’t room in the budget for Elasticsearch. No matter how much we tried to emphasize its benefits, it was a “nice-to-have,” not a “must-have.”

For a moment, we felt deflated. We knew we could deliver something extraordinary with Elasticsearch, and we wanted to prove our skills. But this wasn’t about what we wanted — it was about what the business needed and could afford. We had to accept the challenge as it stood: work with the SQL database we already had and find a solution that didn’t break the bank.

So, with a mix of determination and a little leftover disappointment, we rolled up our sleeves and got to work. If we couldn’t use Elasticsearch, we’d push SQL to its limits and build something we could still be proud of.

Experimenting with SQL Full-Text Search

When we started working on the autocomplete feature using SQL full-text search, it felt a bit like the product team had asked us to cook a gourmet meal without a stove. Sure, we had the ingredients — our trusty SQL database and a decent understanding of full-text search — but the tools didn’t quite match the ambition of the task. We were missing the bells and whistles that tools like Elasticsearch would have brought to the table.

Still, we rolled up our sleeves and got to work. SQL full-text search wasn’t built for autocomplete, but it had some potential. After digging into its documentation and experimenting with a few queries, we realized that it could handle basic text searches and even provide relevance-based scoring. It wasn’t perfect, but it gave us just enough of a foundation to be hopeful.

The first few attempts were rough. Our initial queries returned way too much irrelevant data — think addresses when we were with some document identifiers where users search by partial numeric strings, the results were even worse. At one point, we were staring at a list of what looked like random entries, scratching our heads and wondering if SQL full-text search was a mistake altogether.

But trial and error is the lifeblood of development, and we weren’t ready to throw in the towel. We broke the problem into smaller pieces, refining our approach for each type of search. For addresses, we experimented with wildcards and boolean operators, finally landing on a combination that prioritized relevant matches without overwhelming the system. For numeric fields we took a different approach, using regular expressions and indexed lookups to improve performance and accuracy.

Every success came with its own challenges. Wildcards improved address searches, but they slowed down the queries. Adding indexing helped speed things up, but it required tweaking the database schema, which added complexity. It felt like every step forward came with another problem to solve.

Eventually, after many late nights, cups of coffee, and a few moments of near-defeat, we came up with a set of queries that could handle searches by multiple fields like address, case numbers, and different identifiers. The code wasn’t elegant (yet), but it worked.

The Results: Mixed, but Promising

We ran tests on our 1-million-record database. For most queries, response times ranged from 200ms to 3.6 seconds, with popular searches often coming back under 1 second. However, edge cases — unusual searches with lots of potential matches — stretched to 5 seconds. Not ideal.

The dev team wasn’t thrilled. “It’s okay,” we said, with all the enthusiasm of someone describing plain toast. Still, we built a proof-of-concept (POC) to let the stakeholders try it for themselves.

The POC: Winning Over the Stakeholders

The POC was a simple page with a search bar powered by our SQL full-text implementation. It wasn’t pretty, but it worked. The stakeholders tested it out, typing in their most common queries.

For popular searches, the response time hovered around 600ms — plenty fast. Even the rare 5-second delays didn’t faze them. “This is great,” they said. “Can we integrate it?”

The dev team, who had been skeptical of the results, suddenly felt like heroes. If the stakeholders were happy, we were happy.

Integration: Making It Production-Ready

With the green light, we integrated the autocomplete feature into the platform. To smooth out performance, we added some frontend optimizations:

• Debouncing: This ensured the search wasn’t triggered with every keystroke, reducing unnecessary server calls.

• Client-Side Caching: Frequently queried fields having small number of possible values like Postcodes, states, and cities were stored locally to improve speed.

What We Learned: Embracing “Good Enough”

SQL full-text search isn’t perfect, and we know it won’t scale forever. When the database hits 5 million records, response times will increase, and we’ll likely need to revisit Elasticsearch or another advanced solution. But for now, this implementation strikes the right balance between cost, simplicity, and performance.

The real lesson? Sometimes, “good enough” is exactly what you need. Not every problem demands a perfect solution — especially when your stakeholders are thrilled with the results.

The feature can be seen live at www.propertyonion.com. There is no plan to move to elastic search till end of 2025.

Looking back, the journey to build autocomplete was as much about teamwork as it was about technology. Sure, the dev team started off skeptical, but seeing the stakeholders’ excitement reminded us why we do what we do.

So the next time you’re typing into a search bar and those suggestions pop up, spare a thought for the devs behind the scenes. Sometimes, it’s not about fancy tech or perfect solutions — it’s about getting the job done, even on a soda budget.