Let’s find out what can we achieve; using what we already have, without adding other layers, servers or applications: PostgreSQL. It will help us keep the architecture simple, please be patient \ud83d\ude42<\/p>\n
We need to prepare records for full-text search using 1-to-1 relationship for relevant records in other tables with search term vector from one or more text fields (the assumption is that each of those tables has a unique integer field):<\/p>\n
Here is the infamous full-text search index on the search term vector:<\/p>\n We need to insert and update records in our search index table; for example, when we add a user record in the users table using the text fields such as address details and technical skills.<\/p>\n NOTE: It is a good idea to write trigger function(s) (after insert, after update and after delete) on source table(s) to take care of synchronizing the records in search_index table.<\/p>\n How can we use the search index table? We can easily join this query with the source table and get the results straight away:<\/p>\n However, we can go further and add a table to cache the search terms for each table that we want to enable full-text search:<\/p>\n We don’t want to duplicate values for table name and term; let’s create a unique index:<\/p>\n Here is an example to populate it:<\/p>\n Then, we can store row IDs from the result of a search cache:<\/p>\n This is how we can populate that table:<\/p>\n As long as the source records and search term do not change, the final result will not change, so we can retrieve the results faster. We can also store recent search terms in memory by using Memcached.<\/p>\n Finally, the following code shows how to use the cache rows:<\/p>\n
\nCREATE TABLE public.search_index (
\n table_name VARCHAR(50) NOT NULL,
\n id INTEGER NOT NULL,
\n search_vector TSVECTOR,
\n CONSTRAINT search_index_pkey PRIMARY KEY(table_name, id)
\n)
\nWITH (oids = false);
\n<\/code><\/p>\n
\nCREATE INDEX search_index_idx_search_vector ON public.search_index
\n USING gin (search_vector);
\n<\/code><\/p>\n
\nINSERT INTO search_index (table_name, id, search_vector)
\nSELECT
\n 'users' AS table_name,
\n p.user_id AS id,
\n to_tsvector(
\n COALESCE(u.name, '') || ' ' ||
\n COALESCE(u.surname, '') || ' ' ||
\n COALESCE(u.address_1, '') || ' ' ||
\n COALESCE(u.address_2, '') || ' ' ||
\n COALESCE(u.city, '') || ' ' ||
\n COALESCE(u.country, '') || ' ' ||
\n COALESCE(u.skills, '')
\n ) AS search_vector
\nFROM users u;
\n<\/code><\/p>\n
\nVery simple: using to_tsquery() function and powerful PostgreSQL operators:<\/p>\n
\nSELECT
\n i.id,
\n ts_rank(i.search_vector, to_tsquery('london')) AS rank
\nFROM search_index i
\nWHERE i.table_name = 'users'
\n AND i.search_vector @@ to_tsquery('london')
\nLIMIT 10
\nOFFSET 0
\n;
\n<\/code><\/p>\n
\nSELECT q.rank, u.*
\nFROM (
\n SELECT
\n i.id,
\n ts_rank(i.search_vector, to_tsquery('london')) AS rank
\n FROM search_index i
\n WHERE i.table_name = 'users'
\n AND i.search_vector @@ to_tsquery('london')
\n LIMIT 10
\n OFFSET 0
\n) q
\nINNER JOIN users u ON q.id = u.id
\n;
\n<\/code><\/p>\n
\nCREATE TABLE public.search_cache (
\n search_cache_id SERIAL NOT NULL,
\n table_name VARCHAR(50) NOT NULL, -- also used in search_index.table_name
\n term VARCHAR(50) NOT NULL,
\n created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
\n updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
\n usage_count INTEGER DEFAULT 1 NOT NULL,
\n PRIMARY KEY(search_cache_id)
\n)
\nWITH (oids = false);
\n<\/code><\/p>\n
\nCREATE UNIQUE INDEX search_cache_unq_table_name_and_term ON public.search_cache
\n USING btree (table_name COLLATE pg_catalog.\"default\", term COLLATE pg_catalog.\"default\");
\n<\/code><\/p>\n
\nINSERT INTO search_cache (table_name, term)
\nVALUES ('users', 'london')
\nRETURNING search_cache_id; -- ==> e.g. 1
\n<\/code><\/p>\n
\nCREATE TABLE public.search_cache_row (
\n search_cache_id INTEGER NOT NULL, -- refers to search_cache.search_cache_id
\n id INTEGER NOT NULL, -- also used in search_index.id
\n rank REAL NOT NULL,
\n CONSTRAINT search_cache_row_pkey PRIMARY KEY(search_cache_id, id)
\n)
\nWITH (oids = false);
\n<\/code><\/p>\n
\nINSERT INTO search_cache_row (search_cache_id, id, rank)
\nSELECT
\n 1 AS search_cache_id,
\n i.id,
\n ts_rank(i.search_vector, to_tsquery('london')) AS rank
\nFROM search_index i
\nWHERE i.table_name = 'users'
\n AND i.search_vector @@ to_tsquery('london')
\nLIMIT 100
\nOFFSET 0
\n;
\n<\/code><\/p>\n