13 ms·

Why favor PostgreSQL over MariaDB/MySQL?

227 points by shadykiller 9y ago

threeseed 9y ago
Not sure why this was posted. None of the issues occur in MySQL 8 and if you change the SQL mode to be more strict it doesn't occur in older versions. If you are going to blog about how bad a particular technology is it surely makes sense to understand it first.
- throwaway2016a 9y ago
  The company that wrote it is a Postgres consulting firm so take it with a grain of salt. Kind of like an AngularJS consulting firm that took thousands of hours to become an expert in Angular writing an article about how horrible they determined ReactJS is after 3 hours with it. Edit: With that said, I've been a mySQL user for years (decades?) and it does have some obvious short comings that PostgreSQL does not. I often times consider switching but the thing is that in 90% of my projects the database engine is not relevant.
  - Aeolun 9y ago
    For what it's worth, I have zero hours of experience with ReactJS and I still think it's horrible.
KurtMueller 9y ago
For me, personally, it was because, until very recently (July 2017) PostgreSQL had CTEs and window functions and MySQL did not. I worked on small/medium size apps so performance wasn't an issue and having these features made my life easier as a developer.
cygned 9y ago
We use Postgres over MariaDB/MySQL because of working CHECK constraints, Common Table Expressions, extension support, JSON support, custom types and its great documentation.
- paulryanrogers 9y ago
  Which JSON features were lacking in Maria/MySQL?
  - postgresyes 9y ago
    Binary JSON (JSONB/BSON)
  - philliphaydon 9y ago
    All of them? PostgreSQL has kinda left everyone else in the dust in regards to JSON support.
    - mickeyben 9y ago
      https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html
      - philliphaydon 9y ago
        I'm aware there is Json support in MySQL. But my point is that it's primitive when you compare feature sets of MySQL and PostgreSQL for json.
  - imhoguy 9y ago
    PL/v8
  - valw 9y ago
    Converting JSON collections to/from relations
  - jeltz 9y ago
    It used to be all of them, but now it is indexing which is missing from MySQL.
    - chrisan 9y ago
mrobins 9y ago
Having worked with MySQL for years these are all legitimate issues but, as other users have said, they can be managed via upgrades and settings. Personally, I'm less interested in developer-oriented issues (which are well documented) and much more interested in how people feel they compare in terms of performance and scaling.
- sitharus 9y ago
  Until recently (~5 years I guess, possibly longer now) MySQL beat PostgreSQL in raw read performance and scalability - though you'd have to scale MySQL sooner. PostgreSQL has been better than MySQL for high-write workloads (thanks to MVCC, if you tune autovacuum properly) and for more complex queries as the query planner understands the storage layer. Modern PostgreSQL has a much better story around horizontal scaling thanks to streaming replication and a lot of work from the Citus Data team. Query performance is greatly improved as well.
postgresyes 9y ago
PostgreSQL is better in absolutely every area
jstewartmobile 9y ago
I like the fact that MySQL is so light on features that it forces you to put more of the burden on your application. With richer RDBMSes like Oracle, MSSQL, and Postgres, people tend to throw too many responsibilities on the DB--which is not so good in the one part of the system that depends on global locks. That, and the article is kind of a half-truth when it comes to the MySQL warts. A big one he missed is the utf8_unicode vs utf8mb4_unicode fiasco, which is still a thing to the best of my knowledge...
- sitharus 9y ago
  I'm not sure what you're getting at. Good RDBMSs do not have any global locks, table level at most, and can easily handle thousands of reads and writes per second on modern hardware while maintaining isolation between transactions. Once you approach the limits of an RDBMS you're in really large application territory and very few of us will be working for a Facebook or Google.
  - jstewartmobile 9y ago
    I take it you have never had to work on a legacy Oracle solution with triggers and PL/SQL scattered about everywhere. I'm just saying I like separation of concerns.
    - sitharus 9y ago
      You can make a mess out of any development system, and a knee-jerk reaction to throw out RDBMS guarantees is as sensible as ignoring Javascript because legacy jQuery made the UI really hard to understand and debug. I've worked on some large apps and almost always had separation of concerns - the database is ultimately responsible for data integrity. It will validate foreign keys, data ranges and types, but does not implement write business logic. This takes advantage of the amazing work put in to query planners but avoids most of the magic that triggers cause.
      - jstewartmobile 9y ago
deleted 9y ago
[deleted]
sitharus 9y ago
I've used Postgres for years because it's sane out of the box. I know you can configure MySQL to mitigate these issues and use InnoDB by default, but the mere presence of data-destroying features is a red flag to me. I know I could put the logic in my application, but I like the certainty that comes with knowing what I put in is what I get out.
- chisleu 9y ago
  Yup. I like MariaDB for the syntax and it's very simple to make it more sane. Of course, not ignoring warnings is SOP with mysql.
