3 ms·

Really well put together- I learned several more things about PG text searching from this. From my personal experience working with dozens of California tech c

by jadbox 7y ago

Really well put together- I learned several more things about PG text searching from this. From my personal experience working with dozens of California tech companies of varying sizes, I'd say around 80% of the time I see ElasticSearch being used when the product could have just used their existing PG cluster (and existing team experience). I.e. so many companies pick up ElasticSearch only because they lack of full-text search capability awareness of PG. Of course, ES does have its advantages, but I feel it's in very specific use-cases.

jordic 7y ago
Postgresql is awesome, but I don't see any advantage when you need to add like 40 aggregations plus joining to 10 tables.. (easy if you denormalize to elastic)
- mistahenry 7y ago
  I can think of a bunch off the top of my head that mostly stem from a serious reduction in complexity: - having one data store instead of two - no data store synchronization and related consistency headaches - the ability to easily join/restrict based on business logic (especially access control) - easier deployments - easier qa/local setups - fewer failure scenarios - although non-standard, you're dealing with sql and can explain/analyze your queries with tools one already knows if one understands postgres - the ability to extend my existing (Spring Boot) integration tests to test new searching logic without having to worry about a separate test db. I've been building a travel blogging platform as a side project that has about ~250 users. Through a couple of choice materialized views to create search documents from a number of different tables, Postgres FTS, and some GIN indices, I was able to make a lighting fast search with intelligent suggestions(ie using user specific restrictions such as follower/following, places a user has posted, etc). Is my search perfect? No. I could do a better job normalizing diacritics, stemming could be improved, etc. But it was dead simple to implement well enough in a very short amount of time. I'm very happy with my current solution. There's a time and place for a dedicated search solution but it's certainly not in fledgling systems without massive load.
  - aldoushuxley001 7y ago
    Ever consider doing a write up of how you implemented your Postgres FTS? Sounds like you’ve had a good experience with it.
  - jordic 7y ago
    Missed the part about serving the response bellow 50ms :) Not too much traffic.. between 40/50 req/s.. and a lot of other more important things to be stored on postgres (transactions, stocks.. )
- e12e 7y ago
  How do you denormalize to elastic without preforming joins on your normalized data?
  - jordic 7y ago
    Haha, just joining them ..

by jadbox 7y ago

jordic 7y ago

Postgresql is awesome, but I don't see any advantage when you need to add like 40 aggregations plus joining to 10 tables.. (easy if you denormalize to elastic)

mistahenry 7y ago
I can think of a bunch off the top of my head that mostly stem from a serious reduction in complexity: - having one data store instead of two - no data store synchronization and related consistency headaches - the ability to easily join/restrict based on business logic (especially access control) - easier deployments - easier qa/local setups - fewer failure scenarios - although non-standard, you're dealing with sql and can explain/analyze your queries with tools one already knows if one understands postgres - the ability to extend my existing (Spring Boot) integration tests to test new searching logic without having to worry about a separate test db. I've been building a travel blogging platform as a side project that has about ~250 users. Through a couple of choice materialized views to create search documents from a number of different tables, Postgres FTS, and some GIN indices, I was able to make a lighting fast search with intelligent suggestions(ie using user specific restrictions such as follower/following, places a user has posted, etc). Is my search perfect? No. I could do a better job normalizing diacritics, stemming could be improved, etc. But it was dead simple to implement well enough in a very short amount of time. I'm very happy with my current solution. There's a time and place for a dedicated search solution but it's certainly not in fledgling systems without massive load.
- aldoushuxley001 7y ago
  Ever consider doing a write up of how you implemented your Postgres FTS? Sounds like you’ve had a good experience with it.
- jordic 7y ago
  Missed the part about serving the response bellow 50ms :) Not too much traffic.. between 40/50 req/s.. and a lot of other more important things to be stored on postgres (transactions, stocks.. )
e12e 7y ago
How do you denormalize to elastic without preforming joins on your normalized data?
- jordic 7y ago
  Haha, just joining them ..