Hacker News new | ask | show | jobs
by xps 1074 days 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.
4 comments
Justsignedup 1074 days 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.

link
f4c39012 1073 days ago
Perhaps include a "last modified" timestamp w/ timezone in tables of interest, PG can update this on a trigger so no app code has to change. Index this field. Then build a view on top of the relevant tables that assembles the document for ES. Include in the view a field which contains the most recent of all the "last modified" dates, and filter the view on that timestamp?
link
stephen 1073 days ago
We've solved this ("any of these N related tables needs to update the search record") but building reactivity into our backend ORM:

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.

link
dgb23 1074 days 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.

link