Re: [pgsql-advocacy] Me And My Database

On Tuesday 06 June 2006 18:44, Leif B. Kristensen wrote:
> On Wednesday 7. June 2006 00:10, Jim C. Nasby wrote:
> >Moving to -general.
> >From the bottom of that page:
> >
> > SELECT * FROM sources INTO src WHERE source_id = $1;
> >
> >SELECT * is generally something to avoid. You end up shoving around
> > more data than needed. Granted, in this case it's only getting shoved
> > down into plpgsql, but it's still extra work for the server.
>
> I know that. But the table is only four columns wide, and all the
> columns matter in this query. Eventually I'll remove such things
> as "SELECT * FROM ..." which really is a bad habit.
>
> >Also, the commentary about how MySQL is faster isn't very clear. Are
> > you using MySQL as some kind of result cache? When you get to running
> > actual concurrent access on the website, you could well find yourself
> > very disappointed with the performance of MyISAM and it's table-level
> > locking. There's probably also some gains to be had on the PostgreSQL
> > performance.
>
> I may have been a little unclear here. My production database is
> PostgreSQL, as it quite clearly is the better choice of the two, in
> particular wrt data integrity. My Web presentation software is quite a
> different matter. It's running at a web hotel that's only offering
> MySQL for a database. I find MySQL with MyISAM quite sufficient for
> that use, as its only purpose is to serve up simple selects quickly.
>

I'd think sqlite would be even faster, though it sounds like that might not be
an option for you.

> The reason why the generation of eg. the family sheet is faster in the
> MySQL web context than in my production environment, is that I'm really
> comparing apples and potatoes here. The Web database has a much flatter
> and denormalized structure, due to the fact that there's no editing.
> The entire Web database is repopulated from scratch every time I do an
> update.

If you going through this kind of step now, why not just generate the whole
site from the pg database as html pages and then push those out to the
client? That way you eliminate any dbms overhead and reduce load on your
webservers (and eliminate the need for a 2nd db schema)

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL