Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D5382632FDB for ; Wed, 10 Feb 2010 19:48:22 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 67007-10 for ; Wed, 10 Feb 2010 23:48:05 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f209.google.com (mail-ew0-f209.google.com [209.85.219.209]) by mail.postgresql.org (Postfix) with ESMTP id 020E5632E89 for ; Wed, 10 Feb 2010 19:48:11 -0400 (AST) Received: by ewy1 with SMTP id 1so56649ewy.34 for ; Wed, 10 Feb 2010 15:48:10 -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=hzV1jkajSdHFWi1dW9pdkdW9wc1avKxbs1QZWqI1y+Y=; b=ju9C/vJu6GzDnraY/GSdYa+9/mSnsf02cU9u4rrRptBkFx01fKT4iqwqza/Z4D5bEx jgTMCM3i+hX6zWhR1MhXk6422k2Hq0aofLo4iY+JSZ14PEUiHU+tRIUQYdMnl7UXnk0Y D1P2TF/sIts/rxadMK0scHyBPd/5+jfMz6mjg= 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=t9gFU9VofYPebYSjhQn6BX9VKQKXLRD5rbjGE02c5g8b3gForh+vMemxn1ZY1lc7IA xFzK9gZ6rG/zBLw/1WPbeypu82oYtIH/bU38kR4MkQlkV4pGhiNYRhtsQbLWBxGuRXPl VLG/yGhOZOqDaWAnltJ4lp18PP1n7EaiORT6c= MIME-Version: 1.0 Received: by 10.216.88.78 with SMTP id z56mr558398wee.37.1265845689933; Wed, 10 Feb 2010 15:48:09 -0800 (PST) In-Reply-To: <7ac84c51002101530q59fa87c1w205fdf5243d9d69a@mail.gmail.com> References: <7ac84c51002101530q59fa87c1w205fdf5243d9d69a@mail.gmail.com> Date: Wed, 10 Feb 2010 17:48:09 -0600 Message-ID: Subject: Re: perf problem with huge table From: Dave Crooke To: Jon Lewison Cc: rama , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d9a23bb06180047f47aa5f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.208 tagged_above=-10 required=5 tests=AWL=0.390, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/170 X-Sequence-Number: 37591 --0016e6d9a23bb06180047f47aa5f Content-Type: text/plain; charset=ISO-8859-1 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. > I find partitioning pretty useful in this scenario if the data allows is. > Aging out data just means dropping a partition rather than a delete > statement. > > Forgot to say this - yes, absolutely agree .... dropping a table is a lot cheaper than a transactional delete. In general, I think partitioning is more important / beneficial with PG's style of MVCC than with Oracle or SQL-Server (which I think is closer to Oracle than PG). Cheers Dave --0016e6d9a23bb06180047f47aa5f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Wed, Feb 10, 2010 at 5:30 PM, Jon Lewison <jlewison1@gmail.c= om> 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 how the= rows are stored internally .... in Oracle, the main tablespace contains on= ly 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).
=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.


I find partitioning pretty useful in th= is scenario if the data allows is.=A0 Aging out data just means dropping a = partition rather than a delete statement.


Forgot to say this - yes, absolutely agree .... drop= ping a table is a lot cheaper than a transactional delete.

In genera= l, I think partitioning is more important / beneficial with PG's style = of MVCC than with Oracle or SQL-Server (which I think is closer to Oracle t= han PG).


Cheers
Dave



--0016e6d9a23bb06180047f47aa5f--