Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 



Forgot your password?
typodupeerror
Get HideMyAss! VPN, PC Mag's Top 10 VPNs of 2016 for 55% off for a Limited Time ×
Databases Open Source Software

Why To Choose PostgreSQL Over MySQL, MariaDB (dice.com) 244

Nerval's Lobster writes: PostgreSQL, MySQL, and MariaDB are the three "main" open-source relational databases available today (there are four if you count FireBird, but for brevity we're excluding it). For years, MySQL had a reputation of being faster than PostgreSQL, but much of that was due to the MyISAM database engine, which didn't support transactions. On the flip side of things, PostgreSQL had a reputation for being slower but more reliable. But with the recent versions of both platforms, things have started to change; for example, speed has been less of a problem for PostgreSQL, while MySQL now defaults to the InnoDB engine, which does handle transactions. According to developer David Bolton, here's why PostgreSQL is worth a second look for your database-management needs (Dice link).
This discussion has been archived. No new comments can be posted.

Why To Choose PostgreSQL Over MySQL, MariaDB

Comments Filter:
  • Please... (Score:4, Funny)

    by The-Ixian ( 168184 ) on Thursday December 03, 2015 @03:29PM (#51051829)

    Try my product... I guarantee you will be satisfied with it or your money back... here's how to order...

  • Not rocket science (Score:2, Insightful)

    by Anonymous Coward

    Postgres is an excavator, while the other two are bobcats. For heavy lifting, the choice is obvious.

    • by gmack ( 197796 ) <gmack&innerfire,net> on Thursday December 03, 2015 @03:42PM (#51051931) Homepage Journal

      Postgres is an excavator, while the other two are bobcats. For heavy lifting, the choice is obvious.

      Well, not so fast. For single server, PostgreSQL is superior (although less user friendly from the shell) But Mysql / MariaDB still beat PostgreSQL when it comes to replication.

      • by jdavidb ( 449077 )

        But Mysql / MariaDB still beat PostgreSQL when it comes to replication.

        PostgreSQL's replication must really, really suck, then.

        • by gmack ( 197796 )

          Believe me, I absolutely hate clustering on MySQL/MariaDB, it's just that PostgreSQL's is worse.

      • But Mysql / MariaDB still beat PostgreSQL when it comes to replication.

        ...dafuq?

        Dude, MySQL didn't even get two-way replication until 5.1 (and at the time you had to jimmy with Federation and SQL threads to get even that functionality). I'm sure it may have gotten easier/better since then, but compared to psql, it still ain't all that much to write home about.

        Now master -> slave replication? Sure... MySQL is easier and even more fine-grained in some aspects. But Master-Master replication (you know, like you would set up in making an HA cluster)? IIRC MySQL does it, but it's

        • But Master-Master replication (you know, like you would set up in making an HA cluster)? IIRC MySQL does it, but it's a separate product that I think you have to purchase [mysql.com].*

          * there could be a community/OSS version out there, but I kinda doubt it.

          Doubt no more. Here ya go [mysql.com].

    • by Rei ( 128717 ) on Thursday December 03, 2015 @03:48PM (#51051987) Homepage

      Postgres also has a lot of nice features. For example, I love table inheritance [postgresql.org] and don't understand why it's not more common of a feature - I probably use it in about 80% of the databases I make these days. It's just so logical and useful for real-world data (which often has at least some degree of heirarchial structure), and avoids having to hack together triggers or query logic to emulate it.

      • by jellomizer ( 103300 ) on Thursday December 03, 2015 @03:56PM (#51052057)

        Table Inheritance is purely awesome.
        A good structure is having a commonality across all tables.
        My personal set that I like are the following
        UID,
        CreateDateTime,
        ModifiedDateTime,
        Enabled,
        Archive,
        ArchiveFromUID

        Without it such feature, it is too easy to get into a habit of adding non-standard naming conventions, and actions. Especially if it was originally suppose to be a quick lookup table which happened over time become the core table for the data entry in the system.

      • Re: (Score:3, Informative)

        by Qzukk ( 229616 )

        As a programmer my favorite features are all nonstandard SQL stuff. For instance: arrays, array operators, and literal array notation.

        No more whiny devs complaining about how they can't prepare foo IN ($1) so fuck prepared statements and security:

        PREPARE getuser AS SELECT * FROM user WHERE level = ANY ($1);

        EXECUTE getuser ( '{administrator,manager}' );

        (This works way better from a program than from the psql console - only problem is that if you're doing arbitrary strings, the escaping challenge m

  • PL/pgSQL (Score:5, Informative)

    by nerdyalien ( 1182659 ) on Thursday December 03, 2015 @03:42PM (#51051937)

    At least for me, the killer feature of PostgreSQL is its procedure language PL/pgSQL. By a fortunate accident, I had the opportunity to write some complicated features (read "calculation heavy") for a web app using PL/pgSQL. Once coupled with triggers, you can just leave everything to the DB to the point, controller has to do nothing except query and return JSON objects to the front. It is so expressive, powerful, efficient and reliable.

    I have worked with MySQL, Oracle, MSSQL and as of late MongoDB.
    Given a choice, I will always settle for PostgreSQL... it is just so natural to work with.

    • by flacco ( 324089 )

      Yep, MySQL (and variants) feels clunky and contrived compared to PostgreSQL, to me.

    • Re: (Score:2, Troll)

      by QuietLagoon ( 813062 )

      PL/pgSQL

      Oh great! Another mouthful of random letters....

    • Re:PL/pgSQL (Score:4, Informative)

      by crackspackle ( 759472 ) on Thursday December 03, 2015 @04:13PM (#51052207)

      At least for me, the killer feature of PostgreSQL is its procedure language PL/pgSQL..

      And please add if that's not enough for you, you can also write your triggers and functions in Python, Perl and Tcl Too.

    • by Tom ( 822 )

      This is the part I haven't yet dug into, I still do many of my calculations in PHP or in SQL statements. Do you have a good quickstart tutorial somewhere? I know a couple things I would love to compute on the database in my current main project.

    • For what I do, the combination of PL/pgSQL and PostGIS is like nothing else. I can run complex geographic calculations on the database server, which saves a huge amount of network traffic.

  • by mveloso ( 325617 ) on Thursday December 03, 2015 @03:50PM (#51051999)

    MySQL has JetProfiler, Postgresql does not.

  • Transactional DDL (Score:2, Informative)

    by Anonymous Coward

    PostgreSQL has it. Makes development faster by an order of magnitude with a big database schema.

  • by PhrostyMcByte ( 589271 ) <phrosty@gmail.com> on Thursday December 03, 2015 @03:53PM (#51052031) Homepage
    And not because MySQL is a better product, but because everyone thinks of MySQL as the database that isn't a very good choice for large projects. You should be comparing Postgres to SQL Server, Oracle, etc. -- the big guys.
  • Was speed ever an actual problem with PostgreSQL? Or was it just that clueless folks wanted a better throughput number without caring about the implications?

  • by trybywrench ( 584843 ) on Thursday December 03, 2015 @03:55PM (#51052047)
    DBA's are known for being rational and objective when discussing competing RDBMSs, I'm looking forward to this discussion. Maybe next we could discuss which is better Islam, Christianity, or atheism.
    • by quantaman ( 517394 ) on Thursday December 03, 2015 @04:02PM (#51052127)

      DBA's are known for being rational and objective when discussing competing RDBMSs, I'm looking forward to this discussion. Maybe next we could discuss which is better Islam, Christianity, or atheism.

      The answer is obvious:

      Emacs

      • DBA's are known for being rational and objective when discussing competing RDBMSs, I'm looking forward to this discussion. Maybe next we could discuss which is better Islam, Christianity, or atheism.

        The answer is obvious:

        Emacs

        You misspelled "systemd". "Relational Database" is slated to become one of its new features for next year's release.

      • Nope, the answer is Perl.

    • by jedidiah ( 1196 ) on Thursday December 03, 2015 @04:17PM (#51052229) Homepage

      MySQL is so bad, it's the kind of database that could make Donald Trump convert to Islam.

      • When I use postgresql, every once in a while I learn about some feature that is kind of nice, and might make my life easier (though sometimes at the cost of compatibility).
        When I use MySQL, every once in a while, I learn about a feature (*bug*) that is kind of annoying, and definitely makes things more complicated (and sometimes at the cost of compatibility).
        There is no question in my mind which to use. If a hosting platform only supports MySQL, I won't use that hosting platform.
  • SQL Server is free up to 10 GB. After that, I'll probably still pay for SQL Server. MySQL and MariaDB are toys, and there's nothing that PostGre offers that SQL Server doesn't.
    • Operating system independence? I get that it may not be important TO YOU, but you're going to have a hard time downloading those SQL Server binaries for any Linux distro.

      Oracle is free up to 10GB as well. You just don't get any of the patches or support, but who needs em? Relational data isn't all that valuable anyhow.
      • by DogDude ( 805747 )
        If you're changing server OS's in the middle of an implementation, then you've got much bigger problems than what kind of database you're using.
        • Right. Because it's totally impossible that you might be building something that you're intending to sell to more than one customer.

        • by hawk ( 1151 )

          If you're using an OS that supports SQL Server for a commercially important project, you've got much bigger problems than which DB you're using, and should be firing a CTO who is ignorant of the last 25 years of OS security issues . . .

          hawk

    • by jedidiah ( 1196 )

      Even Oracle is free if you only need 10G and no support.

      It's hard to appreciate how truly trivial that limit is until you actually try to use one of these "free" commercial databases.

      • Hang on a sec, lemme check...

        [root@machine-name-redacted postgres]# du -h base | tail -1
        32G base

        Sorry, you were saying?

    • Cross platform and free are two things postgresql offers that sql server doesn't

    • by labnet ( 457441 )

      Except the 10k licensing fees....

    • Storage beyond 10GB, maybe? Or would that be entirely too obvious?

    • Re: (Score:3, Interesting)

      I'm a Sql Server guy myself (I spent a brief period as #1 user by rep within the sql-server tag on Stack Overflow back in 2009), but Postgresql does offer some nice language features missing in Sql Server. It also has table inheritance and for larger servers can save you a LOT of licensing costs. It performs pretty well these days, too. I agree that MySql is toy, though. Still no windowing functions after 10+ years as part of the ansi standard, awful handling of NULLs, and no FULL JOINs are just three of t
    • by PRMan ( 959735 )
      Plus, EVERYTHING else is terribly slow after using SQL Server.
      • Last time I had to deal with MSSQL, I found PostgreSQL to be faster on same queries/datasets. Sometimes significantly so.

        YMMV

        --Coder
  • Dice spam (Score:4, Insightful)

    by grimmjeeper ( 2301232 ) on Thursday December 03, 2015 @04:04PM (#51052135) Homepage
    Like I'm going to trust a Dice "insights" page to tell me what DBMS to use...
  • Memsql.

    That is all.
  • by johannesg ( 664142 ) on Thursday December 03, 2015 @04:46PM (#51052459)

    My software stores spacecraft testing data. Each test is good for a couple of gigabytes, and we run dozens each year. We use PostgreSQL because:

    - Rock-solid stability. Zero data loss after a decade and a half of operations. Zero problems of _any kind_, over that same period.
    - Great features and excellent standard conformance.
    - Documentation is absolutely excellent, best of any open source project I know of.
    - pgAdmin 3 allows trivial on-the-fly inspection of databases.
    - No licensing issues. No payment "per core", "per connection", or whatever other bullshit they've come up with now. Install where we like, as much as we like.

    We didn't choose MySQL because it lacked ACID compliance (data loss would be problematic), and because it has entirely too many weird gotchas. Sure, you can work around all of those... But why would you if you can also choose PostgreSQL?

    As for Oracle, that's what we used before PostgreSQL. Back in the days, you couldn't store more than 2000 characters in a string, their C interface was the stuff of nightmares, support tools were non-existent, and installing it yourself, or on anything that wasn't the Blessed HPUX Cluster, was impossible. We had two (minor) data loss events in three years, but that might have been a hardware issue. But the killer reason is of course licensing - with Oracle, we had one server on which all work had to take place. Now we have dozens, and setting up an ad-hoc server for some quick testing is trivial - both technically, and in terms of licensing.

    I can take a laptop to a customer site and do a demo or some work, without worrying about licensing. With Oracle you never know whether you are compliant or not, and being found to be non-compliant is extremely, extremely painful.

    • by lgw ( 121541 )

      Does Postgre have reasonable DB replication? Or is losing a server a crisis?

    • by Gramie2 ( 411713 )

      > their C interface was the stuff of nightmares

      I see that you also have traumatic memories of Pro*C

    • by StormReaver ( 59959 ) on Thursday December 03, 2015 @05:47PM (#51052937)

      My software is heavy on financial calculations, and needs to be 100% accurate and reliable. MySQL isn't even a remote consideration, as it will happily, and silently, alter calculation results. PostgreSQL has been 100% reliable and accurate. MySQL is also very slow, whereas PostgreSQL is very snappy. We tried MySQL when we were evaluating databases, and it was laughable.

      My company has abandoned Oracle in favor of PostgreSQL for all new projects (and is rewriting a couple large projects to get off of Oracle), largely due to licensing, but also because Oracle lacks lots of basic functionality that we take for granted in PostgreSQL. We've also found Oracle to be very temperamental and fragile, whereas PostgreSQL is highly reliable and robust.

      It's really not even a fair fight. PostgreSQL is phenomenal.

  • Butchered SQL (Score:4, Interesting)

    by Sam36 ( 1065410 ) on Thursday December 03, 2015 @04:57PM (#51052553)
    The issue I quickly realized with mysql when I tried to replace an aging IBM DB2 data warehouse (with a total of 10 billion rows) was that Mysql only supported a small subset of the SQL standard. This quickly lead to almost all of our normal queries for BI not even being able to be ported. After trying to make it work anyway I eventually gave up. I had recently started using postgresql in my home server set up since I didn't know when direction Debian was going to take with the whole mysql/maridb debacle. So I convinced my boss to scrap the mysql idea and try again with Postgres. I was amazed at how well the SQL standard was supported by postgres. It also had a far superior query planner that mopped the floor compared to mysql performance for any query with more than a couple of joins. I also like how postgres isn't owned by any one entity like mysql. It is all I use now.
  • Does PostgreSQL have decent geologically diverse multi-master replication yet? This is honestly the #1 reason why my projects all use MariaDB right now with Galera Cluster. Having the database on multiple servers in each region, it has been a dream when something like hardware failure or a power outage has happened. There is no longer a need or worry about master-slave with fail over and new master election, because all nodes act as masters in the cluster, and nodes can join and drop freely at any time with

    • Unfortunately, no. Postgres can only replicate to other instances operating on igneous rocks (e.g. obsidian and sovite) but the data gets lost if sent to instances hosted on chalk or gneiss. Might be fixed in 9.6, though.

  • by orlanz ( 882574 ) on Thursday December 03, 2015 @05:33PM (#51052833)

    MariaDB and MySQL are basically the same thing. It comes down to licensing and vendor preference. But Postgresql vs MySQL vs Sqlite is just a question of what your use case is.

    Sqlite is for the prototyping, small projects, and small foot print. Its an amazing piece of software and solution for its niche. It is probably the most widely used DB out there. Extremely easy to setup, program against, and test. And very forgiving.

    MySQL is for the small to large size operations. Easiest to setup and manage for the feature set you obtain. It is fast and reliable and has a lot of 3rd party support. Most devs work in this area and I think this is why it is used so much. It is also many folks first "personal" testing DB and thus has a lot of momentum. You can use it at the enterprise level, but not really where it shines. Its like taking a Camry and putting a HEMI in it. It works, but that's all we can really say about it. Use when migrating an existing solution is too costly.

    Postgresql is large to enterprise level projects. I place it between MSSQL and Oracle. Its a wonderful software minus the "Dedicated Vendor Support" toilet paper that PHBs love. Extremely feature rich. But it needs enterprise level care and maintenance processes just like the others. You can use it on small projects, but its really over kill.

    This is the same discussion we been having since 2005. Each system has improved a lot, and their use cases overlap more, but the general logic on which is best to use is still the same.

    • by MrSome ( 2587847 )

      MariaDB and MySQL are basically the same thing.

      All RDBMS's are BASICALLY the same thing... but we're here to split hairs and talk about features that 5% of the population will find useful. :-)

  • PostgreSQL supports transaction DDL statements (e.g. ALTER TABLE, CREATE TABLE). MySQL doesn't. If you run a poorly-written upgrade script against a MySQL database and something goes sideways, your only recourse is to restore from backups. This means that any sane MySQL upgrade plan involves testing the upgrade on a replica of the database first. For large or mission-critical deployments that usually isn't an option.

    If all you're looking for is a cheap DB to serve a Wordpress blog about your hamster, MyS

    • PostgreSQL supports transaction DDL statements (e.g. ALTER TABLE, CREATE TABLE). MySQL doesn't.

      Even SQLite3 supports transactional DDL statements. That MySQL doesn't is a cloud of shame hanging over them.

  • by Art3x ( 973401 ) on Thursday December 03, 2015 @07:10PM (#51053457)

    I recommend either PostgreSQL or SQLite. PostgreSQL is so easy to install and set up, though, that I would recommend SQLite only if you don't control the server.

    It's pronounced Postgres Q L, if you want to say it all the way. But it's okay if you just want to say Postgres. Even the database's default superuser is still called postgres.

  • A former employer uses PostGIS extensively. It's more sophisticated than the support offered in MySQL (which only uses planar geometry) and is pretty much the standard in the GIS world. I'm writing an app which logs ADS-B position reports from planes, and it makes it trivial to detect when aircraft are landing or taking off from an airport - you can query if a point's within a polygon, and depending on speed, rate of ascent/descent and altitude, can come to some conclusions.

Top Ten Things Overheard At The ANSI C Draft Committee Meetings: (8) I'm on the committee and I *still* don't know what the hell #pragma is for.

Working...