Skip to content

Instantly share code, notes, and snippets.

Avatar

Marco Slot marcocitus

View GitHub Profile
@marcocitus
marcocitus / upsert-trigger.sql
Created Aug 15, 2022
COPY with upsert on Citus via triggers
View upsert-trigger.sql
DROP TABLE IF EXISTS data, data_writes;
CREATE TABLE data (
key int primary key,
value int
);
SELECT create_distributed_table('data','key');
CREATE TABLE data_writes (
LIKE data
@marcocitus
marcocitus / citus-cluster-latency.sql
Created Feb 24, 2022
Citus cluster response times function
View citus-cluster-latency.sql
CREATE OR REPLACE FUNCTION latency(nodename text, nodeport int)
RETURNS double precision
AS $$
DECLARE
start_time timestamptz;
end_time timestamptz;
BEGIN
/* warm up connection cache */
perform master_run_on_worker(array[nodename], array[nodeport], array['select 1'], false);
@marcocitus
marcocitus / cluster-setup.sql
Last active Sep 7, 2022
Basic geo-partitioning for Citus
View cluster-setup.sql
-- set up a global cluster
select citus_set_coordinator_host('us-coordinator-hostname', 5432);
select citus_add_node('us-worker1-hostname', 5432);
select citus_add_node('us-worker2-hostname', 5432);
select citus_add_node('eu-coordinator-hostname', 5432);
select citus_add_node('eu-worker1-hostname', 5432);
select citus_add_node('eu-worker2-hostname', 5432);
-- make sure we can run distributed queries on EU coordinator
select start_metadata_sync_to_node('eu-coordinator-hostname', 5432);
View citus-tables.sql
-- PG set up instructions: https://ankane.org/tpc-ds
SELECT create_reference_table('call_center');
SELECT create_reference_table('catalog_page');
SELECT create_distributed_table('catalog_returns', 'cr_item_sk');
SELECT create_distributed_table('catalog_sales', 'cs_item_sk');
SELECT create_reference_table('customer');
SELECT create_reference_table('customer_address');
SELECT create_reference_table('customer_demographics');
SELECT create_reference_table('date_dim');
@marcocitus
marcocitus / alter_table_access_method.sql
Last active Nov 3, 2020
Functions for altering access method
View alter_table_access_method.sql
CREATE OR REPLACE FUNCTION alter_table_access_method(table_name regclass, access_method name)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
compressed_table_name name := table_name || '_cmp';
original_table_name name := table_name::name;
table_relkind char;
parent_table_name regclass;
partition_boundaries text;
@marcocitus
marcocitus / stock_partitioned.sql
Created Mar 30, 2020
Basic hash-partitioning of the stock table in HammerDB
View stock_partitioned.sql
BEGIN;
CREATE TABLE stock_partitioned (LIKE stock INCLUDING ALL) PARTITION BY HASH (s_w_id);
CREATE TABLE stock_00 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 0);
CREATE TABLE stock_01 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 1);
CREATE TABLE stock_02 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 2);
CREATE TABLE stock_03 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 3);
CREATE TABLE stock_04 PARTITION OF stock_partitioned FOR VALUES WITH (MODULUS 10, REMAINDER 4);
CREATE TABLE stock_05 PARTITION OF s
@marcocitus
marcocitus / Instructions
Last active Jun 8, 2020
Downgrade from 9.3-1 to 9.2-2
View Instructions
Instructions to downgrade from v9.2.3 to 9.2.2:
1. Install Citus v9.2.2
2. Copy citus--9.3-1--9.2-2.sql to <postgresql installation directory>/share/extension/citus--9.3-1--9.2-2.sql
3. Restart PostgreSQL
4. Run: ALTER EXTENSION citus UPDATE;
5. Delete <postgresql installation directory>/share/extension/citus--9.3-1--9.2-2.sql
View queries.sql
-- Q1
select ol_number,
sum(ol_quantity) as sum_qty,
sum(ol_amount) as sum_amount,
avg(ol_quantity) as avg_qty,
avg(ol_amount) as avg_amount,
count(*) as count_order
from order_line
where ol_delivery_d > '2007-01-02 00:00:00.000000'
group by ol_number order by ol_number;
@marcocitus
marcocitus / hammerdb.sql
Last active Oct 7, 2019
HammerDB Function Schema
View hammerdb.sql
CREATE TABLE public.customer (c_id numeric(5,0) NOT NULL, c_d_id numeric(2,0) NOT NULL, c_w_id numeric(4,0) NOT NULL, c_first character varying(16), c_middle character(2), c_last character varying(16), c_street_1 character varying(20), c_street_2 character varying(20), c_city character varying(20), c_state character(2), c_zip character(9), c_phone character(16), c_since timestamp without time zone, c_credit character(2), c_credit_lim numeric(12,2), c_discount numeric(4,4), c_balance numeric(12,2), c_ytd_payment numeric(12,2), c_payment_cnt numeric(8,0), c_delivery_cnt numeric(8,0), c_data character varying(500)) WITH (fillfactor='50');
CREATE UNIQUE INDEX customer_i2 ON public.customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id) TABLESPACE pg_default ;
ALTER TABLE public.customer ADD CONSTRAINT customer_i1 PRIMARY KEY (c_w_id, c_d_id, c_id);
CREATE TABLE public.district (d_id numeric(2,0) NOT NULL, d_w_id numeric(4,0) NOT NULL, d_ytd numeric(12,2), d_tax numeric(4,4), d_next_o_id numeric, d_nam
View citus-cloud-create-dev.sh
#!/bin/sh
name=Marco
formation_name=${1:-test}
formations_url=https://console.citusdata.com/api/v1/formations
api_token=$(cat $HOME/.cituscloud/token)
formation_dir=$HOME/.cituscloud/formations/$formation_name/
worker_count=2
function log {