Re: [PERFORM] [ADMIN] reclaiming disk space after major updates

[Dan Harris]

Andrew Sullivan wrote:

On Thu, Jun 07, 2007 at 03:26:56PM -0600, Dan Harris wrote:
They don't always have to be in a single transaction, that's a good idea to 
break it up and vacuum in between, I'll consider that.  Thanks


If you can do it this way, it helps _a lot_.  I've had to do this
sort of thing, and breaking into groups of a couple thousand or so
really made the difference.

A



One more point in my original post.. For my own education, why does VACUUM FULL 
prevent reads to a table when running (I'm sure there's a good reason)?  I can 
certainly understand blocking writes, but if I could still read from it, I'd 
have no problems at all!


-Dan

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

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

Re: [PERFORM] [ADMIN] reclaiming disk space after major updates

[Andrew Sullivan]

On Fri, Jun 08, 2007 at 08:29:24AM -0600, Dan Harris wrote:
 
 One more point in my original post.. For my own education, why does VACUUM 
 FULL prevent reads to a table when running (I'm sure there's a good 
 reason)?  I can certainly understand blocking writes, but if I could still 
 read from it, I'd have no problems at all!

It has to take an exclusive lock, because it actually moves the bits
around on disk.  Since your SELECT query could be asking for data
that is actually in-flight, you lose.  This is conceptually similar
to the way defrag works on old FAT-type filesystems: if you used one,
you'll remember that when you were defragging your disk, if you did
anything else on that disk the defrag would keep restarting.  This
was because the OS was trying to move bits around, and when you did
stuff, you screwed up its optimization.  The database works
differently, by taking an exclusive lock, but the basic conceptual
problem is the same.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
--Scott Morris

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster