saicitus

#Table and Schema Creation.

CREATE TABLE data_rows(company_id integer,id integer, data text);

ALTER TABLE dATA_ROWS ADD COLUMN tsv tsvector;

select master_create_distributed_table('data_rows','company_id','hash');

select master_create_worker_shards('data_rows',16,1);

saicitus

session_analytics

This PostgreSQL extension provides some functions for querying hstore arrays.

Functions

• count_elements(elements hstore[], pattern text). returns the number of elements that match the given pattern.
• multi_count_elements(elements hstore[], patterns text[]). returns an array

saicitus

--Show Meta data tables

--Distributed tables.
SELECT nodes,partmethod,colocationid FROM pg_dist_partition;

--Shards for the stores table
SELECT logicalrelid,shardid,shardminvalue,shardmaxvalue from pg_dist_shard where logicalrelid='stores'::regclass;

--Shard placements
SELECT shardid,nodename FROM pg_dist_shard_placement;

saicitus

My tests:

CREATE TABLE flows(id integer, app_protocol_details jsonb);

SELECT create_distributed_table('flows','id');

Query which din't run:

select app_protocol_details from flows fu, jsonb_array_elements(fu.app_protocol_details->'protocols') protocols where protocols->>'{dnsProtocol,rc}' like '%ERROR%';

saicitus

# Analyzes a specific distributed table

export PGHOST=...
export PGUSER=citus
export PGPASSWORD=...
#export PGSSLMODE=require
export PGDATABASE=citus
export TABLE_NAME=<table_name>
export PARALLEL_FACTOR=4

saicitus

export PGHOST=...
export PGUSER=citus
export PGPASSWORD=...
export PGSSLMODE=require
export PGDATABASE=citus
export TABLE_NAME=events
export NEW_TABLE_NAME=events_backup
export PARALLEL_FACTOR=4

psql -tA -F" " -c "SELECT s.shardid, \

saicitus

CREATE TABLE key_value (key int, value jsonb);
CREATE TABLE big_table(key int, first_name text, last_name text);
select create_distributed_table('key_value','key');
select create_distributed_table('big_table','key');

select run_command_on_shards($cmd$big_table$cmd$,$cmd$CREATE OR REPLACE FUNCTION insert_fun_%1$I() RETURNS TRIGGER AS $$

    BEGIN

        --

saicitus

CREATE TABLE example_serial(id serial, name text, created_at timestamp);
SELECT create_distributed_table('example_serial', 'id');


INSERT INTO example_serial (name, created_at) VALUES ('My test', now());
-- ERROR:  values given for the partition column must be constants or constant expressions

-- Work around:
-- 1) Create a sequence 'seq' on the coordinator node:
CREATE SEQUENCE seq;

saicitus

SELECT logicalrelid::regclass, partmethod, column_to_column_name(logicalrelid, partkey) FROM pg_dist_partition;

saicitus

CREATE OR REPLACE FUNCTION filter (children jsonb[])
RETURNS jsonb[]
AS $$
DECLARE
    i jsonb;
    result jsonb[];
BEGIN
  FOR i IN SELECT * FROM jsonb_array_elements(children)
  LOOP
    IF i.value -> 'name' != 'null'::jsonb THEN