Skip to content

Instantly share code, notes, and snippets.

Marco Slot marcocitus

View GitHub Profile
@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 / alter-pgshard-table.sh
Created Mar 14, 2015
Shell commands to ALTER a pg_shard distributed table
View alter-pgshard-table.sh
TABLE_NAME=customer_reviews
ALTER_COMMAND="ALTER COLUMN customer_id SET STATISTICS 1000"
psql -h localhost -d postgres -c "ALTER TABLE $TABLE_NAME $ALTER_COMMAND"
psql -tA -F" " -h localhost -d postgres -c "select relname, s.id, p.node_name, p.node_port from pg_class c, pgs_distribution_metadata.shard s, pgs_distribution_metadata.shard_placement p where s.relation_id = c.oid and s.id = p.shard_id and relname = '$TABLE_NAME'" | xargs -n 4 -P 100 sh -c 'psql -h $3 -p $4 -d postgres -c "ALTER TABLE $1_$2 $0;"' "$ALTER_COMMAND"
@marcocitus
marcocitus / create-customer_reviews.sql
Created May 4, 2015
Create different types of customer_reviews tables
View create-customer_reviews.sql
CREATE TABLE customer_reviews_regular
(
customer_id TEXT not null,
review_date DATE not null,
review_rating INTEGER not null,
review_votes INTEGER,
review_helpful_votes INTEGER,
product_id CHAR(10) not null,
product_title TEXT not null,
product_sales_rank BIGINT,
@marcocitus
marcocitus / compress.sql
Created Aug 19, 2015
PL/pgsql functions to compress and decompress PostgreSQL tables using cstore_fdw
View compress.sql
-- Compress a table using cstore_fdw
CREATE OR REPLACE FUNCTION compress_table(uncomp_table regclass) RETURNS VOID AS
$BODY$
DECLARE
comp_table_name varchar := table_name || '_comp';
BEGIN
IF EXISTS (SELECT 1 FROM pg_class WHERE oid = table_name AND relkind = 'f') THEN
RETURN;
END IF;
@marcocitus
marcocitus / events.sql
Created Sep 23, 2015
gdelt inherited table
View events.sql
-- Parent table
CREATE TABLE events (
event_id BIGINT,
event_date INT,
monthyear INT,
year INT,
FractionDate DOUBLE PRECISION,
Actor1Code TEXT,
Actor1Name TEXT,
Actor1CountryCode TEXT,
@marcocitus
marcocitus / master.sql
Last active Feb 13, 2016
Adding stage tables to CitusDB
View master.sql
CREATE TABLE IF NOT EXISTS logs (
id uuid,
date timestamptz,
version jsonb,
data jsonb
);
CREATE INDEX logs_id_idx ON logs (id);
SELECT master_create_distributed_table('logs', 'date', 'append');
@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 / view.sql
Created Apr 12, 2016
Create view in Citus
View view.sql
CREATE OR REPLACE FUNCTION public.create_shard_view(table_shardid bigint, view_name regclass, query_format text)
RETURNS bigint
LANGUAGE plpgsql
AS $function$
DECLARE
view_shardid bigint := master_get_new_shardid();
table_name regclass;
placement record;
BEGIN
SELECT logicalrelid INTO table_name FROM pg_dist_shard WHERE shardid = table_shardid;
@marcocitus
marcocitus / generate-products.sql
Last active Jul 2, 2020
Generate mock product data in PostgreSQL
View generate-products.sql
CREATE TABLE IF NOT EXISTS words (
word text
);
CREATE TABLE IF NOT EXISTS product (
product_id int not null,
name text not null,
description text not null,
price decimal(12,2),
attributes jsonb,
@marcocitus
marcocitus / product_text_search.sql
Created Apr 13, 2016
Scalable real-time product search using PostgreSQL with Citus
View product_text_search.sql
CREATE OR REPLACE FUNCTION product_text_search(name text, description text)
RETURNS tsvector LANGUAGE sql IMMUTABLE AS $function$
SELECT setweight(to_tsvector(name),'A') ||
setweight(to_tsvector(description),'B');
$function$;
You can’t perform that action at this time.