Skip to content

Instantly share code, notes, and snippets.

@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 / 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 / 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 / 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 / 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$;
View citus.source
--
-- Test compatibility with Citus extension
--
-- Function to perform a COPY, but hide error messages
CREATE OR REPLACE FUNCTION silent_copy()
RETURNS void LANGUAGE plpgsql AS $function$
BEGIN
COPY contestant FROM '@abs_srcdir@/data/contestants.1.csv' WITH CSV;
EXCEPTION WHEN OTHERS THEN
@marcocitus
marcocitus / consecutive-users.sql
Last active May 1, 2016
Retention query using Citus
View consecutive-users.sql
CREATE OR REPLACE FUNCTION consecutive_date_pattern(start_date date, num_days int)
RETURNS text[]
AS $BODY$
DECLARE
result text[];
BEGIN
SELECT array_agg('date=>"'||d::date||'"') INTO result
FROM generate_series(start_date, start_date + num_days * interval '1 day', '1 day') d;
RETURN result;
@marcocitus
marcocitus / partitioning-.sql
Last active May 13, 2016
Simple partitioning for Citus
View partitioning-.sql
-- Functions to partition a table and drop old partitions
CREATE SEQUENCE IF NOT EXISTS partition_id_seq;
CREATE OR REPLACE FUNCTION partition_create(
table_name regclass)
RETURNS text
LANGUAGE plpgsql
AS $function$
You can’t perform that action at this time.