Table using more disk space than expected

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

Responses

Browse pgsql-general by date

  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