7 ms·

Looking forward for part 2 / postgres vs elasticsearch. One application at my company uses PG for CRUD on objects and elastic for searching them. We completely

Looking forward for part 2 / postgres vs elasticsearch. One application at my company uses PG for CRUD on objects and elastic for searching them. We completely underestimated the effort of keeping the two datastores in sync and are actually contemplating to get rid of elasticsearch.

cameronpm 3y ago
Normally best practice is to return primary keys in ES and then do a lookup query in the db. Did you attempt this?
- rajamaka 3y ago
  Got any links or documentation about this method by any chance? Not sure if you are talking about querying or syncing data here.
  - lyu07282 3y ago
    I think what they just mean is that you only have all your searchable fields indexed in ES and then, when you do a search query, the only thing ES returns is PKs which you then do a second roundtrip to the database (or cache) with to get all the actual records from. Which means you don't have to worry too much about all the record information being in sync perfectly.
dqv 3y ago
>We completely underestimated the effort of keeping the two datastores in sync and are actually contemplating to get rid of elasticsearch. Can you expand on this? Is it that it's tedious to write code that updates both? I've been meaning to play around with meilisearch and was trying to think about the synchronization issue.
valzam 3y ago
I had a similar setup at a previous gig and didn't find it particularly challenging. Whenever an update happens to an entity in PG we send a message to async replicate that entity in ES (via ID lookup in PG). As always you need good monitoring and retries for async computation but ES is quite stable and fast so we rarely had any issues. Now, we had pretty lax consistency requirements, as long as the "latest state" of PG ended up in ES within a reasonable timeframe everything was fine so maybe your requirements are different.
- hn_throwaway_99 3y ago
  Yeah, I think more folks should learn about postgres LISTEN and NOTIFY functionality, https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html, it's great for stuff like this, and there are now various client libraries like pg-listen in NPM that make this really easy to use. This is especially useful if you have lots of different teams/code paths that may update your DB - just set up a trigger that causes a NOTIFY message to get sent, then have a client responsible for reading for PG and populating ES. Alternatively, if you can accept a bit more latency, just have a trigger that sets a "needsESIndexing" dirty column somewhere and have a polling process that picks rows WHERE needsESIndexing = TRUE and just updates this to FALSE when the indexing is complete.
  - valzam 3y ago
    Or a bit more 'web scale's use wal_insert and the outbox pattern to transactionally replicate events to a messaging system (Kafka etc).
    - lyu07282 3y ago
      you can also just use a CDC to import from Postgres into Kafka using Debezium and then on the other end Kafka Connect to write into Elasticsearch, no need to write any code at all with this setup ymmv ofc
      - paulryanrogers 3y ago
        Sounds like a lot of moving parts though, unless the team is already familiar with maintaining all those parts
        lyu07282 3y ago
        if you already use Kafka anyway its a valid solution, I wouldn't introduce Kafka for this obviously. Also Kafka Connect and Debezium are mature well maintained open source projects in case that wasn't clear enough. https://debezium.io/ https://debezium.io/ https://docs.confluent.io/platform/current/connect/index.html https://docs.confluent.io/platform/current/connect/index.htm...
