Re: insert/update performance

On Sat, Jan 23, 2016 at 1:16 PM, Jinhua Luo <luajit(dot)io(at)gmail(dot)com> wrote:
>
> Hi,
>
> The vacuum doesn't recycle the rows obsoleted by update?
>

It does free up the space which can be used by future inserts.

> I don't think
> so. In the above vacuum result, I do not delete any rows, but the
> vacuum still recycles a fraction of rows, obviously they're obsoleted
> by update.
>
> I know plain vacuum (without full option) do not reduce the size of
> the whole table file/segments, but it should refresh the fsm. In my
> case, the update statement did modify the index column, but is it
> related to the fsm? I think anyways, the update would obsolete
> previous versions, as long as they are not hold by any active
> transactions, they would be recycled and count in the fsm, right?

I also think it will be added to fsm.

> I
> just cannot understand why the recycle ratio is not 50%.
>

At the moment, I am also not able to see why it is so. You might
want to first try with a simple test (Can you extract insert/update
statements from application and run it manually for couple of times
and then run Vacuum to see the result).

By anychance have you set a value for vacuum_defer_cleanup_age?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com