Skip to main content

Posts

Showing posts with the label table growth percent

Table and Index Growth Ratio in PostgreSQL

Hi All, Further to DBGrowth ratio, we have implemented the table and indexes growth ration and growth calculation percentages. Step 1:- Create the following structure CREATE TABLE ostats.tblgr(timestmp timestamp,tablename text,indexname text,table_size bigint,index_size bigint,total_table_size bigint,n_rows bigint,table_growth varchar(100),table_growth_percent real,index_growth varchar(100),index_growth_percent real); Step 2:- Create the following Trigger Source CREATE OR REPLACE FUNCTION ostats.FEED_TAB_GROWTH() RETURNS TRIGGER LANGUAGE PLPGSQL AS $FUNCTION$ DECLARE CHECK_TAB_PRESENCE INT; PREV_TAB_SIZE BIGINT; PREV_IND_SIZE BIGINT; BEGIN SELECT COUNT(*) INTO CHECK_tab_PRESENCE FROM ostats.tblgr WHERE LOWER(tablename)=NEW.tablename; IF(CHECK_TAB_PRESENCE=0) THEN RETURN NEW; ELSE SELECT table_size INTO PREV_tab_SIZE FROM ostats.tblgr WHERE(CTID,tablename) IN (SELECT MAX(CTID),tablename FROM ostats.tblgr GROUP BY tablename HAVING tablename=NEW.tablename); SELEC...