PostgreSQL

Database Maintenance and Monitoring script

Monitor Index dan size for optimization

/*
 * Over optimizing is a thing
	We always our database to be performant, so in order to do that we keep things in memory/cache (see earlier) and we index things so we don’t have to scan everything on disk. 
	But there is a trade-off when it comes to indexing your database. 
	Each index the system has to maintain will slow down your write throughput on the database. 
	This is fine when you do need to speed up queries, as long as they’re being utilized. 
	If you added an index years ago, but something within your application changed and you no longer need it best to remove it.
	Postgres makes it simply to query for unused indexes so you can easily give yourself back some performance by removing them:
 */
SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
--WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
--where relname = 'ret_gen_tsm_targetomzetline'
where relname in ('ret_gen_tsm_targetomzetgrow','ret_gen_tsm_targetomzetline','ret_tsmrekap_category','ret_gen_tsmtarget_category')
ORDER BY relname asc, pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
--idx_scan asc,
pg_relation_size(i.indexrelid) desc
;

--drop index ret_gen_tsm_targetomzetline_tag_status_idx;

Monitor Temporary Table

/*
 * We all aware of the temp tables and many times developers are creating temp table for their ad-hoc testing purpose.
 * But unfortunately sometimes, they forget to drop unused temp tables, and temp tables keep active in those idle connections.
 * It also requires unnecessary utilization of disk space.
 * 
 * DBAs are responsible for identifying unused temp tables and then inform to developers so that they can drop their unused temp tables.
 */

SELECT
	n.nspname as SchemaName
	,c.relname as RelationName
	,CASE c.relkind
	WHEN 'r' THEN 'table'
	WHEN 'v' THEN 'view'
	WHEN 'i' THEN 'index'
	WHEN 'S' THEN 'sequence'
	WHEN 's' THEN 'special'
	END as RelationType
	,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner               
	,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n               
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s') 
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC