I've got a relatively high-traffic public-facing product-based website backed by SQL Server as the point of authority. It has some search capability on some of the columns of the Items table (year, color, that kind of thing), and then a need for a full text search on the Description column (also in the Items table, each description is typically less than a kilobyte). At any given point in time, there are ~30k active rows in the Items table.

The search and search results are done using ElasticSearch. I asked the other dev when I started about this, and why not just use SQL Server itself; his response was that full text indexes in SQL Server are "icky," and since I had other stuff to do, I shrugged and went on with life.

That developer has since left, so now it is my responsibility. In the quest of simplifying things, I'm thinking about removing ElasticSearch and going directly to SQL. For a low rowcount (again, 30k active records), does ElasticSearch make any sense over SQL Server, other than "Don't fix what isn't broken"? What if we increase the active rowcount to 100k? Are there performance gotchas I might not be aware of in SQL Server full text search that ElasticSearch doesn't have at that level?

For the sake of this question, let's assume there isn't enough memory to make in-memory-caching work. :)

  • Why don't you just get the profile of the most common queries, and then run them at volume against ES and FTI and compare results? with 30k this doesn't sound like a complex exercise.
    – zaitsman
    Commented Mar 12, 2017 at 8:16
  • 1
    I could set the tests up, sure, and find out an isolated test proves me right, but I don't have a ton of experience with either one (sql queries, yeah... full text search, not so much). I'd rather have some idea that there's no reason why this isn't a long-term viable decision, and try to understand if there's some reason why the choice for ElasticSearch was made (my own opinion, it smells like someone padding their resume, rather than a real technical need)
    – Jorick918
    Commented Mar 13, 2017 at 0:02
  • Your question is really a which technology is better, which is off-topic.
    – JeffO
    Commented Mar 14, 2017 at 2:35
  • 1
    Sorry.. I saw guidance that software engineering was the opinion board, and tons of "Which should I..." and "Why should I..." type questions already posted.
    – Jorick918
    Commented Mar 14, 2017 at 2:45

1 Answer 1


Keep it how it is. If it ain't broke, don't fix it.

Generally, "simplicity" is very important. To illustrate this reasoning, let's take it to an extreme:

  • You have 30k records
  • You have 30 servers that do all kinds of specialised things (like ElasticSearch)
  • You gain 10% benefits on every factor
  • The customer barely notices the 10% benefit.

In this extreme scenario, you have way higher complexity for little benefit. As you note, when the ElasticSearch expert leaves, your job becomes more complicated.

You definitely need at least the Database - the minimum "servers" is 1. The simplest solution is to use the Full-Text search capability.

However, in your situation, it's not a simple choice. You actually already have ElasticSearch, and here, "changing things" costs more. You don't have 30 systems, you have 2 (perhaps). Generally, you should throw more money at SysOps, and not spend money on programmers fixing a perceived SysOps problem. Programmers cost more money than it seems, and programmers create bugs.

To take it to an extreme again:

  • You have 30k records
  • There is no major problem
  • You are going to rebuilt the "whole" system from scratch.

You might feel a little out of place managing ElasticSearch, but that's you. With some training you should be able to manage. You might also consider moving to a more managed ElasticSearch hosting solution - so you are only using the "service" and you don't need to manage, patch, and backup "servers".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.