postgresyes 9y ago
MySQL is the worst database ever. You can't even store UUID/GUIDs with it and have to use either strings which are much slower or binary data which is unreadable...
postgresyes 9y ago
Another point against Mysql is how horrible drivers/providers are. Years ago I reporter a problem to MySQL team that async/await is not properly supported for .NET platform and after waiting for years it was still not supported. They only added dummies for async, but it was not a proper implementation. However, PostgreSQL has an excellent open source provider Npgsql for .NET and it has full async/await support and unlike Mysql provider - I never ever had any problems with it.
tscs37 9y ago
I've long been a fan of PostgreSQL over MySQL. I do recommend to read through the PGSQL manual (thick PDF file but you can work through the important parts in a week), it's a joy to read compared to the MySQL one.
beefsack 9y ago
As time goes on this is become a less and less controversial opinion. There are still some specific use cases which favour competing RDBMS, but the majority of people who choose competitors at the moment is for reasons tangential to functionality and performance.
hartror 9y ago
Is it still true the MySQL cannot do migrations within transactions?
- pmontra 9y ago
  Still true https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html
d_burfoot 9y ago
It's a very very serious downside for me that there are now two MySQL's (three if you count Amazon's Aurora), just like there are multiple Linuxes and two Pythons.
- kronos29296 9y ago
  Isn't the number of pythons four? One for C, one for JVM, one for CLR and one in pure Rpython for the pure JIT code. Double them for python 2 and three. That makes it 8.
  - sambe 9y ago
    There are two (very closely related) languages. In principle implementations are compatible, whereas language version are explicitly incompatible by intention. I think that was clear from the parent.
- graysonk 9y ago
  > just like there are multiple Linuxes Are you talking about distributions or kernels?
aorloff 9y ago
CHECK CONSTRAINTS
kaamos 9y ago
The guy has deliberately changed a default setting (sql_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded. Which is already telling something about PostgreSQL evangelism. Speaking of comparing MySQL and PostgreSQL functionality, I've been spending some time recently on this very topic. Both are great databases with interesting and sometimes unique features. Here are some of the features PostgreSQL is missing as compared to MySQL: - synchronous multi-master cluster (Galera, Group Replication) - distributed in-memory grid with automatic sharding (NDB) - semi-synchronous replication (will be available in PostgreSQL 10) - built-in logical replication (will be available in PostgreSQL 10) - built-in event scheduler - clustered indexes - declarative partitioning (limited functionality will be available in PostgreSQL 10) - optimizer hints - optimizer tracing - efficient MVCC implementation that is unaffected by XID wraparound problems and VACUUM issues (see also Uber's report on moving from PostgreSQL to MySQL) - page-level compression - page-level encryption - page-level incremental backups - user variables - NoSQL client APIs (HandlerSocket, memcached protocol, X protocol) - virtual columns - ability to choose data page size on database creation - ability to specify column order when adding a new column with ALTER TABLE - write-optimized storage engines similar to MyRocks and TokuDB All of that may seem small from a developer's standpoint. Those things, however, are huge for people who design and operate the busiest websites on the internet. Which is probably why MySQL still shines in that field.
- fulafel 9y ago
  > The guy has deliberately changed a default setting (strict_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded. I don't think the comments read like that. Someone pointed out that some MySQL variants have changed the default in a recent version and the author criticised the defaults, so the implication is that he wasn't using a bleeding-edge MySQL installation and got the previous defaults.
  - kaamos 9y ago
    Agree, I might have misread the discussion. However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it. Even when multiple people mentioned sql_mode.
cyberferret 9y ago
SQL is SQL is SQL. I have no problems jumping between different flavours of SQL, however what defines my use of them in my own projects is the toolsets I have for managing and maintaining databases. Over the years, I have built up a collection of tools, drivers and utilities that just makes working with MySQL an absolute breeze across Windows and OS X. There are times that I want to use ODBC to dump a MySQL table or view into Excel/Access for a client, or copy data from a production system to a test system, or set up replication or do some performance reporting, and I like that I have tools set up which can do these things for me at literally the push of a button. Sure I still use the CLI for MySQL a lot of the time, but nowadays, 75% of my time is spent in GUI tools of one sort or another. If someone can give me an equivalent suite of these sorts of tools for Postgres that can let me be productive in short order, then I don't much care if the SELECT statement of one system is several milliseconds faster than the other. I don't deal with such huge volumes or mission criticality. I just want ease of management/maintenance.
- Buttons840 9y ago
  Last I checked MariaDB doesn't support common table expressions which are part of the SQL standard published in 1999, nearly 20 years ago. Has this changed? If SQL is SQL is SQL as you say, then I would say MariaDB doesn't support SQL.
  - meric 9y ago
    Check again https://mariadb.com/kb/en/the-mariadb-library/with/ https://mariadb.com/kb/en/the-mariadb-library/with/.
- kuschku 9y ago
  JetBrains DataGrip (which supports basically every database) can directly export into excel or access, or migrate between databases.
jeffdavis 9y ago
This is really about database semantics. If you think database semantics matter, you'll almost certainly prefer postgres. A lot of people simply don't care about database semantics though. Possible reasons: * They are more interested in a particular application (e.g. wordpress), and use whatever default database it seems to prefer. * They are more interested in a particular framework/library (e.g. django), and use whatever database it seems to prefer. * They don't see any reason a database should have interesting semantics, because it's just a place to stash data until you need it again. * They really care about programming languages, but see SQL as a second-class citizen that looks archaic and is really just an obstacle to work around using cool language features. I believe database semantics do matter. It's hard to make a single argument that will convince everyone, but here are a couple things to consider: * SQL catches errors that may be impractical to catch even with haskell. For instance: you could have had a bug in the application three versions ago that lead to an inconsistent database state (e.g. a duplicated item ID), and you are just finding the bug now. SQL to the rescue: if the unique constraint exists, then you are fine, and the error would have been raised three versions ago before it ever got in the database. * Few languages have anything that comes close to ROLLBACK. Erlang kind of does, in the sense that it's a mostly-functional language and a process crash mostly resets you to a good state. But the guarantees of ROLLBACK are much stronger and clearer. I believe this is actually why erlang never really took off widely -- a PHP script hitting the database is a better version of the same concept: the PHP code can crash, and that will cause a lost connection and a ROLLBACK in the database, getting you back to a known-good state and the other requests just continue working (security and other PHP jokes aside). Rambling a bit, but I believe postgres really is the leader when it comes to database semantics, and I believe that is one of its most important contributions.
- stephenr 9y ago
  I get your point, but the two semantics you pointed out, work in MySQL/compatible engines too.
  - jeffdavis 9y ago
    If you agree with my point then it's not hard to see how postgres is more friendly: You can write a function in python (or a number of other languages) and share code with your application, and use those functions almost anywhere in postgres (including unique indexes). That makes the unique indexes much more powerful, especially if you want to adapt to changes without rewriting all your data. Or, you can use range types and an exclusion constraint to form a non-overlapping constraint that prevents schedule conflicts: https://www.postgresql.org/docs/10/static/rangetypes.html https://www.postgresql.org/docs/10/static/rangetypes.html There are tons of examples and others list them all the time. The problem is that a lot of people read past them because they don't see why semantics matter.
stickfigure 9y ago
This conversation should go something like: PostgreSQL has transactional DDL. MySQL does not. ...and the conversation is finished.
- developer2 9y ago
  This is one of the many reasons I prefer postgres, but it's worth noting that even in postgres, there are some DDL statements that cannot be executed within a transaction. I can't remember the list, but they do exist - it's just a far smaller number compared to MySQL.
  - adrianmsmith 9y ago
    For example adding a value to an enum type: https://www.postgresql.org/docs/9.6/static/sql-altertype.html https://www.postgresql.org/docs/9.6/static/sql-altertype.htm... (This bit me recently, because our database migration framework (correctly) puts transactions around migrations, and PostgreSQL (correctly) fails with an error in the case you try to do an operation in a transaction which can't be done in a transaction. (I say correctly, as opposed to Oracle/MySQL which will silently do commits etc.))
    - emeijer 9y ago
      Actually, as of the next version (10) you can :)
    - pritambaral 9y ago
      I have been in this situation. I then wondered if it was indeed right for my database migration tool to forcibly wrap migrations in transactions and not let me override that behaviour.
rypskar 9y ago
I use PostgreSQL but are not sure it was the right choice. In MySQL it is easy to set which collation to use for a query to get the correct order in the users language when using order by. In PostgreSQL it depends on the locales installed on the OS making it much harder to get a correct order by and requires more work when setting up the servers
- developer2 9y ago
  For me this is the last major "missing piece" for postgres. MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application, where you are presenting data in (for example) English, German and Chinese on the same UI. Yes, MySQL's ordering of languages within unicode may be arbitrary, not necessarily very meaningful, and in some cases even "wrong" - but at least you can do it, and the results are consistent and usable. Postgres is outright unusable in this scenario.
  - Doctor_Fegg 9y ago
    For me, the last major missing piece for Postgres would be a silent, seamless pg_upgrade when you upgrade your OS.
  - bmn__ 9y ago
    > MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application That's not true.
    - developer2 9y ago
      If you know of a solution, please share. As far as I know, it is not possible to do multi-language unicode sorting in postgres. The idea being you have a utf8 column with some rows having English, while others have German, Chinese, Japanese, etc. Or an individual column containing mixed language, such as English and Chinese in a single string. And of course, the real deal - a consistent sort for all unicode points, including emotes and other non-language code points. In postgres you're stuck with a single-language collation such as en_US.utf8 - there's no multi-purpose "utf8" collation.
c_shu 9y ago
Postgres has better license
taneem 9y ago
Postgres supports a sophisticated full text search featureset while mysql does not. Implementing full text search on Postgres is not that difficult and can prove to be a much cheaper alternative to Elastic Search up to a certain scale. This was a major reason for me to recently pick Postgres over any other storage technology.
- threeseed 9y ago
  ElasticSearch is open source and free. And nothing is stopping you running it on a single node like PostgreSQL. It's also better in every aspect as a search engine.
  - arkh 9y ago
    It is also: another server to handle, another view of your data you have to manage, another point of failure and another security entry-point. If you don't need all the functionality of ES you can go a long way with the Postgres fulltext search. Once you scale enough or if search is your core business ditch it and deploy a better solution like Elastic Search.
    - taneem 9y ago
      Exactly. When getting started initially, it can be a huge time savings to get your basic full text experience running in Postgres directly, if that's already your main storage DB. Setting up ES is not at all trivial for the average developer and can waste a lot of precious time too early in a product's lifecycle.
  - riku_iki 9y ago
    Also ElasticSearch updates will not be in the same transactions as your DB data updates, which can create some mess.
- fusiongyro 9y ago
  I have found Solr to be more reliable than ES and faster than Postgres for FTS. It is cool to be able to do it in-database though. Solr is a bit more work to configure than ES though.
gsich 9y ago
Does MySQL have something like NOTIFY and LISTEN? What a great feature.
- merb 9y ago
  depends, it does not work well with replication
mathnode 9y ago
In MariaDB 10.2 and MySQL 5.7 and above, this wouldn't happen. It's FUD. The sql_mode, and mysql/mariadb version is not specified. This seems purposefully contrived; just another developer who throws their toys out of the pram, because they don't read the documentation and just expect software to work for them. If anything the author has done nothing more than demonstrate ignorance, malice, or both. When choosing a database, of any kind, you are choosing where to store precious data! Read the fine manual! MariaDB, MySQL, PostgreSQL, and even the likes of MongoDB offer so many diverse options and storage formats and retrieval options.
- metaops 9y ago
  I agree to you mathnode.
- kuschku 9y ago
  If you set up a database, the defaults should be sensible. Any benchmark should always be done with the defaults, and so should any test. He’s testing the latest MariaDB that is available on his distro, 10.0
concede_pluto 9y ago
There's an alarming list of behavior MySQL got negligently wrong at some point. That code is mostly still present (you can't prevent a client from enabling it!) and I have to assume some of the maintainers who thought it was reasonable are still involved. And not everything on the list has a fix available. Off the top of my head, there are still broken storage engines that can't rollback, not all of them can be disabled, and STRICT_ALL_TABLES won't stop you from writing to them. Some foreign key constraints are supported while others are parsed and then silently ignored.
- sambe 9y ago
  Maintainers often have a perspective that rates backward compatibility higher priority than an individual user would. Even if they have learned from previous mistakes in policy or implementation.
