PostgreSQL : Volumétrie

Taille des bases d'une instance

SELECT datname AS Nom, 
pg_database_size(datname) AS Taille_en_Octets, 
pg_size_pretty(pg_database_size(datname)) AS Taille
FROM pg_database
ORDER BY Taille_en_Octets DESC

Génère un tableau de résultats de la forme : (ex: bases sur vmpw38)

	nom	taille_en_octets	taille
1	sde	31982625592	30 GB
2	postgis20	11223864	11 MB
3	postgres	6030136	5889 KB
…	…

Taille des schémas d'une base

SELECT schemaname, 
       pg_size_pretty(t.taille_schema::BIGINT) AS taille_schema, 
       pg_size_pretty(t.taille_totale_schema::BIGINT) AS taille_totale_schema
FROM (SELECT schemaname,          
             SUM(pg_relation_size(schemaname || '.' || tablename)) AS taille_schema, 
             SUM(pg_total_relation_size(schemaname || '.' || tablename)) AS taille_totale_schema
       FROM pg_tables           
GROUP BY schemaname) AS t 
ORDER BY taille_totale_schema DESC

Génère un tableau de résultats de la forme : (ex: base 'sde' sur vmpw38)

	schemaname	taille_schema	taille_totale_schema
1	refext_ign_bd_topo	8687 MB	10 GB
2	refext_cadastre	2995 MB	4215 MB
3	refext_monde	2049 MB	4029 MB
…	…

Taille des tables d'une base de données

SELECT TABLE_NAME, 
       pg_size_pretty(t.Taille_donnees::BIGINT) AS Taille_donnees, 
       pg_size_pretty(t.Taille_totale::BIGINT) AS Taille_totale
FROM ( 
    SELECT TABLE_NAME, 
           SUM(pg_relation_size(table_schema || '.' || TABLE_NAME)) AS Taille_donnees,
           SUM(pg_total_relation_size(table_schema || '.' || TABLE_NAME)) AS Taille_totale
    FROM information_schema.tables
    GROUP BY TABLE_NAME
    )
AS t 
ORDER BY Taille_totale DESC