Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Scalable real-time product search using PostgreSQL with Citus
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 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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.