Skip to content

Instantly share code, notes, and snippets.

Marco Slot marcocitus

View GitHub Profile
@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 / pubsub-coordinator.sql
Last active Jun 30, 2020
Prototype for PubSub on PG 10 with Citus 7
View pubsub-coordinator.sql
/* commands to run on the coordinator */
CREATE EXTENSION citus;
SELECT master_add_node('10.0.0.2', 5432);
SELECT master_add_node('10.0.0.3', 5432);
SELECT start_metadata_sync_to_node(nodename, nodeport) FROM pg_dist_node;
SET citus.replication_model TO 'streaming'
CREATE TABLE events (
event_id bigserial primary key,
@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
@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 / example.sql
Last active Feb 25, 2020
Safe incremental rollups on Postgres and Citus
View example.sql
-- Create the raw events table
CREATE TABLE page_views (
site_id int,
path text,
client_ip inet,
view_time timestamptz default now(),
view_id bigserial
);
-- Allow fast lookups of ranges of sequence IDs
@marcocitus
marcocitus / rollups.sql
Last active Nov 14, 2019
Efficient real-time rollups with backfilling in Citus
View rollups.sql
CREATE TABLE rollups (
name text,
rolled_up_generation bigint default -1
);
-- Create a stub on workers to allow usage as a default in distributed tables
SELECT run_command_on_workers($$
CREATE OR REPLACE FUNCTION current_rollup_generation(rollup_name text)
RETURNS bigint LANGUAGE sql
AS $function$
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
@marcocitus
marcocitus / pg_partman.sql
Last active Mar 16, 2019
Setting up pg_partman on cloud
View pg_partman.sql
-- as superuser:
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO citus;
CREATE SCHEMA partman;
GRANT USAGE ON SCHEMA partman TO citus;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
GRANT ALL ON TABLE partman.part_config TO citus;
GRANT ALL ON TABLE partman.part_config_sub TO citus;
@marcocitus
marcocitus / load_github_data.sql
Last active Mar 12, 2019
Load data from GitHub in postgres
View load_github_data.sql
CREATE OR REPLACE FUNCTION public.load_github_data(events_date date, hour integer)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
CREATE TEMPORARY TABLE input (data jsonb) ON COMMIT DROP;
EXECUTE format($$COPY input FROM PROGRAM 'curl -s http://data.githubarchive.org/%s-%s.json.gz | zcat | grep -v \\u0000' CSV QUOTE e'\x01' DELIMITER e'\x02'$$, events_date, hour);
INSERT INTO github.events SELECT
(data->>'id')::bigint AS event_id,
You can’t perform that action at this time.