Rach Belaid, a software engineer, writes about PostgreSQL, including full-text search, storage internals, and monitoring, plus SQLAlchemy.
HN repeatedly cites her post saying Postgres full-text search is good enough, recommending it when you want modest search without another service.
Anectodal note:
A few years ago we added yet-another part to our product and, whilst ES worked "okay", we got a bit weary of ES due to "some issues" (some bug in the architecture keeping things not perfect in sync, certain queries with "joins" of types taking long, demand on HW due to the size of database, no proper multi-node setup due to $$$ and time constraint, etc.; small things piling up over time).
Bright idea: let's see how far Postgres, which is our primary datastore, can take us!
Unfortunately, the feature never made it fully into production.
We thought that on paper, the basic requirements were ideal:
- although the table has multiple hundreds of millions of entries, natural segmentation by customer IDs made possible individual results much smaller
- no weighted search result needed: datetime based is perfect enough for this use-case, we thought it would be easy to come up with the "perfect index [tm]"
Alas, we didn't even get that far:
- we identified ("only") 2 columns necessary for the search => "yay, easy"
- one of those columns was multi-language; though we didn't have specific requirements and did not have to deal with language specific behaviour in ES, we had to decide on one for the TS vectorization (details elude me why "simple" wasn't appropriate for this one column; it was certainly for the other one)
- unsure which one, or both, we would need, for one of the columns we created both indices (difference being the "language")
- we started out…
I probably wouldn't migrate a project, but I switched my default db to PostgreSQL after seeing the many ways MySQL can silently screw up data. Truncating strings, truncating numbers, allowing NULLs in a NOT NULL, etc. Much (all?) of this can be fixed with settings these days: https://dev.mysql.com/doc/refman/5.7/en/constraint-invalid-data.html
PostgreSQL has a hard bias toward correctness. If you give it invalid data, it will blow up loudly every time.
PostgreSQL also has a boatload of useful features. Need full-text search? It has it, and it's good enough for lots of stuff: http://rachbelaid.com/postgres-full-text-search-is-good-enough/
Need to search for locations with coordinates < N kilometers from a coastline? PostGIS does it.
Need to store and query JSON? Partition tables by a date range? Index only the rows matching a WHERE condition? Use a CHECK constraint? Prevent race conditions from creating reservation rows with overlapping datetime ranges (exclusion constraints)? Run a query periodically and cache the results as a queryable table (materialized views)? The list goes on and on.
More correct data + fewer reasons to add another tool to the stack - especially when that would mean having to keep data in sync - is pretty compelling to me.
> Why would one use Algolia over Postgres's built in text search features? Ex: http://rachbelaid.com/postgres-full-text-search-is-good-enou...
Postgres built-in search features are really for large document searching -- we've tried using it for indexing e.g. an array of tags for auto-complete and the search times were in the ~500ms+ range, as a matter of fact the non-indexed regex search was only slightly slower. I thought the later was rather surprising - but asking on the mailing-lists, I was told that this was basically working as intended.
edit: I don't fully understand the value-proposition of Algolia either, most of the trouble with going outside of your production database for search - is the problem of keeping your data in sync and current with the production database; compared to that setting up an Elasticsearch cluster really isn't such a big deal, there are also half-way solutions, for example Amazon has an Elasticsearch-service.
What other examples are there of "single dependency stacks"?
This article is really about the versatility and reliability of postgres.
And I'm all in agreement.
Reminiscent of:
https://www.craigkerstiens.com/2017/04/30/why-postgres-five-years-later/
https://webapp.io/blog/postgres-is-the-answer/
http://rachbelaid.com/postgres-full-text-search-is-good-enough/
http://boringtechnology.club/
As much as HN could lead you astray with the hype of this and that tech, articles like the above are some of the most consistently upvoted on this website.
Why would one use Algolia over Postgres's built in text search features? Ex: http://rachbelaid.com/postgres-full-text-search-is-good-enough/
I already have Postgres search queries running on the order of 1-3ms for large queries, basically faster than Algolia, with seemingly the same feature sets.
I don't understand the value proposition over something like Postgres, for sites that already have Postgres databases. (And I'm sure other databases might offer similar built-in search capabilities, I just don't know those other RDBMSs)
SEEKING WORK - San Francisco - Senior Software Engineer
Experienced Python developer with a good database knowledge, with experience in Ops and Javascript.
- Programming: Python, Javascript, Go
- Frameworks: Pyramid, SQLAlchemy, Django, React
- Software: Nginx, Postgres, Redis
- Experience: 10y
- Experimenting with: Swift, Haskell, Elixir, Phoenix
Download my resume and read more about me here: http://rachbelaid.com/about/
Blog : http://rachbelaid.com/
Github: https://github.com/rach