Skip to content

Instantly share code, notes, and snippets.

Avatar

Marco Slot marcocitus

View GitHub Profile
@marcocitus
marcocitus / citus-explain.out
Last active May 20, 2016
Citus EXPLAIN example
View citus-explain.out
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
@marcocitus
marcocitus / master_create_range_shard.sql
Created Jun 8, 2016
Create range-partitioned shard in Citus
View master_create_range_shard.sql
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
@marcocitus
marcocitus / deinstrument.sql
Last active Jun 15, 2016
Instrumentation for master_append_table_to_shard
View deinstrument.sql
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;
@marcocitus
marcocitus / create_table.sql
Last active Jun 30, 2016
Scripts for loading Github events into Citus
View create_table.sql
CREATE TABLE github_events
(
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb,
actor jsonb,
org jsonb,
@marcocitus
marcocitus / citus_tools.sql
Last active Sep 20, 2016
Function to run a SQL command across all workers, shards, or placements on Citus
View citus_tools.sql
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
@marcocitus
marcocitus / example.sql
Last active May 15, 2017
Partitioning in Citus 6.1, 6.2
View example.sql
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)
@marcocitus
marcocitus / split.sql
Last active Jun 19, 2017
Split shards in Citus (experimental)
View split.sql
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
@marcocitus
marcocitus / replace-pg_table_size.sql
Last active Aug 29, 2017
Show distributed table sizes in \d+
View replace-pg_table_size.sql
-- 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
@marcocitus
marcocitus / alter-citusdb-table.sh
Last active Sep 19, 2017
Shell commands to ALTER a CitusDB distributed table
View alter-citusdb-table.sh
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"
@marcocitus
marcocitus / create-range-shards.sql
Created Nov 6, 2017
Create co-located shards for range-distributed tables
View create-range-shards.sql
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;
You can’t perform that action at this time.