Skip to content

Instantly share code, notes, and snippets.

Marco Slot marcocitus

Block or report user

Report or block marcocitus

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@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;
@marcocitus
marcocitus / pubsub-coordinator.sql
Last active Aug 20, 2019
Prototype for PubSub on PG 10 with Citus 7
View pubsub-coordinator.sql
/* commands to run on the coordinator */
CREATE EXTENSION citus;
SELECT master_add_node('10.0.0.2', 5432);
SELECT master_add_node('10.0.0.3', 5432);
SELECT start_metadata_sync_to_node(nodename, nodeport) FROM pg_dist_node;
SET citus.replication_model TO 'streaming'
CREATE TABLE events (
event_id bigserial primary key,
@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 / 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 / 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 / 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 / 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 / partitioning-.sql
Last active May 13, 2016
Simple partitioning for Citus
View partitioning-.sql
-- Functions to partition a table and drop old partitions
CREATE SEQUENCE IF NOT EXISTS partition_id_seq;
CREATE OR REPLACE FUNCTION partition_create(
table_name regclass)
RETURNS text
LANGUAGE plpgsql
AS $function$
@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 / consecutive-users.sql
Last active May 1, 2016
Retention query using Citus
View consecutive-users.sql
CREATE OR REPLACE FUNCTION consecutive_date_pattern(start_date date, num_days int)
RETURNS text[]
AS $BODY$
DECLARE
result text[];
BEGIN
SELECT array_agg('date=>"'||d::date||'"') INTO result
FROM generate_series(start_date, start_date + num_days * interval '1 day', '1 day') d;
RETURN result;
You can’t perform that action at this time.