5 ms·

Hi, I started an Elasticsearch hosting company, since sold, and have built products on PG's search and SQLite FTS search. There are in my mind two reasons to n

by fizx 5y ago

Hi, I started an Elasticsearch hosting company, since sold, and have built products on PG's search and SQLite FTS search. There are in my mind two reasons to not use PG's search. 1. Elasticsearch allows you to build sophisticated linguistic and feature scoring pipelines to optimize your search quality. This is not a typical use case in PG. 2. Your primary database is usually your scaling bottleneck even without adding a relatively expensive search workload into the mix. A full-text search tends to be around as expensive as a 5% table scan of the related table. Most DBAs don't like large scan workloads.

quietbritishjim 5y ago
Do you see any particular reasons to use or not use ZomboDB [1]? It claims to lets you use ElasticSearch from PG seamlessly e.g. it manages coherency of which results ought to be returned according to the current transaction. (I've never quite ended up needing to use ES but it's always seemed to me I'd be likely to need ZomboDB if I did.) [1] https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
- mixmastamyk 5y ago
  You can do anything, anything at all, at https://zombo.com/ https://zombo.com/ "The only limit, is yourself…"
  - habibur 5y ago
    It's still around?
    - mixmastamyk 5y ago
      Klicken-Sie Link.
    - mumblemumble 5y ago
      That seems like one for the philosophers. If you completely rewrite a Flash site in HTML5, but it looks the same and has the same URL, is it still the same site?
      - da_chicken 5y ago
        So, YouTube?
  - FpUser 5y ago
    This one is my favorite for many many years. Good for relaxing.
- Conlectus 5y ago
  Though I have only a recreational interest in datastores, I would be pretty wary of a service that claims to strap a Consistant-Unavailable database (Postgres) to an Inconsistent-Available database (ElasticSearch) in a useful way. Doing so would require ElasticSearch to reach consensus on every read/write, which would remove most of the point of a distributed cluster. Despite this, ZomboDB's documentation says "complex aggregate queries can be answered in parallel across your ElasticSearch cluster". They also claim that transactions will abort if ElasticSearch runs into network trouble, but the ElasticSearch documentation notes that writes during network partitions don't wait for confirmation of success[1], so I'm not sure how they would be able to detect that. In short: I'll wait for the Jepsen analysis. [1] https://www.elastic.co/blog/tracking-in-sync-shard-copies#:~:text=chooses%20write%20availability https://www.elastic.co/blog/tracking-in-sync-shard-copies#:~...
  - zombodb 5y ago
    > Doing so would require ElasticSearch to reach consensus on every read/write ZomboDB only requires that ES have a view of its index that's consistent with the active Postgres transaction snapshot. ZDB handles this by ensuring that the ES index is fully refreshed after writes. This doesn't necessarily make ZDB great for high-update loads, but that's not ZDB's target usage. > They also claim that transactions will abort if ElasticSearch runs into network trouble... I had to search my own repo to see where I make this claim. I don't. I do note that network failures between PG & ES will cause the active Postgres xact to abort. On top of that, any error that ES is capable of reporting back to the client will cause the PG xact to abort -- ensuring consistency between the two. Because the ES index is properly refreshed as it relates to the active Postgres transaction, all of ES' aggregate search functions are capable of providing proper MVCC-correct results, using the parallelism provided by the ES cluster. I don't have the time to detail everything that ZDB does to project Postgres xact snapshots on top of ES, but the above two points are the easy ones to solve.
    - mrslave 5y ago
      ZomboDB scratches an itch in a way I find fascinating, though I have yet to do more with it than shoehorn it into a prototype that was a bit square-peg-in-a-round-hole. It is in my catalogue of technologies I hope exploit someday. And I hope you're enjoying the work and making some $$$ too. While I'm here might I ask, are you finding the hosted PostgreSQL services (AWS, Azure, etc.) growing or shrinking your market opportunities? Also, does it play nice with Citus?
NortySpock 5y ago
Regarding point 2: Shouldn't you be moving your search queries from your transaction server to a separate analysis or read-replica server? OLTP copies to OLAP and suddenly you've separated these two problems.
- blowski 5y ago
  …and created a new one if the projection is only eventually consistent. No free lunches here.
  - zepolen 5y ago
    How is that different from running Postgres and Elasticsearch separately?
    - blowski 5y ago
      It’s not. It’s a difference between having separate OLAP and OLTP databases, which is what the parent post suggested.
    - justinclift 5y ago
      Probably skill set of staff? For example, if a place has developed fairly good knowledge of PG already, they can "just" (!) continue developing their PG knowledge. Adding ES into the mix though, introduces a whole new thing that needs to be learned, optimised, etc.
kurko 5y ago
I agree with reason 1, but reason 2 is an answer for, "should I use PG search in the same PG instance I already have", and that's a different discussion. You can set up a replica for that.
pmarreck 5y ago
> sophisticated linguistic and feature scoring pipelines to optimize your search quality You CAN score results using setweight, although it's likely not as sophisticated as Elasticsearch's https://www.postgresql.org/docs/9.1/textsearch-controls.html https://www.postgresql.org/docs/9.1/textsearch-controls.html Disclaimer: I use Postgres fulltext search in production, very happy with it although maintaining the various triggers and stored procs it requires to work becomes cumbersome whenever you have to write a migration that alters any of them (or that in fact touches any related field, as you may be required to drop and recreate all of the parts in order not to violate referential integrity) It is certainly nice having not to worry about 1 additional dependency when deploying, though
busymom0 5y ago
Regarding point 2, my implementation was to have a duplicate database server where all the search queries are sent. This would ensure that the search wouldn't slow down the main database. And most of the duplication would happen quick enough so that the search results were almost up to date.

by fizx 5y ago

quietbritishjim 5y ago

Do you see any particular reasons to use or not use ZomboDB [1]? It claims to lets you use ElasticSearch from PG seamlessly e.g. it manages coherency of which results ought to be returned according to the current transaction. (I've never quite ended up needing to use ES but it's always seemed to me I'd be likely to need ZomboDB if I did.) [1] https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb

mixmastamyk 5y ago
You can do anything, anything at all, at https://zombo.com/ https://zombo.com/ "The only limit, is yourself…"
- habibur 5y ago
  It's still around?
  - mixmastamyk 5y ago
    Klicken-Sie Link.
  - mumblemumble 5y ago
    That seems like one for the philosophers. If you completely rewrite a Flash site in HTML5, but it looks the same and has the same URL, is it still the same site?
    - da_chicken 5y ago
      So, YouTube?
- FpUser 5y ago
  This one is my favorite for many many years. Good for relaxing.
Conlectus 5y ago
Though I have only a recreational interest in datastores, I would be pretty wary of a service that claims to strap a Consistant-Unavailable database (Postgres) to an Inconsistent-Available database (ElasticSearch) in a useful way. Doing so would require ElasticSearch to reach consensus on every read/write, which would remove most of the point of a distributed cluster. Despite this, ZomboDB's documentation says "complex aggregate queries can be answered in parallel across your ElasticSearch cluster". They also claim that transactions will abort if ElasticSearch runs into network trouble, but the ElasticSearch documentation notes that writes during network partitions don't wait for confirmation of success[1], so I'm not sure how they would be able to detect that. In short: I'll wait for the Jepsen analysis. [1] https://www.elastic.co/blog/tracking-in-sync-shard-copies#:~:text=chooses%20write%20availability https://www.elastic.co/blog/tracking-in-sync-shard-copies#:~...
- zombodb 5y ago
  > Doing so would require ElasticSearch to reach consensus on every read/write ZomboDB only requires that ES have a view of its index that's consistent with the active Postgres transaction snapshot. ZDB handles this by ensuring that the ES index is fully refreshed after writes. This doesn't necessarily make ZDB great for high-update loads, but that's not ZDB's target usage. > They also claim that transactions will abort if ElasticSearch runs into network trouble... I had to search my own repo to see where I make this claim. I don't. I do note that network failures between PG & ES will cause the active Postgres xact to abort. On top of that, any error that ES is capable of reporting back to the client will cause the PG xact to abort -- ensuring consistency between the two. Because the ES index is properly refreshed as it relates to the active Postgres transaction, all of ES' aggregate search functions are capable of providing proper MVCC-correct results, using the parallelism provided by the ES cluster. I don't have the time to detail everything that ZDB does to project Postgres xact snapshots on top of ES, but the above two points are the easy ones to solve.
  - mrslave 5y ago
    ZomboDB scratches an itch in a way I find fascinating, though I have yet to do more with it than shoehorn it into a prototype that was a bit square-peg-in-a-round-hole. It is in my catalogue of technologies I hope exploit someday. And I hope you're enjoying the work and making some $$$ too. While I'm here might I ask, are you finding the hosted PostgreSQL services (AWS, Azure, etc.) growing or shrinking your market opportunities? Also, does it play nice with Citus?

NortySpock 5y ago

Regarding point 2: Shouldn't you be moving your search queries from your transaction server to a separate analysis or read-replica server? OLTP copies to OLAP and suddenly you've separated these two problems.

blowski 5y ago
…and created a new one if the projection is only eventually consistent. No free lunches here.
- zepolen 5y ago
  How is that different from running Postgres and Elasticsearch separately?
  - blowski 5y ago
    It’s not. It’s a difference between having separate OLAP and OLTP databases, which is what the parent post suggested.
  - justinclift 5y ago
    Probably skill set of staff? For example, if a place has developed fairly good knowledge of PG already, they can "just" (!) continue developing their PG knowledge. Adding ES into the mix though, introduces a whole new thing that needs to be learned, optimised, etc.

kurko 5y ago

I agree with reason 1, but reason 2 is an answer for, "should I use PG search in the same PG instance I already have", and that's a different discussion. You can set up a replica for that.

pmarreck 5y ago

> sophisticated linguistic and feature scoring pipelines to optimize your search quality You CAN score results using setweight, although it's likely not as sophisticated as Elasticsearch's https://www.postgresql.org/docs/9.1/textsearch-controls.html https://www.postgresql.org/docs/9.1/textsearch-controls.html Disclaimer: I use Postgres fulltext search in production, very happy with it although maintaining the various triggers and stored procs it requires to work becomes cumbersome whenever you have to write a migration that alters any of them (or that in fact touches any related field, as you may be required to drop and recreate all of the parts in order not to violate referential integrity) It is certainly nice having not to worry about 1 additional dependency when deploying, though

busymom0 5y ago

Regarding point 2, my implementation was to have a duplicate database server where all the search queries are sent. This would ensure that the search wouldn't slow down the main database. And most of the duplication would happen quick enough so that the search results were almost up to date.