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?
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.
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.
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".
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With