# Partition Postgres Squid data

## Motivation

On substrate based blockchain, blocks are growing at a speed of 5 sec/block so with the db schema that squid comes with, there are chances of table outgrowing the data as the primary key is bpchar (16) of which last six characters are hash
example - 0000000002-**409d0**

-- public.block definition

CREATE TABLE public.block (
	id bpchar(16) NOT NULL,
	height int4 NOT NULL,
	hash bpchar(66) NOT NULL,
	parent_hash bpchar(66) NOT NULL,
	"timestamp" timestamptz NOT NULL,
	"validator" varchar NULL,
	spec_id text NOT NULL,
	CONSTRAINT block_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_block__hash ON public.block USING btree (hash);
CREATE INDEX idx_block__height ON public.block USING btree (height);
CREATE INDEX idx_block__timestamp ON public.block USING btree ("timestamp");
CREATE INDEX idx_block__validator ON public.block USING btree (validator);

There are three ways to partition postgres database
1. By range - partition has to be done on the primary key of a table, in this case the primary key is alpha numeric and not a number so cannot partition by id.
`(00[12][0-9]{6}-[0-9A-Fa-f]{5})` regex satisfies for blocks from 001000000-xxxxx to 002999999-xxxxx
However regex is not supported by partition in postgres.
2. By List - Cannot be used here for the same reason.
3. By hash - we can hash the primary key and put the record in different partition.

    Drop the existing table (non partitioned) created by squid
    drop table public.event;
    drop table public.call;
    drop table public.extrinsic;
    drop table public.block;
    
    
    
    CREATE TABLE public.block (
        id bpchar(16) NOT NULL,
        height int4 NOT NULL,
        hash bpchar(66) NOT NULL,
        parent_hash bpchar(66) NOT NULL,
        "timestamp" timestamptz NOT NULL,
        "validator" varchar NULL,
        spec_id text NOT NULL,
        CONSTRAINT block_pkey1 PRIMARY KEY (id)
    )PARTITION BY HASH (id);
    
    CREATE INDEX idx_block__hash1 ON public.block USING btree (hash);
    CREATE INDEX idx_block__height1 ON public.block USING btree (height);
    CREATE INDEX idx_block__timestamp1 ON public.block USING btree ("timestamp");
    CREATE INDEX idx_block__validator1 ON public.block USING btree (validator);
    CREATE TABLE public.block_0 partition of block for values with (MODULUS 3, REMAINDER 0);
    CREATE TABLE public.block_1 partition of block for values with (MODULUS 3, REMAINDER 1);
    CREATE TABLE public.block_2 partition of block for values with (MODULUS 3, REMAINDER 2);
    
    CREATE TABLE public.extrinsic (
        id bpchar(23) NOT NULL,
        block_id bpchar(16) NOT NULL,
        index_in_block int4 NOT NULL,
        "version" int4 NOT NULL,
        signature jsonb NULL,
        call_id varchar(30) NOT NULL,
        fee numeric NULL,
        tip numeric NULL,
        success bool NOT NULL,
        error jsonb NULL,
        pos int4 NOT NULL,
        hash bpchar(66) NOT NULL,
        CONSTRAINT extrinsic_pkey1 PRIMARY KEY (id)
    )PARTITION BY HASH (id);
    
    CREATE INDEX idx_extrinsic__block__index1 ON public.extrinsic USING btree (block_id, index_in_block);
    CREATE INDEX idx_extrinsic__call1 ON public.extrinsic USING btree (call_id);
    CREATE INDEX idx_extrinsic__hash1 ON public.extrinsic USING btree (hash);
    CREATE INDEX idx_extrinsic__signature1 ON public.extrinsic USING gin (signature);
    
    ALTER TABLE public.extrinsic ADD CONSTRAINT extrinsic_block_id_fkey FOREIGN KEY (block_id) REFERENCES block(id) ON DELETE CASCADE;
    
    CREATE TABLE public.extrinsic_0 partition of extrinsic for values with (MODULUS 3, REMAINDER 0);
    CREATE TABLE public.extrinsic_1 partition of extrinsic for values with (MODULUS 3, REMAINDER 1);
    CREATE TABLE public.extrinsic_2 partition of extrinsic for values with (MODULUS 3, REMAINDER 2);
    
    
    CREATE TABLE public."call" (
        id varchar(30) NOT NULL,
        parent_id varchar(30) NULL,
        block_id bpchar(16) NOT NULL,
        extrinsic_id bpchar(23) NOT NULL,
        "name" varchar NOT NULL,
        args jsonb NULL,
        success bool NOT NULL,
        error jsonb NULL,
        origin jsonb NULL,
        pos int4 NOT NULL,
        contract varchar NULL,
        CONSTRAINT call_pkey1 PRIMARY KEY (id)
    )PARTITION BY HASH (id);
    
    CREATE INDEX idx_call__block1 ON public.call USING btree (block_id);
    CREATE INDEX idx_call__contract__block1 ON public.call USING btree (contract, block_id);
    CREATE INDEX idx_call__extrinsic__index1 ON public.call USING btree (extrinsic_id);
    CREATE INDEX idx_call__name__block1 ON public.call USING btree (name, block_id);
    CREATE INDEX idx_call__parent1 ON public.call USING btree (parent_id);
    
    ALTER TABLE public."call" ADD CONSTRAINT call_block_id_fkey FOREIGN KEY (block_id) REFERENCES block(id) ON DELETE CASCADE;
    ALTER TABLE public."call" ADD CONSTRAINT call_extrinsic_id_fkey FOREIGN KEY (extrinsic_id) REFERENCES extrinsic(id) ON DELETE CASCADE;
    ALTER TABLE public."call" ADD CONSTRAINT call_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES call(id);
    
    CREATE TABLE public.call_0 partition of call for values with (MODULUS 3, REMAINDER 0);
    CREATE TABLE public.call_1 partition of call for values with (MODULUS 3, REMAINDER 1);
    CREATE TABLE public.call_2 partition of call for values with (MODULUS 3, REMAINDER 2);
    
    
    
    CREATE TABLE public."event" (
        id bpchar(23) NOT NULL,
        block_id bpchar(16) NOT NULL,
        index_in_block int4 NOT NULL,
        phase varchar NOT NULL,
        extrinsic_id bpchar(23) NULL,
        call_id varchar(30) NULL,
        "name" varchar NOT NULL,
        args jsonb NULL,
        pos int4 NOT NULL,
        contract varchar NULL,
        CONSTRAINT event_pkey1 PRIMARY KEY (id)
    )PARTITION BY HASH (id);
    CREATE INDEX idx_event__args1 ON public.event USING gin (args);
    CREATE INDEX idx_event__block__index1 ON public.event USING btree (block_id, index_in_block);
    CREATE INDEX idx_event__call1 ON public.event USING btree (call_id);
    CREATE INDEX idx_event__contract__block1 ON public.event USING btree (contract, block_id);
    CREATE INDEX idx_event__extrinsic1 ON public.event USING btree (extrinsic_id);
    CREATE INDEX idx_event__name__block1 ON public.event USING btree (name, block_id);
    
    ALTER TABLE public."event" ADD CONSTRAINT event_block_id_fkey FOREIGN KEY (block_id) REFERENCES block(id) ON DELETE CASCADE;
    ALTER TABLE public."event" ADD CONSTRAINT event_call_id_fkey FOREIGN KEY (call_id) REFERENCES call(id);
    ALTER TABLE public."event" ADD CONSTRAINT event_extrinsic_id_fkey FOREIGN KEY (extrinsic_id) REFERENCES extrinsic(id) ON DELETE CASCADE;
    
    CREATE TABLE public.event_0 partition of event for values with (MODULUS 3, REMAINDER 0);
    CREATE TABLE public.event_1 partition of event for values with (MODULUS 3, REMAINDER 1);
    CREATE TABLE public.event_2 partition of event for values with (MODULUS 3, REMAINDER 2);
    
    
    DELETE FROM public.metadata WHERE id='polkadot@0';
    
    select * from block b order by height;
    select * from block_0 b order by height ;
    select * from block_1 b order by height ;
    select * from block_2 b order by height ;