Re: time sensitve: comparing performance to MySQL

On Thu, 2 Oct 2003, Matt Christian wrote:

> Hello,
>
> I've been tasked to do some quick research on Postgres vs. MySQL that I
> need to present later today. I've already done a lot of Googlin' and
> reviewing of the Postgres websites.
>
> The project at hand will be a  high-volume website with many complex
> queries. It will likely use replication. Performance will be most
> important for SELECT statements.
>
> The project will be ported form an existing codebase which uses Oracle
> as the backend.
>
> I understand that Postgres has been closing the speed gap with MySQL,
> but I'm having trouble finding hard data on this. What specific
> information is available in this area?

Actually, ther "performance gap" here is more mythical than real.  What
MySQL is fast at is a mostly read, simple select query system, with few
users, while what postgresql is fast at is a transactional high load /
high number of users / complex query scenario.

MySQL's lack of complex query handling, and poor performance in those few
areas that have been added lately (sub selects are still quite slow, for
instance) means that it is often more suited for content management, while
Postgresql's ability to hand 100s of parallel updates while still
delivering decent select performance make it more suited for large
transactional systems, such as the .org domain, financial systems,
collaboration, and data warehousing.

I'd suggest taking your top 5 most complex / slowest running queries,
importing the your schema into both databases, and seeing how they compare
when you run those queries in parallel with 1, 5, 10, 20 etc increasing
numbers to get an idea of how the performance envelope looks under load
for you.

note that postgresql and mysql will both need a certain amount of tuning
to handle this kind of load, a default install of either is not going to
be up to snuff for this.