... | @@ -36,12 +36,29 @@ deploy a cluster, for example just by including a single class. |
... | @@ -36,12 +36,29 @@ deploy a cluster, for example just by including a single class. |
|
|
|
|
|
## Find what is taking up space
|
|
## Find what is taking up space
|
|
|
|
|
|
This will report size information for all tables, in both raw bytes and "pretty" form:
|
|
This will report size and count information for all "relations", which
|
|
|
|
includes indexes:
|
|
SELECT *, pg_size_pretty(total_bytes) AS total
|
|
|
|
|
|
SELECT relname AS objectname
|
|
|
|
, relkind AS objecttype
|
|
|
|
, reltuples AS "#entries"
|
|
|
|
, pg_size_pretty(relpages::bigint*8*1024) AS size
|
|
|
|
FROM pg_class
|
|
|
|
WHERE relpages >= 8
|
|
|
|
ORDER BY relpages DESC;
|
|
|
|
|
|
|
|
It might be difficult to track the *total* size of a table because it
|
|
|
|
doesn't add up index size, which is typically small but *can* grow
|
|
|
|
quite significantly.
|
|
|
|
|
|
|
|
This will report the same, but with aggregated results:
|
|
|
|
|
|
|
|
SELECT table_name
|
|
|
|
, row_estimate
|
|
|
|
, pg_size_pretty(total_bytes) AS total
|
|
|
|
, pg_size_pretty(table_bytes) AS TABLE
|
|
, pg_size_pretty(index_bytes) AS INDEX
|
|
, pg_size_pretty(index_bytes) AS INDEX
|
|
, pg_size_pretty(toast_bytes) AS toast
|
|
, pg_size_pretty(toast_bytes) AS toast
|
|
, pg_size_pretty(table_bytes) AS TABLE
|
|
|
|
FROM (
|
|
FROM (
|
|
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes 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
|
|
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
|
... | @@ -53,7 +70,7 @@ This will report size information for all tables, in both raw bytes and "pretty" |
... | @@ -53,7 +70,7 @@ This will report size information for all tables, in both raw bytes and "pretty" |
|
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE relkind = 'r'
|
|
WHERE relkind = 'r'
|
|
) a
|
|
) a
|
|
) a ORDER BY total_bytes;
|
|
) a ORDER BY total_bytes DESC LIMIT 10;
|
|
|
|
|
|
Same with databases:
|
|
Same with databases:
|
|
|
|
|
... | | ... | |