Detect and remove duplicated indexes in PostgreSQL

In Postgres, you can create multiple indexes on same table and identical list of columns. While it is useful for maintenance (you can create new index and drop the old one, for example) it is NOT advisable in long term.

Here is a catalog query which will detect and print duplicate indexes:

WITH dup AS (
	SELECT x.indrelid, x.indkey, i.relam, md5(x.indpred)
	FROM pg_index x
	JOIN pg_class i ON i.oid = x.indexrelid
	GROUP BY 1,2,3,4
	HAVING count(*) > 1
)
SELECT
	n.nspname AS schemaname,
	c.relname AS tablename,
    i.relname AS indexname,
	/*am.amname, x.indnatts, x.indkey,
    ARRAY(
        SELECT pg_get_indexdef(x.indexrelid, k + 1, true)
        FROM generate_subscripts(x.indkey, 1) as k
        ORDER BY k
    ) AS indkeyname,
	x.indpred, */
    pg_get_indexdef(x.indexrelid) AS indexdef,
    pg_size_pretty(c.relpages::int8 * current_setting('block_size')::int) AS tablesize,
    pg_size_pretty(i.relpages::int8 * current_setting('block_size')::int) AS indexsize
FROM pg_index x 
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_am AS am ON i.relam = am.oid
JOIN dup ON (
	dup.indrelid = x.indrelid
	AND dup.indkey = x.indkey
	AND dup.relam = i.relam
	AND dup.md5 IS NOT DISTINCT FROM md5(x.indpred))
ORDER BY
	n.nspname, c.relname, dup.md5 nulls first, i.relname
;

k8grep: Yet Another Ultimate Kubernetes Tool

Hello!

Maybe you're struggling how to find any IP in your Kubernetes cluster? [*]

Or you want to find out what images your deployments use? [**]

Or you need to know which configmaps or secrets have to anything with Redis? [***]

Here's a powerful func in bash to find almost ANY information about my current kubernetes cluster:

k8grep () {
	local objtype=$1
	shift 1
	local grep_args="$@"
	[ "$objtype" -a "$grep_args" ] || {
		echo "# usage: k8grep OBJTYPE GREP_ARGS"
		echo "# example: k8grep pod -n -m 5 postgres"
		return
	}
	kubectl get --no-headers "$objtype" --all-namespaces |
		while read namespace object junk; do
			echo "# kubectl get -n $namespace $objtype $object"
			kubectl get $objtype -n $namespace $object -oyaml | grep $grep_args
		done
}

See it in action. This is what I am getting after running k8grep configmap 'REDIS_\w\+' -n -1

By the way, answers to questions (LOL, dare I compete with StackOverflow?) are:

How to find any IP in your Kubernetes cluster?

k8grep endpoints 10.11.12.134

How do I find out what images my k8s deployments use?

k8grep deployment -n "image: "

How do I find which config maps and secrets contain anything about Redis?

for t in configmap secret; do k8grep $t -m1 -n -i redis; done