Page 1 of 1
PostgreSQL FTW?
Posted: Sat Oct 20, 2007 10:12 am
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?
Posted: Sat Oct 20, 2007 10:37 am
by volka
Do you have mysql specific code (and features) spread all over your scripts?
Posted: Sat Oct 20, 2007 10:56 am
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
Posted: Mon Oct 22, 2007 3:18 am
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.
Posted: Mon Oct 22, 2007 4:55 am
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.
Posted: Mon Oct 22, 2007 5:46 am
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
Posted: Mon Oct 22, 2007 6:10 am
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
Posted: Mon Oct 22, 2007 7:28 am
by aaronhall
Is that not classified as an inner join, or does the query optimizer treat that syntax differently?
Posted: Mon Oct 22, 2007 7:37 am
by Benjamin
Yes, it's an inner join.
Posted: Tue Oct 23, 2007 8:45 am
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.
Posted: Tue Oct 23, 2007 6:50 pm
by CoderGoblin
I know I am probably stating the obvious... Have you checked your indexes ?
Posted: Wed Oct 24, 2007 1:17 am
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
Posted: Wed Oct 24, 2007 10:26 am
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.