Re: [HACKERS] row reuse while UPDATE and vacuum analyze problem

On Wed, 28 Jul 1999, The Hermit Hacker wrote:

> Date: Wed, 28 Jul 1999 09:00:21 -0300 (ADT)
> From: The Hermit Hacker <scrappy(at)hub(dot)org>
> To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> Cc: pgsql-hackers(at)postgreSQL(dot)org, tgl(at)sss(dot)pgh(dot)pa(dot)us, vadim(at)krs(dot)ru
> Subject: Re: [HACKERS] row reuse while UPDATE and vacuum analyze problem
>
> On Wed, 28 Jul 1999, Oleg Bartunov wrote:
>
> > How update performance could be increased if:
> > 1. 'vacuum analyze' will analyze index file
> > 2. reuse row instead of inserting
>
> Just to clarify, 'reuse row' won't replace inserting (to the best of my
> knowledge), only reduce space wastage between vacuum's. Especially, again
> TTBOMK, with MVCC, where each "instance" of a row is serialized.
>
> Actually, there is a tought...if I understand the concept of MVCC, how is
> reusing a row going to work? My understanding is that I can "physically"
> have to copies of a row in a table, one newer then the other. So, if
> someone is running a SELECT while I'm doing an UPDATE, their SELECT will
> take the older version of hte row (the row at the time their SELECT
> started)...depending on how busy that table is, there will have to be some
> sort of mechanism for determining how 'stale' a row is, no?
>
> ie. on a *very* large table, with multiple SELECT/UPDATEs happening?

This is what I noticed when start my testing about a week ago - I got
duplicates, because of multiple concurrent processes trying
inserts/updates. After LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE
all problem were gone except performance slow downs a little bit.
But after many updates performance dergrades very much IMO because
table and index size grow even if I update the same row and
even 'vacuum analyze' doesn't reduce the size of index file.
In principle, I could live with cron job running vaccumdb every hour but
vacuum doesn't touch indices.
I hope I'll meet Vadim in Moscow and we discuss MVCC aspects in native
russian :-)

Regards,

Oleg

>
> Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
>

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83