Re: partitionning

On Wed, 2005-03-09 at 14:47, Greg Stark wrote:
> 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.

Wait, I'm not sure I understand your point here yet.  Are you saying
something along the lines of that with a 1TB storage array, and putting
all the data in one big partitions, the DBAs had problems, but when they
partitioned it down to say 10 100G partitions, and split up the data set
across it they had fewer problems?

Or are you talking more about quick access because when you have a table
with three fields, and one is rather narrow, that field can be accessed
much quicker because you aren't dragging around the extra fields you
don't need?

Was this some kind of massive batch processing system?

> 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.

While I'm sure administration overhead is the major problem, I'm
wondering what other ones you've seen, i.e. performance, reliability,
user error, that are more common on a home rolled partitioning.

> > 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.

sorry, by application, I meant Postgresql client layer.  I.e.
implemented in the user layer of postgresql, not in the core of it.

> 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?

Why in the world would a view encased union of several tables generate
garbage for a vacuum to clean up?  I'd think a well rolled
view/union/trigger based solution would operated identically to a
postgresql internally implemented partitioning system.

But what I'm really saying is that between good home grown partitioning
and fast hardware, the need for the pg devel team to implement
partitioning is pretty low.