Scalable real-time product search using PostgreSQL with Citus

product_text_search.sql

CREATE OR REPLACE FUNCTION product_text_search(name text, description text)
RETURNS tsvector LANGUAGE sql IMMUTABLE AS $function$
  SELECT setweight(to_tsvector(name),'A') || 
         setweight(to_tsvector(description),'B');
$function$;

tables.sql

CREATE TABLE IF NOT EXISTS product (
  product_id int primary key,
  name text not null,
  description text not null,
  price decimal(12,2),
  attributes jsonb
);

CREATE TABLE IF NOT EXISTS offer (
  product_id int not null,
  offer_id int not null,
  seller_id int,
  price decimal(12,2),
  new bool,
  primary key(product_id, offer_id)
);

CREATE OR REPLACE FUNCTION product_text_search(name text, description text)
RETURNS tsvector LANGUAGE sql IMMUTABLE AS $function$
  SELECT setweight(to_tsvector(name),'A') || 
         setweight(to_tsvector(description),'B');
$function$;

CREATE INDEX text_idx ON product USING GIN (product_text_search(name, description));

CREATE INDEX attributes_idx ON product USING GIN (attributes jsonb_path_ops);

SELECT master_create_distributed_table('product', 'product_id', 'hash');
SELECT master_create_worker_shards('product', 16, 2);

SELECT master_create_distributed_table('offer', 'product_id','hash');
SELECT master_create_worker_shards('offer', 16, 2);