Re: [DOCS] Partition: use triggers instead of rules - Mailing list pgsql-patches

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 29 Nov 2007 17:29:51 +0000
Gregory Stark <stark@enterprisedb.com> wrote:

> "Joshua D. Drake" <jd@commandprompt.com> writes:
> 
> > Heh, o.k. that was an ambiguous sentence. In a partitioned
> > environment you are likely not moving millions of rows around. Thus
> > the "rule" benefit is lost. You are instead performing many
> > (sometimes lots-o-many) inserts and updates that involve a small
> > number of rows.
> 
> I'm still not following at all. If you're partitioning it's because
> you have a *lot* of data. It doesn't say anything about what you're

If you have lots of data it doesn't mean you are modifying lots of
data. I took perhaps incorrectly what tgl said as modifying lots of
data. E.g; I am doing a large transaction that is going to
insert/update 500 thousand rows.

I don't think anyone here (good lord I hope not) would say that firing
a trigger over 500k rows is fast. Instead you should likely just work the data
outside the partition and then move it directly into the target
partition.

> doing with that data. Partitioning is useful for managing large
> quantities of data for both OLTP and DSS systems.

Certainly. I am not really arguing that and I would tend to agree that
I am being very focused on my arguments about partitioning. To me it is
obvious that you don't use triggers or rules when moving tons of data,
either one is just a burden you don't need.

Partitioning is generally most useful for:

Breaking up large tables and indexes so you are dealing with less data
on active queries.

Breaking up large tables so you don't end up vacuuming at 500 million
row table that only 1 million rows are ever updated.

Rotating out highly updated data so you can keep bloat down (HOT
resolves this in certain cases).

General data management of large sets. Archives and the like.

In any of the above cases a trigger is going to work better than a rule
with the exceptions of what TGL pointed out and in simpler
partitioning environments where the number of partitions are very low.

Either way, to drive this back to topic :).. on the docs if we keep the
rule example it should be below the partitioned example and we need to
list caveats on both. Which I personally think is overkill for
reference documentation but it seems to be what some people want.

> 
> I tend to be happier recommending triggers over rules if only because
> rules are just harder to understand. Arguably they don't really work
> properly for this use anyways given what happens if you use volatile
> functions like random() in your where clause.
> 

nod.

Sincerely,

Joshua D. Drake



- -- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTvxkATb/zqfZUUQRAhRtAKCHcWBhVZgTM8XXq8kJWWmi0m49cACgmAU5
xqosTo6sJPqpMMKWNvoTWGU=
=zDUw
-----END PGP SIGNATURE-----