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 17:09:17 -0700
Message-ID: <7ac84c51002101609l190ec043s7fdde292969da457@mail.gmail.com> (raw)
In-Reply-To: <ca24673e1002101548k2513c23k302ebeca77070b2a@mail.gmail.com>
References: <BDE4B02A-24A4-46C1-9C76-63AA5DD57B40@tiscali.it>
<ca24673e1002101516wa2545aek80cbe88df935698@mail.gmail.com>
<7ac84c51002101530q59fa87c1w205fdf5243d9d69a@mail.gmail.com>
<ca24673e1002101548k2513c23k302ebeca77070b2a@mail.gmail.com>
On Wed, Feb 10, 2010 at 4:48 PM, Dave Crooke <dcrooke@gmail.com> wrote:
> On Wed, Feb 10, 2010 at 5:30 PM, Jon Lewison <jlewison1@gmail.com> 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
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: <7ac84c51002101609l190ec043s7fdde292969da457@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