Posted 11 months ago. Visible to the public. Linked content.
PostgreSQL: How to show table sizes
When you have a large PG database, you may want to find out which tables are consuming the most disk space.
You can easily check this using the following SQL statement from the PostgreSQL wiki.
CopySELECT 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 5;
Example output:
Copyrelation | total_size ----------------------+------------ public.snapshots | 823 MB public.invoice_items | 344 MB public.messages | 267 MB public.topics | 40 MB public.invoices | 35 MB (5 rows)
See the PostgreSQL wiki Data Usage page for more information on calculating sizes of databases or tables.
By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.
