Why PostgreSQL?
xhenxhe
postgresql is the preferred database for Django. Is this true? If so,
why?
I'm just curious because really, I have never used it.
Jacob Kaplan-Moss
> This may be a loaded question, but I was reading a blog post that
> postgresql is the preferred database for Django. Is this true? If so,
> why?
Not exactly. Django works equally well with all the databases we
support -- we wouldn't claim support for a database that didn't work
somehow. Nor is Django itself "opinionated" in this manner; neither
the software nor its developers give a hoot what database you use.
Engrave all your data in stone tablets; doesn't bother me!
That said, many of the Django core developers -- me included -- do
prefer Postgres for our own work. Its emphasis on data integrity and
SQL standards along with the radically open development model I think
appeal to our feelings about software design and Open Source.
Jacob
Tim Chase
> that postgresql is the preferred database for Django. Is this
> true? If so, why?
I think the "preference" comes from "that's what we happen to be
using, so we may be be better equipped to answer questions you
might have" as there seems to be a recommendation in the 0.96 docs
http://www.djangoproject.com/documentation/0.96/install/
but that preference has been removed in the current docs
http://www.djangoproject.com/documentation/install/
I can't speak for the authors of the documentation, but I've
found that PostgreSQL is generally more complete when it comes to
standards compliance, though I must say that MySQL has been
rapidly closing that gap. It used to be that MySQL lacked Views,
stored procedures, and my biggest pet-peeve, sub-selects (there
may have been some additional features I'm missing in this list).
Postgres also has greater support for GIS info if that's
important to you.
MySQL did have native full-text and replication features before
Postgres (which had them as add-on modules, but not natively).
Postgres has taken a path of "get it correct & complete, then
optimize" while MySQL seems to have taken the path of "get it
fast and then fill in the missing features".
The last shoot-out I saw, MySQL performed better under light to
modest load (especially with read-only queries), but started
choking at higher sustained load, while Postgres was a tiny bit
slower at low to medium volumes but just kept on truckin' at
higher sustained load. And Postgres is no slouch in the speed
department anyways.
These days, I find them fairly equal in functionality -- MySQL
has added support for most of the missing elements, and Postgres
has added support for full-text and native replication. Speed is
also pretty comparable these days. I personally find MySQL a
little easier to administer but that's my own experience.
Additionally, you're often more likely to find MySQL on most
hosting company servers. But if you're building your own server,
then you can install whatever you like.
-tim
xhenxhe
know it well... unless at some future date I find a compelling reason
to swtich to PostgreSQL
Sebastian Bauer
xhenxhe pisze:
Michael Wieher
although it will support larger loads.
Postgresql is where you'll end up going for performance when you
approach larger sites. It can compete with Oracle in situations MySQL
cannot.
This is off the top of my head, so I don't have any numbers or
anything, but thats what I've come to understand.
Ramdas S
RS
Ramdas S
+91 9342 583 065
My Personal Blog: http://ramdas.diqtech.com
AmanKow
I've just been bitten badly by the 'dumpdata <app>' 'loaddata <app>'
problems with MySQL and innodb. I originally decided to go with MySQL
(familiarity, for the most part). Using MyIsam tables is not an
option for me, I need the transaction support.
In an app with no circular references or anything scary, I have a
model with several foreign keys. When I dumpdata, the created fixture
dumps that model first, followed by the referenced tables. As innodb
doesn't support deferment of foreign key constraint checking until the
end of the transaction, loaddata fails. I must go in and edit the
fixture to move the data for the referencing table after the data for
the referenced tables to get loaddata to work.
Of course, it quickly becomes obvious that editing the output of
dumpdata is a bear with any significant amount of data or an app of
any complexity. As there is no way to specify the order of tables for
dumpdata, and apparently dumpdata doesn't attempt to dump referenced
tables first, this is quite a problem for MySQL users.
Dumpdata and loaddata are just too important to forgo, especially when
setting up test implementations and evolving a database. Speaking of
evolving, django-evolution has many issues with MySQL versus
postgress. I've therfore decided to bite the bullet and switch to
postgres...
For me that has it's own implications... While it is installed on the
server, my host does not support it officially, and thus I am on my
own administering it. Now, at a point where I am hard-pressed for
time, I am explaining to my clients that I've needed to make a change
to the backend, and am hustling to become proficient enough in
postgres to get the project back on track.
In short, I don't think it is fair to say that Django supports all db
backends equally. I wish that early on, when choosing Django, I had
been led definitively down the postgres path.
On Jun 18, 11:46 am, "Jacob Kaplan-Moss" <jacob.kaplanm...@gmail.com>
wrote:
Russell Keith-Magee
>
> I have to disagree that Django supports these backends equally well.
> I've just been bitten badly by the 'dumpdata <app>' 'loaddata <app>'
> problems with MySQL and innodb. I originally decided to go with MySQL
> (familiarity, for the most part). Using MyIsam tables is not an
> option for me, I need the transaction support.
Lets be clear here - the problem you are describing is fundamentally
MySQL's problem, not Django's. MySQL doesn't interpret the standard
right, and doesn't provide for an easy workaround. There isn't much
Django can do in this case. This is an example of what Tim was talking
about - MySQL is still adding features and fixing problems with their
SQL implementation.
If you choose a piece of infrastructure, you have to accept the
limitations of that infrastructure. Any database backend will have
strengths and weaknesses; the fixture loading problem is one of
MySQL's limitations.
> Of course, it quickly becomes obvious that editing the output of
> dumpdata is a bear with any significant amount of data or an app of
> any complexity. As there is no way to specify the order of tables for
> dumpdata, and apparently dumpdata doesn't attempt to dump referenced
> tables first, this is quite a problem for MySQL users.
Ordering tables is a partial solution, but it won't work in the
general case. However, something would be better than nothing, and
there is something to be said for making serialized output ordered for
purely aesthetic reasons.
If someone (cough cough... hint hint) were to submit a patch to
implement table ordering on serialized output, I'd have no problem
committing such a patch.
> Speaking of
> evolving, django-evolution has many issues with MySQL versus
> postgress. I've therfore decided to bite the bullet and switch to
> postgres...
(for those not aware - I'm a core developer of Django Evolution. These
next comments are from me wearing my Evolution core developer hat)
Django Evolution is a slightly different beast - Django Evolution
isn't part of the core Django project, and if you read the FAQ, I
don't make the claim that MySQL is equally supported under Django
Evolution - in fact, I clearly state that MySQL is a work in progress,
simply due to my personal priorities. I have also said on many
occasions that I would like to fix this situation, but I am only one
man, and I only have so much time to give.
I will gladly accept any patch to Django Evolution that improves MySQL
support (and I have done so on a number of occasions in the recent
past).
> In short, I don't think it is fair to say that Django supports all db
> backends equally. I wish that early on, when choosing Django, I had
> been led definitively down the postgres path.
If a bug is found in Django's MySQL, we are committed to fixing it (if
it can be fixed). The ticket just needs to work through the triage
process. There is only 1 ticket in the "Ready for Checkin" list that
is MySQL specific, and I'm not completely convinced it's the right
solution. If MySQL were a non-supported platform, this list would be
longer.
Yours,
Russ Magee %-)
Tim Chase
> isn't part of the core Django project, and if you read the FAQ, I
> don't make the claim that MySQL is equally supported under Django
> Evolution - in fact, I clearly state that MySQL is a work in progress,
> simply due to my personal priorities. I have also said on many
> occasions that I would like to fix this situation, but I am only one
> man, and I only have so much time to give.
Hmm...that would explain the bug I'm getting in Evolution:
Traceback (most recent call last):
File "testevolution.py", line 42, in exercise:
OutOfRussMageeError: Insufficient Russ Magee's; please
contribute patches and try again.
:-)
-tim
AmanKow
On Jun 18, 8:48 pm, "Russell Keith-Magee" <freakboy3...@gmail.com>
wrote:
> MySQL's problem, not Django's. MySQL doesn't interpret the standard
> right, and doesn't provide for an easy workaround. There isn't much
> Django can do in this case. This is an example of what Tim was talking
> about - MySQL is still adding features and fixing problems with their
> SQL implementation.
>
> If you choose a piece of infrastructure, you have to accept the
> limitations of that infrastructure. Any database backend will have
> strengths and weaknesses; the fixture loading problem is one of
> MySQL's limitations.
the answer to "Why PostgresSQL?" I think, given the current state of
MySQL, the problems I outlined are very strong arguments for using
postgres instead of mysql when choosing Django. I just wish that,
when I first made the Django choice, that I had made the jump to
postgres then. If I came off sounding accusatory, please forgive me,
it was not my intent.
> Ordering tables is a partial solution, but it won't work in the
> general case. However, something would be better than nothing, and
> there is something to be said for making serialized output ordered for
> purely aesthetic reasons.
with circular references in mysql, you need to allow your foreign keys
to be null so that you can save the records and then update them.
Yuck. I guess any general purpose app needing circular references
would need to do so to support mysql. But, dumping referenced tables
first would go a long way to solving many mysql user issues with
dumpdata.
> If someone (cough cough... hint hint) were to submit a patch to
> implement table ordering on serialized output, I'd have no problem
> committing such a patch.
maybe things will cool down at work in a month or two (seriously, I'm
swamped right now). In the mean time, should I submit a ticket?
Also, I wasn't knocking django-evolution, just the opposite. I want
to use it! The ability to use evolution in all its glory is a very
strong reason to prefer postgres. Yes, I read the faq, and it is one
of the main reasons I am making the switch to postgres.
If you like, I change my statement from "Django doesn't really support
all backends equally" to "not all backends support Django equally".
The only point I really want to make is that if you're serious about
Django, you are best served, for very practical reasons, choosing
postgres for your db, and I wish I had gotten that advice strongly and
early in the game.
Thanks for all your great work! Sorry if I sound whiny, I'm just
feeling sorry for myself with all the work I've made for myself not
choosing postgres from the get-go.
Wayne
Russell Keith-Magee
>
> On Jun 18, 8:48 pm, "Russell Keith-Magee" <freakboy3...@gmail.com>
> wrote:
>
> If I came off sounding accusatory, please forgive me,
> it was not my intent.
No problems. In the spirit of reconciliation, I didn't intend to be
harsh either - I just wanted remove any doubt that MySQL is a first
class citizen from the point of view of the Django core developers.
>> If someone (cough cough... hint hint) were to submit a patch to
>> implement table ordering on serialized output, I'd have no problem
>> committing such a patch.
>
> Hmm.... hint taken. I'll start to look at it in my "spare time"...
> maybe things will cool down at work in a month or two (seriously, I'm
> swamped right now). In the mean time, should I submit a ticket?
Already logged: http://code.djangoproject.com/ticket/3615
There's a patch already on that ticket, and it works, but the solution
provided isn't ideal. The problem with the patch is that when foreign
key checks are turned on again, there is not validation of data
inserted when they were off. It's more of a workaround than a
solution.
Yours,
Russ Magee %-)
Peter Melvyn
> Absolutely. I am not pointing fingers, but throwing my two cents into
> the answer to "Why PostgresSQL?" I think, given the current state of
> MySQL, the problems I outlined are very strong arguments for using
> postgres instead of mysql when choosing Django.
I have exactly the same experience.
IMHO, if you don't have a *very* good reason to choose MySQL, do *not*
choose it, otherwise you will run into problems, especially if a
schema evolution/migration is a significant task for you...
> when I first made the Django choice, that I had made the jump to
> postgres then.
Unfortunatelly, we use Django application as a configuring/monitoring
web based tool for large back-office application using MySQL as shared
database, hence we cannot move PostgreSQL easily...
Peter
bruno desthuilliers
Just brillant !
AmanKow
I took a look at that ticket, and there was a comment at the end
pointing to a mysql function that checks for constraint violations...
http://forge.mysql.com/tools/tool.php?id=11
Way too mysql guruish for me, but it looks interesting...
If it could be modified in some way to return a list of useful errors
(empty list if no errors) it could be used with a modification of the
patch to provide verification and commit or failure with rollback and
meaningful errors for managed transactions... in other words, it would
eliminate the entire issue of no deferment of constraint checks for
mysql users.
It still wouldn't be ideal, and might impose a significant penalty to
run, but it could make loaddata work as expected in mysql.
On Jun 19, 12:45 am, "Russell Keith-Magee" <freakboy3...@gmail.com>
wrote:
Russell Keith-Magee
>
>> Already logged:http://code.djangoproject.com/ticket/3615
>
> I took a look at that ticket, and there was a comment at the end
> pointing to a mysql function that checks for constraint violations...
>
> http://forge.mysql.com/tools/tool.php?id=11
>
> Way too mysql guruish for me, but it looks interesting...
Yes - I added that link last night. Ticket #3615 came up in a separate
discussion yesterday, and someone suggested that script as a possible
solution. If someone wants to go to the effort of integrating that
script (and the license allows) I'd be happy to commit such a fix.
Yours
Russ Magee %-)
Jeff FW
mysqldump about a million times. (That's mostly what we use at my
job, though I've been slowly pushing us towards postgres.) Try
adding:
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
at the top of the file, and:
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
at the bottom. That *should* do it. Sorry if I'm suggesting
something you've tried already.
As for the original poster's question of why PosgresQL is better (and
it certainly is), and ignoring the *really* important things that
others have already said:
* much better authentication/authorization
* ability to *easily* create your own datatypes
* ability to write functions in several languages (like Python!)
* much better command line interface (yes, that's just icing on the
cake, but it really is so helpful)
-Jeff
Russell Keith-Magee
>
> I haven't yet used Django's dumpdata and loaddata, but I've used
> mysqldump about a million times. (That's mostly what we use at my
> job, though I've been slowly pushing us towards postgres.) Try
> adding:
>
> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
> FOREIGN_KEY_CHECKS=0 */;
>
> at the top of the file, and:
>
> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
>
> at the bottom. That *should* do it. Sorry if I'm suggesting
> something you've tried already.
AFAICT, that's the same idea suggested on #3615. The discussion on
that ticket describes why it's not the final solution.
Yours,
Russ Magee %-)
Jeff FW
wrote:
data in a dumpfile is correct (which, of course, I know you can't), it
is a valid solution. Meaning, maybe it could be a command-line flag
for the loaddata command. If I *know* my data is valid, (say I just
dumped it from a working database using foreign keys), then why not
let me disable the checks when I load in the data? Running the script
linked in the ticket afterwards would certainly be an improvement, but
not essential in all cases.
As for ordering the tables in the dumpfile: I agree that it might not
be a general solution, as couldn't there be cycles in the foreign
keys? Or is that not allowed?
Anyway, it's certainly a better quickfix for AmanKow's situation than
editing the dumpfile and rearranging the order of tables.
-Jeff
Russell Keith-Magee
>
> On Jun 21, 12:46 am, "Russell Keith-Magee" <freakboy3...@gmail.com>
> wrote:
>> AFAICT, that's the same idea suggested on #3615. The discussion on
>> that ticket describes why it's not the final solution.
>>
> data in a dumpfile is correct (which, of course, I know you can't), it
> is a valid solution. Meaning, maybe it could be a command-line flag
> for the loaddata command. If I *know* my data is valid, (say I just
> dumped it from a working database using foreign keys), then why not
> let me disable the checks when I load in the data? Running the script
> linked in the ticket afterwards would certainly be an improvement, but
> not essential in all cases.
The problem is that the cost of being wrong is so high. I might be
absolutely sure that I don't have any problems in my data, but I could
be wrong. If I'm right, there's no problem, but if I'm wrong, I can
get the database into an invalid state, and the first time I'll know
about it is when everything goes to hell because I try to follow an
invalid reference.
> As for ordering the tables in the dumpfile: I agree that it might not
> be a general solution, as couldn't there be cycles in the foreign
> keys? Or is that not allowed?
Cycles are certainly both possible and allowed. Hence the problems
with finding a general solution based on model ordering.
> Anyway, it's certainly a better quickfix for AmanKow's situation than
> editing the dumpfile and rearranging the order of tables.
Completely aside from the 'it might help some MySQL fixture loading"
angle, there's a cosmetic angle - at the moment, fixtures are dumped
in dictionary order - which is to say, no order at all. Sorting
fixture output (both by model, and internally by key) would make the
dumped output much nicer to spelunk through manually.
Yours,
Russ Magee %-)
Jeff FW
my mouth. I had never actually used the dumpdata command (I usually
just used the database server's dump feature), so I didn't realize
that it doesn't actually dump just straight SQL. Of course, had I
thought about it (instead of just babbling), I would have realized
that would be a very naive way of doing things. I should have known
to expect better from Django :-)
So, sorry for wasting your time. I still think having a command-line
flag for turning checks off (with nice, big warnings on it) might be a
help, but I'll be quiet.
And yes, ordering the tables certainly couldn't hurt, and would be
quite nice. I would submit a patch, but clearly, I need to actually
have a clue as to what I'm taking about first.
-Jeff
On Jun 21, 10:59 am, "Russell Keith-Magee" <freakboy3...@gmail.com>
wrote: