From: Jon Lewison <jlewison1@gmail.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: rama <rama.rama@tiscali.it>
Cc: pgsql-performance@postgresql.org
Subject: Re: perf problem with huge table
Date: Wed, 10 Feb 2010 16:30:54 -0700
Message-ID: <7ac84c51002101530q59fa87c1w205fdf5243d9d69a@mail.gmail.com> (raw)
In-Reply-To: <ca24673e1002101516wa2545aek80cbe88df935698@mail.gmail.com>
References: <BDE4B02A-24A4-46C1-9C76-63AA5DD57B40@tiscali.it>
<ca24673e1002101516wa2545aek80cbe88df935698@mail.gmail.com>
On Wed, Feb 10, 2010 at 4:16 PM, Dave Crooke <dcrooke@gmail.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 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jlewison1@gmail.com, dcrooke@gmail.com, rama.rama@tiscali.it
Subject: Re: perf problem with huge table
In-Reply-To: <7ac84c51002101530q59fa87c1w205fdf5243d9d69a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox