Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgresql total database size not matching sum of individual table sizes

Tags:

postgresql

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?

like image 447
Bhava Avatar asked Feb 23 '11 05:02

Bhava


People also ask

How is total calculated in PostgreSQL?

Use the SUM() function to calculate the sum of values. Use the DISTINCT option to calculate the sum of distinct values. Use the SUM() function with the GROUP BY clause to calculate the sum for each group.

How can I check database size in PostgreSQL?

To determine the size of a database, type the following command. Replace dbname with the name of the database that you want to check: Copy SELECT pg_size_pretty( pg_database_size('dbname') ); Psql displays the size of the database.

How many tables is too much for Postgres?

Technically Postgres does not have a limit on the number of tables. However, each table is a file on the OS filesystem. And the OS probably has some opinion on how many files is "too many".

Can Postgres handle 100 million rows?

Aggregations vs. If you're simply filtering the data and data fits in memory, Postgres is capable of parsing roughly 5-10 million rows per second (assuming some reasonable row size of say 100 bytes). If you're aggregating then you're at about 1-2 million rows per second.


1 Answers

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;
like image 53
rudi-moore Avatar answered Oct 21 '22 23:10

rudi-moore