drnic / searchcraft Goto Github PK

From @andyatkinson in #2

Also, re: indexes, passing on something else useful in case you didn't already know: refreshing a materialized view ideally is done "concurrently", so that other queries using it can continue to run while the results are replaced. Concurrent refreshes do require one unique index exists on the materialized view:

For here: https://github.com/drnic/searchcraft/blob/develop/lib/searchcraft/model.rb#L44

Docs on the concurrently keyword and unique index requirement: https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html

SearchCraft does support concurrent refreshes; and there is a technique to add a new unique ID column; but these are not yet documented.

In brief:

• Each MV currently also gets a bonus sequence, even if it doesn't use it. See Builder#create_sequence! and Builder#view_id_sequence_name
• To add an id column to your MV using Arel, try something like Arel::Nodes::SqlLiteral.new("nextval('#{view_id_sequence_name}') AS id") - which could of course become a nice Builder helper in future.

Hi @drnic Looks cool! Planning to try it out. Skimmed the readme and you answered one question I had: why not scenic? Sounds like you focused on a faster iteration DX.

I noticed one typo "idation" which I think was meant to be ideation.

Random additional trivia:

Are you aware that materialized views can have indexes? I didn't see a mention of it in the readme, but you may already be aware of that and/or using indexes.

Have you dove into the world of "incrementally maintained materialized views" which don't require a full replacement, but can receive an incremental update?

One such open source mechanism is the pg_ivm extension.
https://github.com/sraoss/pg_ivm

A solution that brought that to Rails in some way would be amazing!