Created
April 13, 2016 13:49
-
-
Save marcocitus/fb49a20404f5fa8d4ff16c25ce04599c to your computer and use it in GitHub Desktop.
Scalable real-time product search using PostgreSQL with Citus
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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