PostgreSQL FTW?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

PostgreSQL FTW?

Post by aaronhall »

I'm working on a largish project that I've implemented in MySQL for the time being. Most queries are fairly expensive.. table joins on huge datasets with simple text matching. Queries can't be effectively cached because all data is constantly being updated. Queries are coming in at about 500ms average (8O), but I'm **guessing** I might be able to shed 20% off that after I really optimize.

In a situation like this, can any of you vouch for PostgreSQL as a faster alternative, or is it even worth the time migrating to find out?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Do you have mysql specific code (and features) spread all over your scripts?
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

A bit unfortunately -- I did some really rapid prototyping and haven't implemented a solid abstraction layer. That, and the over head of installing the server, migrating a fair amount of data, and dealing with any quirks. If this turned out not to be an easy call, I planned on doing some tests with postgres.... but I'm really fishing for any differentiating factors between the two engines that might affect my decision
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Traditionally, MySQL has always been faster than PostgreSQL, while PostgreSQL has more features. Their slowly coming together, but I think MySQL is still faster. The InnoDB format is faster at reading than MyISAM, but it is slower at writing - so pick your poison.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I would avoid using the JOIN syntax if possible. You can join tables without it which cedes control of the joins to MySQL's built in optimizers. A lot of time this can be faster but not always.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

@pickle: I planned on converting the tables to InnoDB and doing some tests. I know the rule is that MySQL is generally faster, but of late, I've been seeing some benchmarks showing PostgreSQL to outperform MySQL under load, and especially on multicore processors.

@astions: I'm curious about what non-JOIN join syntax you're referring to

Thanks for the replies gents
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

select
  a.foo,
  b.bar
from
  table_a a, table_b b
where
  a.looking_for = 'something'
  and a.primary_key = b.primary_key
order by
  b.sort_order
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Is that not classified as an inner join, or does the query optimizer treat that syntax differently?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Yes, it's an inner join.
ev0l
Forum Commoner
Posts: 56
Joined: Thu Jun 21, 2007 1:50 pm

Post by ev0l »

It depends highly on the situation and you are only going to know through testing. MySQL does offer some things that PostgreSQL does not. Namely interchangeable storage engines and fantastic commercial support.

The InnoDB format is faster at reading than MyISAM, but it is slower at writing - so pick your poison.
That depends on your situation. If all you are doing is reading that table MyISAM is going to smoke InnoDB. InnoDB has to take time to lock each row while MyISAM only has to lock the table.

Ask your self. Do you need transactions for everything or is speed more important in some situations? How fast is fast enough? Is there something that one vender provides that you require? Are you more familiar with one than the other? Does the org you are working for already have a database infrastructure from one vender? There are a tonne of other questions but ultimately the decision depends largely on your situation.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

I know I am probably stating the obvious... Have you checked your indexes ?
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

This isn't really an issue of MySQL being too slow. Given the nature of the queries and the size of the tables I'm working with, I'm not surprised about the query speed. I was wondering if anyone had enough experience with both MySQL and Postgres that they could say if I could expect any significant improvements by switching given the nature of the queries and the tables. Also, when I said engines, I mean MySQL/PostgreSQL, not MyISAM/InnoDB
ev0l
Forum Commoner
Posts: 56
Joined: Thu Jun 21, 2007 1:50 pm

Post by ev0l »

aaronhall wrote: I was wondering if anyone had enough experience with both MySQL and Postgres that they could say if I could expect any significant improvements by switching given the nature of the queries and the tables. Also, when I said engines, I mean MySQL/PostgreSQL, not MyISAM/InnoDB
Like I said it depends. I would be surprised if you experienced any significant improvement moving from one database system to the other but than again, that depends.

The place you might see significant speed differences would be between MyISAM and InnoDB. PostgreSQL's database format would be more like InnoDB than MyISAM and usually the performance difference between a MySQL database using InnoDB and a PostgreSQL database is small but again, it depends.

So as I said, it depends. Different databases from different venders can be optimized in different ways and the way you optimize ... well ... depends on your situation. If, as you say, speed is not a problem don't make it one. If speed is a problem try and optimize what you have and if that does not work for you look for alternatives.

MySQL commercial support is very good. For a couple grand you can have someone who is extremely knowledgeable about the database system walk you through all sorts of optimization. Personally I feel MySQL is a much better and more flexible product than PostgreSQL but I know lots of people that have zeal like love for PostgreSQL.

But it depends.
Post Reply