Comment trouvez-vous la taille de disque d’une table Postgres / PostgreSQL et ses index

Essayez les fonctions de taille d’object de firebase database . Un exemple:

SELECT pg_size_pretty(pg_total_relation_size('"".""')); 

Pour toutes les tables, quelque chose comme:

 SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC; 

Edit: Voici la requête soumise par @phord, pour plus de commodité:

 SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name FROM information_schema.tables ) AS all_tables ORDER BY total_size DESC ) AS pretty_sizes; 

Je l’ai légèrement modifié pour utiliser pg_table_size() afin d’inclure les métadonnées et de faire la pg_table_size() des tailles.

Afficher les tailles de firebase database:

\l+

par exemple

 => \l+ berbatik_prd_commerce | berbatik_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 19 MB | pg_default | berbatik_stg_commerce | berbatik_stg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8633 kB | pg_default | bursasajadah_prd | bursasajadah_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1122 MB | pg_default | 

Montrer les tailles de table:

\d+

par exemple

 => \d+ public | tuneeca_prd | table | tomcat | 8192 bytes | public | tuneeca_stg | table | tomcat | 1464 kB | 

Ne fonctionne que dans psql .

(Résumé de la réponse de @ zkutch .)

Si le nom de la firebase database est snort , la phrase suivante lui donne sa taille:

 psql snort -c "\l+" | grep snort | awk -F "|" '{print $7}' 

Les tables PostgreSQL ont trois composants: la table elle-même, ses index et les données TOAST potentielles. Il y a quelques exemples montrant comment glisser et dés les informations disponibles de différentes manières sur http://wiki.postgresql.org/wiki/Disk_Usage

Tyr this: (Statistiques de taille / usage d’index)

 SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size, pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size, CASE WHEN indisunique THEN 'Y' ELSE 'N' END AS UNIQUE, idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_tables t LEFT OUTER JOIN pg_class c ON t.tablename=c.relname LEFT OUTER JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) AS foo ON t.tablename = foo.ctablename WHERE t.schemaname='public' ORDER BY 1,2; 

Juste pour info, j’ai eu la meilleure réponse de @aib et je l’ai un peu modifiée pour:

• Obtenir uniquement les tables du schéma “public”
• afficher également les vues des vues matérialisées et la taille de l’index

Sur la vue matérialisée, nous pouvons utiliser index pour rafraîchir les vues matérialisées simultanément , ce qui permet de les utiliser lors de la mise à jour.

Eh bien, ma requête sera la suivante:

 SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( -- tables from 'public' SELECT table_name FROM information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE' union -- materialized views SELECT oid::regclass::text as table_name FROM pg_class WHERE relkind = 'm' order by table_name ) AS all_tables -- ORDER BY total_size DESC order by table_name ) AS pretty_sizes 

Vérifiez ce wiki. https://wiki.postgresql.org/wiki/Disk_Usage

 SELECT *, pg_size_pretty (total_bytes) AS total
     , pg_size_pretty (index_bytes) AS INDEX
     , pg_size_pretty (toast_bytes) AS
     , pg_size_pretty (tables_bytes) AS TABLE
   DE (
   SELECT *, total_bytes-index_bytes-COALESCE (toast_bytes, 0) AS table_bytes FROM (
       SELECT c.oid, nspname AS table_schema, relname AS TABLE_NAME
               , c.reltuples AS row_estimate
               , pg_total_relation_size (c.oid) AS total_bytes
               , pg_indexes_size (c.oid) AS index_bytes
               , pg_total_relation_size (reltoastrelid) AS toast_bytes
           FROM pg_class c
           LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
           WHERE relkind = 'r'
   ) une
 ) une

La requête ci-dessous vous servira

 SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20; 

Voir ce lien: https://wiki.postgresql.org/wiki/Disk_Usage

Essayez ce script pour trouver toutes les tailles de table:

 SELECT table_schema || '.' || table_name AS TableName, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC 

Pour un autre script différent pour trouver la taille dans PostgreSQL, veuillez visiter cette URL: http://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/