On Thu, 2007-03-01 at 14:35 +0100, Zeugswetter Andreas ADI SD wrote:
> > -- start the VACUUM from the first non-filled block
> >
> > So if we do this, we wouldn't need to worry about HOT tuples
> > at all, nor would we need to wait until all transactions are gone.
>
> You need to wait until you are allowed to truncate if you want
> concurrency.
> Or a concurrent scan might miss a row, because the visible tuple got
> truncated away.

I was not suggesting that we remove visible rows through truncation.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> > > -- start the VACUUM from the first non-filled block
> > >
> > > So if we do this, we wouldn't need to worry about HOT tuples at
all,
> > > nor would we need to wait until all transactions are gone.
> >
> > You need to wait until you are allowed to truncate if you want
> > concurrency.
> > Or a concurrent scan might miss a row, because the visible tuple got

> > truncated away.
>
> I was not suggesting that we remove visible rows through truncation.

Sure, unless you suggest to not truncate during this vacuum run ?
But we are talking about vacuum full, so truncation is essential.

It was suggested to do a dummy null update to move live tuples up front.
The old version is still visible for serializable txns.

Andreas

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Ühel kenal päeval, N, 2007-03-01 kell 15:03, kirjutas Zeugswetter
Andreas ADI SD:
> > > > -- start the VACUUM from the first non-filled block
> > > >
> > > > So if we do this, we wouldn't need to worry about HOT tuples at
> all,
> > > > nor would we need to wait until all transactions are gone.
> > >
> > > You need to wait until you are allowed to truncate if you want
> > > concurrency.
> > > Or a concurrent scan might miss a row, because the visible tuple got
>
> > > truncated away.
> >
> > I was not suggesting that we remove visible rows through truncation.
>
> Sure, unless you suggest to not truncate during this vacuum run ?
> But we are talking about vacuum full, so truncation is essential.
>
> It was suggested to do a dummy null update to move live tuples up front.
> The old version is still visible for serializable txns.

There should probably be a wait-other-trxs to finish stage between
COMPACT and 2nd VACUUM if truncation at this cycle is absolutely needed

> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> This means that
>
> VACUUM FULL mytable;
>
> would translate to:
>
> VACUUM mytable; -- make free space
> COMPACT mytable; -- move tuples in a bunch of small transactions
> -- might have a GUC for max trx length
> VACUUM mytable; -- free the tuples at the end and give space back to fs


VACUUM my table [n] tuples; -- free tuples at the end but only up to [n]
tuples.


Joshua D. Drake


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, Mar 01, 2007 at 04:54:44PM +0200, Hannu Krosing wrote:
> > > I was not suggesting that we remove visible rows through truncation.
> >
> > Sure, unless you suggest to not truncate during this vacuum run ?
> > But we are talking about vacuum full, so truncation is essential.
> >
> > It was suggested to do a dummy null update to move live tuples up front.
> > The old version is still visible for serializable txns.
>
> There should probably be a wait-other-trxs to finish stage between
> COMPACT and 2nd VACUUM if truncation at this cycle is absolutely needed

Or you could just do a vacuum a bit later. If we've also got the DSM at
that point, vacuum should be fast.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Thu, Mar 01, 2007 at 01:05:28PM +0000, Simon Riggs wrote:
> ISTM a radical approach is needed, so I'm very open to discussion about
> this and how we cope.
>
> If we break down the main thoughts into a few parts:
>
> 1. would like a way to CLUSTER/VACUUM FULL where we don't have to move
> all of the tuple versions, just the current ones.
>
> 2. would like a way to compact a table more efficiently
>
> Your idea does (2) in a concurrent manner, which is very good.

Along similar lines, I think it would also be very useful to have a mode
where any time a tuple on the last X pages gets updated it's moved off
of it's existing page in the relation. Kind of like a 'shrink the heap
in the background'. Granted, this probably won't compact as much as
something more aggressive would, but it should be essentially free.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings