3 ms·

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)

by jordic 7y ago

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 ..

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)

by 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 ..