Skip to content

Instantly share code, notes, and snippets.

@parallelo3301
Last active December 11, 2020 14:14
Show Gist options
  • Save parallelo3301/e02c06706789121476edb9430db294ee to your computer and use it in GitHub Desktop.
Save parallelo3301/e02c06706789121476edb9430db294ee to your computer and use it in GitHub Desktop.
reindex by bloat percent concurrently for postgres < 12
create extension dblink;
drop function if exists reindex_by_bloat_percent;
create or replace function reindex_by_bloat_percent (connection_string text, schema_to_process text = 'public', threshold integer = 40, min_index_bytes integer = 20971520) returns text
language PLPGSQL as $$
declare
processed text[];
index_info record;
index_indexes record;
constraint_type text;
create_index_query text;
alter_table_query text;
process_index_start timestamp;
err_context text;
begin
BEGIN
perform dblink_connect('db', connection_string);
for index_info in (select * from dblink(
'db',
format(
'select indexname as index, (pg_relation_size(quote_ident(indexrelname)::text) / (case when (pg_relation_size(quote_ident(t.tablename)::text) / 100) != 0 then (pg_relation_size(quote_ident(t.tablename)::text) / 100) else 1 end)) as size_ratio_percent
FROM pg_tables t LEFT OUTER JOIN pg_class c ON t.tablename=c.relname LEFT OUTER JOIN (SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) AS foo ON t.tablename = foo.ctablename
WHERE t.schemaname = ''%1$s'' and pg_relation_size(quote_ident(indexrelname)::text) > %3$s and ((pg_relation_size(quote_ident(t.tablename)::text) * (%2$s / 100.0)) <= pg_relation_size(quote_ident(indexrelname)::text)) ORDER BY 2 desc nulls LAST, 1',
schema_to_process, threshold, min_index_bytes
)
) as t(index text, size_ratio_percent numeric))
loop
process_index_start := clock_timestamp();
raise notice 'Starting to process %', index_info.index::text;
select into constraint_type contype from dblink('db', format('select contype::text from pg_constraint where conname = ''%1$s''', index_info.index)) as t(contype text);
select into index_indexes * from dblink(
'db',
format('select schemaname, tablename, indexname, tablespace, indexdef from pg_catalog.pg_indexes where indexname = ''%1$s''', index_info.index)
) as t(schemaname text, tablename text, indexname text, tablespace text, indexdef text);
if constraint_type = 'f' then
raise notice '- Foreign key, ignoring (TODO?)';
continue;
end if;
create_index_query := index_indexes.indexdef;
create_index_query := replace(create_index_query, ' INDEX ' || index_info.index::text, ' INDEX CONCURRENTLY ' || index_info.index::text);
create_index_query := replace(create_index_query, ' ' || index_info.index::text || ' ', ' ' || index_info.index::text || '_t ');
raise notice '- Create index concurrently';
raise notice '-- Query to execute: %', create_index_query;
perform dblink('db', create_index_query);
raise notice '-- New index was created, took: %', TO_CHAR(clock_timestamp() - process_index_start, 'HH24:MI:SS');
if constraint_type = 'p' then
raise notice '- Index % is primary key, altering', index_info.index::text;
alter_table_query := format(
'ALTER TABLE %1$s.%2$s DROP CONSTRAINT %3$s, ADD CONSTRAINT %3$s PRIMARY KEY USING INDEX %3$s_t',
schema_to_process, index_indexes.tablename, index_info.index
);
end if;
if constraint_type = 'u' then
raise notice '- Index % is unique key', index_info.index::text;
alter_table_query := format(
'ALTER TABLE %1$s.%2$s DROP CONSTRAINT %3$s, ADD CONSTRAINT %3$s UNIQUE USING INDEX %3$s_t',
schema_to_process, index_indexes.tablename, index_info.index
);
end if;
if constraint_type is null then
raise notice '- Index % has no constraint, drop & rename', index_info.index::text;
alter_table_query := format(
'DROP INDEX %3$s; ALTER INDEX %3$s_t RENAME TO %3$s;',
schema_to_process, index_indexes.tablename, index_info.index
);
end if;
raise notice '-- Query to execute: %', alter_table_query;
perform dblink('db', alter_table_query);
processed := array_append(processed, index_info.index::text);
raise notice 'Reindex for % done in %', index_info.index::text, TO_CHAR(clock_timestamp() - process_index_start, 'HH24:MI:SS');
end loop;
perform dblink_disconnect('db');
exception when others then
perform dblink_disconnect('db');
GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
RAISE INFO 'Error Name:%', SQLERRM;
RAISE INFO 'Error State:%', SQLSTATE;
RAISE INFO 'Error Context:%', err_context;
end;
return processed;
end; $$;
-- run it
select reindex_by_bloat_percent('postgres://user:pass@localhost:5432/dbname', 'public', 20);
@parallelo3301
Copy link
Author

Warning: Must be run from different instance, it stucks in the transaction otherwise.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment