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
postgres=# EXPLAIN ANALYZE SELECT p.product_id, p.name, p.price, to_json(array_agg(to_json(o))) | |
FROM product p LEFT JOIN offer o USING (product_id) | |
WHERE product_text_search(p.name, p.description) @@ plainto_tsquery('copper oven') | |
AND (p.price < 70 OR o.price < 70) | |
AND (p.attributes @> '{"food":"waste"}' OR p.attributes @> '{"food":"air"}') | |
GROUP BY p.product_id, p.name, p.description, p.price | |
ORDER BY ts_rank(product_text_search(p.name, p.description), | |
plainto_tsquery('copper oven')) DESC | |
LIMIT 10; | |
QUERY PLAN |
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 master_create_range_shard( | |
table_name text, | |
minvalue text, | |
maxvalue text) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS $function$ | |
DECLARE | |
new_shard_id bigint; | |
BEGIN |
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
BEGIN; | |
DROP FUNCTION public.master_append_table_to_shard(bigint,text,text,int); | |
ALTER FUNCTION pg_catalog.real_master_append_table_to_shard(bigint,text,text,int) | |
RENAME TO master_append_table_to_shard; | |
END; |
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 github_events | |
( | |
event_id bigint, | |
event_type text, | |
event_public boolean, | |
repo_id bigint, | |
payload jsonb, | |
repo jsonb, | |
actor jsonb, | |
org jsonb, |
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 citus_shard_name(table_name regclass, shard_id bigint) | |
RETURNS text | |
LANGUAGE sql | |
AS $function$ | |
SELECT table_name||'_'||shard_id; | |
$function$; | |
CREATE OR REPLACE FUNCTION citus_shard_name(shard_id bigint) | |
RETURNS text | |
LANGUAGE sql |
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 events ( | |
id int not null, | |
created_at timestamptz, | |
payload jsonb | |
); | |
CREATE INDEX ON events (id); | |
CREATE INDEX ON events USING BRIN (created_at); | |
SELECT create_distributed_table('events', 'id'); | |
-- Set up the trigger on workers (idempotent) |
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 run_command_on_master_and_workers(p_sql text) | |
RETURNS void LANGUAGE plpgsql AS $function$ | |
BEGIN | |
EXECUTE p_sql; | |
PERFORM run_command_on_workers(p_sql); | |
END; | |
$function$; | |
SELECT run_command_on_master_and_workers($cmd$ | |
CREATE SCHEMA IF NOT EXISTS citus_split_lower |
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
-- Replace the pg_table_size function with one that supports distributed tables | |
BEGIN; | |
ALTER FUNCTION pg_catalog.pg_table_size(regclass) RENAME TO orig_table_size; | |
CREATE OR REPLACE FUNCTION pg_catalog.pg_table_size(table_name regclass) | |
RETURNS bigint | |
LANGUAGE plpgsql | |
AS $function$ | |
BEGIN | |
IF EXISTS (SELECT 1 FROM pg_dist_partition WHERE logicalrelid = table_name) THEN |
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
TABLE_NAME=customer_reviews | |
ALTER_COMMAND="ALTER COLUMN customer_id SET STATISTICS 1000" | |
/opt/citusdb/3.0/bin/psql -h localhost -d postgres -c "ALTER TABLE $TABLE_NAME $ALTER_COMMAND" | |
/opt/citusdb/3.0/bin/psql -tA -F" " -h localhost -d postgres -c "select relname, s.shardid, p.nodename, p.nodeport from pg_class c, pg_dist_shard s, pg_dist_shard_placement p where s.logicalrelid = c.oid and s.shardid = p.shardid and relname = '$TABLE_NAME'" | xargs -n 4 -P 100 sh -c '/opt/citusdb/3.0/bin/psql -h $3 -p $4 -d postgres -c "ALTER TABLE $1_$2 $0;"' "$ALTER_COMMAND" |
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 create_colocated_range_shards(table_name regclass, min_value text, max_value text) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS $function$ | |
DECLARE | |
table_colocationid int; | |
group_ids int[]; | |
new_shard_id bigint; | |
current_table text; | |
node record; |