Re: Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 04:04:41PM +0100, Michael Riess wrote:
>
> I thought that vacuum full only locks the table which it currently
> operates on? I'm pretty sure that once a table has been vacuumed, it can
> be accessed without any restrictions while the vacuum process works on
> the next table.

Yes, I think the way I phrased it was unfortunate. But if you issue
VACUUM FULL you'll get an exclusive lock on everything, although not
all at the same time. But of course, if your query load is like
this

BEGIN;
SELECT from t1, t2 where t1.col1 = t2.col2;
[application logic]
UPDATE t3 . . .
COMMIT;

you'll find yourself blocked in the first statement on both t1 and
t2; and then on t3 as well. You sure don't want that to happen
automagically, in the middle of your business day.

> I have 5000 tables and a workstation with 1 GB RAM which hosts an Apache
> Web Server, Tomcat Servlet Container and PostgreSQL. RAM is not
> something that I have plenty of ... and the hardware is fixed and cannot
> be changed.

I see. Well, I humbly submit that your problem is not the design of
the PostgreSQL server, then. "The hardware is fixed and cannot be
changed," is the first optimisation I'd make. Heck, I gave away a
box to charity only two weeks ago that would solve your problem
better than automatically issuing VACUUM FULL.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier