SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast EricBrian's Avatar
    Join Date
    Dec 2002
    Location
    Washington, D.C.
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySql or PostgreSQL, your choice?

    MySql or PostgreSQL... Which one would you choose and why? If you know both, which one is better?

    I use mySql and love it. I've never tried PostgreSQL.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    is this like "ginger or maryann, which one would you choose?"

    if i had a choice, i would take whichever database the client already has, since they all use sql

    as for my own personal use, i would use microsoft access

    and i say that with over 25 years of experience using databases

    no, i will not elaborate further, nor take part in the flamefest that is sure to follow

    in any case, the question lacks context

    what if the tables have only a few rows? what if the database has to support thousands of transactions per second? what if the application requires extensive use of triggers? what if? which one?


    rudy
    http://rudy.ca/

  3. #3
    SitePoint Enthusiast nuno's Avatar
    Join Date
    Oct 2002
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL definitely

  4. #4
    SitePoint Enthusiast EricBrian's Avatar
    Join Date
    Dec 2002
    Location
    Washington, D.C.
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    no, i will not elaborate further, nor take part in the flamefest that is sure to follow
    Ohh.... I don't want to start a flamefest, I am honestly just seeking people's opinions. I know the question lacks context... Was hoping people would just give a one-liner on why they prefer their choice.

    BTW, I developed a web site for client using MS access and it served its purpose well, I am not out to get MS access!
    Last edited by EricBrian; Dec 25, 2002 at 13:40.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PostgreSQL. It has a superior data management, data integrity, and other features and equal or better performance.

    But personally I prefer Sybase ASE.

  6. #6
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    pgSQL, it's more what I'd prefer in a project long term. Same as I'd prefer to use SQL Server over Access, and normally switch my access projects to SQL Server at some point in time

    J
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  7. #7
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It depends on the application, if it's a small app with mainly data reads and with a small data set that rarely changes then I would opt for mySQL. But for anything else i'd choose pgSQL.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  8. #8
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I say MySQL just because I have very limited experience with Postgre (although people have told me I'd enjoy it), but if the client has the $$$ to pony up I'd go with MSSQL or Oracle (this last one assumes an existing Oracle or MSSQL structure).

  9. #9
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll say MySQl if I have to pick between both of them just because that's what is most commonly used w/ PHP. However, I use MS SQL most often now days.
    Ohai!

  10. #10
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have participated in these discussions far too many times, so I will just post a link to my FAQ answer at http://www.brainscraps.com/faq/pg_my.html, which is the result of considerable time invested in both PostgreSQL and MySQL.

    Please note: I speak from direct experience with each of the points I make. This is not academic stuff.

  11. #11
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use a virtual host, you most likely don't have a choice - you have to go with mySQL. But where it is possible to choose, I'd say any sane (knowledgable) person would go with Postgres where you are dealing with writing to the database. mySQL is EXCELLENT for one-way data access, but not much more.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  12. #12
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hosting is a point to consider for sure, not many hosts offer pgSQL, most just offer mySQL as you have pointed out. Personally I love pgSQL and try to use it in all my apps if I possibly can, most are pretty complex or require storing a large amount of data (Like the system I am writing which takes 100+ measurements every 5 minutes of every day - it's growing large quite quick), so there really isn't much to sway me towards mySQL.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  13. #13
    SitePoint Enthusiast
    Join Date
    Oct 2002
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PostreSQL , never see use before. Not even touch it once. I always think of it as elephant database. So no comment. Well it depends on the limitations and your purpose.

    1. If you want to build DSS and data mining system with limited budget then use MySQL.
    2. Web hosting use MySQL.
    3. Don't know anything about View, Stored Procedures, Triggers, Cursor, Domain use MySQL.
    4. Like to use temporary tables and multiple joins in Application Code use MySQL.
    5. Complex project with tonnes of 4th normalized tables use Firebird/Interbase.
    6. Like to build queries which join 2 views on top of 1 view which join 3 tables which join with a view that join 4 tables use Firebird/Interbase.
    8. Got adventurous and want to do 1 , 5 and 6 use SAP DB. No single third-party documentation on the web.
    9. Use proven enterprise level database with no DBA hanging around use Firebird/Interbase.
    10. Want to get subset of data from big database and generate report in 8 seconds use Access2000.
    11. Want to go home early use Access2000
    12. Want to be superprogrammer build your own database. There are many super programmers around. http://www.thefreecountry.com/develo...database.shtml


    So use Firebird, MySQL, SAP DB and Accesss2000 if you has no budget for database. For OSS users all above are free like free beer except Access2000 (free if you use ADOX.Catalog code to generate your tables). So I never use Elephant Database before , no point to use it as there are tonnes of databases around from ISAM class MySQL/Access to Enterprise level Firebird/SAP DB/InnoDB(non-MySQL version). Not to said SQL Server , MSDE etc. Big Blue , Playboy database (Oracle Chief) and Fox database and XML and etc etc etc.

    FireBird/Interbase
    http://www.firebird.sourceforge.net

    SAP DB
    http://www.sapdb.org

    MySQL FlatFile Database
    http://www.mysql.com

  14. #14
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm pretty sure here that pgSQL can handle all the situations you just outlined. It's 99% as easy to use as mySQL for what most people use mySQL for. pgSQL is a free database, so if you want to do data mining then you're better off using pgSQL because of the stored procedures, and views alone, and the fact that it handles relations - something any well designed DB is based around.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  15. #15
    SitePoint Enthusiast
    Join Date
    Oct 2002
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with Karl except for 2 reasons. Report generating is a major features that are needed by many if not all people so Access2000 will always be more popular than pgSQL ;D.However , MySQL is a good enough flat DBMS for most people.

    They need a way to analyze the data so they must retrieve the data from production system (Sybase,MSSQL) to Data Warehouse system. I agree 120% that Stored Procedures and Views can help tremendously because I like to normalized database so much that I need them quite a lot. However 'real' database handles relation which often hamper productivity.

    Tables that had primary key and foreign key must be load in specific sequences. For example, Customer table must be fill before Order table can be fill because of Order table has a FK that refer to Customer table primary key. After that Order-Item table be populated because Order-Item has a key that depends on Order table. After that the Return_Good table as it has 1 FK in Customers table and 1 FK in Order table. So normally it is 70 ++ tables with no relation among them as they are loaded seperately. So here MySQL wins. Besides that there are not much concern as the 70++ tables are only use to generate meaningful data such as sales per quarters and what brand of water cooler are more popular then they raw data are archieved and left to rot. Only SELECT operation here .Besides MySQL is much faster than other database.

    The question is for application that use SELECT,INSERT,UPDATE,MySQL is a pathetic choice. It does not has Stored Procedures, Views ,Triggers. Without them the safety of Enterprise data cannot be safeguard as any programmer error in one of gazillions applications that access the same database can wipe out the company data. Only with Stored Procedures there can be data safety. For example, Employee table got tonnes of confidential information but the web application need the userid and password to authenticate the user. A stored procedure that retrieve only the minimum info need to authenticate the user instead of accidently retrieve salary , home address etc.Access to data can be controlled by stored procedures and web developers may never see a single line of SQL command in his PHP/ASP application. Only udp_validateUser("john","password"); udp_updateAccessToken("username","password"); udp_retrieveOrderList('A123');

    "According to Michael Stonebraker in 1990s, then still a Professor at Berkeley, was
    publicly asking the question, of whether RDBMS were a done deal. In fact, there had only
    been a small amount of progress made in the last ten years (1980-1990) with the exception of attempts to extend the relation model with object-oriented characteristics. "

    So the founder of elephant database(PostreSQL) Michael Stonebraker found Illustra which was acquired by Informix for it 'Data Blade Technology' .The Data Blades concept extends the database kernel with additional data types and functions, so that complex data structures could also be stored in the database system, and their data type could correspondingly be used. Something like ORDBMS(Object Relational Database Management System). So I never want to use academic database. How's PostreSQL data type are always more complex than other database. Can I store unstructure data like movie in the PostreSQL and only retrieve the interval between 23 and 24 minutes for preview ????? Or stored MP3 Blob but can query for it's attributes such as bit rate???? Object is too complex for database.

    However, Firebird 2 Alpha RDBMS are more promising to me as there are 55++ developers that refactor the database to C++ before attempt to extend the features. And the only feature I want is the transaction log and scheduled backup. RDBMS is a done deal but MySQL still has many more to catch up but it is the easy to use as easy as DBASE database back in 1993. How the hot thing is clustered database but definitely not much usage as most companies only use Raid 1 Mirror to guaranteed data safety. Unless you are ecommerce you must use Clustered database with RAID15. SAPDB(http://www.sapdb.org) is another good database but I only use it once for a company that use SAP ERP system. Very fine database but not popular so nobody around knew anything about the database. So many database around but many had lousy tool except SQL Server, Firebird and MySQL and Access. At the EOTD it is the tool that wins the day.

  16. #16
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with vipeint about the importance of stored procedures, but it is not the only way to restrict users and client applications in a database. More importantly, a relational database should have a complete system of constraint management, including DOMAIN and SCHEMA support, which gives a much more complete method of managing and restricting users.

    ... whether RDBMS were a done deal
    Well, the world has not yet seen a complete RDBMS, so how can we call it a done deal? Too many people assume they understand the relational data model, when what they understand is a particular SQL DBMS, which is not necessarily the same thing. Even PostgreSQL violates some of the concepts in the relational data model. Doing away with the relational model is like doing away with Calculus when what you want to do is manipulate numbers. In other words, the relational model, applied right, can deal with ANY data situation, because it only specifies methods of managing your data, not how the storage is achieved, nor even what kind of data you want to manage.

    How's PostreSQL data type are always more complex than other database. Can I store unstructure data like movie in the PostreSQL and only retrieve the interval between 23 and 24 minutes for preview ????? Or stored MP3 Blob but can query for it's attributes such as bit rate???? Object is too complex for database.
    I'm not sure if this is a question or a statement, but PostgreSQL has the same base datatypes as any other SQL system, but, it has an answer to the complexity of objects: extensible data types.

    Don't make the mistake of assuming that the relational model restricts you to using "simple" base data types, such as CHAR, INT, etc... (essentially just numbers and text). The original intent of the relational model was to use the base datatypes as building blocks to allow the user to create more complex, custom datatypes. PostgreSQL supports this, so the user can indeed create an MP3 datatype, with special operators to query bit rate, or one could create an XML datatype, or even a PDF datatype. Why not, as long as you can define methods to store, extract and manipulate that data.

    Objects are too complex for a relational DBMS? Exactly what is a "simple" datatype? Even the basic INT has an incredible number of operators you can use to deal with it.

    In fact, too often developers assume that the way to store objects is to make a table for each class, with the columns of the table handling the attributes of that class. This is appropriate for some object classes, but it is not the only way. Often classes behave as types. So why not make a datatype for that class in your database. Then, why not create some operators for manipulating the attributes of that class? This is totally aside from the concept of an object-relational DBMS (extensible, inherited table definitions), which is really only a nice notation for something that can easily be accomplished with a relational DBMS done right.

    Interestingly enough, PostgreSQL will soon support Java as a procedural language, so you will even be able to use Java methods for operators in custom Java datatypes.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •