How to perform a database-wide full text search in PostgreSQL?

user_4135310 0
Viewed 378

I have a PostgreSQL database with about 500 tables. Each table has a unique ID column named id and a user ID column named user_id. I would like to perform a full-text search of all varchar columns across all of these tables for a particular user. I do this today with ElasticSearch but I'd like to simplify my architecture. I understand that I can add full text search columns to all of the tables with things like stored generated columns and then add indices for fast full text search:

ALTER TABLE pgweb
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);

However, I'm not familiar with how to do cross-table searches efficiently. Maybe a view across all textsearchable_index_col columns? I'd like the result to be something like the table name and id of the matching row. For example:

 table_name  |  id   
-------------+-------
 table1      |  492
 table42     |  20

If it matters, I'm using Ruby on Rails as the client with ActiveRecord. I'm using a managed PostgreSQL 13 database at Digital Ocean so I won't be able to install custom psql plugins.

postgresql
1 Answers

Maybe It is not the answer you are looking for, because I am not sure if there is a better approach, but first I will try to automate the process.

I will make two dynamic queries, the first one to create columns textsearchable_index_col (in each table with at least one varchar column) and the other to create an index on that columns (one index per table).

You could ADD a textsearchable_index_col column for each "character varying" column instead only one concatenating all "character varying" columns, but in this case I will create one textsearchable_index_col column per table like you propose.

I assume table schema "public" but you can use the real one.

-- Create columns textsearchable_index_col:

SELECT 'ALTER TABLE ' || table_schema || '.' || table_name || E' ADD COLUMN textsearchable_index_col tsvector GENERATED ALWAYS AS (to_tsvector(\'english\', coalesce(' ||
  string_agg(column_name, E', \'\') || \' \' || coalesce(') || E', \'\'))) STORED;'
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type IN ('character varying')
GROUP BY table_schema, table_name;


-- Create indexes on textsearchable_index_col columns:

SELECT 'CREATE INDEX ' || table_name || '_textsearch_idx ON ' || table_schema || '.' || table_name || ' USING GIN (textsearchable_index_col);'
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type IN ('character varying')
GROUP BY table_schema, table_name;

Then I will use a dynamic query to create a query (using UNION) to search on all that textsearchable_index_col columns:

You need to replace question mark by parameters (user_id and searched text), and take out the last "UNION ALL"

SELECT E'SELECT \'' || table_name || E'\' AS table_name, id FROM ' || table_schema || '.' || table_name || E' WHERE user_id = ? AND textsearchable_index_col' || ' @@ to_tsquery(?) UNION ALL'    
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type IN ('character varying')
GROUP BY table_schema, table_name;
user_154242270
Related