(disclaimer: author of the post)

I plan a follow up to compare it with Elasticsearch, however, I don't think I'm going to attempt benchmarking, because whatever realistic scenario I come up with, it will not necessarily be relevant to your use case.

I mostly agree with you and I probably wouldn't use this at large scale (say, more than a few million records). I was primarily interested how much of the functionality I can replicate. Because for small search use cases this has some clear advantages: less infra to maintain, strong consistency, joins, etc.

Also, at Xata we're thinking about having a smooth transition between using Postgres at small scale, then migrating to use Elasticsearch with minimal breaking changes.

Using measured comparisons and making informed choices is certainly the best way.

If you can start with Postgres to have a relational database with the benefit of Full Text Search (i.e. avoid Elastisearch) as well as JSON fields (i.e. avoid MongoDB) then you end up simplifying initial hardware/software requirements while retaining the ability to migrate to those solutions when user demand requires it.

So many developers seem to build with the idea that they'll become the next FAANG when actual (or reasonably forecasted) user load doesn't remotely require such a complex software stack.

I think you’re being ungenerous in the reasoning. The initial setup to get something running is similar while a specific technology may scale better. From that perspective, you may be better off picking ES to avoid paying back technical debt if you do need to grow. Early strategic investments help - sure, you won’t get everything right, but getting enough things right can be the difference between the sales channel being bottlenecks on deal flow vs on engineering. The former can be a lot faster to resolve than the latter.

No failed company ever failed because of a lack of technical complexity, nor have successful companies been held back by not adopting complex architectures early.

> "It is hard for less experienced developers to appreciate how rarely architecting for future requirements / applications turns out net-positive."

— John Carmack

https://en.m.wikipedia.org/wiki/You_aren%27t_gonna_need_it

I think picking lucene/es for search is hardly "architecting for future requirements" if your task is to build search functionality. Using Postgres FTS for search feels much more like cutting corners for me and an under-appreciation of the complexity inherent in full-text search, a complexity that ES solves competently and Postgres barely even addresses.

I'm not against picking the right tool for the job.

My only point is that making an architecture decision because it will immediately reduce complexity is much more sensible than basing your choice on potential future needs.

Evaluating needs from a "complexity reduction" standpoint is safe and will net returns. Evaluating needs from a "potential risks" standpoint is a lot harder, and easy to do wrong; the true risk is not growing at all, so the heuristic for any new project should be to do the simplest possible thing that solves the problem and starts the scaling process (i.e. whatever produces a saleable product).

The other benefit to starting with uncomplicated architecture is that you leave yourself with more scaling vectors, so once you deeply understand the problem(s) you actually need to solve, you can pick the right tool.

For us, Postgres FTS covered 95% of our use-cases. If we had started by just using ElasticSearch, we would have had a lot more complexity to maintain, and we would never have discovered our current (surprisingly elegant) architecture.

You're talking about application complexity when the parent is talking about operational complexity. The difference between some app servers talking to a Postgres instance with a replica and that plus an ES cluster is jump. If you're paying for something managed then it probably doesn't matter much to you. In my experience it's actually quite rare that teams working on a product where search is a feature and not the product itself ever graduate from what Postgres offers so I tend to push back on ES when it isn't free.

You may be able to hedge your bets by separating the Postures search DB from the OLTP. Perhaps it could use FDW or similar to make them appear the same from the app layer.

Then if FTS won't scale to unpredictable future needs it should be easier to rip out and replace with ES or anything else. And one doesn't have to pay that cost if/until it's certainly a requirement.

I had done something similar recently. Goal was to take a huge Postgres database and make it searchable and usable.

It ended up that doing a offline batch job to boil down the much bigger dataset into a single pre-optimized table was the best approach for us. Once optimized and tsvectored it was fairly performant and not a huge gain with Elastic. Still keeping the elastic code around “in case”, but yeah, Postgres search can be “good enough” when you aren’t serving a ton of clients.

Sure, benchmarks won't prove that it works in ALL cases, but it can at least prove it works in SOME cases?

I interviewed somewhere with an ex-Googler who revealed they cache all of the search results for terms they have seen before, then when they update the index they also update the cached results. From that perspective, fast search results aren't actually that exciting since you can constantly run a background task to update the cached results and just serve those as the requests come in. This caching and response time seem orthogonal to the speed of the actual act of calculating search results.