jarym 3y ago
Curious, did you try zombodb? [https://www.zombodb.com/ https://www.zombodb.com/]
- worthless-trash 3y ago
  For a moment I thought it was from zombo.com
  - mekoka 3y ago
    The site was updated just to replace flash and add TLS. Amazing!
softwaredoug 3y ago
You might trade for a different problem, which is that search could end up being a very different query load on the Postgres cluster compared to existing queries. That might make it hard to scale it out for both use cases.
- selcuka 3y ago
  You can always have a separate, streaming replica [1] used for full text searches. [1] https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#ST...
devoutsalsa 3y ago
The easiest log retention setup I've ever used was just a server w/ logs dumped into a text file searched by zgrep. I'm not it's not ideal for all use cases, but I miss the simplicity it.
- chrismarlow9 3y ago
  This can be done and centralized with rsyslog and it's magical. And yes it can scale.
pmarreck 3y ago
This is exactly why I (as a solo developer at the time) just used Postgres' built-in fulltext search when that was a requirement. Worked out great, although the "simple" interface to it was a bit too simple and I ended up having to build up a DSL to translate searches into the more complex interface to the fulltext index. And any modifications to any field that was indexed, or having to update how things were indexed, was a chore thanks to referential integrity enforcement at the DB level: I had to remove and afterwards reapply things like foreign key constraints, triggers, stored procs, etc.... for both the "up" AND the "down"! Fortunately, since Postgres lets you make schema changes in a transaction, there usually wasn't anything to worry about integrity-wise.
throwaway2990 3y ago
We did that at my previous job. Because we had a really good idea of what was searched and used we indexed everything really well. We only had around ~ 6b records but it turned out to be faster to search than ES. And we didn’t have nodes turning bad every other month.
riverdroid 3y ago
I was researching this recently and came across this article: https://pganalyze.com/blog/gin-index https://pganalyze.com/blog/gin-index The GIN index has some similarities to Elasticsearch's inverted indices (last I knew anyway), which also can be quite expensive to write to. If you're doing heavy writes, something to test and consider carefully. TLDR; writes get a lot more expensive with GIN indices.
plasma 3y ago
A technique I've used before is to treat Elasticsearch as rebuildable at any time, consider this approach: A cron runs every 5 minutes that looks at your database for any objects you're indexing where last_modified_at timestamp > last_indexing_started_timestamp. Index the objects into Elasticsearch, then update the last_indexing_started_timestamp value to be when you started the original sync process, so we catch any modified objects between the start/end of the update run, next run. Then if Elasticsearch needs rebuilding you can just clear out the last indexing timestamp and resync from the start of time, and its self-recovering / won't get out of sync.
- mesarvagya 3y ago
  Or we can also use Logstash
- xps 3y ago
  Tried this as well but the difficulty is that Postgres is a relational database whereas ElasticSearch stores schema-less documents. Your record in ES might include data from many different tables, and figuring out what to (efficiently) update when there is a change in Postgres is not a simple task.
  - dgb23 3y ago
    This issue comes up in several domains and tech layers (back to front, bottom up etc.) Are there any generic, algorithmic or even just heuristics that help with this? It’s something I‘ve been thinking about over some time now. Any pointers, strategies and tips are appreciated.
  - Justsignedup 3y ago
    For me, a shotgun approach seemed the least likely to break. Anything that is a dependency in the elastisearch index should trigger a job to export to it. And since it is idempotent it doesn't matter if it accidentally exports two or ten times the same index in a bg job. Just make sure before writing that you do a quick check that you're not overriding a fresher one. So just have a freshness timestamp which is the latest timestamp of any record used in the indexing data. Furthermore you can do a daily job to just re export a critical part of the index. Doesn't matter if it is or isn't fresh. So let's say you query all records that were modified in the last day, and trigger the export job thatnmaynincludebthat record. Even if it causes duicate work. Idempotency saves you there.
jdpedrie 3y ago
Check out debezium.
- worldsayshi 3y ago
  Has anyone tried it on a large and frequently updated database?
  - jdpedrie 3y ago
    Anecdotally, I've heard of successes using it at relatively large companies for syncing pg to elastic. I don't have first hand experience. There's also pgsync[0], but again, this is just from some preliminary research on my part, can't speak to relative or absolute quality for either option. [0] https://pgsync.com/ https://pgsync.com/
danielheath 3y ago
I have done this. It was a challenging task, even with significant experience, and some results aren’t as good (most are as good, very few are better). Overall it was a success - our ops work is very much reduced, enough so to have easily paid for the engineering time invested. Not to be undertaken lightly though. Depending on your needs, you may be better served by materialised views, normal views, or triggers. The builtin text search may not suit your use cases; it’s not necessarily hard to come up with alternative schemes.
drewpc 3y ago
I went a similar route (Postgres for CRUD and Elastic for searching) and also underestimated the effort of keeping the two datastore in sync as well as underestimated the effort in maintaining a reliable Elastic cluster with limited manpower/experience. After moving to Postgres full text search with indexes and query boosting, I accomplished everything I needed inside Postgres with update triggers and search queries that were incredibly performant.
hinkley 3y ago
I want a public standard for a WAL format that disparate databases can share to stay in sync.
baur 3y ago
[dead]
- worldsayshi 3y ago
  CrateDB is very promising. It's not acid though. Another potential of to use zombodb (haven't tried it myself).
  - baur 3y ago
    Yep, CrateDB is not ACID but has eventual consistency at the row level. I think for some use cases it's fine that some inserts are not visible in search results immediately. Also, once could tweak refresh_interval or use explicit refresh for "must be visible" inserts.
