Re: Postgres performance

Mauro Bertoli wrote:
> Hi Richard, thank you for your apreciated answers!!!
> - start quote -
> Well, do you care whether your data is consistent or
> not? If not, you
> don't need transactions.
> - end quote -
> I don't require transaction because the query aren't
> complex and update a single tuple (in SELECT
> transactions are useless)

You're still using transactions. One per UPDATE/INSERT granted, but
you're using them anyway. Even if you don't issue BEGIN...COMMIT.
Otherwise you don't know your update was written to disk.

> - start quote -
> You'll find inserts/updates with lots of users is
> where PostgreSQL works
> well compared to other systems.
> - end quote -
> Uhhmm.. this is interesting...
>
> - tutorial links -
> Thx, now I read it and test an hardware tuned
> configuration... I read that is not very simple... :O
>
> Another question:
> - why postgres release aren't already configured
> (hardware tuning)? isn't possible configure it during
> installation?

Configured for what? PG can't tell how many disks you have, or how
you've set them up. It also can't tell whether this machine is a
dedicated DB server, or sharing space with a webserver. Or part of a
virtual OS installation and the hardware is shared by 100 other virtual
OSes.
Occasionally, people do propose an auto-tuning utility at setup, but you
really need at least a dozen different options to do it properly. Oh,
and then you'll need to do it for 30 versions of Unix on a variety of
hardware and Windows too.

> - why postgres use a new process for every query ?
> (mySQL, if I'm not wrong, use threads... I think its
> faster)

Using a separate process for each means a problem in one process only
affects that process. Threads aren't necessarily much faster (except on
Windows) and in any case that only affects connection time.

> - why connection time is slower? (compared to mySQL)?

See above, but it's still not bad. If connection time is a limiting
factor for you, then you have a very strange or very large workload. You
might want to explore pgpool for connection pooling if you have a large
website to avoid having a lot of idle connections though.

> - why postgres require analyze? (mySQL, if I'm not
> wrong, don't require it)

PG's planner is statistics-based. That is, it can tell that if you have
a list of English surnames then "Smith" occurs more than "zgwasq". In
some cases reading the whole table might be quicker than going to the
index many times.
The analyse scans (a percentage of) the whole table to see if these
statistics have changed. This is different from a VACUUM which recovers
space where rows have been deleted or updated.

--
Richard Huxton
Archonet Ltd