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

On 8 Srpen 2011, 8:02, Vivekkumar Pandey wrote:
> Hi ,
>
> I have version of PostgreSQL as given below:---
>
> version
> -------------------------------------------------------------------------------------
> PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.3.3 (SuSE Linux)
>
> And it does't have any n_dead_tup columns in pg_stat_all_tables like this:

That's because this column was added in 8.3.

BTW you're using a way too old version. It's not just that 8.1 is
unsupported, but the last minor subversion in this branch is 8.1.23. There
were numerous bugfixes since 8.1.2, so you should upgrade at least to this
version (and plan to upgrade to something more fresh).

> In what situations table creates a new row instead of using deleted
> marked tuples that's why size of table increases abnormally.

Whenever a row is modified - that means insert, update or delete. That's
how PostgreSQL MVCC works. This wasted space needs to be reclaimed, which
is exactly what (auto)vacuum does.

PS: With HOT this is not exactly true, but HOT is not available in 8.1
anyway. Another reason to upgrade to a more recent version.

Tomas