From: | Steve Pritchard <steve(dot)pritchard(at)bto(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Table using more disk space than expected |
Date: | 2015-09-23 16:36:37 |
Message-ID: | CAF7Aqmz7OD5qpg2F6KJah5qFkk58ct+vZLxp=t438EoFb=p7yg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a large table in Postgres 9.4.4 called 'observation', which is by
far the largest item in the database. When I pg_dump this table the
resulting file is about 9GB. However I've noticed that the pg data
directory uses about 30GB (excluding pg_xlog).
Looking at the space usage:
-- Size of 'warehouse' database:
select pg_size_pretty(pg_database_size('warehouse'));
-- 29GB
-- Total space used by observation table* including indexes*:
select pg_size_pretty(pg_total_relation_size('observation'));
-- 29GB
-- Excluding indexes:
select pg_size_pretty(pg_relation_size('observation'));
-- 20GB
-- Percentage of dead tuples:
select pg_stat_get_dead_tuples('observation'::regclass)::numeric * 100 /
pg_stat_get_live_tuples('observation'::regclass);
-- 13%
-- Average length of a row in bytes:
select avg(octet_length(t.*::text)) FROM observation t;
-- 287 bytes
-- Number of rows * average size of row:
select pg_size_pretty(count(obs_id) * 287) from observation;
-- 9.4 GB
If the live tuples take 9.4GB, then dead ones take 1.2GB = 10.6GB total.
What accounts for the remaining 9.4GB? (20GB - 10.6GB)
Steve Pritchard
British Trust for Ornithology
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-09-23 16:50:27 | Re: Table using more disk space than expected |
Previous Message | Israel Brewster | 2015-09-23 16:36:36 | Postgresql HA questions |