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);
SELECT index_size INTO PREV_IND_SIZE FROM ostats.tblgr WHERE(CTID,indexname) IN (SELECT MAX(CTID),indexname FROM ostats.tblgr GROUP BY indexname HAVING indexname=NEW.indexname);
IF(PREV_TAB_SIZE!=NEW.table_size) THEN
SELECT PG_SIZE_PRETTY(NEW.table_size::BIGINT-PREV_tab_SIZE) INTO NEW.table_growth;
SELECT ((NEW.table_size::REAL-1 -PREV_tab_SIZE::REAL)/(PREV_tab_SIZE+1)::REAL * 100) INTO NEW.table_growth_percent;
IF(PREV_IND_SIZE!=NEW.index_size and new.indexname is not null) THEN
SELECT PG_SIZE_PRETTY(NEW.index_size::BIGINT-PREV_ind_SIZE) INTO NEW.index_growth;
SELECT ((NEW.index_size::REAL-1 -PREV_ind_SIZE::REAL)/(PREV_ind_SIZE+1)::REAL * 100) INTO NEW.index_growth_percent;
END IF;
RETURN NEW;
end if;
END IF;
RETURN NULL;
END;
$FUNCTION$;

Step 3: Create the following trigger definition for the above source.

CREATE TRIGGER FEED_TAB_GROWTH_TRIG BEFORE INSERT ON ostats.tblgr FOR EACH ROW EXECUTE PROCEDURE ostats.FEED_TAB_GROWTH();

Step 4: Put the following Insert in the cronjob for getting the table and indexes growth.

insert into ostats.tblgr(timestmp,tablename,indexname,table_size,index_size,total_table_size,n_rows)
select now() timestmp,a.tablename,b.indexname,
pg_relation_size(a.schemaname||'.'||a.tablename::text) table_size,
pg_relation_size(b.schemaname||'.'||b.indexname::text) index_size,
pg_total_relation_size(a.schemaname||'.'||a.tablename::text) table_total_size,
d.reltuples::bigint reltuples
from
pg_tables a left outer join pg_indexes b on a.tablename=b.tablename inner join pg_class d on a.tablename=d.relname where a.schemaname !~ '^information_schema|^pg_catalog|^pg_toast|^pg_temp';

--Dinesh