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 / 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, 2019
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.