PostgreSQL vs. MySQL
Thomas Ashelford
Django, but I'm wondering if anyone can explain what its concrete
advantages are.
I've just moved from DreamHost to Webfaction to get the advantage of
using mod_python instead of fcgi. Already the advantages of mod_python
are clear (it's basically less flaky). But I now have the choice of 2
dB platforms and I'm wondering if there are any strong reasons to
switch to PostgreSQL. So far I can only see a potential negative - I
may want to use fulltext indexing in the future, and I believe this is
not available on PostgreSQL.
Any opinions?
Ian Holsman
had any issues with it.
I chose mysql as I am more familiar with it. others are more familiar
with postgreSQL.
choose the one you are more comfortable with.
regards
ian.
lbolo...@gmail.com
Thomas Ashelford wrote:
> So far I can only see a potential negative - I
> may want to use fulltext indexing in the future, and I believe this is
> not available on PostgreSQL.
Look into TSearch2 for full text search in PG
Lorenzo
James Bennett
> I keep reading that PostgreSQL is the preferred database for use with
> Django, but I'm wondering if anyone can explain what its concrete
> advantages are.
The advantages of Postgres are mostly to do with its better support of
SQL (you have more options for specifying constraints, writing
functions and views in the database -- not to be confused with Django
views, which are something very different -- and so on) and more
robust integrity features; where with MySQL transaction support is
only available if you choose the correct storage engine, Postgres
gives you transactions automatically.
--
"May the forces of evil become confused on the way to your house."
-- George Carlin
Malcolm Tredinnick
(I'm not going to repeat what others have already mentioned in this
thread; I agree with all of them. My two cents, though...)
I would add, for most projects, it's not a really clear cut case. Django
hides the SQL oddities for you, so there's not going to much difference
there (using MySQL 5 with transaction support is kind of assumed here,
although even that's not clear cut in some cases).
Ian's comment about familiarity is very valid: for most things, they are
equivalent, so whichever you feel more comfortable with is important. In
a crisis, having to worry about *how* to do something, as opposed to
*what* to do is not an extra burden one usually needs. I've had days
where I have had to switch between Oracle, MySQL and PostgreSQL within a
few hours of each other and it can become a bit head-spinning.
Consistency between the various sites you do is not a crazy goal.
By and large, unless you are going to have tens or hundreds of thousands
(or more) or rows in a lot of tables and be doing lots of multi-table
joins, I don't think you're going to see a lot of difference. At the
upper levels, I think PostgreSQL does a bit better in memory management
(this didn't use to be universally the case, by the way) and it performs
better with large numbers of simultaneous connections doing frequent
reads (100's of connections at once) -- although I haven't verified this
last claim for a year or so, so MySQL may have gotten better.
Beyond that, unless you are doing lots of low-level SQL, there's not a
lot to choose between them for many circumstances. If you are doing very
high data volume stuff, it's going to be a matter of testing typical
uses anyway, since no two setups are exactly the same.
The problem is that this is a "vi vs. emacs" / "python vs. ruby" / "MS
Windows vs. Commodore 64" sort of debate. The two choices are basically
equivalent and for the bulk of uses each is sufficiently capable.
I would say "pick on" and if your experience over time suggests that the
database is a problem and tests with the alternative work better, then
it's not even *that* painful to move data between the two systems. They
both basically talk SQL, after all, and a few sed scripts and the like
applied to the right pressure points in a data dump means it's not too
hard. So I'm going for the pragmatic "don't sweat it (too much)!"
option.
Best wishes,
Malcolm
Kenneth Gonsalves
On 07-Jul-06, at 3:18 PM, Malcolm Tredinnick wrote:
> I would say "pick on" and if your experience over time suggests
> that the
> database is a problem and tests with the alternative work better, then
> it's not even *that* painful to move data between the two systems.
> They
> both basically talk SQL, after all, and a few sed scripts and the like
> applied to the right pressure points in a data dump means it's not too
> hard. So I'm going for the pragmatic "don't sweat it (too much)!"
another thing, your have to be careful about postgresql is that data
recovery tools for a borked db for postgres are rare to the point of
non-existence, whereas there are lots of them for mysql
--
regards
kg
http://lawgon.livejournal.com
http://avsap.org.in
Geert Vanderkelen
Malcolm Tredinnick wrote:
> Hi Thomas,
>
> On Thu, 2006-07-06 at 23:17 -0700, Thomas Ashelford wrote:
>> I keep reading that PostgreSQL is the preferred database for use with
>> Django, but I'm wondering if anyone can explain what its concrete
>> advantages are.
I go also with: whatever you used to, use it.
>> I've just moved from DreamHost to Webfaction to get the advantage of
>> using mod_python instead of fcgi. Already the advantages of mod_python
>> are clear (it's basically less flaky). But I now have the choice of 2
>> dB platforms and I'm wondering if there are any strong reasons to
>> switch to PostgreSQL. So far I can only see a potential negative - I
>> may want to use fulltext indexing in the future, and I believe this is
>> not available on PostgreSQL.
If you are using FULLTEXT indices, you will need MyISAM. MyISAM doesn't
support transactions. This you need to consider too. You can mix storage
engines of course.
> I would add, for most projects, it's not a really clear cut case. Django
> hides the SQL oddities for you, so there's not going to much difference
> there (using MySQL 5 with transaction support is kind of assumed here,
> although even that's not clear cut in some cases).
MySQL supports transcations since 3.23 (which is ages ago). James Bennett
did mention it, but I'll repeat: you need to use InnoDB storage engine to
get transactional support.
You can mix non-transactional (like MyISAM) and transaction (InnoDB) storage
engines, but when a rollback occures the MyISAM 'commit' can't be rolled back.
MySQL Cluster also support transactions (but only with one isolation level).
Cheers,
Geert
--
Geert Vanderkelen, Support Engineer
MySQL GmbH, Germany, www.mysql.com
Geert Vanderkelen
James Bennett wrote:
> On 7/7/06, Thomas Ashelford <ether.mu...@gmail.com> wrote:
>> I keep reading that PostgreSQL is the preferred database for use with
>> Django, but I'm wondering if anyone can explain what its concrete
>> advantages are.
>
> The advantages of Postgres are mostly to do with its better support of
> SQL (you have more options for specifying constraints, writing
> functions and views in the database -- not to be confused with Django
> views, which are something very different -- and so on) and more
> robust integrity features; where with MySQL transaction support is
> only available if you choose the correct storage engine, Postgres
> gives you transactions automatically.
Note that when starting new projects and choosing MySQL, you better go MySQL
5.0 directly (or even 5.1 if you want to test new features there..).
Note that there have been some important features added to MySQL 5.0:
- More standard SQL
- Stored routines (or procedures)
- Views, triggers
- Etc, Etc,..
Like James mentions, you need InnoDB for transactional support (which has
been in MySQL since 3.23) and integrity checking. Though, foreign keys
support might come in the future over all storage engines..
One of the big advantages of MySQL is ease of use and a choice when it comes
to how you store data. For example: if you need fast reads and less updates,
make your table MyISAM (you can even compress it making it smaller and
faster!). If you need high availability of your data, you can go MySQL
Cluster. Need a data to /dev/null, use the blackhole engine! ;)
Anyway, whatever you choose, choose the one you are most comfortable with!
Cheers,
Geert
--
Geert Vanderkelen, Support Engineer
MySQL GmbH, Germany, www.mysql.com
Hauptsitz: MySQL GmbH, Radlkoferstr. 2, D-81373 München
Geschäftsführer: Hans von Bell, Kaj Arnö - HRB München 162140
Malcolm Tredinnick
> Malcolm Tredinnick wrote:
> > I would add, for most projects, it's not a really clear cut case. Django
> > hides the SQL oddities for you, so there's not going to much difference
> > there (using MySQL 5 with transaction support is kind of assumed here,
> > although even that's not clear cut in some cases).
>
> MySQL supports transcations since 3.23 (which is ages ago). James Bennett
> did mention it, but I'll repeat: you need to use InnoDB storage engine to
> get transactional support.
My main reason for mentioning version 5 was mainly to avoid thing the
limitations on the length of VARCHAR fields (which could only be up to
255 chars prior to MySQL 5.0). This has bitten some people using
CharFields in Django and they had to switch to TextFields, which present
themselves slightly differently.
I wasn't intending to imply the verison number was related to
transactional availability. Apologies for any confusion.
Regards,
Malcolm
Don Arbow
>
> another thing, your have to be careful about postgresql is that data
> recovery tools for a borked db for postgres are rare to the point of
> non-existence, whereas there are lots of them for mysql
Perhaps MySQL borks its databases much more frequently, requiring
said tools. I have much more experience with Postgres than MySQL and
have never needed any tools other than those provided with the
install and never have had Postgres corrupt a database.
Don
Geert Vanderkelen
Well.. Since corruptions comes mostly from failing hardware, you better have
more tools to make backups, than just one or even none.
If you don't have tools to make backups correctly, you better don't use this
particular DBMS at all. :)
If using MySQL with Django, or any tool, some quick check list for backups:
- binary log on (point in time recovery)
- replication going
- regular backups using mysqldump or LVM snapshotting
- backup the my.cnf and mysql database
- and make sure the backups are actually usable!!!
If you are hosting your project, make sure the hosting company has at least
some replication going and daily backups in place. Never trust your or
others hardware..
Cheers,
Geert
--
Geert Vanderkelen
http://some-abstract-type.com
Ian Holsman
this is turning into a religious debate.
I think the question has been answered.
both are excellent choices.
and in the hands of an expert can be made to shine.
Iain Duncan
> One of the big advantages of MySQL is ease of use and a choice when it comes
> to how you store data. For example: if you need fast reads and less updates,
> make your table MyISAM (you can even compress it making it smaller and
> faster!). If you need high availability of your data, you can go MySQL
> Cluster. Need a data to /dev/null, use the blackhole engine! ;)
This probably explains shoot outs I had read last year that claimed
MySQL was more appropriate in a case where your database needs are
simpler, but you expect to have the server doing a *lot* of small hits (
say a high traffic forum ), whereas PostGres shone for more
sophisticated transaction support ( say an ordering system with payment
gateway). My data may well be out of date on this, however. As with many
things, the two keep getting closer.
IMHO, if you are doing simple stuff with the DB ( say cms things ) and
you are relatively new to it all, MySQL documentation seems to be more
accessible to newbies and more prevalent on the internet. It's worth
thinking that what might be *best for you* is not necessarily *the best
product*. Ease of use and documentation are more important for db-lite
beginner cases, less so for complicated e-commerce transactions where
your going to be making damn sure you know what you're doing.
Iain
Kenneth Gonsalves
On 08-Jul-06, at 3:56 AM, Ian Holsman wrote:
> guys...
> this is turning into a religious debate.
given the nature of the question, it was inevitable, but i agree we
could stop here
Adam Blinkinsop
> Don Arbow wrote:
> > On Jul 7, 2006, at 3:35 AM, Kenneth Gonsalves wrote:
> >> another thing, your have to be careful about postgresql is that data
> >> recovery tools for a borked db for postgres are rare to the point of
> >> non-existence, whereas there are lots of them for mysql
> >
> > Perhaps MySQL borks its databases much more frequently, requiring
> > said tools. I have much more experience with Postgres than MySQL and
> > have never needed any tools other than those provided with the
> > install and never have had Postgres corrupt a database.
>
> Well.. Since corruptions comes mostly from failing hardware, you better have
> more tools to make backups, than just one or even none.
> If you don't have tools to make backups correctly, you better don't use this
> particular DBMS at all. :)
For clarification, Don said that he _did_ have tools. He noted that
_Postgres_ had never corrupted a database on him (while failing
hardware might have).
For my own part, I've used Postgres, and I find their backup/restore
system to be fully functional. See their documentation for more
information: http://www.postgresql.org/docs/8.1/interactive/backup.html