tarsinge 9y ago
Not a technical argument but I have a hard time using MySQL knowing Oracle is behind. Some relevant recent posts: https://news.ycombinator.com/item?id=15166410 https://news.ycombinator.com/item?id=15166410 https://news.ycombinator.com/item?id=15160149 https://news.ycombinator.com/item?id=15160149 Also I think it had it's time, great tool in the early days of web dev but not very professional, sure it was improved in recent years but why put so much energy in it instead of other projects like MariaDB or PostgreSQL before, especially now that it's owned by Oracle? I'm amazed by webdev discussing languages, frameworks and the best stack endlessly and then spawning a MySQL without thinking and looking at alternatives like it was 2004.
- teddyh 9y ago
  Note that Debian uses (and migrates MySQL databases to) MariaDB by default since Debian 9 “stretch”, released in June.
- thehardsphere 9y ago
  Are you sure the webdevs discussing every language and the webdevs spawning MySQL are the same people? I mean, speaking purely about languages, there are lots of webdevs who just love PHP and will never consider an alternative to it, even if you make a persuasive argument that PHP is unsuited to the use case they're writing for. It's the same sort of "like it was 2004" mentality in a slightly different context. I actually wouldn't be surprised if that's what it is, because those are the M and P in the LAMP stack that everyone seems to have started learning at around that time. I don't mean to pick on PHP here; it's just the one I see the most that has this property.