joking 3y ago
I think they are not comparable, for some basic queries maybe PG is enough, but I don't think than the text processing capabilities of lucene are there, and it's ok because one is a database, and the other a search engine. Elastic has drifted a lot to being like a database, but I least for me, what has worked very well is having a database as a source of truth, and denormalizate the database as needed to Solr which is queried by the site. Just something like faceted search navigation, is way easier to do well in Solr or elastic versus PG
taf2 3y ago
Back in 2017 when spectrum and meltdown hit I was boarding a flight to mexico with my wife for a short get away. I noticed a pagerduty as the flight started to take off about a backup in our queue. Our company handles millions of calls daily and at that time we indexed each call record as a single document into elasticsearch (ES). When I landed and finally got to our hotel and plugged into the wifi the problem was significantly worse. As a result of the reduced compute capacity from the patches aws rolled out the night before we observed over all servers including our application servers, database and elasticsearch roughly a 30% reduction in computing power. I remember sitting on the beach trying to figure out a good solution. batching the index operations thanks to _bulk endpoint was immediately what I realized we had to do just wasn't clear on how to get there. After 2 days of hacking on the problem and explaining to customers it would be okay no data was lost, I had a solution that involves redis sets to ensure we only index unique call records and a self queueing bulk indexing job that also limits the total number of jobs queued as a function of our overall capacity. Most frameworks assume a single record indexed based on update/create/delete operations is the way to go but that doesn't scale... It's been a few years now since then and elasticsearch has proven it's worth over and over again as a denormalized index allowing for faster search and aggregated reporting that our normalized database could not provide...
tudorg 3y ago
I posted the follow-up here: https://xata.io/blog/postgres-full-text-search-postgres-vs-elasticsearch https://xata.io/blog/postgres-full-text-search-postgres-vs-e...

cameronpm 3y ago

Normally best practice is to return primary keys in ES and then do a lookup query in the db. Did you attempt this?

rajamaka 3y ago
Got any links or documentation about this method by any chance? Not sure if you are talking about querying or syncing data here.
- lyu07282 3y ago
  I think what they just mean is that you only have all your searchable fields indexed in ES and then, when you do a search query, the only thing ES returns is PKs which you then do a second roundtrip to the database (or cache) with to get all the actual records from. Which means you don't have to worry too much about all the record information being in sync perfectly.

dqv 3y ago

>We completely underestimated the effort of keeping the two datastores in sync and are actually contemplating to get rid of elasticsearch. Can you expand on this? Is it that it's tedious to write code that updates both? I've been meaning to play around with meilisearch and was trying to think about the synchronization issue.

valzam 3y ago

I had a similar setup at a previous gig and didn't find it particularly challenging. Whenever an update happens to an entity in PG we send a message to async replicate that entity in ES (via ID lookup in PG). As always you need good monitoring and retries for async computation but ES is quite stable and fast so we rarely had any issues. Now, we had pretty lax consistency requirements, as long as the "latest state" of PG ended up in ES within a reasonable timeframe everything was fine so maybe your requirements are different.

