View citus-cluster-latency.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 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); |
View cluster-setup.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
-- 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
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
-- 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'); |
View alter_table_access_method.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 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; |
View stock_partitioned.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
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 |
View Instructions
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
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
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
-- 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; |
View hammerdb.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 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
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
#!/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 { |
View citus-fdw.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
/* add the user mappings from the distributed table to the shards */ | |
CREATE OR REPLACE FUNCTION bind_shards_to_remote_shards(table_name regclass) | |
RETURNS void | |
LANGUAGE plpgsql | |
AS $function$ | |
DECLARE | |
foreign_server text; | |
fdw_options text[]; | |
user_mapping_options text[]; | |
server_options text[]; |
NewerOlder