karmakaze 9y ago
TL;DR a couple of examples where pgsql doesn't silently coerse data values. How to choose one would be a worthwhile article. If someone has a reference to such, a link would be appreciated. The comments in the thread are great starters.
nthcolumn 9y ago
There will be any number of reasons to favor PostgreSQL over the DolphinDB and vice versa. I have owned various on linux, aix, windows, sco-unix, vax from pick, rms, isams, 4gls dbs, ingres, informix, sybase, mssql, db2, oracle, solid, postgres, mysql to mongo, influx, and redis. I hate them all. I found mysql very slow, much slower than postgresql on equivalent datasets/queries. I had done evaluations before choosing an open-source db for a particular project. And that really is the crux of the thing - comparing benchmarks is usually meaningless, some don't scale as well, some suit a different kind of application, some need to be distributed, some not, it really is on a case by case basis, what is good in one case is not always the case. Perhaps stating the blindingly obvious for those getting very het up about evangelism...
hdhdhdbdbhdh 9y ago
What are the good use cases for non-strict sql-mode in MySQL?
- thehardsphere 9y ago
  Industrial sabotage?
- floatboth 9y ago
  Running ancient crappy applications that rely on MySQL's bad behavior.
alkonaut 9y ago
How can the "set non-null column to null" MySQL not be fixed? There is a warning, and possibly a new stricter mode that avoids it - but shouldn't this be a critical bug fix in all modes?

227 points by shadykiller 9y ago

