Do you have a preferred one? We're currently on the big 3 US ones, in process of working on our 4th provider, our goal is very much to deliver the best Postgres experience whether on bare metal/on-premise or in the cloud, self hosted or fully managed. Edit: Always feel free to reach out directly, I'm always happy to spend time with anyone that has questions and usually pretty easy to track me down.
Dublin usually works well from a regulatory perspective.
They are the 52 state of america. The 51 was already taken.
Huh, just yesterday I blogged[0] about using FTS in SQLite[1] to search my PDF database. SQLite's full-text search is really excellent. The thing that tripped me up for a while was `GROUP BY` with the `snippet`/`highlight` function but that's the point of the blog post. [0] https://jcuenod.github.io/bibletech/2021/07/26/full-text-sea... [1] https://www.sqlite.org/fts5.html
A very well written article about SQLite FTS5! One question - it seems that your search result displays the matching paging number, how did you do that? because as far as I know, unlike FTS4, FTS5 has no `offsets` function.
I index each page individually. It's not in the article (because I also don't explain separating out the metadata) but I did weigh the options. I considered indexing page pairs, for example (and having every page indexed twice). But I figured that if search terms are broken across pages, they're likely to also be separated by text in headers & footers, page numbers, and footnotes. So in the end I decided to just index individual pages. The FTS table now has `id`, `page_number`, and `content` columns (where `id` is the foreign key to a table that stores the metadata).
Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being ab…
Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…
>"Used to work on Google Search ..."
Might you or anyone else have some recommendations for books or other resource on large scale search architecture that you think are are worthy reads on the subject?
I would also be interested in hearing if you or anyone else might ave any similar resources you could recommend on the subject of "search ranking"?
It's more a matter of configuring it right. I'd recommend trying out Elastic Cloud. It's a bit easier to deal with than Amazon's offering and much better supported. AWS has always been a bit hands-off on that front. Their opensearch project does not seem to break that pattern so far. Also, with Elastic Cloud you get some access to useful features for logging (like life cycle management and data streams) that will hel…
Thanks mate. I did tried Elastic before settling on AWS ES, it was slower somehow. As for Kibana features, im happy with barebones :) > you use e.g. file or docker beats for collecting logs. My setup is custom app reading from CW Logs. But can Elastic cluster scale automatically on indexing latency spikes, so my apps writes do not time out? If yes then how, please?
If you use life cycle management and data streams (which AWS doesn't have), you'd be able to control the sizes of your hot indices (i.e. the ones you write to). Basically keeping your hot indices small helps keeping things fast. If you have issues with app writes spiking, I'd use some queuing solution in between. Plenty of solutions for that.
The rest is just a matter of configuring things right in terms of number of shards and setting up properly. Basically, you get what you pay for in the end.
Silly question, I'm using pg right now and most of my queries are something like this (in english) Find me some results in my area that contain these categoryIds and are slotted to start between now and next 10 days. Since its already quite a filtered set of data, would that mean I should have little issues adding pg text search because with correct indexing and all, it will usually be applied to a small set of data?…
You might be just fine adding an unindexed tsvector column, since you've already filtered down the results. The GIN indexes for FTS don't really work in conjunction with other indices, which is why https://github.com/postgrespro/rum exists. Luckily, it sounds like you can use your existing indices to filter and let postgres scan for matches on the tsvector. The GIN tsvector indices are quite expensive to build, so do…
Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…
So let’s say that you are building a search engine for performance car parts. There are going to be a bunch of technical terms you use there that are not necessarily going to stand out in the document itself but you know them to be important. For example, the amount of boost pressure a turbo can provide or the number of pistons in a brake caliper. Is there some structured way to specify the grammar which is used for…
This subproblem is called "bigram detection", or more generally "n-gram detection" for phrases of > 2 words. I'm not going to give away Google's algorithms, but you should be able to look up public academic research with that keyword.
Used to work on Google Search, used ES extensively for a startup I founded (which was sort of quasi-search...it was built around feed ranking, where the query is constant and a stream of documents is constantly coming in), and have also used Postgres extensively in other companies. The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated…
>"Used to work on Google Search ..." Might you or anyone else have some recommendations for books or other resource on large scale search architecture that you think are are worthy reads on the subject? I would also be interested in hearing if you or anyone else might ave any similar resources you could recommend on the subject of "search ranking"?
"Managing gigabytes" is one of the standard introductory textbooks. It's quite dated now (published 1994, before Google was started, and hence why it says "gigabytes" rather than "exabytes"), but a lot of the fundamental concepts are the same. At least it'll give you a vocabulary and conceptual framework for thinking about search.
> Google's big insight is that how other people talk about a website is more important than how the website talks about itself, and its ranking algorithm weights accordingly. I'm having trouble believing that seeing how top results on opinionated keywords are all SEO spam of websites no one visits by themselves.
You don't remember what a game-changer Google was back in 2000 or so. Its results are pretty awful now, probably because of Goodhart's Law, but they were such a huge improvement over Jeeves et al..
An interesting exercise that most reasonably competent programmers can do now is to setup their own websearch engine with Common Crawl, Elastic MapReduce, JSoup or Gumbo or equivalent HTML parser, and ElasticSearch. It costs on the order of high-hundreds to low-thousands of $$$ to process the ~2B webpages in the Common Crawl corpus (about half the size of the first tier of Google's index) and stand up an ElasticSearch cluster with it for a few days.
You too can build your very own search engine.
Unfortunately, the result quality is roughly what AltaVista was like in 1995. That is why people keep going back to Google.
Thanks mate. I did tried Elastic before settling on AWS ES, it was slower somehow. As for Kibana features, im happy with barebones :) > you use e.g. file or docker beats for collecting logs. My setup is custom app reading from CW Logs. But can Elastic cluster scale automatically on indexing latency spikes, so my apps writes do not time out? If yes then how, please?
Autoscaling is of course something they can do: https://www.elastic.co/guide/en/cloud/current/ec-autoscaling... If you use life cycle management and data streams (which AWS doesn't have), you'd be able to control the sizes of your hot indices (i.e. the ones you write to). Basically keeping your hot indices small helps keeping things fast. If you have issues with app writes spiking, I'd use some queuing solution in be…
> If you use life cycle management and data streams (which AWS doesn't have), you'd be able to control the sizes of your hot indices (i.e. the ones you write to)
AWS has lifecycle stuff, a year ago. And index management is not cluster scaling, its a good advice, but not what I asked about.
Thanks mate. I did tried Elastic before settling on AWS ES, it was slower somehow. As for Kibana features, im happy with barebones :) > you use e.g. file or docker beats for collecting logs. My setup is custom app reading from CW Logs. But can Elastic cluster scale automatically on indexing latency spikes, so my apps writes do not time out? If yes then how, please?
Autoscaling is of course something they can do: https://www.elastic.co/guide/en/cloud/current/ec-autoscaling... If you use life cycle management and data streams (which AWS doesn't have), you'd be able to control the sizes of your hot indices (i.e. the ones you write to). Basically keeping your hot indices small helps keeping things fast. If you have issues with app writes spiking, I'd use some queuing solution in be…
To add. Your link to autoscaling describes storage scaling up, not the cluster scaling based on performance metrics.