Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D0C4F9F9E10 for ; Mon, 17 Sep 2007 09:24:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 10222-07 for ; Mon, 17 Sep 2007 09:24:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 4DE799F9CA2 for ; Mon, 17 Sep 2007 09:24:29 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id C3D9C564D4; Mon, 17 Sep 2007 12:24:18 +0000 (UTC) Received: (hashcash-sendmail, from uid 1001); Mon, 17 Sep 2007 07:24:15 -0500 Date: Mon, 17 Sep 2007 07:24:14 -0500 From: Decibel! To: Erik Jones Cc: Frank Schoep , pgsql-performance@postgresql.org Subject: Re: [Again] Postgres performance problem Message-ID: <20070917122414.GJ16759@decibel.org> References: <46E636CB.8080008@rentalia.com> <43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org> <46E64891.9080005@rentalia.com> <20070911225242.GG38801@decibel.org> <46E79865.906@rentalia.com> <2ca799770709121156l7ddbb20agf8086e8bfcbc881a@mail.gmail.com> <2752E5D2-6FA0-4416-8FF4-4CE20573F67A@myemma.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="DNUSDXU7R7AVVM8C" Content-Disposition: inline In-Reply-To: <2752E5D2-6FA0-4416-8FF4-4CE20573F67A@myemma.com> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070917:erik@myemma.com::jFT5IlW86t0aP/fN:002bDw X-Hashcash: 1:20:070917:frank@ffnn.nl::c35Knevj4qt12zcF:00004M0H X-Hashcash: 1:20:070917:pgsql-performance@postgresql.org::h6nypr32dtKC5nxo:00000 0000000000000000000000001tBC X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/290 X-Sequence-Number: 26835 --DNUSDXU7R7AVVM8C Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Wed, Sep 12, 2007 at 03:01:12PM -0500, Erik Jones wrote: >=20 > On Sep 12, 2007, at 2:19 PM, Frank Schoep wrote: >=20 > >On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote: > >>On 9/12/07, Mikko Partio wrote: > >>>? > >>>Aren't you mixing up REINDEX and CLUSTER? > >> > >>? > >>Either one does what a vacuum full did / does, but generally does =20 > >>it better. > > > >On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE =20 > >I'd like to ask if CLUSTER is safe to run on a table that is in =20 > >active use. > > > >After updating my maintenance scripts from a VACUUM FULL (add me to =20 > >the list) to CLUSTER (which improves performance a lot) I noticed I =20 > >was getting "could not open relation ?" errors in the log while the =20 > >scripts ran so I reverted the change. This was on 8.1.9. >=20 > You'd probably see the same behavior on 8.2.x. CLUSTER is not =20 > transactionally safe so you don't want to run CLUSTER on tables that =20 > are actively being used. I believe that's been fixed for 8.3. Actually, that's a bit over-conservative... what happens prior to 8.3 is that CLUSTER rewrites the table using it's XID for everything. That can break semantics for any transactions that are running in serializable mode; if you're just using the default isolation level of read committed, you're fine with CLUSTER. --=20 Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org=20 Give your computer some brain candy! www.distributed.net Team #1828 --DNUSDXU7R7AVVM8C Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.3 (FreeBSD) iD8DBQFG7nHudO30qud8SkgRAiswAKCgoMiT80Ei+6R/ZmAGYsQU/OBqGACg2GV5 3712UJ7hpSYBwMHeKFQh1yw= =Gpfr -----END PGP SIGNATURE----- --DNUSDXU7R7AVVM8C--