Not sure why this was posted. None of the issues occur in MySQL 8 and if you change the SQL mode to be more strict it doesn't occur in older versions. If you are going to blog about how bad a particular technology is it surely makes sense to understand it first.

throwaway2016a 9y ago
The company that wrote it is a Postgres consulting firm so take it with a grain of salt. Kind of like an AngularJS consulting firm that took thousands of hours to become an expert in Angular writing an article about how horrible they determined ReactJS is after 3 hours with it. Edit: With that said, I've been a mySQL user for years (decades?) and it does have some obvious short comings that PostgreSQL does not. I often times consider switching but the thing is that in 90% of my projects the database engine is not relevant.
- Aeolun 9y ago
  For what it's worth, I have zero hours of experience with ReactJS and I still think it's horrible.

KurtMueller 9y ago

For me, personally, it was because, until very recently (July 2017) PostgreSQL had CTEs and window functions and MySQL did not. I worked on small/medium size apps so performance wasn't an issue and having these features made my life easier as a developer.

cygned 9y ago

We use Postgres over MariaDB/MySQL because of working CHECK constraints, Common Table Expressions, extension support, JSON support, custom types and its great documentation.

paulryanrogers 9y ago
Which JSON features were lacking in Maria/MySQL?
- postgresyes 9y ago
  Binary JSON (JSONB/BSON)
- philliphaydon 9y ago
  All of them? PostgreSQL has kinda left everyone else in the dust in regards to JSON support.
  - mickeyben 9y ago
    https://dev.mysql.com/doc/refman/5.7/en/json.html https://dev.mysql.com/doc/refman/5.7/en/json.html
    - philliphaydon 9y ago
      I'm aware there is Json support in MySQL. But my point is that it's primitive when you compare feature sets of MySQL and PostgreSQL for json.
- imhoguy 9y ago
  PL/v8
- valw 9y ago
  Converting JSON collections to/from relations
- jeltz 9y ago
  It used to be all of them, but now it is indexing which is missing from MySQL.
  - chrisan 9y ago

mrobins 9y ago

Having worked with MySQL for years these are all legitimate issues but, as other users have said, they can be managed via upgrades and settings. Personally, I'm less interested in developer-oriented issues (which are well documented) and much more interested in how people feel they compare in terms of performance and scaling.

sitharus 9y ago
Until recently (~5 years I guess, possibly longer now) MySQL beat PostgreSQL in raw read performance and scalability - though you'd have to scale MySQL sooner. PostgreSQL has been better than MySQL for high-write workloads (thanks to MVCC, if you tune autovacuum properly) and for more complex queries as the query planner understands the storage layer. Modern PostgreSQL has a much better story around horizontal scaling thanks to streaming replication and a lot of work from the Citus Data team. Query performance is greatly improved as well.

postgresyes 9y ago

PostgreSQL is better in absolutely every area

jstewartmobile 9y ago

I like the fact that MySQL is so light on features that it forces you to put more of the burden on your application. With richer RDBMSes like Oracle, MSSQL, and Postgres, people tend to throw too many responsibilities on the DB--which is not so good in the one part of the system that depends on global locks. That, and the article is kind of a half-truth when it comes to the MySQL warts. A big one he missed is the utf8_unicode vs utf8mb4_unicode fiasco, which is still a thing to the best of my knowledge...

sitharus 9y ago
I'm not sure what you're getting at. Good RDBMSs do not have any global locks, table level at most, and can easily handle thousands of reads and writes per second on modern hardware while maintaining isolation between transactions. Once you approach the limits of an RDBMS you're in really large application territory and very few of us will be working for a Facebook or Google.
- jstewartmobile 9y ago
  I take it you have never had to work on a legacy Oracle solution with triggers and PL/SQL scattered about everywhere. I'm just saying I like separation of concerns.
  - sitharus 9y ago
    You can make a mess out of any development system, and a knee-jerk reaction to throw out RDBMS guarantees is as sensible as ignoring Javascript because legacy jQuery made the UI really hard to understand and debug. I've worked on some large apps and almost always had separation of concerns - the database is ultimately responsible for data integrity. It will validate foreign keys, data ranges and types, but does not implement write business logic. This takes advantage of the amazing work put in to query planners but avoids most of the magic that triggers cause.
    - jstewartmobile 9y ago

deleted 9y ago

[deleted]

sitharus 9y ago

I've used Postgres for years because it's sane out of the box. I know you can configure MySQL to mitigate these issues and use InnoDB by default, but the mere presence of data-destroying features is a red flag to me. I know I could put the logic in my application, but I like the certainty that comes with knowing what I put in is what I get out.

chisleu 9y ago
Yup. I like MariaDB for the syntax and it's very simple to make it more sane. Of course, not ignoring warnings is SOP with mysql.

postgresyes 9y ago

MySQL is the worst database ever. You can't even store UUID/GUIDs with it and have to use either strings which are much slower or binary data which is unreadable...

postgresyes 9y ago

Another point against Mysql is how horrible drivers/providers are. Years ago I reporter a problem to MySQL team that async/await is not properly supported for .NET platform and after waiting for years it was still not supported. They only added dummies for async, but it was not a proper implementation. However, PostgreSQL has an excellent open source provider Npgsql for .NET and it has full async/await support and unlike Mysql provider - I never ever had any problems with it.

