PostgreSQL FTW?
Moderator: General Moderators
- aaronhall
- DevNet Resident
- Posts: 1040
- Joined: Tue Aug 13, 2002 5:10 pm
- Location: Back in Phoenix, missing the microbrews
- Contact:
PostgreSQL FTW?
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?
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?
- aaronhall
- DevNet Resident
- Posts: 1040
- Joined: Tue Aug 13, 2002 5:10 pm
- Location: Back in Phoenix, missing the microbrews
- Contact:
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
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.
- aaronhall
- DevNet Resident
- Posts: 1040
- Joined: Tue Aug 13, 2002 5:10 pm
- Location: Back in Phoenix, missing the microbrews
- Contact:
@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
@astions: I'm curious about what non-JOIN join syntax you're referring to
Thanks for the replies gents
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
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.
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.
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.The InnoDB format is faster at reading than MyISAM, but it is slower at writing - so pick your poison.
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.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
- aaronhall
- DevNet Resident
- Posts: 1040
- Joined: Tue Aug 13, 2002 5:10 pm
- Location: Back in Phoenix, missing the microbrews
- Contact:
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
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.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
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.