19 ms·

I have been using Postgres since I was a student, but I must recognize that being a better programmer than sysadmin, I always used it after some kind of abstrac

• tragic 13y ago
  A list of reasons not to use MySQL: http://grimoire.ca/mysql/choose-something-else http://grimoire.ca/mysql/choose-something-else
  • matwood 13y ago
    I was just complaining about mysql yesterday. Data truncation and silent failures should not be warnings! They should be errors. For example, 'str1' + 'str2' results in '0' and a warning in the default mysql config. A real rdbms should fail and tell you + is not for string concatenation in that system instead of running your query and perhaps overwriting your data and then warning you about it after the fact ('That query you just ran is broken, but we overwrote your data anyways - hey, here's a warning.'). Broken, horrible design.
    • davidw 13y ago
      One that has bitten my colleagues lately: Auto-increment primary keys get reused when, say, you create a record, then erase it, then pull the power plug. Mysql does not save the highest number used, meaning that you turn on the machine again, add a new record, and it has the same ID as the previous one. My very subjective impression is that every time I have anything to do with Mysql, sooner or later, some weird thing like that jumps out and bites someone in the ass.
      • ericcoleman 13y ago
        This effects InnoDB, but not MyISAM
        • davidw 13y ago
          Ok, but ... MyISAM is like racing down a hill on a bicycle with no brakes.
          • smacktoward 13y ago
            That's actually better than MyISAM, because at least experiencing it is thrilling...
        • matwood 13y ago
          If you are using MyISAM you might as well use a raw file system and assume you will lose your data at some point.
      • laumars 13y ago
        I dislike MySQL as much as the next guy, but I'm not convinced what you've posted there is true because you can query the auto-increment number - in fact you can even define it in SQL when creating the the table. So MySQL does store the increment number (and it's not even some volatile runtime value that might get lost during power cycles as I've bounced enough dev machines to notice if that was the case).
        • davidw 13y ago
          The autoincrement number is stored just fine unless you erase the last record and then stop Mysql, in which case that last one is lost and reused when you restart Mysql. This can cause problems if, for instance, you did something with that record between the time it was created and deleted and used that ID... http://stackoverflow.com/questions/3718229/stop-mysql-reusing-auto-increment-ids http://stackoverflow.com/questions/3718229/stop-mysql-reusin... http://dba.stackexchange.com/questions/16602/prevent-reset-of-auto-increment-id-in-innodb-database-after-server-restart http://dba.stackexchange.com/questions/16602/prevent-reset-o...
    • KingMob 13y ago
      Yup. I encountered MySQL's silent truncation behavior back in 2000, and it blew my mind that a tool whose primary job is to safely store my data would ever delete some of it without my explicit command. Been a Postgres person ever since.
  • sp332 13y ago
    I've never liked using MySQL (silent errors everywhere!), but it always seemed to be the most practical option. It would be really nice if Postgres is catching up in terms of features.
    • davidw 13y ago
      Features? I've always thought of things like 'transactions' as the features I'm most interested in in a database. Granted, Mysql is catching up in terms of features like that, but for a long time, many people were using it without them (MYISAM).
    • stephen_g 13y ago
      For the most part, that happened long ago... What features are you using that aren't in Postgres?
      • sp332 13y ago
        Speed :) On one project we were able to get MySQL onto an embedded system where Postgres kept choking. Also replication is dead-simple with MySQL.
        • Refefer 13y ago
          It's amazing how fast you can go when you don't care about safety ;) That said, for all non trivial work loads, the latest postgres is quite the work horse, but of course requires some tuning for performance. We ended up switch to it after MySQL consistently sucked on smaller joins. Can't argue on the replication deal: it's a work in progress.
        • ufmace 13y ago
          If it's an embedded system, why not use SQLite instead?
          • sp332 13y ago
            Honestly I wasn't part of that conversation on that project. I just heard rumblings from a distance :)
  • davidgerard 13y ago
    The only reason to use MySQL: that you have to run an application heavily optimised for MySQL, where any other DB is an afterthought (e.g. MediaWiki, Wordpress).
    • marcosdumay 13y ago
      On those cases, I normaly have better luck with the aftertought. Use MySQL when your application is incompatible with other databases. 99 times out of 100, that means that the database has inconsistencies, and the application depends on them, so be carefull when doing that.
• stephenson 13y ago
  Why do you want to use Postgres instead? When you answer that you have the list of reasons to show your boss.
• JanezStupar 13y ago
  Tell him MySQL is PHP/MongoDB of relational world.
• neverminder 13y ago
  I'm a prisoner of MySQL. Of the top of my head at least from my own experience I can name these features that are present on Postgres and not on MySQL: PL/Language (stored procedures written in "3rd party" languages), custom data types, Arrays, JSON, dynamic SQL in triggers, better regular expression support, etc.
