[PostgreSQL] Query per dimensionare un Database

[Roberto]

1. Numero totale di tuple in una tabella

Codice: Seleziona tutto

select count (*) as Totale from nome_tabella;

2. Dimensione fisica su disco di una tabella

Codice: Seleziona tutto

SELECT pg_size_pretty(pg_total_relation_size('nome_tabella'));

3. Dimensione fisica su disco di un database

Codice: Seleziona tutto

SELECT pg_size_pretty(pg_database_size('nome_database'));

4. Elenco ordinato per dimensione fisica su disco dalla più grande alla più piccola di tutte le tabelle di un database

Codice: Seleziona tutto

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

5. Elenco ordinato per dimensione fisica su disco dal più grande al più piccola degli indici di tutte le tabelle di un database

Codice: Seleziona tutto

SELECT table_schema,
       table_name,
       pg_size_pretty(pg_indexes_size('"' || table_schema || '"."' || table_name || '"')) AS index_size
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_indexes_size('"' || table_schema || '"."' || table_name || '"') DESC;

6. Calcolo del DB Bloat percentuale di tutte le tabelle in uno schema

Codice: Seleziona tutto

SELECT tablename,
       pg_size_pretty(bsize) AS "size",
       pg_size_pretty(real_size) AS "actual_size",
      (bsize - real_size) AS wasted_bytes,
       pg_size_pretty((bsize - real_size)) AS "wasted_size",
       CASE
         WHEN bsize > 0
         THEN (100 * (bsize - real_size)/bsize)::numeric(5,2)
         ELSE 0
       END AS "bloat_percent"
FROM (SELECT tablename,
	     ceil(table_size / (current_setting('block_size')::numeric)) * current_setting('block_size')::bigint as bsize,
	     ceil(real_size / (current_setting('block_size')::numeric)) * current_setting('block_size')::bigint as real_size
      FROM (SELECT tablename,
		   pg_table_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) as table_size,
		   pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) as real_size
	    FROM pg_tables
	    WHERE schemaname = 'public')
      t)
t2;