
Vendredi 21 mars 2025 à 15:15
Note de type #mémento pour calculer la taille des tables PostgreSQL.
Commande pour calculer la taille de la base de données entière :
postgres=# select pg_size_pretty(pg_database_size('postgres'));
pg_size_pretty
----------------
74 GB
Commande pour voir les détails table par table :
SELECT
nspname AS "schema",
pg_class.relname AS "table",
pg_size_pretty(pg_total_relation_size(pg_class.oid)) AS "total_size",
pg_size_pretty(pg_relation_size(pg_class.oid)) AS "data_size",
pg_size_pretty(pg_indexes_size(pg_class.oid)) AS "index_size",
pg_stat_user_tables.n_live_tup AS "rows",
pg_size_pretty(
pg_total_relation_size(pg_class.oid) /
(pg_stat_user_tables.n_live_tup + 1)
) AS "total_row_size",
pg_size_pretty(
pg_relation_size(pg_class.oid) /
(pg_stat_user_tables.n_live_tup + 1)
) AS "row_size"
FROM
pg_stat_user_tables
JOIN
pg_class
ON
pg_stat_user_tables.relid = pg_class.oid
JOIN
pg_catalog.pg_namespace AS ns
ON
pg_class.relnamespace = ns.oid
ORDER BY
pg_total_relation_size(pg_class.oid) DESC;
schema | table | total_size | data_size | index_size | rows | total_row_size | row_size
--------+-----------------+------------+------------+------------+---------+----------------+------------
public | table1 | 72 GB | 1616 MB | 1039 MB | 7456403 | 10 kB | 227 bytes
public | table2 | 1153 MB | 754 MB | 399 MB | 2747998 | 440 bytes | 287 bytes
public | table3 | 370 MB | 8192 bytes | 47 MB | 8 | 41 MB | 910 bytes
public | table4 | 232 kB | 136 kB | 56 kB | 422 | 561 bytes | 329 bytes
(7 rows)