PostgreSQL Defrag les plus gros indexes et tables

Réduire la fragmentation 

https://www.timescale.com/learn/how-to-reduce-bloat-in-large-postgresql-tables

Afficher les (10) plus gros indexes : 

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(relid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(relid)) As "Total Size"
FROM pg_catalog.pg_stat_all_indexes 
WHERE schemaname ='public'
ORDER BY pg_total_relation_size(relid) DESC
--LIMIT 10;

Défragmenter en ligne les 5 plus gros indexes : 

t

o reindex_online.log

SELECT ‘REINDEX INDEX CONCURRENTLY ‘||indexrelname||’;’

FROM pg_catalog.pg_stat_all_indexes

WHERE schemaname =’public’

ORDER BY pg_total_relation_size(relid) DESC

LIMIT 5;

gexec

Fonction pour reindex en ligne les 5 plus gros indexes : 

CREATE OR REPLACE FUNCTION TOP5_reindex(schema_name TEXT)
RETURNS VOID AS
$$
DECLARE
    index_record RECORD;
BEGIN
    FOR index_record IN
        SELECT indexrelname
        FROM pg_stat_all_indexes
        WHERE schemaname = schema_name
        ORDER BY pg_relation_size(relid) DESC
        LIMIT 5
    LOOP
        EXECUTE format('REINDEX INDEX %I.%I', schema_name, index_record.indexrelname);
        RAISE NOTICE 'Reindexed index %I.%I', schema_name, index_record.indexrelname;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

REINDEX X plus gros indexes : 


CREATE OR REPLACE FUNCTION reindex_schema(schema_name TEXT, idx_nbr INT )
RETURNS VOID AS
$$
DECLARE
    index_record RECORD;
BEGIN
    FOR index_record IN
        SELECT indexrelname
        FROM pg_stat_all_indexes
        WHERE schemaname = schema_name
        ORDER BY pg_relation_size(relid) DESC
        LIMIT idx_nbr
    LOOP
        EXECUTE format('REINDEX INDEX %I.%I', schema_name, index_record.indexrelname);
        RAISE NOTICE 'Reindexed index %I.%I', schema_name, index_record.indexrelname;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

select reindex_schema('public',5);


Créer l'extension pgstattuple (show tuple-level statistics) pour travailler sur la fragmentation : 

CREATE EXTENSION pgstattuple;

 SELECT i.indexrelid::regclass,
       s.leaf_fragmentation
FROM pg_index AS i
   JOIN pg_class AS t ON i.indexrelid = t.oid
   JOIN pg_opclass AS opc ON i.indclass[0] = opc.oid
   JOIN pg_am ON opc.opcmethod = pg_am.oid
   CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE t.relkind = 'i'
  AND pg_am.amname = 'btree';

Afficher  la fragmentation des indexes pour le role toto : 

SELECT i.indexrelid::regclass,
       s.leaf_fragmentation
FROM pg_index AS i
   JOIN pg_class AS t ON i.indexrelid = t.oid
   JOIN pg_roles AS r ON t.relowner = r.oid
   JOIN pg_opclass AS opc ON i.indclass[0] = opc.oid
   JOIN pg_am ON opc.opcmethod = pg_am.oid
   CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE t.relkind = 'i'
  AND pg_am.amname = 'btree'
  AND r.rolname ='toto';

Affiche les 10 indexes les plus fragmentés à plus de 80 % pour le role toto:

SELECT i.indexrelid::regclass,
       s.leaf_fragmentation
FROM pg_index AS i
   JOIN pg_class AS t ON i.indexrelid = t.oid
   JOIN pg_roles AS r ON t.relowner = r.oid
   JOIN pg_opclass AS opc ON i.indclass[0] = opc.oid
   JOIN pg_am ON opc.opcmethod = pg_am.oid
   CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
WHERE t.relkind = 'i'
  AND pg_am.amname = 'btree'
  AND r.rolname ='toto'
  AND s.leaf_fragmentation > 80
  AND s.leaf_fragmentation != 'NaN'::NUMERIC
  order by s.leaf_fragmentation desc 
  LIMIT 10;

Pour les tables : 

-- Perform ANALYZE on your table
ANALYZE <table_name>;

-- Get the number of deadlines in your tables.
select schemaname,
relname,
pg_size_pretty(pg_relation_size(schemaname|| '.' || relname)) as size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0 THEN round((n_dead_tup::float / 
n_live_tup::float)::numeric, 4) END AS dead_tup_ratio,
last_autovacuum,
last_autoanalyze
from pg_stat_user_tables
order by dead_tup_ratio desc NULLS LAST;

si la valeur de la colonne dead_tup_ratio est élevée, cela signifie que la table est fragmentée.

DO $$
DECLARE
    schema_name TEXT := 'your_schema_name'; -- Specify your schema name here
    num_indexes_to_reindex INT := 5; -- Specify the number of indexes to reindex
BEGIN
    FOR index_info IN
        SELECT schemaname, indexname
        FROM pg_indexes
        WHERE schemaname = schema_name
        ORDER BY pg_total_relation_size(indexname) DESC
        LIMIT num_indexes_to_reindex
    LOOP
        EXECUTE format('REINDEX INDEX %I.%I', index_info.schemaname, index_info.indexname);
        RAISE NOTICE 'Reindexed index %I.%I', index_info.schemaname, index_info.indexname;
    END LOOP;
END $$;


SELECT reindex_schema_indexes('your_schema_name');


CREATE OR REPLACE FUNCTION reindex_schema_indexes(schema_name TEXT)

RETURNS VOID AS

$$

DECLARE

    index_record RECORD;

BEGIN

    FOR index_record IN

        SELECT indexname

        FROM pg_indexes

        WHERE schemaname = schema_name

    LOOP

        EXECUTE format('REINDEX INDEX %I.%I', schema_name, index_record.indexname);

        RAISE NOTICE 'Reindexed index %I.%I', schema_name, index_record.indexname;

    END LOOP;

END;

$$

LANGUAGE plpgsql;

Trouver les x plus gros indexes

SELECT

    indexname AS index_name,

    tablename AS table_name,

    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size

FROM

    pg_catalog.pg_indexes

WHERE

    schemaname = 'your_schema_name'

ORDER BY

    pg_relation_size(indexrelid) DESC

LIMIT x;



DO $$
DECLARE
    index_record RECORD;
    counter INT := 0;
BEGIN
    FOR index_record IN 
        SELECT
            indexname AS index_name,
            tablename AS table_name
        FROM
            pg_catalog.pg_indexes
        WHERE
            schemaname = 'your_schema_name'
        ORDER BY
            pg_relation_size(indexrelid) DESC
        LIMIT x
    LOOP
        EXECUTE 'REINDEX INDEX ' || quote_ident(index_record.index_name) || ' ON ' || quote_ident(index_record.table_name);
        counter := counter + 1;
    END LOOP;

    RAISE NOTICE '% indexes reindexed successfully.', counter;
END $$;