ops partitioning

sql partitions + tablespaces

alter table declarations rename to declarations_old;

/*Drop triggers*/
drop trigger on_declaration_insert ON public.declarations_old;
drop trigger on_declaration_update ON public.declarations_old;

/*Drop all indexes*/
drop index declarations_declaration_number_index;
drop index declarations_declaration_request_id_index;
drop index declarations_employee_id_status_index;
drop index declarations_end_date_status_index;
drop index declarations_legal_entity_employee_index;
drop index declarations_legal_entity_inserted_at_status_active_ix;
drop index declarations_legal_entity_status_active;
drop index declarations_pending_inserted_at_id_index;
drop index declarations_person_id_status_index ;

/*Create new table*/
CREATE TABLESPACE fastspace LOCATION '/Users/edenlab/workspace/fasttablespace';
CREATE TABLE declarations (id uuid NOT NULL PRIMARY KEY,
    employee_id uuid NOT NULL,
    person_id uuid NOT NULL,
    start_date date NOT NULL,
    end_date date NOT NULL,
    status character varying(255) NOT NULL,
    signed_at timestamp without time zone NOT NULL,
    created_by uuid NOT NULL,
    updated_by uuid NOT NULL,
    is_active boolean DEFAULT false,
    scope character varying(255) NOT NULL,
    division_id uuid NOT NULL,
    legal_entity_id uuid NOT NULL,
    inserted_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    declaration_request_id uuid NOT NULL,
    seed character varying(255) NOT NULL,
    reason character varying(255),
    reason_description text,
    overlimit boolean,
    declaration_number character varying(255) NOT NULL) PARTITION BY HASH(id) TABLESPACE fastspace;

/////
/* Generate SQL for partitions */
/////
select 'CREATE TABLE declarations_'||i||' PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER '||i||');' from (select generate_series(0,20) as i)a;



/////
/* Generated SQL by prev command*/
/////

 CREATE TABLE declarations_0 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 0);
 CREATE TABLE declarations_1 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 1);
 CREATE TABLE declarations_2 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 2);
 CREATE TABLE declarations_3 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 3);
 CREATE TABLE declarations_4 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 4);
 CREATE TABLE declarations_5 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 5);
 CREATE TABLE declarations_6 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 6);
 CREATE TABLE declarations_7 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 7);
 CREATE TABLE declarations_8 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 8);
 CREATE TABLE declarations_9 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 9);
 CREATE TABLE declarations_10 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 10);
 CREATE TABLE declarations_11 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 11);
 CREATE TABLE declarations_12 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 12);
 CREATE TABLE declarations_13 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 13);
 CREATE TABLE declarations_14 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 14);
 CREATE TABLE declarations_15 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 15);
 CREATE TABLE declarations_16 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 16);
 CREATE TABLE declarations_17 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 17);
 CREATE TABLE declarations_18 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 18);
 CREATE TABLE declarations_19 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 19);
 CREATE TABLE declarations_20 PARTITION OF  declarations FOR VALUES WITH (MODULUS 21, REMAINDER 20);
 
 /*ATTENTION - number and declaration_request_id would be not uniq*/
 CREATE UNIQUE INDEX declarations_declaration_number_index ON public.declarations USING btree (id, declaration_number);
 CREATE UNIQUE INDEX declarations_declaration_request_id_index ON public.declarations USING btree (id, declaration_request_id);
 CREATE INDEX declarations_employee_id_status_index ON public.declarations USING btree (employee_id, status);
 CREATE INDEX declarations_end_date_status_index ON public.declarations_1 USING btree (end_date, status);
 CREATE INDEX declarations_legal_entity_employee_index ON public.declarations_1 USING btree (legal_entity_id, employee_id);
 CREATE INDEX declarations_legal_entity_inserted_at_status_active_ix ON public.declarations_1 USING btree (legal_entity_id, inserted_at DESC, status) WHERE is_active;
 CREATE INDEX declarations_legal_entity_status_active ON public.declarations_1 USING btree (legal_entity_id, status) WHERE is_active;
 CREATE INDEX declarations_pending_inserted_at_id_index ON public.declarations_1 USING btree (inserted_at, id) WHERE ((status)::text = 'pending_verification'::text);
 CREATE INDEX declarations_person_id_status_index ON public.declarations_1 USING btree (person_id, status);
 
 /*Create triggers*/
 CREATE TRIGGER on_declaration_insert AFTER INSERT ON public.declarations FOR EACH ROW EXECUTE PROCEDURE public.insert_declarations_status_hstr();
 CREATE TRIGGER on_declaration_update AFTER UPDATE ON public.declarations FOR EACH ROW WHEN (((old.status)::text IS DISTINCT FROM (new.status)::text)) EXECUTE PROCEDURE public.insert_declarations_status_hstr();