Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 870AB632925 for ; Wed, 10 Feb 2010 20:09:30 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 30040-06 for ; Thu, 11 Feb 2010 00:09:19 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f197.google.com (mail-iw0-f197.google.com [209.85.223.197]) by mail.postgresql.org (Postfix) with ESMTP id EA3986334AE for ; Wed, 10 Feb 2010 20:09:19 -0400 (AST) Received: by iwn35 with SMTP id 35so687722iwn.4 for ; Wed, 10 Feb 2010 16:09:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=Wb476vPHQ9sgLNy5uohrPVfKzlaYbhmreM2+RO+nzZo=; b=KjvAbCvovO2zB+KEy2JyKwFI9EWGiKbU5vidkxu9qAlTKS9gB2xq6/bTBBiVbybYFf GjWIdcqTIOlPDrrkC7F8bBloB7ZUQwV6jf6oCfRBcIq7O8s6A3WQU0CUVRCwZz4WIOLW LM6M4I4JLLSVZGPPmasyM/olDUDRw/wj6UEr8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=ZzverJiQRPEEdmAcV9JlCmsRLllf6tmX14KZ2YqWpjkualv6EQZ6nkUy5a1XfNNNzk 8uTKxqs1gXh1w/c8cRPKcEQZx9pne42Or+8bsim4TNa6fLlwItfyFxmyGOEMTEnfc4no 1akArpo6ty3Ul04whaBI9RYZw5DrhGuBRptoc= MIME-Version: 1.0 Received: by 10.231.155.18 with SMTP id q18mr1639971ibw.80.1265846957947; Wed, 10 Feb 2010 16:09:17 -0800 (PST) In-Reply-To: References: <7ac84c51002101530q59fa87c1w205fdf5243d9d69a@mail.gmail.com> Date: Wed, 10 Feb 2010 17:09:17 -0700 Message-ID: <7ac84c51002101609l190ec043s7fdde292969da457@mail.gmail.com> Subject: Re: perf problem with huge table From: Jon Lewison To: Dave Crooke Cc: rama , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001636d3425545f76e047f47f662 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/171 X-Sequence-Number: 37592 --001636d3425545f76e047f47f662 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Feb 10, 2010 at 4:48 PM, Dave Crooke wrote: > On Wed, Feb 10, 2010 at 5:30 PM, Jon Lewison wrote: > >> >> >> Just a nit, but Oracle implements MVCC. 90% of the databases out there >> do. >> > > Sorry, I spoke imprecisely. What I meant was the difference in how the rows > are stored internally .... in Oracle, the main tablespace contains only the > newest version of a row, which is (where possible) updated in place - > queries in a transaction that can still "see" an older version have to pull > it from the UNDO tablespace (rollback segments in Oracle 8 and older). > > In Postgres, all versions of all rows are in the main table, and have > validity ranges associated with them ("this version of this row existed > between transaction ids x and y"). Once a version goes out of scope, it has > to be garbage collected by the vacuuming process so the space can be > re-used. > > In general, this means Oracle is faster *if* you're only doing lots of > small transactions (consider how these different models handle an update to > a single field in a single row) but it is more sensitive to the scale of > transactions .... doing a really big transaction against a database with an > OLTP workload can upset Oracle's digestion as it causes a lot of UNDO > lookups, PG's performance is a lot more predictable in this regard. > > Both models have benefits and drawbacks ... when designing a schema for > performance it's important to understand these differences. > Yes, absolutely. It's not unusual to see the UNDO tablespace increase in size by several gigs for a large bulk load. Speaking of rollback segments I'm assuming that since all storage for non-visible row versions is in the main table that PostgreSQL has no equivalent for an ORA-01555. - Jon --001636d3425545f76e047f47f662 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Feb 10, 2010 at 4:48 PM, Dave Cr= ooke <dcrooke@gma= il.com> wrote:
On Wed, Feb 10, 2010 at 5:30 P= M, Jon Lewison <jlewison1@gmail.com> wrote:


Just a nit, but Oracle implements M= VCC.=A0 90% of the databases out there do.

Sorry, I spoke imprecisely. What I meant was the difference in h= ow the rows are stored internally .... in Oracle, the main tablespace conta= ins only the newest version of a row, which is (where possible) updated in = place - queries in a transaction that can still "see" an older ve= rsion have to pull it from the UNDO tablespace (rollback segments in Oracle= 8 and older).
=A0
In Postgres, all versions of all rows are in the main table, and hav= e validity ranges associated with them ("this version of this row exis= ted between transaction ids x and y"). Once a version goes out of scop= e, it has to be garbage collected by the vacuuming process so the space can= be re-used.

In general, this means Oracle is faster *if* you're only doing lots= of small transactions (consider how these different models handle an updat= e to a single field in a single row) but it is more sensitive to the scale = of transactions .... doing a really big transaction against a database with= an OLTP workload can upset Oracle's digestion as it causes a lot of UN= DO lookups, PG's performance is a lot more predictable in this regard.<= br>
Both models have benefits and drawbacks ... when designing a schema for= performance it's important to understand these differences.
<= /div>

Yes, absolutely.=A0 It's not unusual to see = the UNDO tablespace increase in size by several gigs for a large bulk load.= =A0

Speaking of rollback segments I'm assuming that since all storage f= or non-visible row versions is in the main table that PostgreSQL has no equ= ivalent for an ORA-01555.

- Jon

--001636d3425545f76e047f47f662--