Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D8AC42E02A8 for ; Tue, 5 Feb 2008 17:13:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67748-05 for ; Tue, 5 Feb 2008 17:13:50 -0400 (AST) Received: from hi-media.com (9.140-14-84.ripe.coltfrance.com [84.14.140.9]) by postgresql.org (Postfix) with ESMTP id EB9132E01B5 for ; Tue, 5 Feb 2008 17:13:46 -0400 (AST) Received: from dalistar.local (cac94-7-82-243-27-82.fbx.proxad.net [82.243.27.82]) by hi-media.com (Postfix) with ESMTP id 6EA791880DF; Tue, 5 Feb 2008 22:11:19 +0100 (CET) From: Dimitri Fontaine To: pgsql-admin@postgresql.org Subject: Re: Where to find information on the new HOT tables? Date: Tue, 5 Feb 2008 22:13:37 +0100 User-Agent: KMail/1.9.7 Cc: Alvaro Herrera , Chris Hoover , Bruce Momjian References: <1d219a6f0802051142k882827cyd38ea7a3d7c1e4ea@mail.gmail.com> <1d219a6f0802051211o4814d979rdc988c2ad60b81d0@mail.gmail.com> <20080205202831.GF6483@alvh.no-ip.org> In-Reply-To: <20080205202831.GF6483@alvh.no-ip.org> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart2469296.iHBBnY6Ei4"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200802052213.41185.dfontaine@hi-media.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/39 X-Sequence-Number: 28113 --nextPart2469296.iHBBnY6Ei4 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, Le Tuesday 05 February 2008 21:28:31 Alvaro Herrera, vous avez =E9crit=A0: > Perhaps there is a user-level document somewhere. I don't think such a document already exists, even rtfm_please doesn't know= =20 about any as of now. So I'll try to begin something here, and depending on= =20 the comments I'll publish a short user oriented HOT introduction article. Here we go... PostgreSQL implements HOT (Heap Only Tuples), a way for the server to limit= =20 the work it has to make when updating tuples. That's what we call an=20 optimization :) PostgreSQL MVCC implementation choice means that updating a tuple create a= =20 entire new version of it and mark the old one as no longer valid (as of the= =20 updating transaction id). Then VACUUM will have to clean out the old=20 reference as soon as possible. Let's not forget that the indexes pointing the the old tuples need to point= to=20 the new version of it as of transaction id. PostgreSQL currently does not=20 save visibility information into the index, though, reducing the janitoring= =20 here. But still, for the index, the operation of updating a tuple is=20 equivalent to a delete and an insert. That's before HOT. Starting with PostgreSQL 8.3, when a tuple is updated and if the update onl= y=20 concerns non-indexed columns, the RDBMS is smart enough for the existing=20 indexes not to need any update at all. This is done by creating a new tuple if possible on the same page as the ol= d=20 one, and maintaining a chain of updated tuples linking a new version to the= =20 old one. An HOT tuple is in fact one that can't be reached from any index.= =20 VACUUM will now only have to prune the tuple versions of the chain that are= =20 no more visible, and as no index were updated (there was no need to), there= 's=20 no VACUUM work to get done on the indexes. Of course, for HOT to work properly, PostgreSQL has now to follow each HOT= =20 chain when SELECT'ing tuples and using an index, but the same amount of=20 tuples version was to be read before HOT too. The difference is that with H= OT=20 the new versions of the HOT-updated tuples are no more reachable via the=20 index directly, so PostgreSQL has to follow the chain when reading the heap. Please comment and correct me if my understanding is wrong (which wouldn't = be=20 a surprise), if this article over simplified, or not really written in=20 English :) I'd like to publish a correct version of this for us to point asking users = to,=20 or maybe it could even end up as official documentation/FAQ material? Regards, =2D-=20 dim --nextPart2469296.iHBBnY6Ei4 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQBHqNGFlBXRlnbh1bkRAvSsAKCSSDvcTNB57TZEDx97xQ2dPL+m4QCgtlyX 2pvFjn+wW7FSM1c4v+eMx9o= =MGCb -----END PGP SIGNATURE----- --nextPart2469296.iHBBnY6Ei4--