What's the frequency of the letter "l" in:
Q: ElasticSearch vs SQL Server full text index for small datasets? 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. :)

A: 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".

user
In that passage, there are 97 occurrences of the letter "l."
assistant