Re: Postgres performance - Mailing list pgsql-sql

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