Skip to content

Instantly share code, notes, and snippets.

Avatar

Marco Slot marcocitus

View GitHub Profile
@marcocitus
marcocitus / repair-placements.sql
Last active Apr 1, 2018
Repair all shard placements in shardstate 3
View repair-placements.sql
CREATE OR REPLACE FUNCTION public.repair_all()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM
master_copy_shard_placement(shardid, h.nodename, h.nodeport, u.nodename, u.nodeport)
FROM
(SELECT shardid, nodename, nodeport FROM pg_dist_shard_placement WHERE shardstate = 3) u
JOIN
@marcocitus
marcocitus / drain_node.sql
Last active Mar 22, 2018
Functions to drain and remove a node in Citus Enterprise
View drain_node.sql
CREATE OR REPLACE FUNCTION public.drain_node(remove_host text, remove_port int DEFAULT 5432, shard_transfer_mode citus.shard_transfer_mode DEFAULT 'auto'::citus.shard_transfer_mode)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
coordinator_name text;
coordinator_port int;
candidate_count int;
candidate_hosts text[];
candidate_ports int[];
@marcocitus
marcocitus / rollups.sql
Last active Nov 14, 2019
Efficient real-time rollups with backfilling in Citus
View rollups.sql
CREATE TABLE rollups (
name text,
rolled_up_generation bigint default -1
);
-- Create a stub on workers to allow usage as a default in distributed tables
SELECT run_command_on_workers($$
CREATE OR REPLACE FUNCTION current_rollup_generation(rollup_name text)
RETURNS bigint LANGUAGE sql
AS $function$
@marcocitus
marcocitus / pg_partman.sql
Last active Mar 16, 2019
Setting up pg_partman on cloud
View pg_partman.sql
-- as superuser:
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO citus;
CREATE SCHEMA partman;
GRANT USAGE ON SCHEMA partman TO citus;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
GRANT ALL ON TABLE partman.part_config TO citus;
GRANT ALL ON TABLE partman.part_config_sub TO citus;
@marcocitus
marcocitus / load_github_data.sql
Last active Mar 12, 2019
Load data from GitHub in postgres
View load_github_data.sql
CREATE OR REPLACE FUNCTION public.load_github_data(events_date date, hour integer)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
CREATE TEMPORARY TABLE input (data jsonb) ON COMMIT DROP;
EXECUTE format($$COPY input FROM PROGRAM 'curl -s http://data.githubarchive.org/%s-%s.json.gz | zcat | grep -v \\u0000' CSV QUOTE e'\x01' DELIMITER e'\x02'$$, events_date, hour);
INSERT INTO github.events SELECT
(data->>'id')::bigint AS event_id,
@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 Dec 9, 2020
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)