====== PostgreSQL : Volumétrie ======
\\ 

==== Taille des bases d'une instance ====
<code sql>
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
</code>

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 ====
<code sql>
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
</code>

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 ====
<code sql>
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
</code>