Re:Optimizing Postgres text search with trigrams
by kleebeesh on Friday February 25, 2022 @05:52PM
(#30473358)
I've personally had some painful experiences with refreshing materialized views in Postgres. In particular, highly variable performance on read replicas that were receiving a refreshed matview every few minutes. Maybe we were just doing it wrong, but I tend to avoid it if I can. Plus the eventual consistency can introduce confusion.
In any case, there's an interesting feature called Incremental View Maintenance that is being worked on by some Postgres developers: https://wiki.postgresql.org/wiki/Incremental_View_Maintenanc...
This would let us define a materialized view that gets automatically updated as the source tables change. When I last checked (late 2021), they were saying it might land in PG15.
Re:Optimizing Postgres text search with trigrams
by thom on Tuesday March 01, 2022 @06:24AM
(#30512779)
This is a really interesting area, would love it if Postgres provided a strong option here. In the meantime, Materialize has very good Postgres integration if it might work for you:
Re:Optimizing Postgres text search with trigrams
by crgwbr on Wednesday February 23, 2022 @08:41AM
(#30440823)
I’d advise just using materialized views. Yes, they don’t support incremental updates. But anything homemade will eventually be buggy somewhere and cause the index to get out of date in unexpected and probably unmonitored ways.
Re:Optimizing Postgres text search with trigrams
by thom on Wednesday February 23, 2022 @09:41AM
(#30441459)
As long as there's a key on the materialized view you can rebuild it concurrently with no locks, and if you remember to vacuum there's no particular overhead to doing this regularly. Smaller more targeted updates seem good to me though if you want things instantly in the index.
Not that Postgres probably wouldn't handle the full table refresh effortlessly as it generally does - at least at my scale. But that feels kind of icky to me and I'm surprised there doesn't seem to be a better solution for this yet - everyone building a search function for their site must run into this and I'd expect it to scale terribly at dimensions startups generally aim at.