tscs37 9y ago

I've long been a fan of PostgreSQL over MySQL. I do recommend to read through the PGSQL manual (thick PDF file but you can work through the important parts in a week), it's a joy to read compared to the MySQL one.

beefsack 9y ago

As time goes on this is become a less and less controversial opinion. There are still some specific use cases which favour competing RDBMS, but the majority of people who choose competitors at the moment is for reasons tangential to functionality and performance.

hartror 9y ago

Is it still true the MySQL cannot do migrations within transactions?

pmontra 9y ago
Still true https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

d_burfoot 9y ago

It's a very very serious downside for me that there are now two MySQL's (three if you count Amazon's Aurora), just like there are multiple Linuxes and two Pythons.

kronos29296 9y ago
Isn't the number of pythons four? One for C, one for JVM, one for CLR and one in pure Rpython for the pure JIT code. Double them for python 2 and three. That makes it 8.
- sambe 9y ago
  There are two (very closely related) languages. In principle implementations are compatible, whereas language version are explicitly incompatible by intention. I think that was clear from the parent.
graysonk 9y ago
> just like there are multiple Linuxes Are you talking about distributions or kernels?

aorloff 9y ago

CHECK CONSTRAINTS

kaamos 9y ago

The guy has deliberately changed a default setting (sql_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded. Which is already telling something about PostgreSQL evangelism. Speaking of comparing MySQL and PostgreSQL functionality, I've been spending some time recently on this very topic. Both are great databases with interesting and sometimes unique features. Here are some of the features PostgreSQL is missing as compared to MySQL: - synchronous multi-master cluster (Galera, Group Replication) - distributed in-memory grid with automatic sharding (NDB) - semi-synchronous replication (will be available in PostgreSQL 10) - built-in logical replication (will be available in PostgreSQL 10) - built-in event scheduler - clustered indexes - declarative partitioning (limited functionality will be available in PostgreSQL 10) - optimizer hints - optimizer tracing - efficient MVCC implementation that is unaffected by XID wraparound problems and VACUUM issues (see also Uber's report on moving from PostgreSQL to MySQL) - page-level compression - page-level encryption - page-level incremental backups - user variables - NoSQL client APIs (HandlerSocket, memcached protocol, X protocol) - virtual columns - ability to choose data page size on database creation - ability to specify column order when adding a new column with ALTER TABLE - write-optimized storage engines similar to MyRocks and TokuDB All of that may seem small from a developer's standpoint. Those things, however, are huge for people who design and operate the busiest websites on the internet. Which is probably why MySQL still shines in that field.

fulafel 9y ago
> The guy has deliberately changed a default setting (strict_mode) to make MariaDB/MySQL look worse. When someone pointed that out in the comments, he left it unresponded. I don't think the comments read like that. Someone pointed out that some MySQL variants have changed the default in a recent version and the author criticised the defaults, so the implication is that he wasn't using a bleeding-edge MySQL installation and got the previous defaults.
- kaamos 9y ago
  Agree, I might have misread the discussion. However, strict mode is on my default since MySQL 5.7 (released in 2015) and MariaDB 10.2 (which is also the version he was using). So he changed that, but didn't explicitly state it. Even when multiple people mentioned sql_mode.

cyberferret 9y ago

SQL is SQL is SQL. I have no problems jumping between different flavours of SQL, however what defines my use of them in my own projects is the toolsets I have for managing and maintaining databases. Over the years, I have built up a collection of tools, drivers and utilities that just makes working with MySQL an absolute breeze across Windows and OS X. There are times that I want to use ODBC to dump a MySQL table or view into Excel/Access for a client, or copy data from a production system to a test system, or set up replication or do some performance reporting, and I like that I have tools set up which can do these things for me at literally the push of a button. Sure I still use the CLI for MySQL a lot of the time, but nowadays, 75% of my time is spent in GUI tools of one sort or another. If someone can give me an equivalent suite of these sorts of tools for Postgres that can let me be productive in short order, then I don't much care if the SELECT statement of one system is several milliseconds faster than the other. I don't deal with such huge volumes or mission criticality. I just want ease of management/maintenance.

Buttons840 9y ago
Last I checked MariaDB doesn't support common table expressions which are part of the SQL standard published in 1999, nearly 20 years ago. Has this changed? If SQL is SQL is SQL as you say, then I would say MariaDB doesn't support SQL.
- meric 9y ago
  Check again https://mariadb.com/kb/en/the-mariadb-library/with/ https://mariadb.com/kb/en/the-mariadb-library/with/.
kuschku 9y ago
JetBrains DataGrip (which supports basically every database) can directly export into excel or access, or migrate between databases.

jeffdavis 9y ago

This is really about database semantics. If you think database semantics matter, you'll almost certainly prefer postgres. A lot of people simply don't care about database semantics though. Possible reasons: * They are more interested in a particular application (e.g. wordpress), and use whatever default database it seems to prefer. * They are more interested in a particular framework/library (e.g. django), and use whatever database it seems to prefer. * They don't see any reason a database should have interesting semantics, because it's just a place to stash data until you need it again. * They really care about programming languages, but see SQL as a second-class citizen that looks archaic and is really just an obstacle to work around using cool language features. I believe database semantics do matter. It's hard to make a single argument that will convince everyone, but here are a couple things to consider: * SQL catches errors that may be impractical to catch even with haskell. For instance: you could have had a bug in the application three versions ago that lead to an inconsistent database state (e.g. a duplicated item ID), and you are just finding the bug now. SQL to the rescue: if the unique constraint exists, then you are fine, and the error would have been raised three versions ago before it ever got in the database. * Few languages have anything that comes close to ROLLBACK. Erlang kind of does, in the sense that it's a mostly-functional language and a process crash mostly resets you to a good state. But the guarantees of ROLLBACK are much stronger and clearer. I believe this is actually why erlang never really took off widely -- a PHP script hitting the database is a better version of the same concept: the PHP code can crash, and that will cause a lost connection and a ROLLBACK in the database, getting you back to a known-good state and the other requests just continue working (security and other PHP jokes aside). Rambling a bit, but I believe postgres really is the leader when it comes to database semantics, and I believe that is one of its most important contributions.

stephenr 9y ago
I get your point, but the two semantics you pointed out, work in MySQL/compatible engines too.
- jeffdavis 9y ago
  If you agree with my point then it's not hard to see how postgres is more friendly: You can write a function in python (or a number of other languages) and share code with your application, and use those functions almost anywhere in postgres (including unique indexes). That makes the unique indexes much more powerful, especially if you want to adapt to changes without rewriting all your data. Or, you can use range types and an exclusion constraint to form a non-overlapping constraint that prevents schedule conflicts: https://www.postgresql.org/docs/10/static/rangetypes.html https://www.postgresql.org/docs/10/static/rangetypes.html There are tons of examples and others list them all the time. The problem is that a lot of people read past them because they don't see why semantics matter.

stickfigure 9y ago

This conversation should go something like: PostgreSQL has transactional DDL. MySQL does not. ...and the conversation is finished.

developer2 9y ago
This is one of the many reasons I prefer postgres, but it's worth noting that even in postgres, there are some DDL statements that cannot be executed within a transaction. I can't remember the list, but they do exist - it's just a far smaller number compared to MySQL.
- adrianmsmith 9y ago
  For example adding a value to an enum type: https://www.postgresql.org/docs/9.6/static/sql-altertype.html https://www.postgresql.org/docs/9.6/static/sql-altertype.htm... (This bit me recently, because our database migration framework (correctly) puts transactions around migrations, and PostgreSQL (correctly) fails with an error in the case you try to do an operation in a transaction which can't be done in a transaction. (I say correctly, as opposed to Oracle/MySQL which will silently do commits etc.))
  - emeijer 9y ago
    Actually, as of the next version (10) you can :)
  - pritambaral 9y ago
    I have been in this situation. I then wondered if it was indeed right for my database migration tool to forcibly wrap migrations in transactions and not let me override that behaviour.

