Postgres Pro
Downloads
Home   >   mailing lists

Zero dead tuples, when significant apparent bloat - Mailing list pgsql-general

From John Melesky
Subject Zero dead tuples, when significant apparent bloat
Date
Msg-id CAJ1GNCotqp=9qyCJAwz_20Q769x5jZ5C7AgR-qg8xhaBbyfQUA@mail.gmail.com
Whole thread Raw
Responses Re: Zero dead tuples, when significant apparent bloat  (bricklen <bricklen@gmail.com>)
Re: Zero dead tuples, when significant apparent bloat  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
Here's the situation:

 relation_size  | indexes_size   | total_relation_size 
----------------+----------------+---------------------
 997 MB         | 2073 MB        | 3070 MB
(1 row)

If I select n_dead_tup from pg_stat_user_tables, I get:

 n_dead_tup
------------
          0

Okay, so I run ANALYZE table, then check again:

 n_dead_tup
------------
          0

Finally, I run VACUUM FULL ANALYZE table. Now I see:

 relation_size  | indexes_size   | total_relation_size 
----------------+----------------+---------------------
 118 MB         | 200 MB         | 319 MB
(1 row)

.... ?

It seems clear that there were dead tuples, since the table size shrank to an eighth of its previous size. Why did analyze not pick that up?

Am I missing something?

This is a very large database, so we want to introspect against live/dead tuple percentage to minimize the tables we run a VACUUM FULL against.

I've been staring at this for days.

-john

--
John Melesky | Sr Database Administrator
503.284.7581 x204 | john.melesky@rentrak.com
RENTRAK | www.rentrak.com | NASDAQ: RENT

Notice: This message is confidential and is intended only for the
recipient(s) named above. If you have received this message in error,
or are not the named recipient(s), please immediately notify the
sender and delete this message.

pgsql-general by date:

Previous
From: Jerry Sievers
Date:
Subject: Re: validate synatax
Next
From: bricklen
Date:
Subject: Re: Zero dead tuples, when significant apparent bloat