Re: PostgreSQL or MySQL, What are the +'s and -'s?
> > > @Jason - I think that's the number one reason I'm going to PostgreSQL. I > don't use everything all the time but I really like to have the widest array > of query syntax options. MySQL is just too limiting, especially when > PostgreSQL is available. > > The other issue I have with MySQL is the "flexibility" to choose your own db/table engine (sorry if that isn't the correct terminology). While some find this a plus, and indeed, in *some* cases you want this sort of flexibility as some of the more obscure database environments would benefit from peculiarities of one engine over another, I don't find this sort of thing as attractive. The reasoning is that of simplicity - as an engineer and artist, I put a lot of value in simplicity of design, implementation, and maintenance. While I don't have a lot of experience with MySQL, the more I've read about it's design and this engine "flexibility" the more I've been turned away. In my mind, it is as analogous to the linux to BSD situation.. on one hand you have a lot of flexibility (package managers, configuration tools, etc).. but in reality it turns into a total rat's nest in comparison to the "smoothness" of BSD. just my 2 cents.. ~Jason --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
On Tue, Sep 8, 2009 at 5:53 AM, Jan Ostrochovsky wrote: > > I won't compare PostgreSQL and MySQL as whole, because: > - I do not know MySQL so well, as PostgreSQL > - another flamewar could arise ;) > > I am only saying: my experience with Django+PostgreSQL is far better, > than Django+MySQL, see > > http://groups.google.com/group/django-users/browse_thread/thread/3abfd993c3c976f9/2e410d567d4ba2db#2e410d567d4ba2db > . > If you will need some help with PostgreSQL, do not hesitate to contact > me directly. > > Jano > > On Sep 8, 2:58 am, Jason Beaudoin wrote: > > While I don't have a whole lot of experience with either, the main issue > I > > have against MySQL is it's adherence (lack, that is) to the SQL standard. > > Moving a MySQL dB--> postgreSQL needn't be so messy. > > > > I'm also a bit weary of MySQL having been bought up. > > > > Cheers, > > > > ~Jason > @Jason - I think that's the number one reason I'm going to PostgreSQL. I don't use everything all the time but I really like to have the widest array of query syntax options. MySQL is just too limiting, especially when PostgreSQL is available. @Jano - Interesting post that you linked to. I've not see the same problems, though I'm not dealing with external data. I'm currently working on an internal app that will be used in Portuguese, thus dealing with UTF-8 everywhere and MySQL seems to be consuming it fine. Tho it can get a little tricky in Python code when it comes unicode characters. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
I won't compare PostgreSQL and MySQL as whole, because: - I do not know MySQL so well, as PostgreSQL - another flamewar could arise ;) I am only saying: my experience with Django+PostgreSQL is far better, than Django+MySQL, see http://groups.google.com/group/django-users/browse_thread/thread/3abfd993c3c976f9/2e410d567d4ba2db#2e410d567d4ba2db. If you will need some help with PostgreSQL, do not hesitate to contact me directly. Jano On Sep 8, 2:58 am, Jason Beaudoin wrote: > On Fri, Sep 4, 2009 at 4:29 PM, Joshua Russo wrote: > > I personally don't have any experience with PostgreSQL and I'm generally > > working in a mixed MS and Linux environment. I'm interested to hear peoples > > views on the pluses and minuses of the two different systems. I'm a bit of a > > query geek too. How does that play in? I know in MySQL there are limitations > > on where you can use subqueries. Is that true with PostgreSQL? (Ya I could > > just look that one up but it's just an example.) > > While I don't have a whole lot of experience with either, the main issue I > have against MySQL is it's adherence (lack, that is) to the SQL standard. > Moving a MySQL dB--> postgreSQL needn't be so messy. > > I'm also a bit weary of MySQL having been bought up. > > Cheers, > > ~Jason --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
On Fri, Sep 4, 2009 at 4:29 PM, Joshua Russo wrote: > I personally don't have any experience with PostgreSQL and I'm generally > working in a mixed MS and Linux environment. I'm interested to hear peoples > views on the pluses and minuses of the two different systems. I'm a bit of a > query geek too. How does that play in? I know in MySQL there are limitations > on where you can use subqueries. Is that true with PostgreSQL? (Ya I could > just look that one up but it's just an example.) > While I don't have a whole lot of experience with either, the main issue I have against MySQL is it's adherence (lack, that is) to the SQL standard. Moving a MySQL dB--> postgreSQL needn't be so messy. I'm also a bit weary of MySQL having been bought up. Cheers, ~Jason --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
On Mon, Sep 7, 2009 at 2:30 PM, Zberteoc wrote: > > Some consider good practice to actually never expose directly the > tables to the users but do this through stored procedures and views > only. In this way you achieves 2 major things, complete control and > security. Users have no permission to select/update/insert/delete on > the tables but they are given permission to stored procedures and > views that will do that instead. A side but important advantage is > that using a stored procedure or a view in application code is far > more cleaner and easier than a select statement that can be cumbersome > at times to write and maintain. A stored procedure also embeds the > business logic and if/when it changes you don't have to touch the > application code but only the stored procedure itself, providing that > the parameters and column names and number stays the same. Even if > they change is far more easier to maintain that then the SQL code > which they replace. Beside that stored procedure code is compiled and > cashed so they perform much better than ad-hoc queries. Unfortunately > very few apply these principles when they do database design and > development. > > With triggers is another story. In my opinion triggers should be used > only when there is NO alternative. The problems with triggers is that > they are "hidden" and they can create extremely serious problems > performance wise. Imagine an insert of million rows and with each one > some complicated code has to be executed in triggers especially if > querying some other tables is involved. You have to be careful here as > in the short term a trigger might seem the easiest and fastest > solution but in the long term you might run into serious problems like > very low performance or even locks and/or timeouts. That doesn't mean > we shouldn't use them though but as I said we have to be careful. A > very good way of avoiding triggers is using stored procedures for > insert/update/delete where you can adapt the code to perform much > better that you would do it in a trigger and still have the same > control and effect on the table. Unfortunately usually what happens is > this, web/application developers write queries (select/insert/update/ > delete statements at times very complicated and low performing because > of their limited SQL experience) which they embed in their code or, in > the happy cases, in some external files (XML) and later when they come > to change or modify the functionality will add triggers on tables just > to let applications intact. In time this practice develops into a > bloated database with poorer and poorer performance as the time goes > by. In the end they will have to revamp much or everything just to > optimize the database code which translates in more cost and wasted > time, and only because they treated superficially the database design > and development part in the first place. I've seen tables with 10 or > even more triggers on them, which in my opinion should be > "forbidden". :o) > > Cheers. Excellent points. I've been on the user side of views as a consultant in some larger corporations, usually just for periodic data extraction, though they make things s much easier than moving around flat files (which was actually the preferred alternative by their sys admins!) Personally I've used views commonly when I have a recurring query to simplify my queries in code. I've stayed away from stored procedures primarily because I like to keep all of the business logic in one body of code, as opposed to split between application code and stored procedures. I can see it helpful tho when you have multiple applications accessing the same database. I also appreciate their speed. We've used them for field level encryption with fantastic results. In fact, it was the only way it was even viable. The speed hit went from unusably crippling in application code, to unperceptible in as a stored procedure! I tend to agree with you on the triggers bit. I've played around with using them to log bits of data to later synchronize with a smaller, read only copy of the same database, but they decided against the project. It probably could have been handled with stored procedures tho. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
Some consider good practice to actually never expose directly the tables to the users but do this through stored procedures and views only. In this way you achieves 2 major things, complete control and security. Users have no permission to select/update/insert/delete on the tables but they are given permission to stored procedures and views that will do that instead. A side but important advantage is that using a stored procedure or a view in application code is far more cleaner and easier than a select statement that can be cumbersome at times to write and maintain. A stored procedure also embeds the business logic and if/when it changes you don't have to touch the application code but only the stored procedure itself, providing that the parameters and column names and number stays the same. Even if they change is far more easier to maintain that then the SQL code which they replace. Beside that stored procedure code is compiled and cashed so they perform much better than ad-hoc queries. Unfortunately very few apply these principles when they do database design and development. With triggers is another story. In my opinion triggers should be used only when there is NO alternative. The problems with triggers is that they are "hidden" and they can create extremely serious problems performance wise. Imagine an insert of million rows and with each one some complicated code has to be executed in triggers especially if querying some other tables is involved. You have to be careful here as in the short term a trigger might seem the easiest and fastest solution but in the long term you might run into serious problems like very low performance or even locks and/or timeouts. That doesn't mean we shouldn't use them though but as I said we have to be careful. A very good way of avoiding triggers is using stored procedures for insert/update/delete where you can adapt the code to perform much better that you would do it in a trigger and still have the same control and effect on the table. Unfortunately usually what happens is this, web/application developers write queries (select/insert/update/ delete statements at times very complicated and low performing because of their limited SQL experience) which they embed in their code or, in the happy cases, in some external files (XML) and later when they come to change or modify the functionality will add triggers on tables just to let applications intact. In time this practice develops into a bloated database with poorer and poorer performance as the time goes by. In the end they will have to revamp much or everything just to optimize the database code which translates in more cost and wasted time, and only because they treated superficially the database design and development part in the first place. I've seen tables with 10 or even more triggers on them, which in my opinion should be "forbidden". :o) Cheers. On Sep 5, 7:25 am, Joshua Russo wrote: > Great site! thanks > > With my SQL experience, your comment about database coding seems to ring > true. The database is a much different environment, with > different paradigms, than a standard application environment. When you > really need database coding you know it (kind of like meta programming). > > So far from my experience has been, the times I found we needed database > programming were pretty simply speed related, so difficult to distinguish > from application logic. What types of situations do others find useful for > stored procedures and triggers? > > > > On Sat, Sep 5, 2009 at 9:57 AM, Zberteoc wrote: > > > This is a common mistake almost all non SQL developers make thinking > > in procedural/programming language terms in regards wit SQL and > > database coding. If you're asking me there is nothing cool in the > > feature of creating stored procedures in other than the SQL language. > > MS-SQL introduced that with 2005 version, CLR integration, but I is > > hardly used for one very simple reason it is NOT really necessary. SQL > > code needs to be understood first and only then look elsewhere. > > Anyways, in terms of comparison PgSQL vs MySQL here is a very detailed > > wiki: > > >http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL > > > I have never used PgSQL but i wouldn't hesitate to use it if I needed > > it. All DBMS these days are robust and mature enough to be able to > > rely on them. It comes in the end to what you prefer, how comfortable > > you feel and ease of use, rather than how many terrabytes they can > > deal with as the features lists are more and more the same for all of > > them. Support and online community is also very important, probably > > the most if you're novice in both, and here MySQL prevails as it is > > far more popular. > > > Cheers. > > > On Sep 4, 10:46 pm, Joshua Russo wrote: > > > Wow, that's a cool trick to be able to implement stored procedures in > > > different languages. I might actually use them more if I could do > > everything > > > in the same language as the app
Re: PostgreSQL or MySQL, What are the +'s and -'s?
Thanks, that is a good example. Back to the original point of the post. It looks like I'm going to be doing a lot more work with PgSQL. I see they are working to put replication in the core in the next major subversion (which honestly I rarely need). The real kicker for me is that PgSQL seems to have all of the query features I've been missing in MySQL. I'm generally creating CRUD applications and then data reporting. It seems like the combo of Django and PgSQL is really where it's at for me. I just completed the bulk of my first Django app and I'm eating it up. So much so that I almost immediately jumped into the core to see how I could help. It's like this entire new world of speed and efficiency was just dropped in my lap and I only want to help make it better. I have both Practical Django and Pro Django on their way. :o) Thanks for this thread. This group has always been a great help! On Sun, Sep 6, 2009 at 2:31 AM, Siemster wrote: > > ++ Zberteoc for the link to the comparison. > > w.r.t. using triggers/procedures. If a database is getting updated by > multiple applications then using triggers/procedures to move > duplicated business rules into the database starts to make some > sense-- even more so if you don't have good control over all the > applications that are doing the updating. > > > On Sep 5, 6:25 am, Joshua Russo wrote: > > Great site! thanks > > > > With my SQL experience, your comment about database coding seems to ring > > true. The database is a much different environment, with > > different paradigms, than a standard application environment. When you > > really need database coding you know it (kind of like meta programming). > > > > So far from my experience has been, the times I found we needed database > > programming were pretty simply speed related, so difficult to distinguish > > from application logic. What types of situations do others find useful > for > > stored procedures and triggers? > > > > On Sat, Sep 5, 2009 at 9:57 AM, Zberteoc wrote: > > > > > This is a common mistake almost all non SQL developers make thinking > > > in procedural/programming language terms in regards wit SQL and > > > database coding. If you're asking me there is nothing cool in the > > > feature of creating stored procedures in other than the SQL language. > > > MS-SQL introduced that with 2005 version, CLR integration, but I is > > > hardly used for one very simple reason it is NOT really necessary. SQL > > > code needs to be understood first and only then look elsewhere. > > > Anyways, in terms of comparison PgSQL vs MySQL here is a very detailed > > > wiki: > > > > >http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL > > > > > I have never used PgSQL but i wouldn't hesitate to use it if I needed > > > it. All DBMS these days are robust and mature enough to be able to > > > rely on them. It comes in the end to what you prefer, how comfortable > > > you feel and ease of use, rather than how many terrabytes they can > > > deal with as the features lists are more and more the same for all of > > > them. Support and online community is also very important, probably > > > the most if you're novice in both, and here MySQL prevails as it is > > > far more popular. > > > > > Cheers. > > > > > On Sep 4, 10:46 pm, Joshua Russo wrote: > > > > Wow, that's a cool trick to be able to implement stored procedures in > > > > different languages. I might actually use them more if I could do > > > everything > > > > in the same language as the application. > > > > I only looked quickly through the PostgreSQL docs for subqueries. > Thanks > > > for > > > > the heads up. > > > > > > As far as the Gis functionality goes, I could see a database like > that > > > > needing some serious horse power if it became popular. Any thoughts > on > > > how > > > > that would reconcile with the weakness in replication? I suppose most > Gis > > > > systems are more for out put than input so the slow replication might > not > > > > really matter that much. > > > > > > On Sat, Sep 5, 2009 at 1:17 AM, Siemster < > gregory.si...@pca.state.mn.us > > > >wrote: > > > > > > > PostgreSQL does support subqueries in the from clause, however > iirc, > > > > > the subquerys require an alias. > > > > > > > If you decide to do geo then the PostGis addon to Postgres is very > > > > > nice. > > > > > > > Another nice capability in PostgreSQL is that you can use different > > > > > languages for writing your stored procedures (should you wish to > use > > > > > them). Some of the available languages (in addition to PL/pgSQL) > are > > > > > Perl, Python, Tcl, PHP, Ruby, R, Scheme, and Java. > > > > > > > Whether you choose to use Postgres or not, I'd recommend at least > > > > > looking at it. There is even a live cd (which I have not tried) at > > > > >http://www.postgresql.org/download/whichlets you try PostgreSQL out > > > > > without having to install it. > > > > > > > On Sep 4, 7:38 pm, Joshua Russo wrote: > > > > > > On Fri, Sep 4, 2009 at 1
Re: PostgreSQL or MySQL, What are the +'s and -'s?
++ Zberteoc for the link to the comparison. w.r.t. using triggers/procedures. If a database is getting updated by multiple applications then using triggers/procedures to move duplicated business rules into the database starts to make some sense-- even more so if you don't have good control over all the applications that are doing the updating. On Sep 5, 6:25 am, Joshua Russo wrote: > Great site! thanks > > With my SQL experience, your comment about database coding seems to ring > true. The database is a much different environment, with > different paradigms, than a standard application environment. When you > really need database coding you know it (kind of like meta programming). > > So far from my experience has been, the times I found we needed database > programming were pretty simply speed related, so difficult to distinguish > from application logic. What types of situations do others find useful for > stored procedures and triggers? > > On Sat, Sep 5, 2009 at 9:57 AM, Zberteoc wrote: > > > This is a common mistake almost all non SQL developers make thinking > > in procedural/programming language terms in regards wit SQL and > > database coding. If you're asking me there is nothing cool in the > > feature of creating stored procedures in other than the SQL language. > > MS-SQL introduced that with 2005 version, CLR integration, but I is > > hardly used for one very simple reason it is NOT really necessary. SQL > > code needs to be understood first and only then look elsewhere. > > Anyways, in terms of comparison PgSQL vs MySQL here is a very detailed > > wiki: > > >http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL > > > I have never used PgSQL but i wouldn't hesitate to use it if I needed > > it. All DBMS these days are robust and mature enough to be able to > > rely on them. It comes in the end to what you prefer, how comfortable > > you feel and ease of use, rather than how many terrabytes they can > > deal with as the features lists are more and more the same for all of > > them. Support and online community is also very important, probably > > the most if you're novice in both, and here MySQL prevails as it is > > far more popular. > > > Cheers. > > > On Sep 4, 10:46 pm, Joshua Russo wrote: > > > Wow, that's a cool trick to be able to implement stored procedures in > > > different languages. I might actually use them more if I could do > > everything > > > in the same language as the application. > > > I only looked quickly through the PostgreSQL docs for subqueries. Thanks > > for > > > the heads up. > > > > As far as the Gis functionality goes, I could see a database like that > > > needing some serious horse power if it became popular. Any thoughts on > > how > > > that would reconcile with the weakness in replication? I suppose most Gis > > > systems are more for out put than input so the slow replication might not > > > really matter that much. > > > > On Sat, Sep 5, 2009 at 1:17 AM, Siemster > >wrote: > > > > > PostgreSQL does support subqueries in the from clause, however iirc, > > > > the subquerys require an alias. > > > > > If you decide to do geo then the PostGis addon to Postgres is very > > > > nice. > > > > > Another nice capability in PostgreSQL is that you can use different > > > > languages for writing your stored procedures (should you wish to use > > > > them). Some of the available languages (in addition to PL/pgSQL) are > > > > Perl, Python, Tcl, PHP, Ruby, R, Scheme, and Java. > > > > > Whether you choose to use Postgres or not, I'd recommend at least > > > > looking at it. There is even a live cd (which I have not tried) at > > > >http://www.postgresql.org/download/whichlets you try PostgreSQL out > > > > without having to install it. > > > > > On Sep 4, 7:38 pm, Joshua Russo wrote: > > > > > On Fri, Sep 4, 2009 at 11:07 PM, Tim Chase > > > > > wrote: > > > > > > > > I personally don't have any experience with PostgreSQL and I'm > > > > generally > > > > > > > working in a mixed MS and Linux environment. I'm interested to > > hear > > > > > > peoples > > > > > > > views on the pluses and minuses of the two different systems. I'm > > a > > > > bit > > > > > > of a > > > > > > > query geek too. How does that play in? I know in MySQL there are > > > > > > limitations > > > > > > > on where you can use subqueries. Is that true with PostgreSQL? > > (Ya I > > > > > > could > > > > > > > just look that one up but it's just an example.) > > > > > > > I did a writeup of MySQL vs. PostgreSQL a while back: > > >http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html > > > > > > > Most of the issues still stand -- though I understand MySQL now > > > > > > has native(ish) support for Geo information (check the GeoDjango > > > > > > code to see if it supports the MySQL Geo implementation -- last I > > > > > > checked the source it was Oracle & PostgreSQL only). > > > > > > > To answer your direct question, PostgreSQL has long-standing > > > > > > support for all kin
Re: PostgreSQL or MySQL, What are the +'s and -'s?
Great site! thanks With my SQL experience, your comment about database coding seems to ring true. The database is a much different environment, with different paradigms, than a standard application environment. When you really need database coding you know it (kind of like meta programming). So far from my experience has been, the times I found we needed database programming were pretty simply speed related, so difficult to distinguish from application logic. What types of situations do others find useful for stored procedures and triggers? On Sat, Sep 5, 2009 at 9:57 AM, Zberteoc wrote: > > This is a common mistake almost all non SQL developers make thinking > in procedural/programming language terms in regards wit SQL and > database coding. If you're asking me there is nothing cool in the > feature of creating stored procedures in other than the SQL language. > MS-SQL introduced that with 2005 version, CLR integration, but I is > hardly used for one very simple reason it is NOT really necessary. SQL > code needs to be understood first and only then look elsewhere. > Anyways, in terms of comparison PgSQL vs MySQL here is a very detailed > wiki: > > http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL > > I have never used PgSQL but i wouldn't hesitate to use it if I needed > it. All DBMS these days are robust and mature enough to be able to > rely on them. It comes in the end to what you prefer, how comfortable > you feel and ease of use, rather than how many terrabytes they can > deal with as the features lists are more and more the same for all of > them. Support and online community is also very important, probably > the most if you're novice in both, and here MySQL prevails as it is > far more popular. > > Cheers. > > On Sep 4, 10:46 pm, Joshua Russo wrote: > > Wow, that's a cool trick to be able to implement stored procedures in > > different languages. I might actually use them more if I could do > everything > > in the same language as the application. > > I only looked quickly through the PostgreSQL docs for subqueries. Thanks > for > > the heads up. > > > > As far as the Gis functionality goes, I could see a database like that > > needing some serious horse power if it became popular. Any thoughts on > how > > that would reconcile with the weakness in replication? I suppose most Gis > > systems are more for out put than input so the slow replication might not > > really matter that much. > > > > On Sat, Sep 5, 2009 at 1:17 AM, Siemster >wrote: > > > > > > > > > > > > > PostgreSQL does support subqueries in the from clause, however iirc, > > > the subquerys require an alias. > > > > > If you decide to do geo then the PostGis addon to Postgres is very > > > nice. > > > > > Another nice capability in PostgreSQL is that you can use different > > > languages for writing your stored procedures (should you wish to use > > > them). Some of the available languages (in addition to PL/pgSQL) are > > > Perl, Python, Tcl, PHP, Ruby, R, Scheme, and Java. > > > > > Whether you choose to use Postgres or not, I'd recommend at least > > > looking at it. There is even a live cd (which I have not tried) at > > >http://www.postgresql.org/download/which lets you try PostgreSQL out > > > without having to install it. > > > > > On Sep 4, 7:38 pm, Joshua Russo wrote: > > > > On Fri, Sep 4, 2009 at 11:07 PM, Tim Chase > > > > wrote: > > > > > > > > I personally don't have any experience with PostgreSQL and I'm > > > generally > > > > > > working in a mixed MS and Linux environment. I'm interested to > hear > > > > > peoples > > > > > > views on the pluses and minuses of the two different systems. I'm > a > > > bit > > > > > of a > > > > > > query geek too. How does that play in? I know in MySQL there are > > > > > limitations > > > > > > on where you can use subqueries. Is that true with PostgreSQL? > (Ya I > > > > > could > > > > > > just look that one up but it's just an example.) > > > > > > > I did a writeup of MySQL vs. PostgreSQL a while back: > > > > > > > > http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html > > > > > > > Most of the issues still stand -- though I understand MySQL now > > > > > has native(ish) support for Geo information (check the GeoDjango > > > > > code to see if it supports the MySQL Geo implementation -- last I > > > > > checked the source it was Oracle & PostgreSQL only). > > > > > > > To answer your direct question, PostgreSQL has long-standing > > > > > support for all kinds of crazy sub-queries. MySQL has added most > > > > > of those abilities over time. This used to be a deal-breaker for > > > > > me, making Postgres the clear winner. Now they're about even. > > > > > > > Lastly, my closing arguments in that link still stand -- if you > > > > > don't have a pressing need to choose one or the other, code & > > > > > test for both. > > > > > > Good point on geo side side of things. > > > > > > One place I have found subqueries very useful is in the From. > > > Fu
Re: PostgreSQL or MySQL, What are the +'s and -'s?
This is a common mistake almost all non SQL developers make thinking in procedural/programming language terms in regards wit SQL and database coding. If you're asking me there is nothing cool in the feature of creating stored procedures in other than the SQL language. MS-SQL introduced that with 2005 version, CLR integration, but I is hardly used for one very simple reason it is NOT really necessary. SQL code needs to be understood first and only then look elsewhere. Anyways, in terms of comparison PgSQL vs MySQL here is a very detailed wiki: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL I have never used PgSQL but i wouldn't hesitate to use it if I needed it. All DBMS these days are robust and mature enough to be able to rely on them. It comes in the end to what you prefer, how comfortable you feel and ease of use, rather than how many terrabytes they can deal with as the features lists are more and more the same for all of them. Support and online community is also very important, probably the most if you're novice in both, and here MySQL prevails as it is far more popular. Cheers. On Sep 4, 10:46 pm, Joshua Russo wrote: > Wow, that's a cool trick to be able to implement stored procedures in > different languages. I might actually use them more if I could do everything > in the same language as the application. > I only looked quickly through the PostgreSQL docs for subqueries. Thanks for > the heads up. > > As far as the Gis functionality goes, I could see a database like that > needing some serious horse power if it became popular. Any thoughts on how > that would reconcile with the weakness in replication? I suppose most Gis > systems are more for out put than input so the slow replication might not > really matter that much. > > On Sat, Sep 5, 2009 at 1:17 AM, Siemster wrote: > > > > > > > PostgreSQL does support subqueries in the from clause, however iirc, > > the subquerys require an alias. > > > If you decide to do geo then the PostGis addon to Postgres is very > > nice. > > > Another nice capability in PostgreSQL is that you can use different > > languages for writing your stored procedures (should you wish to use > > them). Some of the available languages (in addition to PL/pgSQL) are > > Perl, Python, Tcl, PHP, Ruby, R, Scheme, and Java. > > > Whether you choose to use Postgres or not, I'd recommend at least > > looking at it. There is even a live cd (which I have not tried) at > >http://www.postgresql.org/download/which lets you try PostgreSQL out > > without having to install it. > > > On Sep 4, 7:38 pm, Joshua Russo wrote: > > > On Fri, Sep 4, 2009 at 11:07 PM, Tim Chase > > > wrote: > > > > > > I personally don't have any experience with PostgreSQL and I'm > > generally > > > > > working in a mixed MS and Linux environment. I'm interested to hear > > > > peoples > > > > > views on the pluses and minuses of the two different systems. I'm a > > bit > > > > of a > > > > > query geek too. How does that play in? I know in MySQL there are > > > > limitations > > > > > on where you can use subqueries. Is that true with PostgreSQL? (Ya I > > > > could > > > > > just look that one up but it's just an example.) > > > > > I did a writeup of MySQL vs. PostgreSQL a while back: > > > > >http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html > > > > > Most of the issues still stand -- though I understand MySQL now > > > > has native(ish) support for Geo information (check the GeoDjango > > > > code to see if it supports the MySQL Geo implementation -- last I > > > > checked the source it was Oracle & PostgreSQL only). > > > > > To answer your direct question, PostgreSQL has long-standing > > > > support for all kinds of crazy sub-queries. MySQL has added most > > > > of those abilities over time. This used to be a deal-breaker for > > > > me, making Postgres the clear winner. Now they're about even. > > > > > Lastly, my closing arguments in that link still stand -- if you > > > > don't have a pressing need to choose one or the other, code & > > > > test for both. > > > > Good point on geo side side of things. > > > > One place I have found subqueries very useful is in the From. > > Functionally > > > identical to a view but you don't need to clutter the database with > > rarely > > > used views. That and you can use variables. If you really wanted to get > > > fancy you can even nest them. It can save a lot on application logic and > > > produce some interesting reports. I don't believe either of our friends > > here > > > support them though. That is one feature I would love to see. > > > > I tend to agree with your closing arguments. I try to stay away from any > > > DBMS unique functionality. I very rarely even find much of a need for > > > triggers and/or stored procedures. (But they can come in exceptionally > > handy > > > when turning 10s of 1000s of rows of un-normalized data, into close to a > > > million rows of normalized. Done in a matter of minutes!) --~--~
Re: PostgreSQL or MySQL, What are the +'s and -'s?
Wow, that's a cool trick to be able to implement stored procedures in different languages. I might actually use them more if I could do everything in the same language as the application. I only looked quickly through the PostgreSQL docs for subqueries. Thanks for the heads up. As far as the Gis functionality goes, I could see a database like that needing some serious horse power if it became popular. Any thoughts on how that would reconcile with the weakness in replication? I suppose most Gis systems are more for out put than input so the slow replication might not really matter that much. On Sat, Sep 5, 2009 at 1:17 AM, Siemster wrote: > > PostgreSQL does support subqueries in the from clause, however iirc, > the subquerys require an alias. > > If you decide to do geo then the PostGis addon to Postgres is very > nice. > > Another nice capability in PostgreSQL is that you can use different > languages for writing your stored procedures (should you wish to use > them). Some of the available languages (in addition to PL/pgSQL) are > Perl, Python, Tcl, PHP, Ruby, R, Scheme, and Java. > > Whether you choose to use Postgres or not, I'd recommend at least > looking at it. There is even a live cd (which I have not tried) at > http://www.postgresql.org/download/ which lets you try PostgreSQL out > without having to install it. > > > > On Sep 4, 7:38 pm, Joshua Russo wrote: > > On Fri, Sep 4, 2009 at 11:07 PM, Tim Chase > > wrote: > > > > > > > > > > > > > > I personally don't have any experience with PostgreSQL and I'm > generally > > > > working in a mixed MS and Linux environment. I'm interested to hear > > > peoples > > > > views on the pluses and minuses of the two different systems. I'm a > bit > > > of a > > > > query geek too. How does that play in? I know in MySQL there are > > > limitations > > > > on where you can use subqueries. Is that true with PostgreSQL? (Ya I > > > could > > > > just look that one up but it's just an example.) > > > > > I did a writeup of MySQL vs. PostgreSQL a while back: > > > > >http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html > > > > > Most of the issues still stand -- though I understand MySQL now > > > has native(ish) support for Geo information (check the GeoDjango > > > code to see if it supports the MySQL Geo implementation -- last I > > > checked the source it was Oracle & PostgreSQL only). > > > > > To answer your direct question, PostgreSQL has long-standing > > > support for all kinds of crazy sub-queries. MySQL has added most > > > of those abilities over time. This used to be a deal-breaker for > > > me, making Postgres the clear winner. Now they're about even. > > > > > Lastly, my closing arguments in that link still stand -- if you > > > don't have a pressing need to choose one or the other, code & > > > test for both. > > > > Good point on geo side side of things. > > > > One place I have found subqueries very useful is in the From. > Functionally > > identical to a view but you don't need to clutter the database with > rarely > > used views. That and you can use variables. If you really wanted to get > > fancy you can even nest them. It can save a lot on application logic and > > produce some interesting reports. I don't believe either of our friends > here > > support them though. That is one feature I would love to see. > > > > I tend to agree with your closing arguments. I try to stay away from any > > DBMS unique functionality. I very rarely even find much of a need for > > triggers and/or stored procedures. (But they can come in exceptionally > handy > > when turning 10s of 1000s of rows of un-normalized data, into close to a > > million rows of normalized. Done in a matter of minutes!) > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
PostgreSQL does support subqueries in the from clause, however iirc, the subquerys require an alias. If you decide to do geo then the PostGis addon to Postgres is very nice. Another nice capability in PostgreSQL is that you can use different languages for writing your stored procedures (should you wish to use them). Some of the available languages (in addition to PL/pgSQL) are Perl, Python, Tcl, PHP, Ruby, R, Scheme, and Java. Whether you choose to use Postgres or not, I'd recommend at least looking at it. There is even a live cd (which I have not tried) at http://www.postgresql.org/download/ which lets you try PostgreSQL out without having to install it. On Sep 4, 7:38 pm, Joshua Russo wrote: > On Fri, Sep 4, 2009 at 11:07 PM, Tim Chase > wrote: > > > > > > > > I personally don't have any experience with PostgreSQL and I'm generally > > > working in a mixed MS and Linux environment. I'm interested to hear > > peoples > > > views on the pluses and minuses of the two different systems. I'm a bit > > of a > > > query geek too. How does that play in? I know in MySQL there are > > limitations > > > on where you can use subqueries. Is that true with PostgreSQL? (Ya I > > could > > > just look that one up but it's just an example.) > > > I did a writeup of MySQL vs. PostgreSQL a while back: > > >http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html > > > Most of the issues still stand -- though I understand MySQL now > > has native(ish) support for Geo information (check the GeoDjango > > code to see if it supports the MySQL Geo implementation -- last I > > checked the source it was Oracle & PostgreSQL only). > > > To answer your direct question, PostgreSQL has long-standing > > support for all kinds of crazy sub-queries. MySQL has added most > > of those abilities over time. This used to be a deal-breaker for > > me, making Postgres the clear winner. Now they're about even. > > > Lastly, my closing arguments in that link still stand -- if you > > don't have a pressing need to choose one or the other, code & > > test for both. > > Good point on geo side side of things. > > One place I have found subqueries very useful is in the From. Functionally > identical to a view but you don't need to clutter the database with rarely > used views. That and you can use variables. If you really wanted to get > fancy you can even nest them. It can save a lot on application logic and > produce some interesting reports. I don't believe either of our friends here > support them though. That is one feature I would love to see. > > I tend to agree with your closing arguments. I try to stay away from any > DBMS unique functionality. I very rarely even find much of a need for > triggers and/or stored procedures. (But they can come in exceptionally handy > when turning 10s of 1000s of rows of un-normalized data, into close to a > million rows of normalized. Done in a matter of minutes!) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
On Fri, Sep 4, 2009 at 11:07 PM, Tim Chase wrote: > > > I personally don't have any experience with PostgreSQL and I'm generally > > working in a mixed MS and Linux environment. I'm interested to hear > peoples > > views on the pluses and minuses of the two different systems. I'm a bit > of a > > query geek too. How does that play in? I know in MySQL there are > limitations > > on where you can use subqueries. Is that true with PostgreSQL? (Ya I > could > > just look that one up but it's just an example.) > > I did a writeup of MySQL vs. PostgreSQL a while back: > > http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html > > Most of the issues still stand -- though I understand MySQL now > has native(ish) support for Geo information (check the GeoDjango > code to see if it supports the MySQL Geo implementation -- last I > checked the source it was Oracle & PostgreSQL only). > > To answer your direct question, PostgreSQL has long-standing > support for all kinds of crazy sub-queries. MySQL has added most > of those abilities over time. This used to be a deal-breaker for > me, making Postgres the clear winner. Now they're about even. > > Lastly, my closing arguments in that link still stand -- if you > don't have a pressing need to choose one or the other, code & > test for both. Good point on geo side side of things. One place I have found subqueries very useful is in the From. Functionally identical to a view but you don't need to clutter the database with rarely used views. That and you can use variables. If you really wanted to get fancy you can even nest them. It can save a lot on application logic and produce some interesting reports. I don't believe either of our friends here support them though. That is one feature I would love to see. I tend to agree with your closing arguments. I try to stay away from any DBMS unique functionality. I very rarely even find much of a need for triggers and/or stored procedures. (But they can come in exceptionally handy when turning 10s of 1000s of rows of un-normalized data, into close to a million rows of normalized. Done in a matter of minutes!) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
Nice! thanks for that one. On Fri, Sep 4, 2009 at 10:59 PM, Continuation wrote: > > MySQL has better replication support. > > MySQL has built-in replication based on log shipping. > > Postgresql doesn't have built in replication. You have to use external > tools. From > http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#PostgreSQL_Replication_Weakness > : > > "Slony-I, the most widely used PostgreSQL replication tool, is > inherently inferior to MySQL's built in replication for a number of > reasons. First, it uses SQL and triggers to replicate the data across > servers. This is considerably slower than MySQL's binary log shipping > and makes the communication costs much higher. Second, Slony-I's > communication costs grow quadratically in relation to the number of > servers in the replication pool (Order(n^2)). This makes it inherently > unusable for larger clusters. If we conservatively figure that Slony- > I's SQL/trigger method takes twice as much communication as MySQL's > binary log shipping, we can easily see how poorly this would work for > larger clusters in the real world. > > With two servers: MySQL: 2 = 2 PostgreSQL: 2*2^2 = 8 > > With 4 servers: MySQL: 4 = 4 PostgreSQL: 2*4^2 = 32 > > With 12 servers: MySQL: 12 = 12 PostgreSQL: 2*12^2 = 288. > > > > On Sep 4, 4:29 pm, Joshua Russo wrote: > > I personally don't have any experience with PostgreSQL and I'm generally > > working in a mixed MS and Linux environment. I'm interested to hear > peoples > > views on the pluses and minuses of the two different systems. I'm a bit > of a > > query geek too. How does that play in? I know in MySQL there are > limitations > > on where you can use subqueries. Is that true with PostgreSQL? (Ya I > could > > just look that one up but it's just an example.) > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
> I personally don't have any experience with PostgreSQL and I'm generally > working in a mixed MS and Linux environment. I'm interested to hear peoples > views on the pluses and minuses of the two different systems. I'm a bit of a > query geek too. How does that play in? I know in MySQL there are limitations > on where you can use subqueries. Is that true with PostgreSQL? (Ya I could > just look that one up but it's just an example.) I did a writeup of MySQL vs. PostgreSQL a while back: http://www.mail-archive.com/django-users@googlegroups.com/msg70188.html Most of the issues still stand -- though I understand MySQL now has native(ish) support for Geo information (check the GeoDjango code to see if it supports the MySQL Geo implementation -- last I checked the source it was Oracle & PostgreSQL only). To answer your direct question, PostgreSQL has long-standing support for all kinds of crazy sub-queries. MySQL has added most of those abilities over time. This used to be a deal-breaker for me, making Postgres the clear winner. Now they're about even. Lastly, my closing arguments in that link still stand -- if you don't have a pressing need to choose one or the other, code & test for both. -tim --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
MySQL has better replication support. MySQL has built-in replication based on log shipping. Postgresql doesn't have built in replication. You have to use external tools. From http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#PostgreSQL_Replication_Weakness: "Slony-I, the most widely used PostgreSQL replication tool, is inherently inferior to MySQL's built in replication for a number of reasons. First, it uses SQL and triggers to replicate the data across servers. This is considerably slower than MySQL's binary log shipping and makes the communication costs much higher. Second, Slony-I's communication costs grow quadratically in relation to the number of servers in the replication pool (Order(n^2)). This makes it inherently unusable for larger clusters. If we conservatively figure that Slony- I's SQL/trigger method takes twice as much communication as MySQL's binary log shipping, we can easily see how poorly this would work for larger clusters in the real world. With two servers: MySQL: 2 = 2 PostgreSQL: 2*2^2 = 8 With 4 servers: MySQL: 4 = 4 PostgreSQL: 2*4^2 = 32 With 12 servers: MySQL: 12 = 12 PostgreSQL: 2*12^2 = 288. On Sep 4, 4:29 pm, Joshua Russo wrote: > I personally don't have any experience with PostgreSQL and I'm generally > working in a mixed MS and Linux environment. I'm interested to hear peoples > views on the pluses and minuses of the two different systems. I'm a bit of a > query geek too. How does that play in? I know in MySQL there are limitations > on where you can use subqueries. Is that true with PostgreSQL? (Ya I could > just look that one up but it's just an example.) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
On Fri, Sep 4, 2009 at 9:01 PM, Léon Dignòn wrote: > > One of many: > PostgreSQL supports naive foreign referential-integrity constraints. > MySQL (default: myISAM) does it only with InnoDB which leads to other > Problems: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html > > On Sep 4, 10:29 pm, Joshua Russo wrote: > > I personally don't have any experience with PostgreSQL and I'm generally > > working in a mixed MS and Linux environment. I'm interested to hear > peoples > > views on the pluses and minuses of the two different systems. I'm a bit > of a > > query geek too. How does that play in? I know in MySQL there are > limitations > > on where you can use subqueries. Is that true with PostgreSQL? (Ya I > could > > just look that one up but it's just an example.) > Yup, that one I got. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
Re: PostgreSQL or MySQL, What are the +'s and -'s?
One of many: PostgreSQL supports naive foreign referential-integrity constraints. MySQL (default: myISAM) does it only with InnoDB which leads to other Problems: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html On Sep 4, 10:29 pm, Joshua Russo wrote: > I personally don't have any experience with PostgreSQL and I'm generally > working in a mixed MS and Linux environment. I'm interested to hear peoples > views on the pluses and minuses of the two different systems. I'm a bit of a > query geek too. How does that play in? I know in MySQL there are limitations > on where you can use subqueries. Is that true with PostgreSQL? (Ya I could > just look that one up but it's just an example.) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
PostgreSQL or MySQL, What are the +'s and -'s?
I personally don't have any experience with PostgreSQL and I'm generally working in a mixed MS and Linux environment. I'm interested to hear peoples views on the pluses and minuses of the two different systems. I'm a bit of a query geek too. How does that play in? I know in MySQL there are limitations on where you can use subqueries. Is that true with PostgreSQL? (Ya I could just look that one up but it's just an example.) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
