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