hn_throwaway_99 3y ago
Yeah, I think more folks should learn about postgres LISTEN and NOTIFY functionality, https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html, it's great for stuff like this, and there are now various client libraries like pg-listen in NPM that make this really easy to use. This is especially useful if you have lots of different teams/code paths that may update your DB - just set up a trigger that causes a NOTIFY message to get sent, then have a client responsible for reading for PG and populating ES. Alternatively, if you can accept a bit more latency, just have a trigger that sets a "needsESIndexing" dirty column somewhere and have a polling process that picks rows WHERE needsESIndexing = TRUE and just updates this to FALSE when the indexing is complete.
- valzam 3y ago
  Or a bit more 'web scale's use wal_insert and the outbox pattern to transactionally replicate events to a messaging system (Kafka etc).
  - lyu07282 3y ago
    you can also just use a CDC to import from Postgres into Kafka using Debezium and then on the other end Kafka Connect to write into Elasticsearch, no need to write any code at all with this setup ymmv ofc
    - paulryanrogers 3y ago
      Sounds like a lot of moving parts though, unless the team is already familiar with maintaining all those parts
      - lyu07282 3y ago
        if you already use Kafka anyway its a valid solution, I wouldn't introduce Kafka for this obviously. Also Kafka Connect and Debezium are mature well maintained open source projects in case that wasn't clear enough. https://debezium.io/ https://debezium.io/ https://docs.confluent.io/platform/current/connect/index.html https://docs.confluent.io/platform/current/connect/index.htm...

jarym 3y ago

Curious, did you try zombodb? [https://www.zombodb.com/ https://www.zombodb.com/]

worthless-trash 3y ago
For a moment I thought it was from zombo.com
- mekoka 3y ago
  The site was updated just to replace flash and add TLS. Amazing!

softwaredoug 3y ago

You might trade for a different problem, which is that search could end up being a very different query load on the Postgres cluster compared to existing queries. That might make it hard to scale it out for both use cases.

selcuka 3y ago
You can always have a separate, streaming replica [1] used for full text searches. [1] https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#ST...

devoutsalsa 3y ago

The easiest log retention setup I've ever used was just a server w/ logs dumped into a text file searched by zgrep. I'm not it's not ideal for all use cases, but I miss the simplicity it.

chrismarlow9 3y ago
This can be done and centralized with rsyslog and it's magical. And yes it can scale.

pmarreck 3y ago

This is exactly why I (as a solo developer at the time) just used Postgres' built-in fulltext search when that was a requirement. Worked out great, although the "simple" interface to it was a bit too simple and I ended up having to build up a DSL to translate searches into the more complex interface to the fulltext index. And any modifications to any field that was indexed, or having to update how things were indexed, was a chore thanks to referential integrity enforcement at the DB level: I had to remove and afterwards reapply things like foreign key constraints, triggers, stored procs, etc.... for both the "up" AND the "down"! Fortunately, since Postgres lets you make schema changes in a transaction, there usually wasn't anything to worry about integrity-wise.

throwaway2990 3y ago

We did that at my previous job. Because we had a really good idea of what was searched and used we indexed everything really well. We only had around ~ 6b records but it turned out to be faster to search than ES. And we didn’t have nodes turning bad every other month.

riverdroid 3y ago

I was researching this recently and came across this article: https://pganalyze.com/blog/gin-index https://pganalyze.com/blog/gin-index The GIN index has some similarities to Elasticsearch's inverted indices (last I knew anyway), which also can be quite expensive to write to. If you're doing heavy writes, something to test and consider carefully. TLDR; writes get a lot more expensive with GIN indices.

plasma 3y ago

A technique I've used before is to treat Elasticsearch as rebuildable at any time, consider this approach: A cron runs every 5 minutes that looks at your database for any objects you're indexing where last_modified_at timestamp > last_indexing_started_timestamp. Index the objects into Elasticsearch, then update the last_indexing_started_timestamp value to be when you started the original sync process, so we catch any modified objects between the start/end of the update run, next run. Then if Elasticsearch needs rebuilding you can just clear out the last indexing timestamp and resync from the start of time, and its self-recovering / won't get out of sync.

mesarvagya 3y ago
Or we can also use Logstash
xps 3y ago
Tried this as well but the difficulty is that Postgres is a relational database whereas ElasticSearch stores schema-less documents. Your record in ES might include data from many different tables, and figuring out what to (efficiently) update when there is a change in Postgres is not a simple task.
- dgb23 3y ago
  This issue comes up in several domains and tech layers (back to front, bottom up etc.) Are there any generic, algorithmic or even just heuristics that help with this? It’s something I‘ve been thinking about over some time now. Any pointers, strategies and tips are appreciated.
- Justsignedup 3y ago
  For me, a shotgun approach seemed the least likely to break. Anything that is a dependency in the elastisearch index should trigger a job to export to it. And since it is idempotent it doesn't matter if it accidentally exports two or ten times the same index in a bg job. Just make sure before writing that you do a quick check that you're not overriding a fresher one. So just have a freshness timestamp which is the latest timestamp of any record used in the indexing data. Furthermore you can do a daily job to just re export a critical part of the index. Doesn't matter if it is or isn't fresh. So let's say you query all records that were modified in the last day, and trigger the export job thatnmaynincludebthat record. Even if it causes duicate work. Idempotency saves you there.

jdpedrie 3y ago

Check out debezium.

worldsayshi 3y ago
Has anyone tried it on a large and frequently updated database?
- jdpedrie 3y ago
  Anecdotally, I've heard of successes using it at relatively large companies for syncing pg to elastic. I don't have first hand experience. There's also pgsync[0], but again, this is just from some preliminary research on my part, can't speak to relative or absolute quality for either option. [0] https://pgsync.com/ https://pgsync.com/

danielheath 3y ago

I have done this. It was a challenging task, even with significant experience, and some results aren’t as good (most are as good, very few are better). Overall it was a success - our ops work is very much reduced, enough so to have easily paid for the engineering time invested. Not to be undertaken lightly though. Depending on your needs, you may be better served by materialised views, normal views, or triggers. The builtin text search may not suit your use cases; it’s not necessarily hard to come up with alternative schemes.

drewpc 3y ago

I went a similar route (Postgres for CRUD and Elastic for searching) and also underestimated the effort of keeping the two datastore in sync as well as underestimated the effort in maintaining a reliable Elastic cluster with limited manpower/experience. After moving to Postgres full text search with indexes and query boosting, I accomplished everything I needed inside Postgres with update triggers and search queries that were incredibly performant.

hinkley 3y ago

I want a public standard for a WAL format that disparate databases can share to stay in sync.

baur 3y ago

[dead]

worldsayshi 3y ago
CrateDB is very promising. It's not acid though. Another potential of to use zombodb (haven't tried it myself).
- baur 3y ago
  Yep, CrateDB is not ACID but has eventual consistency at the row level. I think for some use cases it's fine that some inserts are not visible in search results immediately. Also, once could tweak refresh_interval or use explicit refresh for "must be visible" inserts.

