Ash Elixir•4w ago

Adding Postgres Full Text Search to an Ash Project

I recently added Postgres Full Text Search to an existing Ash project. This was honestly the first time I've had to add some functionality that Ash didn't already handle, so I was interested to experience some of these escape hatches first-hand. Figured I'd share here https://blog.1-800-rad-dude.com/posts/2025/08-13-Adding-Postgres-Full-Text-Search-to-an-Ash-Project.html

9 Replies

matt_savvyOP•4w ago

Please feel free to hit me with any suggestions on how I could have done this differently Also, is there any interest in adding first class support to ash_postgres for some of this? If so, I might be interested in taking a crack at it

ZachDaniel•4w ago

First class support would be great, would need to see what it might look like of course 🙂 A couple notes you can use custom_statements to have migration statements live in the resource and be managed by the migration generator. Helps avoid hidden things. you want to avoid using "literal" references like that because it will affect query composition. Instead, add an attribute with select_by_default?: false to prevent it from being loaded. Then, add migration_ignore_attributes [:search_vectors] to ignore that attribute in migrations (works nicely paired with custom statements). Then you can refer to the field directly in your expressions 🙂 i.e expr(fragment("? @@ websearch_to_tsquery(?)", search_vectors, ^search_query))

Abu kumathra•4w ago

this is something I've been looking at last week managed to get it to work but it didn't give me good results for my use case

matt_savvyOP•4w ago

Hmm, okay so it'd be more like this

      custom_statements do
        statement :add_search_vectors do
          up """
          alter table tasks add column search_vectors tsvector generated always as (
              setweight(to_tsvector('english', title), 'A')
              || ' ' ||
              coalesce(to_tsvector('english', body), '')
          ) stored;
          """

          down "alter table tasks drop column search_vectors;"
        end

        statement :idx_search_vectors_gin do
          up """
          create index idx_tasks_search_vectors_gin on tasks using gin(search_vectors);
          """

          down "drop index idx_tasks_search_vectors_gin;"
        end

        migration_ignore_attributes [:search_vectors]
      end

      custom_statements do
        statement :add_search_vectors do
          up """
          alter table tasks add column search_vectors tsvector generated always as (
              setweight(to_tsvector('english', title), 'A')
              || ' ' ||
              coalesce(to_tsvector('english', body), '')
          ) stored;
          """

          down "alter table tasks drop column search_vectors;"
        end

        statement :idx_search_vectors_gin do
          up """
          create index idx_tasks_search_vectors_gin on tasks using gin(search_vectors);
          """

          down "drop index idx_tasks_search_vectors_gin;"
        end

        migration_ignore_attributes [:search_vectors]
      end

      attribute :search_vectors, AshPostgres.Tsvector, select_by_default?: false

      attribute :search_vectors, AshPostgres.Tsvector, select_by_default?: false

And the expressions

expr(
                  fragment(
                    "ts_rank(?, websearch_to_tsquery(?))",
                    search_vectors,
                    ^arg(:search_query)
                  )
                )

expr(
                  fragment(
                    "ts_rank(?, websearch_to_tsquery(?))",
                    search_vectors,
                    ^arg(:search_query)
                  )
                )

                    expr(fragment("? @@ websearch_to_tsquery(?)", search_vectors, ^search_query))

                    expr(fragment("? @@ websearch_to_tsquery(?)", search_vectors, ^search_query))

ZachDaniel•4w ago

https://tenor.com/jJKT8p8S1O0.gif

Tenor

matt_savvyOP•4w ago

Seems to work Thanks! One other question; My understanding isthat I should be able to keep my existing migration and just run

mix ash.codegen --snapshots-only

mix ash.codegen --snapshots-only

and commit the file that gets generated. Is that correct?

ZachDaniel•4w ago

Yes since you've already written the corresponding migrations 👌

matt_savvyOP•4w ago

Cool cool Pushing a couple footnotes to the article

Aureate•3w ago

Had a similar experience a few months ago implementing postgres fulltext search in one of my projects, thanks for posting so I can see Zachs improvement comments and apply them to my own 😆 One thing I did on top (just due to project requirements) was wrapping it in an extension, so that for any resource I could define a combination of attributes as searchable like:

searchable do
      attributes [:summary, :description]
    end

searchable do
      attributes [:summary, :description]
    end

Was mid way through trying to implement vector search into this mix, but started working on other things. All in all, was a very big light switch moment with how much ash lets you do, while keeping it cleanly in it's semantics and easily reusable

Gaming

Programming

Adding Postgres Full Text Search to an Ash Project

Did you find this page helpful?