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.
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.
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.
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.
https://joist-orm.io/docs/advanced/full-text-search
Granted, currently we still do pgsearch against this derived field, but could sync it over to ES.
In my case I'm using Solr and my last_indexed field isn't written to until the Solr index call completes without error. I have a very basic lock on the indexing process which hasn't failed me yet, and if it ever did fail the consequences would only be wasted CPU cycles. I consider that a lower risk than updating last_indexed only to have the actual indexing fail unexpectedly.
In the rare instances I've needed to re-index from scratch the process has been incredibly simple:
1. Start a new instance of Solr on a powerful AWS instance and direct index updates to it
2. Set all last_indexed fields to NULL
3. Wait for the scheduled task to complete the re-indexing
4. Reboot the new Solr instance on a sufficient AWS instance
5. Shift to the new Solr instance for search engine reads
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.
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.
https://docs.confluent.io/platform/current/connect/index.htm...
That’s a significant risk for things that need to be in sync between two systems, so we stuck with listen/notify for info-level ops things and used polling/queue systems that offered better guarantees for more important tasks. Don’t want to be in a position where a quiet hiccup with a deploy or something results in fun bugs like 0.5% of rows being silently out of sync between ES and Postgres.
We do something like this with our systems. External events get written to the event bus but all operations are idempotent on the event bus. So at night we send another round of the days events to clean up any inconsistencies.
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.
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.
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.
[1] https://www.postgresql.org/docs/current/warm-standby.html#ST...
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.
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.