Slashdot Mirror
Why I Choose PostgreSQL Over MySQL/MariaDB
Nerval's Lobster writes For the past ten years, developers and tech pros have made a game of comparing MySQL and PostgreSQL, with the latter seen by many as technically superior. Those who support PostgreSQL argue that its standards support and ACID compliance outweighs MySQL's speed. But MySQL remains popular thanks to its inclusion in every Linux Web hosting package, meaning that a mind-boggling number of Web developers have used it. In a new article, developer David Bolton compares MySQL/MariaDB 5.7.6 (released March 9, 2015) with PostgreSQL 9.4.1 and thinks the latter remains superior on several fronts, including subqueries, JSON support, and better licensing and data integrity: "I think MySQL has done a great job of improving itself to keep relevant, but I have to confess to favoring PostgreSQL."
Best of all worlds. And guess what, in the grand scheme of things, the price is a drop in the bucket compared to salaries.
Who trusts MySQL with important data? No one who knows about PG. Good web frameworks like Django prefer PG, while crap ones like Drupal and other PHPtards prefer MySQL.
It's not Access
Let me quote, from the comments thread at a recent article by same submitter:
Another user, in the same thread, had speculated:
No, sir, you were utterly wrong. It came "Postgresql is better than Mysql".
MS SQL Server is for 3rd world lamers. Macho PHBs demand Oracle!
I use MySQL/MariaDB mostly out of inertia. When I started working on websites, MySQL was typically the default software available. When I went on to set up my own websites and various other projects I continued to use MySQL out of habit, it was what I knew. Today I continue to use MySQL/MariaDB, mostly because I've never needed to do anything that it did not do well.
I have heard about corner cases where other databases perform better are behave in a "more correct" way, but none of those cases has ever been relevant to me, so I continue to happily use MySQL.
And for many tasks, you don't need any of that. Have a look at SQLite3 (also, it's built into Python, which can be handy.)
Worried about stability? You can compile the SQLite3 source code right into your project. That way, your databases always match your shipping product, indefinitely, period.
It's not usable for everything -- only a decent subset of SQL is supported -- but you might be surprised at just how much is there, and working well.
I've fallen off your lawn, and I can't get up.
I come from a Sybase SQL Anywhere shop. It never ceases to amaze me how stuff that can be elegantly expressed in a couple of queries in Watcom-SQL typically takes four times as much code in MySQL's dialect. I love Sybase's support for the ANSI standards, subqueries, Java/.NET/C/PHP/Perl stored procedures when they are the right tool for the job (ever needed to resize raster images in an INSERT trigger coming from some third-party application?), and great drivers. I shouldn't have to spend 10 minutes trying to figure out why MySQL doesn't support the standard casting string concatenation operator by default (||), or why subqueries don't work like they ought, etc.
Having used Postgres, all of the worthwhile MySQL features are there, most of the SQLA features are there, and the pain level is much, much lower in Postgres than MySQL for someone coming from a full-featured commercial RDBMS.
What really sucks is all of the applications that are so coded around MySQLisms that they don't run on ANSI-compliant engines.
Show us on the doll where "open sores" touched your micro-soft and gave you this raging case of bs.
I just need a database to hold crap
If you use MySQL, that's exactly what you'll get.
Then use NoSQL instead since you don't care about the structure of your data or how you might query that data to extract value.
Isn't everyone all NoSQL nowadays or has that faded away?
I'm god, but it's a bit of a drag really...
Don't even bother, you can pretty much identify him on every post lately
One expression I remember seeing on the topic went something like: "I can make it as fast as you want as long as it does not have to actually work". The conversation was about filesystems comparing (the non-complying) async-mode with the safer (but slower) alternatives, that actually stood by the promise of fsync(2).
And another, more modern idea (only about 10 years old) quote is "Object/Relational Mapping is the Vietnam of Computer Science". Which, for the purposes of TFA, may be interpreted as something like "who cares for ACID compliance — we can deal with occasional data-corruption and inconsistencies — just make it fast in the usual case".
I rather doubt, we'll settle the question in this discussion...
In Soviet Washington the swamp drains you.
I'm not quite sure about that but I though that not even MySQL's InnoDB allows you to alter your DB schema "under full throttle", whereas databases like PostgreSQL or Firebird shouldn't really have problems with that - transactions up to n see the old schema, transactions from n+1 onwards see the new one, and the RDBMS insulates you from all the nasty bookkeeping. I'm mentioning this as a post scriptum to the "Table Changes Without Locking" feature which is really 1980s stuff. (True, for Firebird, even table structure altering without locking is 1980s stuff, but that would be a low blow for MySQL. ;-))
Ezekiel 23:20
The MOST important reason for using Postgres is that it has object ids (OIDs). This allows true referential integrity. You can have a row point at another row and this reference stays the same REGARDLESS of whether you change the primary key of the referenced row. This allows true object orientation.
What about Firebird? It's a fully featured RDBMS with a fair bit of performance.
I dare say it beats MySQL on almost every front. Mostly popular on Windows with the Delphi crowd.
1 nanosecond adds up when you have millions/billions of records.
If something is so important that you feel the need to post it on the internet... It probably isn't that important.
I love Postgresql... However I agree pgAdmin, isn't that great. I would love an alternative.
If something is so important that you feel the need to post it on the internet... It probably isn't that important.
And I'm probably going to step on a lot of toes here, but people like me strongly prefer Postgres to MySQL. And by "people like me" I mean folks for whom their first real rdbms experience was theoretical or "commercial". I did both.
I used ingres in college to a small extent and then the Ingres commercial product for years after that. I have also used Sybase and Oracle professionally. PostgreSQL easily walks among the giants of that industry.
Every time this discussion comes up the MySQL side has to say "yeah, but..." about a thousand times. MySQL doesn't do ______ properly? "Yeah, but if you just install this other piece of software and change a couple of config files it *can* do it.' Well, con-fucking-gratulations!
The point is that PostgreSQL does exactly what it should do out of the box. I don't have to change a configuration file to make it ACID compliant, fast, correct, whatever. It just works and works correctly out of the box.
Every time someone tells me how easy MySQL is to set up they've betrayed their experience level in this realm.
I know a lot of you are going to mod me down - I don't care. But why not reply instead?
Do you have ESP?
They probably inserted the story in MySQL at the same time they inserted the reference to the parent company. There was a race condition and the reference to Dice was dropped. Shame they didn't use Postgresql, the objectIds representing the slashdot/dice relationship would never change and it would be never be lost in the name of speed.
Yeah but they still got you to click the inane Dice.com clickbait.
We have a lot of XML publishing workflows. MySQL provides a -X commandline option which returns the results of a query in XML. I don't know if PostgreSQL, MSSQL, or Oracle have the equivalent (perhaps someone who knows can post). Right now, it's a pain-free way to get what we need in the form we want, with zero additional effort. If it exists in other rDBMSs, that makes our choices wider.
Chances are, you don't know anything about databases. JetProfiler will show you the crappy queries you're using in an easy-to-understand way so you can fix your stuff and make everything faster.
AFAIK, no such tool exists for Postgresql.
As a bonus you don't have to deal with the annoying psql/pgsql crap, which for some reason drives me bonkers. I mean come on, make it psql or pgsql, not both. WTF?
Does anyone seriously think that mere millions is remotely impressive anymore?
Billions isn't even all that impressive. Some of us were dealing with databases like that 15 years ago.
That tragic "stuff that slows you down" is also the stuff that saves your ass when things inevitably go wrong. Ditch that stuff and you are just gambling with your future.
A Pirate and a Puritan look the same on a balance sheet.
In the beginning, Postgress set out with correctness as the primary goal. Whatever it did, it had to do it correctly. It started life on the slow and resource hungry side. MySQL set out to be fast and more or less correct in the common case. Back in the '90s that made a lot of sense for small servers.
In the decades since, servers have gotten bigger and Postgress got fast and efficient while still being correct. Why would I want to incur a performance penalty in the surrounding software to check behind the database to make sure it didn't just scrag my data?
no use cases in which we all go into armchair mode and have endless debates already hashed out a million times over.
Yes, this is slashdot.
It's already on the bottom of every web page.
>> Slashdot is a Dice Holdings, Inc. service.
Anyone who's been here more than two weeks already knows that most of "Nerval's Lobster" submissions come in the form of "compare similar terms X and Y often found on resumes"
Come on - who really cares about MIT versus GPL licensing in this context? And by "who" I mean "people who manage and use databases as part of their paid job"?
And why does anyone care what someone writing for Dice says on this topic? I read the article, and it doesn't sound like the author has even used any of the features he's decided favor postgres.
What's next - a Dice article on emacs vs. vi?
#DeleteChrome
I haven't -- I've seen cases where the filesystem corrupted itself (with or without the help of hardware failure, no idea), but I've used SQLite most extensively (seriously, I bet I'm close to having used every feature in the thing) in its various revisions for years, as have tens of thousands of my users consequent to my incorporation of same, and I've heard of, and experienced, exactly zero events of DB failure (and one of my most popular apps would have crapped itself and gone blind if such happened, so I'd surely have heard about it.)
Also, different app (SdrDx), but I use it the other way around here; I've got an SQLite DB on my website that tracks startups of the code as to version, revision, step, beta status, platform (windows, OSX), time, date and IP, as part of the handshake that lets the users know if there are any upgrades (in-app title bar notification, nothing invasive.) This lets me keep track, somewhat, how many people are using what version of the app. SdrDx is well over 15 thousand regular users, many more that aren't regular, and the DB is moderately busy as a result, again, no problems. The writes to this particular DB are pretty straightforward, but the queries I've written for it span a decent range of futzing about. No problems to date. Of course it's backed up, but no need for a backup as yet.
I'm also presently using it, again extensively, in an incomplete, but complete enough for my day to day use, DSLR application of mine, code compiled in, along the lines of Lightroom and Aperture, and again, zero DB failures of any kind. Oodles of DB activity for every image, every library op, every bit of auto-categorizing, library backup, tagging, annotating, posted-to recording, flagging, versioning, plug-in and plug-in settings recording, and so on.
So I'm going to go with... possible, but even slightly likely. Also, any DB can take a crap if the drive underneath it goes bad, or the OS (or some all too clever use of root privs) allows corruption of its storage. None of which should be construed as any kind of criticism of any DB engine subject to the same.
Also, did you report these purported corruption events to the author of SQLite? Sure hope you did. :/
I've fallen off your lawn, and I can't get up.
If you don't care about your data, and don't mind it getting corrupted every few years, put it in mysql or a fork of it. I've seen it time and again over the last 15 years clobber data at various employers. Developers use that hobbyist grade toy because they don't know any better, it's what they played with on their pc so they use it at work.
As long as a database engine has stored procedures and a decent client binding library, I can make it go. I've worked with MySQL, SAP/Sybase ASE, DB/2 LUW, PostgreSQL, Oracle, and SQL Server extensively over the years. There comes a point where you just know enough about the quircks and foibles of each of the databases to get around their particular issues and "just make it go."
People who bitch about the minor syntactic differences between the vendors clearly haven't really ported an application, because the differences in behaviour go far beyond syntactic sugar. Despite the ANSI standards, you can't just install the schema on a competitor's database and expect it to run an application properly without a lot of rework and restructuring.
Sure your basic table structures may remain compatible, but that's about it.
Every vendor has at least a few features that encourage "lock-in" by being incompatible with all their competitor's products.
I do have a rule about which databases I work with, though: if it doesn't have stored procedures, I won't use it. The performance benefits of complex stored procedures vs. logic in the client is just too dramatic to ignore and gloss over. Not to mention the fact that coding the logic in the client application is extremely verbose compared to any stored procedure syntax I've ever encountered.
I do not fail; I succeed at finding out what does not work.
http://www.brightball.com/postgresql/why-should-you-learn-postgresql
Nervals Lobster's last 15 posts have been links to news.dice.com, the parent company of Slashdot. They're nothing but ads.
[Add obligatory Slashdot's-going-to-hell-in-a-handbasket slam here.]
AFAIK, the most popular RDBMS by installed base is still SQLite which the authors released into the public domain many years ago. It won't keep up with oracle's performance on very large data sets but it's a hell of lot less complex to set up, and as you say most business/consumer applications simply don't need the performance (and price tag) of something like Oracle or MSSQL.
And did you exchange a walk on part in the war for a lead role in a cage? - Pink Floyd.
then write one
putting the 'B' in LGBTQ+
Microsoft SQL:
- select top 100 * from table instead of select * from table limit 100
- White space after values is ignored ('Bob' = 'Bob ')
- Command-line client sucks
Oracle
- A column of type date is actually timestamp. There is no column type that stores just a date.
- Command-line client sucks
- expensive
MySQL
- You can quote strings with single ticks, double quotes, or backticks
- The MyISAM engine
- Query cache based on the text of the select statement, rather than its meaning. So slightly rewording your query will skip the cache. Also updating a single row will clear the cache. This is inferior to how I understand PostgreSQL's shared buffer cache, which keeps frequently read rows in cache, only flushing out the ones that are updated, and deciding whether to use the cache after the query is parsed, and so not dependent on the query being literally written the same way.
It's no wonder so few web developers fully exploit the powers of the database, reimplementing many of its features in PHP, poorly. I once went to a local PHP meeting. The leader gave a talk, mainly about object-oriented programming, which I never got into. Anyway, he also recommended some kind of job queue application, like to email new users a welcome message. Don't use your database for that, he said, because keeping track of who you've emailed in the users table would upset MySQL's delicate query cache. At the end of the talk, I asked the group of 20 or 30 who had used PostgreSQL. Nobody.
Like others have said, most web developers probably should use SQLite. It's great not only as an embedded database but also the backend for most of the little web apps out there. Or if you're writing business applications for a large company, use PostgreSQL. The rest can go to the dumpster.
And if you had read the manual and turned on the option to make it do those checks ( sql_mode='TRADITIONAL' ) It would have objected.
Blaming mysql because you can't RTFM makes you look like a beginner. If you had actually used a large range of databases professionally you would know that apart from CRUD operations every single database has its own quirks and there is no meaningful standard. Seriously, go read something like the SQL cookbook the variations are all over the place. I've also seen both PG and Mysql do some seriously dumb things with joins.
One of the things real engineers do is understand the tradeoffs between different options and choose the one that suits the particular application and environment at hand. Postgresql's unstoppable OCD has always made it a good choice for accounting type systems while mysql has always been good for blog style sites where the data is very dirty. PG 9.3 and 9.4 have finally got reasonable replication but prior to that it wasn't always the best choice if you needed replication.
That article was just click bait with large parts either wrong or irrelevant.
200 comments and nobody has asked whether it's webscale or not. This place is going to the dogs already.
Confucius say, "Find worm in apple - bad. Find half a worm - worse."
I use Drupal 7 on Postgres 9.3.4 and it works like a charm, never crashed in 1.5 year.
I did have to convince the hosting provider to upgrade Postgres to a higher version, because we were the only ones not on MySQL, but after pointing out that they declared on their own site to being technically advanced etc they did this.
It's funny, but I find Postgres much easier and quicker to setup than MySQL. Probably just familiarity. I will say that in 20 years of using Postgres I've never had it eat any data. MySQL, well, I'd had it destroy its data a couple of time.
I will say that Postgres's command-line tool "psql" is about a million times better than Oracle's "sqlplus". It's been a while since I used MySQL's command-line tool, but I remember not liking it as much as psql.
They didn't provide a fact, they expressed an opinion. It's an irrelevant opinion given that other database servers also have had vulnerabilities.
All I want is a secure system where it's easy to do anything I want. Is that too much to ask ~~ Randall Munroe
I've used MySQL for almost 20 years for different projects of mine. In my professional life, I've also used ADABAS, Oracle and this and that other.
I was interested in Postgres some years ago but never went beyond reading one book. Then two years ago I decided to start a new project with Postgres from the start, because I wanted PostGIS.
I'm not looking back. Every future project I do will always use Postgres. Aside from the technical and functional and other rational arguments, the feeling you get is like graduating from BASIC to a real programming language.
Assorted stuff I do sometimes: Lemuria.org
One thing very relevant for discussion, that I didn't see mentioned here, is how connections are handled across the different RDBMS. PG still uses one process per connection. This can make its memory utilization grow substantially under high connection counts. Oracle and Mysql don't suffer from this issue.
The biggest problem I find with the Express edition is backups. You cannot use a regular file-based backup system to backup such databases due to locks, data pointers, etc. The paid edition has scheduled backup dumps built in.
There are tricks, such as scripts to deactivate the DB, copy the files, and re-activate it, but that's too risky and clunky.
Does anybody know a better way to get backups from E?
Table-ized A.I.
Yes, PowerShell scripts to trigger a copy-only backup - no issues with locking etc, no need to take the db off line or anything of that ilk.
The pgAdmin developers agree, and have already started on a full rewrite in Python. The goal of having a GUI app that's cross-platform used to take tools like C++ and wxWidgets, and that's what the current pgAdmin III is written in. The new one doesn't need to be so low-level in its library use.
Stored Procedures are big for me, and MySQL is my absolute favorite. It's also really simple to create MySQL UDFs (user defined functions). I wrote and currently maintain MVProc (on SourceForge), which uses every feature MySQL offers in stored procs. I have wanted since the beginning to implement MVProc for other databases, especially PostgreSQL, but it's so feature poor I can't make it work without creating an entirely different product. Just my 2 cents.
Retired from software... maybe. Sort of.
I've seen multiple sources that say NOT to backup while the database is on-line. Maybe one can get lucky and nothing bad happens regarding pointer references or locks most of the time after hours, but is that good enough? If you want to gamble that much, use MS-Access; it's nimbler to set up and change.
Table-ized A.I.
...so essentially, the author of this article sat back and tried to comprehend why anyone else would use a database that he would never use, put himself in their position, fap, and blog about it, only to conclude that he still loves the one he uses commonly, to begin with. I think the influx of reddit migrants has sent slashdot back to n00b flamebait central.
the only permanence in existence, is the impermanence of existence.
When ExtJS changed their license to GPL3, not LGPL, as you would expect for a library.
The owner of Sencha then put out a statement that if you built something that made use of ExtJS, then you had to release your software under GPL3 ... including the server components.
I have no problem with releasing the client side -- that's all javascript that people could view the source and see ... but releasing the server side? That requires security audits and a review by legal ... it's just not going to happen.
Reading the review, the reviewer seemed to have the same take on what GPL meant from the statement :
Now, if the issue is simply the *client* code, then you could get around it by using ODBC, or something like Perl's DBD::mysqlPP, which doesn't use the MySQL client code. Do you have to release the whole application if it's just something that makes use of a mysql database? I don't know, but with all other things being equal, and more and more people coming to this conclusion, I'd rather just stick with something that's LGPL or MIT.
Build it, and they will come^Hplain.
If you have a team of unemployed DBA plodders that you need to provide work for, and an employer that needs to get rid of piles of money fast as a tax dodge, use Oracle, ...
Where are we going and why are we in a handbasket?
Copy-only is fine for online databases, you will never have an issue with locks etc as its specifically designed to cope with them.
Also the action of copy-only is done by the database engine, so it knows what it has to deal with.
Incase you are misunderstanding, copy-only is an option you provide to SQL Server when copying the database via SQL commands and the database engine, it has nothing to do with dealing with the actual data files the live database is using.
I would never advocate backing up the actual mdf and ldf files, always use the systems the database engine provides because that knows better than you how to handle various situations and will flush stuff to disk as needed.
I would if I had a team. Now what features do you want?
Django *rules* as long as it's chained.
From a developers standpoint either are good... Pros and cons for each.
BUT
Postgres does not do Master Master streaming replication, which means hitless upgrades, VIP fail over and fail backs at ease just are not possible.
Uhuh
The dangers of excessive individualism are nothing compared to the oppressiveness of excessive collectivism
A few months back, we had completed initial development on a new persistence layer on a demanding application. We'd put it all into PostgreSQL, and were enjoying the easy JSON and other features. It worked great.
So we got it up and running on high-end hardware in our five data centers, then we turned on the pipes for all the writes. But our systems team members were going insane, trying to get High Availability working right. It turns out that there is just no good way to accomplish this in PostgreSQL. It could fail over to the slave if the master stopped responding, but fail-back was basically impossible. It had to do an rsync on the file system level, which was expected to fail. When it failed, the docs said, just do it again. It took almost a full day to run, each time.
And it failed with alarming regularity! When under load, every couple of days the database would just freeze for ten to fifteen minutes, choking on some non-scary query. It would just sit there, stuck. Calls to it would just block, and eventually timeout. When this happened, it would fail over to the slave, and we're days away from getting back to a sane state.
Don't think we didn't do our best to solve this issue. We spent many thousands of dollars on two different highly recommended consulting companies, who specialized in PostgreSQL. They came onsite and looked at everything, and recommended a number of configuration adjustments, but nothing helped.
In desperation, the project now seriously behind schedule, we worked over Christmas, and branched the code to use MySQL as the database, instead of PostgreSQL. Then we set up two parallel systems. Both on identical high-end hardware ($50,000 machines), one for each database, and turned on all the pipes.
The result? MySQL answered its queries in 50% less time than PostgreSQL. Plus we already knew that it did HA quite well, and it never just froze up like PostgreSQL would.
We have since completely obliterated all traces of PostgreSQL from our code base.
--- wad
The Express edition does not have those features. It's why it's free.
Table-ized A.I.
That's funny, I use copy-only backups all the time on SQL Server Express.
Stop talking out of your arse.
Is your rudeness necessary? I helps nothing.
Well, it hasn't worked for us, and I've found others complaining about it on the Internet also. There are work-arounds, but they are hokey and assume certain permission access levels.
Note that I am talking about automatic periodic backups, not one-time manual runs.
Table-ized A.I.
Is your rudeness necessary? It solves nothing.
It hasn't worked for us, and others have reported similar on forums. There are round-about work-arounds, but they have a lot of layers and dependencies. Not worth risk to save a buck.
Note I am talking about automatic periodic backups, not one-time manual backups.
(My apologies if this message shows up twice. The first didn't appear for some reason after submitting. I can't rule out Mondayitus.)
Table-ized A.I.