servidor - instalar postgresql

¿Cómo se encuentra el tamaño del disco de una tabla Postgres/PostgreSQL y sus índices?

Question

Estoy llegando a Postgres desde Oracle y estoy buscando una forma de encontrar la tabla y el tamaño del índice en términos de bytes / MB / GB / etc., o incluso mejor el tamaño para todas las tablas. En Oracle tuve una desagradable larga consulta que miraba user_lobs y user_segments para devolver una respuesta. Supongo que en Postgres hay algo que puedo usar en las tablas de information_schema, pero no veo dónde. Gracias por adelantado.

Answer 1

La siguiente consulta le servirá

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;

Ver este enlace: https://wiki.postgresql.org/wiki/Disk_Usage

Answer 2

Las tablas de PostgreSQL tienen tres componentes: la tabla en sí misma, los índices que contiene y los datos potencialmente TOAST. Hay un par de ejemplos que muestran cómo deslizar y cortar la información disponible de varias maneras en wiki.postgresql.org/wiki/Disk_Usage

Answer 3

Mostrar tamaños de base de datos:

/l+

p.ej

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

Mostrar tamaños de tabla:

/d+

p.ej

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

Solo funciona en psql .

(Resumen de la respuesta de @ zkutch ).

Answer 4

Pruebe esta secuencia de comandos para encontrar todo el tamaño de la tabla:

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

Para obtener otro script diferente para encontrar el tamaño en PostgreSQL, visite esta url: http://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/

Answer 5

Pruebe las funciones de tamaño de objeto de base de datos . Un ejemplo:

SELECT pg_size_pretty(pg_total_relation_size(''"<schema>"."<table>"''));

Para todas las tablas, algo como:

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;

Editar: Aquí está la consulta enviada por @phord, para su conveniencia:

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;

Lo he modificado ligeramente para usar pg_table_size() para incluir metadatos y hacer que los tamaños se sumen.

Answer 6

Si el nombre de la base de datos es snort , la siguiente oración le da tamaño:

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

Answer 7

Solo por información, obtuve la excelente respuesta de @aib y la modifiqué un poco por:

• obteniendo solo tablas del esquema "público"
• mostrar también datos de visitas materializadas y tamaño de índice

En la vista materializada, podemos usar el índice para actualizar vistas materializadas al mismo tiempo , lo que permite usarlas durante la actualización.

Bueno, mi consulta será la siguiente:

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

Answer 8

Tyr esto: (Tamaño de índice / estadísticas de uso)

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;

Answer 9

revisa esta 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 toast , pg_size_pretty(table_bytes) AS TABLE FROM ( 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'' ) a ) a