5 ms·

This is great, hadn’t heard about siglen! For the solution at the end, you’ll often find yourself wanting to query across multiple tables instead of multiple c

by thom 5y ago

This is great, hadn’t heard about siglen! For the solution at the end, you’ll often find yourself wanting to query across multiple tables instead of multiple columns in a single table, in which case creating a materialised view with a single search term column and a GIST index is the best I’ve come up with. Obviously you then have a refresh to do instead of the expression being maintained as part of the index. But no matter what, if your search isn’t fast, you’re probably doing something wrong. Postgres is great for this stuff (obviously for longer documents and fuzzier searches we can argue about quality).

Grollicus 5y ago
I'm currently thinking about the multi table search problem and lean towards updating the index table in the ORM (save method of the corresponding Django models in this case). Postgres doesn't seem to support incremental updates of materialized views yet, and always recomputing the whole table just because some few rows changed seems very much overkill to me. That it integrates better into the rest of the app and makes configuring the indexed columns easier is just a bonus. 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.
- crgwbr 5y ago
  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.
- SigmundA 5y ago
  Triggers would be another less fragile way to do this.
- thom 5y ago
  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.
- kleebeesh 5y ago
  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_Maintenance 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.
  - thom 5y ago
    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: https://materialize.com/ https://materialize.com/
kleebeesh 5y ago
Thanks for the feedback -- sorry I missed this a couple days ago! Had no clue it got posted to HN. I've deployed a solution that uses roughly this same method with multiple tables. I experimented with a materialized view that would centralize all the text columns, but ultimately found that it was much simpler and fast enough to have a single expression index in each of the tables. Then the query either joins the tables and checks each column, or you can run a separate query for each of the tables and stitch together the results in app code.

by thom 5y ago

Grollicus 5y ago

I'm currently thinking about the multi table search problem and lean towards updating the index table in the ORM (save method of the corresponding Django models in this case). Postgres doesn't seem to support incremental updates of materialized views yet, and always recomputing the whole table just because some few rows changed seems very much overkill to me. That it integrates better into the rest of the app and makes configuring the indexed columns easier is just a bonus. 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.

crgwbr 5y ago
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.
SigmundA 5y ago
Triggers would be another less fragile way to do this.
thom 5y ago
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.
kleebeesh 5y ago
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_Maintenance 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.
- thom 5y ago
  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: https://materialize.com/ https://materialize.com/

kleebeesh 5y ago

Thanks for the feedback -- sorry I missed this a couple days ago! Had no clue it got posted to HN. I've deployed a solution that uses roughly this same method with multiple tables. I experimented with a materialized view that would centralize all the text columns, but ultimately found that it was much simpler and fast enough to have a single expression index in each of the tables. Then the query either joins the tables and checks each column, or you can run a separate query for each of the tables and stitch together the results in app code.