Re: One large v. many small - Mailing list pgsql-performance

Folks,

Many, many replies on this topic:

Jeff:
> App design & performance go hand-in-hand. the trick is to balance them.
> Who wants a _wonderful_ design that runs like a piece of poo?  in this
<snip>
>Select somevalue from sometable where othervalue not in (select badvalues
> from badvaluetable where id = 12345)
> we all know this runs horrifically on postgres. using your method I should
> go out, spend thousands on multi-processor boxes, raid, ram

Sorry, no, Jeff.   The above is what one calls a "bad query" and is not,
therefore, a performance vs. design issue: that query is bad design-wise, and
bad performance-wise.  Perhpas another example of your argument?

Since you do not seem to have understood my argument, it is this:
Design changes, made for the sake of performance or rapid app building, which
completely violate good RDBMS design and normalization principles, almost
always cost you more over the life of the application than you gain in
performance in the short term.

Curt:
> It's all very nice to have a general system that can do well on all
> sorts of queries, but if you lose time on the queries you do do, in
> order to save time on queries you don't do, you're definitely not
> getting the best performance out of the system.

This is a good point; I tend to build for posterity because, so far, 90% of my
clients who started out having me build a "single-purpose" database ended up
expanding the application to cover 2-10 additional needs, thus forcing me to
clean up any design shortcuts I took with the original app.    However, Noah
may have more control over his company than that.

<and>
> RAM is not cheap enough yet for me to buy several hundred gigabytes of
> it for typical applications, even if I could find a server that I could
> put it in. Disk performance is not growing the way CPU performance is.
> And three weeks of engineering time plus a ten thousand dollar server
> is, even at my top billing rate, still a heck of a lot cheaper than a
> quarter-million dollar server.

I was thinking more of the difference between a $3000 server and a $9000
server myself; unless you're doing nuclear test modelling, I don't see any
need for a $250,000 server for anything.
To give an extreme example, I have a client who purchased a $150,000
accounting system that turned out to be badly designed, normalization-wise,
partly because the accounting system engineers were focusing on 8-year-old
technology with performance restrictions which were no longer really
applicable (for example, they talked the client into buying a quad-processor
server and then wrote all of their middleware code on a platform that does
not do SMP).  Over the last two years, they have paid my company $175,000 to
"fix" this accounting database ... more, in fact, than I would have charged
them to write a better system from scratch.

<and>
> Applying your strategy to all situations is not always going to produce
> the most cost-effective solution.

That's very true.   In fact, that could be taken as a "general truism" ... no
one strategy applies to *all* situations.

> PS. Josh, are you referring to Pascal's "Practical Issues In Database
> Management" book or does he have a different book out that I'm not aware
> of?

Yes, you're correct.  Sorry!

--
Josh Berkus
Aglio Database Solutions
San Francisco