The following bug has been logged on the website:
Bug reference: 7571
Logged by: Radovan Jablonovsky
Email address: radovan.jablonovsky@replicon.com
PostgreSQL version: 9.1.5
Operating system: CentOs 5.8 Linux 2.6.18-308.el5 x86_64 =
Description: =
During checking our company database size we used query, which was not the
best to find out the tables/db size but should do the job. The query was
tested on server with 32GB of RAM, 2 CPU with 4 cores and it was running
alone without other activity. It consumed almost all RAM forced server to
use swap and after 1hour it was still running. The simplified version of
query used 20% of memory and finished after 1hour 8min.
The size of pg_class is 3mil rows/objects and pg_namespace has 3000
rows/schemata.
query:
SELECT
schema_name,
sum(table_size)
FROM =
(SELECT =
pg_catalog.pg_namespace.nspname as schema_name, =
pg_relation_size(pg_catalog.pg_class.oid) as table_size,
sum(pg_relation_size(pg_catalog.pg_class.oid)) over () as database_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace =
ON relnamespace =3D pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name, database_size;
top - 10:50:44 up 20 days, 19:00, 1 user, load average: 1.15, 1.10, 0.84
Tasks: 239 total, 3 running, 236 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.1%us, 1.5%sy, 0.0%ni, 83.0%id, 0.5%wa, 0.0%hi, 0.0%si, =
0.0%st
Mem: 32946260k total, 32599908k used, 346352k free, 141924k buffers
Swap: 55043952k total, 85216k used, 54958736k free, 14036516k cached
Info from top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2016 postgres 25 0 22.8g 17g 3.2g R 96.1 56.0 19:17.01 postgres:
postgres db 10.0.1.10(49928) SELECT
Simplified version of query uses pg_tables. It has 0.5mil rows/tables.
Simplified version of query:
SELECT =
schemaname,
sum(pg_relation_size(schemaname || '.' || tablename))::bigint =
FROM pg_tables =
GROUP BY schemaname;