Re: partitionning - Mailing list pgsql-general

Scott Marlowe <smarlowe@g2switchworks.com> writes:

> Actually, I think it is the more common scenario of migrating off of
> oracle or db2 and onto postgresql, and bringing along the experience
> gained there over the years that has caused this refrain to sprout up
> more and more often.  With a database sitting on an enterpries class
> storage subsystem with 8 gigs of battery backed cache onboard, the needs
> for partitioning are less and less necessary.  Not completely so, and
> there are times when they can come in handy (i.e. when you have to "roll
> your own" on a more limited budget).

You don't think the people using Oracle are even *more* likely to have an big
storage subsystem with gobs of cache? At my previous job we had a Hitachi
system that was really ludicrously fast. Nonetheless when we implemented
partitioning it was a real life saver for the DBAs. Their workload went from
being >50% dealing with problems with the large weekly jobs to basically being
able to ignore these jobs. In fact the job to handle the changover was moved
to the middle of the peak period because it was just more convenient that way.

The bigger the storage the *more* important partitioning is. Not less. That's
why it's such a huge feature for Oracle and it's why databases used on smaller
projects don't find it a compelling feature.

> I'm note sure what your point about purging and loading is.  A properly
> built home rolled partitioning setup reqiures none of that.

Well that's sort of the point. But home rolled partitioning setups have other
problems. That's why it would be good to have a solid implementation that
didn't have these problems.

> Witness how often OTHER issues pop up (=NULL versus IS NULL, autonomous
> transactions, quotas) that are from oracle land nowadays.  It isn't that
> the Oracle way is always the best way, it's just what folks are often
> used to.

=NULL is an Access thing actually. But yes, these other features are also
things that the bigger boys need. But the most common requested one these days
seems to be partitioning. Maybe I'm biased though.

> I really didn't see anything in your post that argued against my point
> that a large enterprise class raid array largely eliminates the needs
> for application level partitioning of data.

Firstly it's not application level if it's native. The postgres options such
as inherited tables or union views do indeed impose application level
constraints, but a good native implementation is completely transparent to the
programmer.

Well, so you're saying that you believe me that on my 1GB database I find it
more convenient to be able to pull off 100M of data instantaneously and
without generating any garbage for vacuum to clean up. But that you don't
believe someone running a 1TB storage subsystem would appreciate the same
feature as much when they have to pull off 10GB of data because their system
is 10x faster at doing this unnecessary work than mine would be, so it only
takes 100x as much time?

--
greg