rypskar 9y ago

I use PostgreSQL but are not sure it was the right choice. In MySQL it is easy to set which collation to use for a query to get the correct order in the users language when using order by. In PostgreSQL it depends on the locales installed on the OS making it much harder to get a correct order by and requires more work when setting up the servers

developer2 9y ago
For me this is the last major "missing piece" for postgres. MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application, where you are presenting data in (for example) English, German and Chinese on the same UI. Yes, MySQL's ordering of languages within unicode may be arbitrary, not necessarily very meaningful, and in some cases even "wrong" - but at least you can do it, and the results are consistent and usable. Postgres is outright unusable in this scenario.
- Doctor_Fegg 9y ago
  For me, the last major missing piece for Postgres would be a silent, seamless pg_upgrade when you upgrade your OS.
- bmn__ 9y ago
  > MySQL's utf8mb4_unicode_ci makes it possible to order results containing a mix of any languages. With postgres you are forced to pick a single language. It's impossible to use postgres for a truly multi-language application That's not true.
  - developer2 9y ago
    If you know of a solution, please share. As far as I know, it is not possible to do multi-language unicode sorting in postgres. The idea being you have a utf8 column with some rows having English, while others have German, Chinese, Japanese, etc. Or an individual column containing mixed language, such as English and Chinese in a single string. And of course, the real deal - a consistent sort for all unicode points, including emotes and other non-language code points. In postgres you're stuck with a single-language collation such as en_US.utf8 - there's no multi-purpose "utf8" collation.

c_shu 9y ago

Postgres has better license

taneem 9y ago

Postgres supports a sophisticated full text search featureset while mysql does not. Implementing full text search on Postgres is not that difficult and can prove to be a much cheaper alternative to Elastic Search up to a certain scale. This was a major reason for me to recently pick Postgres over any other storage technology.

