tbh I haven't looked into PostgreSQL as a direct competitor to ES in the text search space, mostly because I have quite a lot of ES experience of things like aggregation for faceting, stemming and textual analysis, and I had a (perhaps incorrect) assumption that PostgresSQL was less capable in those more specialised areas.
One thing I would like to understand is how the PostgreSQL search experience scales with data volume.
My main cluster for CTX will potentially get (very) big and I don't know enough about PostgreSQL, multi-master and full-text search across large amounts of data.
> tbh I haven't looked into PostgreSQL as a direct competitor to ES in the text search space, mostly because I have quite a lot of ES experience of things like aggregation for faceting, stemming and textual analysis, and I had a (perhaps incorrect) assumption that PostgresSQL was less capable in those more specialised areas.
I think your assumption is/was correct. And I'm a postgres dev, so I'm biased as hell...
PostgreSQL is very capable at stemming and textual analysis, but the issue as you likely know starts with large amounts of data – because then you suddenly need multiple replicas, and that’s where the issues start.
You might want to look at https://github.com/zombodb/zombodb. It extends PG to use ES as an external source for indexes. So you can do an ES based text search and return a data stored in PG along with doing joins to other tables. Zombo even works with Citus to handle PG level sharding.
One thing I would like to understand is how the PostgreSQL search experience scales with data volume. My main cluster for CTX will potentially get (very) big and I don't know enough about PostgreSQL, multi-master and full-text search across large amounts of data.
I'll definitely do some digging though, thanks!