• gdulli 13y ago
  Reasons I like PostgreSQL compared to MySQL: - Timestamp with time zone - More robust, fewer crashes, less corruption of data - More features (JSON data type, partial indexes, function/expression indexes, window functions, CTEs, hstore, ranges/sequences/sets, materialized views, too many to list) - More disciplined (doesn't do things like auto-truncate input to get it to fit into a column) - Not owned by Oracle, it's actively developed, regular major release schedule, developers/maintainers are talented and trustworthy, etc. - Better Python driver (don't know about other languages) - Choice of languages for database functions/procedures (Python, JS, etc.) - Better partitioning support - Better explain output, explain analyze, buffers - Multiple indexes allowed per table in a query (I hear MySQL has made a little progress here since I last used it)
  • antjanus 13y ago
    Add "built-in fulltext search" and the fact that you've just replaced Redis, Sphinx/Solr/Lucene, and Mongo with a single application. THAT, I think, is the biggest sell, at least to me. The fact that PgSQL does so much and so well. I used Pg for full-text search in the past and the fact that I did not have to bother with setting up Solr or an interface was a wonderful feature. The search index lived right alongside my data.
    • jlogsdon 13y ago
      Technically mysql has "built-in fulltext search" as well, but the implementation leaves something to be desired.
      • antjanus 13y ago
        I couldn't find anything nearly as powerful as Pg so I would discount it fully.
    • mrweasel 13y ago
      How good is the fulltext search? Comparable with Solr, better or does it depend on your needs?
• jakobe 13y ago
  The most amazing Postgres feature: Transactional schema changes. Create tables, alter tables, insert data, all inside a transaction. If any step fails, everything is rolled back! This blew my mind when I switched from MySQL (which doesn't even support transactions for inserts/updates in most engines).
  • davidw 13y ago
    I had my mind blow the other way, in the past, when I realized that Mysql did not do that. I took it for granted.
  • sixbrx 13y ago
    Neither can Oracle do this, FWIW.
• piokuc 13y ago
  One amazing little extension of Postgres I found lately is Multicorn http://multicorn.org/ http://multicorn.org/ It lets you write a Foreign Data Wrapper in Python. One Python class with two methods is enough to e.g. expose files in a proprietary data format as normal Postgres tables. That's what I did in about two hours, the users can now query their data with SQL and access it in a programming language of their choice, as long as the language has a Postgres driver (most have). Really cool.
• jfb 13y ago
  Personally, the cavalier attitude towards data integrity displayed by the MySQL folks circa the last time I used it in anger (~1998?) made Postgres a simple choice for me. The specific problems I had then are undoubtably fixed, but the initial culture of "return possibly bad values, fast!" seems like it is still causing problems. I've had issues with Postgres in the past, but it's trustworthy software developed by a trustworthy team.
• copergi 13y ago
  If you are using activerecord it really doesn't matter. You have a "the database is stupid and shouldn't do anything so we'll write a buggy ad-hoc database in ruby on top of it" layer on top of the database, which pretty much precludes using the database effectively. If you want to actually use the database, here's some reasons postgresql is a better choice than mysql: strict treatment of invalid data mysql views with aggregates DISTINCT GROUP BY HAVING LIMIT UNION or UNION ALL Subquery in the select list are too slow to be useable mysql has no check constraints mysql has no conditional/expression indexes mysql only has broken geospatial support mysql has no full text indexing on innodb tables postgresql has an extensible type system (and booleans!) postgresql has sequences postgresql has table inheritance mysql triggers don't fire on cascaded actions or replication mysql has no window functions mysql can't set default values to be a function (there is a workaround for only dates, and only one per table) postgresql has schemas postgresql has a transactional DDL (you can roll back an alter table) rollbacks in mysql are incredibly slow, and if you cancel one it can corrupt the table mysql limit can't accept variables!? mysql subqueries are limited: can't modify and select from the same table mysql stored procedures can't have default args mysql functions (and thus triggers) can't use prepare/execute, so no dynamic sql mysql functions can't be called recursively mysql triggers can't alter the table they are being called against mysql stored procedures can't be called from dynamic sql (prepare) mysql can't log error producing queries mysql slow query log has a resolution of seconds
  • dijit 13y ago
    >mysql has no full text indexing on innodb tables it has this in recent versions, but it's shockingly bad at matching.
• craigkerstiens 13y ago
  It's a bit old but the PostgreSQL wiki has some good details on the differences - https://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 https://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_M... Further I've written a couple of posts that don't directly compare to MySQL, but many of the points are pretty relevant for MySQL vs. Postgres http://www.craigkerstiens.com/2012/04/30/why-postgres/ http://www.craigkerstiens.com/2012/05/07/why-postgres-part-2/
• steveklabnik 13y ago
  Rails 4 and 4.1 have added a ton of Postgres-specific features to ActiveRecord, too.