threeseed 9y ago
ElasticSearch is open source and free. And nothing is stopping you running it on a single node like PostgreSQL. It's also better in every aspect as a search engine.
- arkh 9y ago
  It is also: another server to handle, another view of your data you have to manage, another point of failure and another security entry-point. If you don't need all the functionality of ES you can go a long way with the Postgres fulltext search. Once you scale enough or if search is your core business ditch it and deploy a better solution like Elastic Search.
  - taneem 9y ago
    Exactly. When getting started initially, it can be a huge time savings to get your basic full text experience running in Postgres directly, if that's already your main storage DB. Setting up ES is not at all trivial for the average developer and can waste a lot of precious time too early in a product's lifecycle.
- riku_iki 9y ago
  Also ElasticSearch updates will not be in the same transactions as your DB data updates, which can create some mess.
fusiongyro 9y ago
I have found Solr to be more reliable than ES and faster than Postgres for FTS. It is cool to be able to do it in-database though. Solr is a bit more work to configure than ES though.

gsich 9y ago

Does MySQL have something like NOTIFY and LISTEN? What a great feature.

merb 9y ago
depends, it does not work well with replication

mathnode 9y ago

In MariaDB 10.2 and MySQL 5.7 and above, this wouldn't happen. It's FUD. The sql_mode, and mysql/mariadb version is not specified. This seems purposefully contrived; just another developer who throws their toys out of the pram, because they don't read the documentation and just expect software to work for them. If anything the author has done nothing more than demonstrate ignorance, malice, or both. When choosing a database, of any kind, you are choosing where to store precious data! Read the fine manual! MariaDB, MySQL, PostgreSQL, and even the likes of MongoDB offer so many diverse options and storage formats and retrieval options.

metaops 9y ago
I agree to you mathnode.
kuschku 9y ago
If you set up a database, the defaults should be sensible. Any benchmark should always be done with the defaults, and so should any test. He’s testing the latest MariaDB that is available on his distro, 10.0

concede_pluto 9y ago

There's an alarming list of behavior MySQL got negligently wrong at some point. That code is mostly still present (you can't prevent a client from enabling it!) and I have to assume some of the maintainers who thought it was reasonable are still involved. And not everything on the list has a fix available. Off the top of my head, there are still broken storage engines that can't rollback, not all of them can be disabled, and STRICT_ALL_TABLES won't stop you from writing to them. Some foreign key constraints are supported while others are parsed and then silently ignored.

sambe 9y ago
Maintainers often have a perspective that rates backward compatibility higher priority than an individual user would. Even if they have learned from previous mistakes in policy or implementation.

tarsinge 9y ago

Not a technical argument but I have a hard time using MySQL knowing Oracle is behind. Some relevant recent posts: https://news.ycombinator.com/item?id=15166410 https://news.ycombinator.com/item?id=15166410 https://news.ycombinator.com/item?id=15160149 https://news.ycombinator.com/item?id=15160149 Also I think it had it's time, great tool in the early days of web dev but not very professional, sure it was improved in recent years but why put so much energy in it instead of other projects like MariaDB or PostgreSQL before, especially now that it's owned by Oracle? I'm amazed by webdev discussing languages, frameworks and the best stack endlessly and then spawning a MySQL without thinking and looking at alternatives like it was 2004.

teddyh 9y ago
Note that Debian uses (and migrates MySQL databases to) MariaDB by default since Debian 9 “stretch”, released in June.
thehardsphere 9y ago
Are you sure the webdevs discussing every language and the webdevs spawning MySQL are the same people? I mean, speaking purely about languages, there are lots of webdevs who just love PHP and will never consider an alternative to it, even if you make a persuasive argument that PHP is unsuited to the use case they're writing for. It's the same sort of "like it was 2004" mentality in a slightly different context. I actually wouldn't be surprised if that's what it is, because those are the M and P in the LAMP stack that everyone seems to have started learning at around that time. I don't mean to pick on PHP here; it's just the one I see the most that has this property.

karmakaze 9y ago

TL;DR a couple of examples where pgsql doesn't silently coerse data values. How to choose one would be a worthwhile article. If someone has a reference to such, a link would be appreciated. The comments in the thread are great starters.

nthcolumn 9y ago

There will be any number of reasons to favor PostgreSQL over the DolphinDB and vice versa. I have owned various on linux, aix, windows, sco-unix, vax from pick, rms, isams, 4gls dbs, ingres, informix, sybase, mssql, db2, oracle, solid, postgres, mysql to mongo, influx, and redis. I hate them all. I found mysql very slow, much slower than postgresql on equivalent datasets/queries. I had done evaluations before choosing an open-source db for a particular project. And that really is the crux of the thing - comparing benchmarks is usually meaningless, some don't scale as well, some suit a different kind of application, some need to be distributed, some not, it really is on a case by case basis, what is good in one case is not always the case. Perhaps stating the blindingly obvious for those getting very het up about evangelism...

hdhdhdbdbhdh 9y ago

What are the good use cases for non-strict sql-mode in MySQL?

thehardsphere 9y ago
Industrial sabotage?
floatboth 9y ago
Running ancient crappy applications that rely on MySQL's bad behavior.

alkonaut 9y ago

How can the "set non-null column to null" MySQL not be fixed? There is a warning, and possibly a new stricter mode that avoids it - but shouldn't this be a critical bug fix in all modes?