CODEWITHSUNDEEP
postgresql
user2099762
user2099762

Reputation: 183

why is my postgres database so big?

i'm running PostgreSQL 9.4.6 and I have a database that's 78GB found by running 

SELECT pg_size_pretty(pg_database_size('<db name>')) As fulldbsize;

In order to track down why my database is so big I've tried the following:-

SELECT relname as "Table",      
       pg_size_pretty(pg_total_relation_size(relid)) As "Size",
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" 
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

But when adding up the resulting tables I get to about 15GB.

What's using my up the space in my database? What further commands can I run?

Upvotes: 1

Views: 3053

Answers (2)

jimmy8ball
jimmy8ball

Reputation: 756

you can also get information about all objects and their respective sizes

 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;

This should detail:

  • objectname – The name of the object
  • objecttype – r for the table, i for an index, t for toast data, ...
  • entries – The estimated number of entries in the object (e.g. rows)
  • size – The size of the object

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246163

This will list all objects an their size:

SELECT relname, pg_total_relation_size(oid)
FROM pg_class
ORDER BY 2 DESC;

That should help you account for the database size.

Upvotes: 1

Related Questions