On Wed, Jan 25, 2006 at 03:40:38PM -0800, Michael Crozier wrote:
> Why is there so much free space with no dead tuples? This table has likely
> had serveral columns added over time, is that part of the problem? Indexes?
An ordinary vacuum frees space for PostgreSQL's use but it doesn't
shrink the table's file(s) and return space to the operating system;
this remains true in the latest versions. If the table was ever
as large as you're seeing then it won't shrink unless you do a
vacuum full, cluster, dump/drop/create/restore, etc. Here's an
example in 8.1.2:
test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> INSERT INTO foo SELECT 1 FROM generate_series(1, 100000);
INSERT 0 100000
test=> UPDATE foo SET x = 2;
UPDATE 100000
test=> VACUUM foo;
VACUUM
test=> \x
Expanded display is on.
test=> SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]------+--------
table_len | 7225344
tuple_count | 100000
tuple_len | 3200000
tuple_percent | 44.29
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 3205936
free_percent | 44.37
This example shows the same thing you're seeing: lots of free space
but no dead tuples. The table *did* have a lot of dead tuples due
to the update but the vacuum freed that space. If we do another
update the table length doesn't change (not by much, anyway) because
the database is able to reuse the free space without having to
allocate more pages via the operating system:
test=> UPDATE foo SET x = 3;
UPDATE 100000
test=> SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]------+--------
table_len | 7233536
tuple_count | 100000
tuple_len | 3200000
tuple_percent | 44.24
dead_tuple_count | 100000
dead_tuple_len | 3200000
dead_tuple_percent | 44.24
free_space | 12348
free_percent | 0.17
--
Michael Fuhr