Problème de taille base de données / Gestion de la BDD

Entraide Home Assistant
22

Pour lister les tables, avec la taille

SELECT 
	name,
	size/1048576.0 AS size_MB,
	rowcount,
	size/rowcount AS bytes_per_row
FROM (
	SELECT name, sum(pgsize) size FROM dbstat GROUP BY name
) sizes
NATURAL JOIN (
	SELECT 'event_data' AS name, (SELECT count(*) FROM event_data) rowcount UNION ALL
	SELECT 'event_types', (SELECT count(*) FROM event_types) rowcount UNION ALL
	SELECT 'events', (SELECT count(*) FROM events) rowcount UNION ALL
	SELECT 'migration_changes', (SELECT count(*) FROM migration_changes) rowcount UNION ALL
	SELECT 'recorder_runs', (SELECT count(*) FROM recorder_runs) rowcount UNION ALL
	SELECT 'schema_changes', (SELECT count(*) FROM schema_changes) rowcount UNION ALL
	SELECT 'state_attributes', (SELECT count(*) FROM state_attributes) rowcount UNION ALL
	SELECT 'states', (SELECT count(*) FROM states) rowcount UNION ALL
	SELECT 'states_meta', (SELECT count(*) FROM states_meta) rowcount UNION ALL
	SELECT 'statistics', (SELECT count(*) FROM statistics) rowcount UNION ALL
	SELECT 'statistics_meta', (SELECT count(*) FROM statistics_meta) rowcount UNION ALL
	SELECT 'statistics_runs', (SELECT count(*) FROM statistics_runs) rowcount UNION ALL
	SELECT 'statistics_short_term', (SELECT count(*) FROM statistics_short_term) rowcount
) counts
ORDER BY size DESC;

Pour savoir quelles entités bourrent la BDD

SELECT m.metadata_id, m.entity_id, cnt, datetime(ts, 'unixepoch') last_update FROM
(SELECT metadata_id, count(*) cnt, max(last_updated_ts ) ts FROM states GROUP BY metadata_id ORDER BY 2 DESC LIMIT 100) c
JOIN states_meta m USING (metadata_id)

Pour savoir quelles statistiques bourrent la BDD

SELECT m.statistic_id, m.id, cnt, datetime(ts, 'unixepoch') last_update FROM
(SELECT metadata_id, count(*) cnt, max(start_ts) ts FROM statistics_short_term GROUP BY metadata_id ORDER BY 2 DESC LIMIT 100) c
JOIN statistics_meta m ON m.id=c.metadata_id
3 « J'aime »