Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B0908633029 for ; Wed, 10 Feb 2010 19:31:06 -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 22813-05 for ; Wed, 10 Feb 2010 23:30:55 +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 2F261632E89 for ; Wed, 10 Feb 2010 19:30:56 -0400 (AST) Received: by iwn35 with SMTP id 35so656163iwn.4 for ; Wed, 10 Feb 2010 15:30:54 -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=duTyZLyHXWBYmZ/t6n3oYzBJbaeb09DfMZ+kjXxQwD8=; b=bL9/WVQAZdldskF7uiSJDo2syXmnyY9BDc/lAYq8mFOqECRXTMwUJVRwvx8QlKyN3W 3gWok2/oPeIDxv8/GvqLsBjbpJJrddOVCDzSnyq3Bi/le+V1rsZvtbg0oF3aODASEFVN YHEtXzXkmB2bM3gj706cDehBSzYpq/SberI2g= 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=lwWMnD0Vkvi0+l0Qks5Y3RC22K1u5u8uH4bt2DSeogyFZqMVUH1Am8p+vEGPdlynRa Uj4bGigK1AfNzA72JkMZ4O8V72pbbSClnHtCh5D8iZmdzHq1L+8q1H9r9QekHGgeyEjZ yKvYQtCqu3Stpp0W1uUJNPP50Jd8XjSv8CvEI= MIME-Version: 1.0 Received: by 10.231.155.18 with SMTP id q18mr1576413ibw.80.1265844654458; Wed, 10 Feb 2010 15:30:54 -0800 (PST) In-Reply-To: References: Date: Wed, 10 Feb 2010 16:30:54 -0700 Message-ID: <7ac84c51002101530q59fa87c1w205fdf5243d9d69a@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=001636d34255f84953047f476c4f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.283 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, SARE_MILLIONSOF=0.315 X-Spam-Level: X-Archive-Number: 201002/169 X-Sequence-Number: 37590 --001636d34255f84953047f476c4f Content-Type: text/plain; charset=ISO-8859-1 On Wed, Feb 10, 2010 at 4:16 PM, Dave Crooke wrote: > Hi Rama > > I'm actually looking at going in the other direction .... > > I have an app using PG where we have a single table where we just added a > lot of data, and I'm ending up with many millions of rows, and I'm finding > that the single table schema simply doesn't scale. > > In PG, the table partitioning is only handled by the database for reads, > for insert/update you need to do quite a lot of DIY (setting up triggers, > etc.) so I am planning to just use named tables and generate the necessary > DDL / DML in vanilla SQL the same way that your older code does. > > My experience is mostly with Oracle, which is not MVCC, so I've had to > relearn some stuff: > Just a nit, but Oracle implements MVCC. 90% of the databases out there do. > - Oracle often answers simple queries (e.g. counts and max / min) using > only the index, which is of course pre-sorted. PG has to go out and fetch > the rows to see if they are still in scope, and if they are stored all over > the place on disk it means an 8K random page fetch for each row. This means > that adding an index to PG is not nearly the silver bullet that it can be > with some non-MVCC databases. > > - PG's indexes seem to be quite a bit larger than Oracle's, but that's gut > feel, I haven't been doing true comparisons ... however, for my app I have > limited myself to only two indexes on that table, and each index is larger > (in disk space) than the table itself ... I have 60GB of data and 140GB of > indexes :-) > > - There is a lot of row turnover in my big table (I age out data) .... a > big delete (millions of rows) in PG seems a bit more expensive to process > than in Oracle, however PG is not nearly as sensitive to transaction sizes > as Oracle is, so you can cheerfully throw out one big "DELETE from FOO where > ..." and let the database chew on it . 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. --001636d34255f84953047f476c4f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Feb 10, 2010 at 4:16 PM, Dave Cr= ooke <dcrooke@gma= il.com> wrote:
Hi Rama

I'm actually looking at going in the other direction ...= .

I have an app using PG where we have a single table where we just= added a lot of data, and I'm ending up with many millions of rows, and= I'm finding that the single table schema simply doesn't scale.
In PG, the table partitioning is only handled by the database for reads= , for insert/update you need to do quite a lot of DIY (setting up triggers,= etc.) so I am planning to just use named tables and generate the necessary= DDL / DML in vanilla SQL the same way that your older code does.

My experience is mostly with Oracle, which is not MVCC, so I've had= to relearn some stuff:

Just a nit, but Oracle imp= lements MVCC.=A0 90% of the databases out there do.
=A0
- Oracle often answers simple queries (e.g. counts and max / min) using onl= y the index, which is of course pre-sorted. PG has to go out and fetch the = rows to see if they are still in scope, and if they are stored all over the= place on disk it means an 8K random page fetch for each row. This means th= at adding an index to PG is not nearly the silver bullet that it can be wit= h some non-MVCC databases.

- PG's indexes seem to be quite a bit larger than Oracle's, but= that's gut feel, I haven't been doing true comparisons ...=A0 howe= ver, for my app I have limited myself to only two indexes on that table, an= d each index is larger (in disk space) than the table itself ... I have 60G= B of data and 140GB of indexes :-)

- There is a lot of row turnover in my big table (I age out data) .... = a big delete (millions of rows) in PG seems a bit more expensive to process= than in Oracle, however PG is not nearly as sensitive to transaction sizes= as Oracle is, so you can cheerfully throw out one big "DELETE from FO= O where ..." and let the database chew on it .

I find partitioning pretty useful in this scenario if the data all= ows is.=A0 Aging out data just means dropping a partition rather than a del= ete statement.

--001636d34255f84953047f476c4f--