p50/p99 retrieval times at realistic loads or it didn't happen. A search engine that returns results in, say, a minute is not "advanced". Of course a relational database like Postgres can do it on paper.
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.
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.
> "It is hard for less experienced developers to appreciate how rarely architecting for future requirements / applications turns out net-positive."
— John Carmack
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.
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.
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.
Without caching, the cost of operating the site would dramatically escalate.
It gets tough with pages with 100k+ comments though, so there are different tricks and switches for different flows and data sizes.
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...
> 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.
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.
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.
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...
Ranking is definitely easier when you also provide and moderate the content. That implies the technical solutions might differ qualitatively.
select *
from table
where ts_query(...)
order by relevance_metric
select *
from (
select *
from table
where ts_query(...)
order by ts_rank(...)
limit 1000
)
order by relevance_metric
limit 10Mind 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.