I work on Google Search infrastructure. Of course, caching is important, but a large percentage of queries don't hit the cache. The backend that serves results that aren't cached has very strict latency bounds.

> From that perspective, fast search results aren't actually that exciting since you can constantly run a background task to update the cached results and just serve those as the requests come in.

If that's how it worked, I agree, it wouldn't be that impressive (every search result would just be a 1-1 cache lookup). That's not how it works, though, and as someone who works adjacent to the system, it is pretty impressive how fast it is when the work it's doing is actually pretty expensive.

Almost everything is faster if you can precompute.

For sure, but the approach is quite viable. If 19 out of 20 searches by a user are almost instantaneous and single novel one requires a few seconds, they'll assume a hiccup in their internet connection and still view the site as "really fast". It's certainly useful for limiting demands on expensive hardware.

20% of all Google searches are brand new

Then 80% aren't, and those ones will perform very well.

The other 20% also perform well. I have quite literally never encountered a Google search that took more than some tens of milliseconds for a round trip.

You're absolutely correct. Caching common search queries allows the site to allocate hardware for processing "expensive" queries, with the objective of having both complete at near the same time.

Without caching, the cost of operating the site would dramatically escalate.

And use 80% of the resources?

Yup, all FastComments threads are precomputed for all sort directions! Each page comes from the DB in < 1.5ms 75% of the time.

It gets tough with pages with 100k+ comments though, so there are different tricks and switches for different flows and data sizes.

Your site is killing me. Every page I visit I have to accept the cookies :(

Fixed. Was an issue specific to certain countries - my bad!

Oh, that's weird...

I recall reading about the local transit department which had gotten some fancy accelerator card to help with route searches, ie when a customer wanted to go from A to B, which busses, trams etc to take.

Can't recall if it was "just" a bunch of FPGAs but it was a big-ass PCI card.

Some years later I tried to find this story again, and to check if they still used it. Turned out they had ditched it after just a couple of years. As memory sizes had increased, they could just precompute all possible routes for the next day and keep them all in memory...

which is why es uses multiple levels of caching

> p50/p99 retrieval times at realistic loads or it didn't happen.

Therein lies the problem - how do you generate actual realistic loads for a search engine without having a large number of people use it for searches? Simply hitting it with random search terms isn't realistic.

Some people will be on slow connections, search terms for something specific might spike in only a certain region (earthquake, etc), etc.

If your terms are too random, it'll perform worse than it should (results not in the cache), and if not random enough it will perform better than it should.

One actual solution is to use historical search logs. Just because "random" is a bad answer doesn't mean people don't try and make reasonable reproductions of load to replay and benchmark. Cacheing is also a big factor.

I don't know if this is true for elasticsearch, but at least with solr, when you update an index, the default is to run some of the queries in the cache of the old searcher to warm up the new one.

Lucene (what Elasticsearch and Solr use) are based on inverted indexes which is what the GIN in the article also uses.

So the benefits of ES/etc are being able to scale horizontally scale across nodes or any additional features it adds on top of the main index.

We use postgres fts and it works fine, you just gotta how you rank order the rows in the query - if you only use ts_rank then it's perfect but you likely want to use some other relevance metric to adjust the ranking but then you can't rank order primarily by that metric. Once you nail this the results are as fast as any other typical db table query with an index.

Actually good ranking is the most valuable thing in search engine.

What I mean is you can't do

    select * 
      from table 
     where ts_query(...)
  order by relevance_metric

but instead do

    select * 
      from (
        select * 
          from table 
         where ts_query(...) 
      order by ts_rank(...)
         limit 1000
    ) 
  order by relevance_metric 
     limit 10

Good ranking is what made Google a trillion dollar company.

Google indexed the same sites as Altavista, but Google Page Rank made the right sites bubble to the top and made Sergey and Larry billionaires...

You’re right. But at the same time that quality seems to decline or at least stagnate. I think they face the hard problem of an decreasing signal to noise ratio.

Ranking is definitely easier when you also provide and moderate the content. That implies the technical solutions might differ qualitatively.

I've tried using PG FTS in a real application, it didn't scale very well. It worked well for 100s-1000s of records, but after that queries would quickly go from a few ms to like 500ms. And another 400ms for highlighting.

Mind you, this was 10-15 years ago, so thing will have changed and improved. I know the indices have become a lot faster since then.

I've built something like this before, and it was pretty fast. What load could one node have supported, I have no idea, but read-only operations can be spread across replicas easily.