postgresql total database size not matching sum of individual table sizes

Question

My postgres database (version 8.2.3) is showing a size of 24 GB.

To get this figure I execute this query:

SELECT
  oid, datname, pg_database_size(datname) as actualsize, 
  pg_size_pretty(pg_database_size(datname)) as size 
FROM pg_database 
ORDER BY datname

However, the sizes of the individual tables in the same database are not adding 24 GB when I execute this query:

SELECT 
  schemaname, tablename, pg_size_pretty(size) AS size_pretty, 
  pg_size_pretty(total_size) AS total_size_pretty 
FROM
  (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size, 
   pg_total_relation_size(schemaname||'.'||tablename) AS total_size 
   FROM pg_tables where schemaname = 'public') AS TABLES 
ORDER BY total_size DESC;

I've done sum up the individual tables size with pretty size and total_size, but the value does not match:

I am getting a pretty size of 3.5 GB

I am getting a total_size_pretty of 5.2 GB.

Where do I find out what the rest of the total space is being used for?

rudi-moore · Accepted Answer · 2011-02-24 14:30:18Z

10

Normally i'm using the following two querys to get the size of database objects. Hope this helps.

SELECT pg_size_pretty(sum(pg_relation_size(pg_class.oid))::bigint), nspname,
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'toast' ELSE pg_class.relkind::text END
FROM pg_class
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
GROUP BY pg_class.relkind, nspname
ORDER BY sum(pg_relation_size(pg_class.oid)) DESC;

-

SELECT pg_size_pretty(pg_relation_size(pg_class.oid)), pg_class.relname, pg_namespace.nspname,
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'TOAST' ELSE pg_class.relkind::text END
FROM pg_class 
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
ORDER BY pg_relation_size(pg_class.oid) DESC;

answered Feb 24, 2011 at 14:30

rudi-moore

2,7101 gold badge19 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

d.raev Over a year ago

very nice queries, Thanks a lot. (found10GB of indexes ...)

Frank Heikens · Accepted Answer · 2011-02-23 07:04:33Z

2

And what's total size of all the indexes and large objects?

answered Feb 23, 2011 at 7:04

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

postgresql total database size not matching sum of individual table sizes

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related