Re: postgres table have a large number of relpages and occupied a big memory size

On 5 Srpen 2011, 10:52, Vivekkumar Pandey wrote:
> Hi,
> slon process is running on the system .
>
> Now , I have a question that Why dead tupples are remains in the table
> while AUTOVACUUM process running at the fixed interval of time without
> any error.

Well, because that's how vacuum works. Vacuum does not compact the tables,
it just marks the tuples as "deleted" so the space may be reused for new
rows (inserted or updated).

VACUUM FULL compacts the table, but that's not how autovacuum works,
autovacuum uses plain VACUUM.

So it's possible that, for example

(a) once in the past the table grew to this size, then many rows were
deleted but only a few inserted, so the space was not reused

(b) there's a long running transaction that accesses the table, so the
rows may not be marked as dead

It's really difficult to say which is true.

> Also suggest the Query that can view the dead tuples in the table.

You can't see the dead tuples with a query - that's why they're called
dead. It would be possible with the "read uncommitted" isolation level,
but that's not implemented (you get "read committed" instead).

If you really need to inspect the dead tuples, you have to use
"pageinspect" contrib module, that gives you access to the raw data.

Tomas