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;
select TOP5_reindex('public');
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;
Afficher la fragmentation des indexes :
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 $$;