joking 3y ago

I think they are not comparable, for some basic queries maybe PG is enough, but I don't think than the text processing capabilities of lucene are there, and it's ok because one is a database, and the other a search engine. Elastic has drifted a lot to being like a database, but I least for me, what has worked very well is having a database as a source of truth, and denormalizate the database as needed to Solr which is queried by the site. Just something like faceted search navigation, is way easier to do well in Solr or elastic versus PG

taf2 3y ago

Back in 2017 when spectrum and meltdown hit I was boarding a flight to mexico with my wife for a short get away. I noticed a pagerduty as the flight started to take off about a backup in our queue. Our company handles millions of calls daily and at that time we indexed each call record as a single document into elasticsearch (ES). When I landed and finally got to our hotel and plugged into the wifi the problem was significantly worse. As a result of the reduced compute capacity from the patches aws rolled out the night before we observed over all servers including our application servers, database and elasticsearch roughly a 30% reduction in computing power. I remember sitting on the beach trying to figure out a good solution. batching the index operations thanks to _bulk endpoint was immediately what I realized we had to do just wasn't clear on how to get there. After 2 days of hacking on the problem and explaining to customers it would be okay no data was lost, I had a solution that involves redis sets to ensure we only index unique call records and a self queueing bulk indexing job that also limits the total number of jobs queued as a function of our overall capacity. Most frameworks assume a single record indexed based on update/create/delete operations is the way to go but that doesn't scale... It's been a few years now since then and elasticsearch has proven it's worth over and over again as a denormalized index allowing for faster search and aggregated reporting that our normalized database could not provide...

tudorg 3y ago

I posted the follow-up here: https://xata.io/blog/postgres-full-text-search-postgres-vs-elasticsearch https://xata.io/blog/postgres-full-text-search-postgres-vs-e...