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).
Please... (Score:4, Funny)
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)
Postgres is an excavator, while the other two are bobcats. For heavy lifting, the choice is obvious.
Re:Not rocket science (Score:5, Insightful)
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.
Re: (Score:2)
But Mysql / MariaDB still beat PostgreSQL when it comes to replication.
PostgreSQL's replication must really, really suck, then.
Re: (Score:3)
Believe me, I absolutely hate clustering on MySQL/MariaDB, it's just that PostgreSQL's is worse.
Re: (Score:3)
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
Re: (Score:2)
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].
Re:Not rocket science (Score:5, Insightful)
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.
Re:Not rocket science (Score:5, Interesting)
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:Not rocket science (Score:4, Interesting)
It is not true that there are no indices. What this statement means is that Indices do not span tables. If you put an index on the parent, that index ONLY applies to the parent. You can put the same index on the child.
We use inheritance like this: ...
components
\ interfaces - constraint = Interfaces only
\ ports - constraint = Ports only
\ memory - constraint = Memory only
I can select from 'components' and find everything, but I can select from interfaces and not see ports. .. Smaller tables, faster access, and yet a report can select from components to find all types of things.
Re: (Score:3, Informative)
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:
(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)
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.
Re: (Score:2)
Yep, MySQL (and variants) feels clunky and contrived compared to PostgreSQL, to me.
Re: (Score:2, Troll)
PL/pgSQL
Oh great! Another mouthful of random letters....
Re:PL/pgSQL (Score:4, Informative)
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.
Re: (Score:2)
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.
Re: (Score:2)
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.
Re: (Score:2)
"Correct me if I'm wrong, but it seems you're just moving logic into a less accessible place and to a language less developers have extensive experience with."
I'll correct you:
1) It's only processing at the proper level: you process data relationships at the Relationship DataBase Manager
2) Something most application developers seem to forget is that they do NOT own the data, neither does it their applications. Data is a company asset which belongs to the company to access in any means it needs. That impli
Why choose mysql? JetProfiler (Score:3)
MySQL has JetProfiler, Postgresql does not.
Re:Why choose mysql? JetProfiler (Score:4, Informative)
Re:Why choose mysql? JetProfiler (Score:5, Informative)
Not the same. JetProfiler shows you, in realtime and with pretty pictures, what fucked up stuff you're doing to your database. pgFouine is:
"pgFouine is a PostgreSQL log analyzer written in PHP. It is based on PQA, the Practical Query Analyzer written in Ruby. pgFouine aims to be able to parse huge logs and to have a nice and useful HTML output."
Yeah, welcome to 1995.
Re: (Score:2)
WTF cares about jetprofiler? I'll answer that. The people who care about jetprofiler are the exact same people who should be running mysql in the first place. Hell, systemd probably wants jetprofiler too.
For those who have graduated from the kiddie table:
EXPLAIN
I saw a pen in the front office if you need a picture.
Transactional DDL (Score:2, Informative)
PostgreSQL has it. Makes development faster by an order of magnitude with a big database schema.
Comparing to MySQL, you will always lose (Score:4, Interesting)
Re:Comparing to MySQL, you will always lose (Score:4, Insightful)
but because everyone thinks of MySQL as the database that isn't a very good choice for large projects.
That's not true! Some of us think of MySQL as the database that isn't a very good choice for any projects.
Re: (Score:2)
Or often, MySQL is ...
"the database which likely isn't the best choice, but it is the one available on most VPS hosting providers"
Since when was speed a problem for PostgreSQL? (Score:2)
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?
Re:Since when was speed a problem for PostgreSQL? (Score:4, Informative)
I would expect the issue to be MVCC, not FKs. Both Postgres and Firebird do MVCC, which incurs overhead when writing data (never overwrite, always add delta records, then fix pointers so readers can follow the chain, and also cleanup deltas no longer needed by any active transactions) and when reading data (follow pointer chains, verify a given record should be visible to the current transaction despite it being listed in the slightly-larger index), etc.
The switch from myISAM to InnoDB brings MVCC with it (in addition to, as you point out, actual constraints) so the cause/effect may be unclear.
Re: (Score:2)
Early on mysql was limited in the kinds of joins it could do...
Can it do a FULL OUTER JOIN yet?
Re: (Score:2)
Early on mysql was limited in the kinds of joins it could do... Can it do a FULL OUTER JOIN yet?
No, not yet.
While it can do UNION, it can't do INTERSECTION or DIFFERENCE. There's a number of useful things that are left out, but those 3 are at the top of the list for me.
This is going to be a nice discussion (Score:5, Insightful)
Re:This is going to be a nice discussion (Score:5, Funny)
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
Re: (Score:2)
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.
Re: (Score:2)
Nope, the answer is Perl.
Re:This is going to be a nice discussion (Score:5, Funny)
MySQL is so bad, it's the kind of database that could make Donald Trump convert to Islam.
Re: (Score:3)
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, thanks (Score:2)
Re: (Score:3)
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.
Re: (Score:2)
Re: (Score:3)
Right. Because it's totally impossible that you might be building something that you're intending to sell to more than one customer.
Re: (Score:2)
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
Re: (Score:2)
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.
Re: (Score:2)
Hang on a sec, lemme check...
[root@machine-name-redacted postgres]# du -h base | tail -1
32G base
Sorry, you were saying?
Re: (Score:2)
Cross platform and free are two things postgresql offers that sql server doesn't
Re: (Score:2)
Except the 10k licensing fees....
Re: (Score:2)
Storage beyond 10GB, maybe? Or would that be entirely too obvious?
Re: (Score:3, Interesting)
Re: (Score:2)
Re: (Score:2)
YMMV
--Coder
Dice spam (Score:4, Insightful)
ahem (Score:2)
That is all.
Happy PostgreSQL user (Score:5, Interesting)
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.
Re: (Score:2)
Does Postgre have reasonable DB replication? Or is losing a server a crisis?
Exactly which replication? (Score:5, Informative)
It does pretty decent asynchronous master->slave replication.
You can also have a mirror with synchronous writes.
Multi-master replication- there are some 3rd party tools to do that, still pretty young and immature AFAIK.
Clustering- there are some 3rd party projects to do that, some commercial.
More info here: https://wiki.postgresql.org/wi... [postgresql.org]
--Coder
Re: (Score:2)
I have not seen sloan or any other replication based technology ever work reliably with Postgresql. As a result we no longer use it at work
Re: (Score:3)
> their C interface was the stuff of nightmares
I see that you also have traumatic memories of Pro*C
Re:Happy PostgreSQL user (Score:4, Interesting)
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)
Galera Clustering (Score:2)
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
Re: (Score:3)
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.
Different use cases (Score:5, Insightful)
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.
Re: (Score:2)
Transactional DDL (Score:2)
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
Re: (Score:2)
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.
SQLite and PostgreSQL (Score:4, Informative)
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.
Love the GIS Extensions for PostGRES (Score:2)
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.
Re:I won't use a DBMS I cannot pronounce. (Score:4, Insightful)
post-gres-cue-ell.
Now give it a try! :-)
I use it for everything except the stuff where sqlite is more appropriate.
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
WTF is a gree?
something that might eat you in the dark room of a twisty maze?
Re: (Score:2)
Could easily be post-gres-quell as well.
And there's the issue.
Re: (Score:2)
Re:I won't use a DBMS I cannot pronounce. (Score:5, Informative)
Everyone I know just calls it "Postgres".
This was its original name. It was the successor (Get it? "Post"?) to the RDBMS known as Ingres. There was some IP issue associated with the Postgres name when they went to open source it, so they gave it a new name: PostgreSQL. Yes, I agree it was a very poor choice, something only a true nerd would come up with.
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
Re: (Score:2)
Sounds rather similar to OpenOffice vs OpenOffice.org
Re: (Score:2)
I treat the g as silent: post-res-sequel. The web site used to have a button 'click to hear it pronounced' but that is gone and I never used it.
Re: (Score:2)
Everything's better than 'gimp' right?
Re: (Score:2)
Funny names give OSS street-cred. The more it sounds like a bodily function, disease, and/or something from Mork's planet, the better in OSS circles. "Ogg Vorbis" is one of my favorites in that regard.
Re: (Score:2)
I used to run PostgreSQL on a 486/66 linux-based server back in the day. Even then, when running with a PHP application the 256kbit/s link wasn't fast enough to expose any slowness in the database, the web server, or the application platform.
Re: (Score:2, Insightful)
It's all warm and fuzzy until you run into the many MySQL data integrity "quirks" that would cause any other DB to return an error, but MySQL just handily stores the wrong data instead.
Re: (Score:2)
"So don't disable strict mode then! Why configure your db to allow errenous data and then complain that it did exactly that?"
Because that's exactly why MySQL was chosen to start with: because it allows an easier head start for people that don't exactly know what they are doing.
Re: (Score:3, Interesting)
MySQL is easy until you want to do something interesting with it (which you usually do, at some point). Then it becomes a huge pain in the ass and you wish you'd done things right in the first place (i.e. you wish you'd gone with PostgreSQL).
Re: (Score:2)
MySQL is simply easier to use and administer. Postgres has a sharper learning curve. This made MySQL the go-to for shared web hosting (back before you could have a VPS for pocket change) and so it's what everyone ended up using for anything web related.
That's true but it was also at the time much faster because it lacked support for most of the features expected in a modern relational database.like foreign keys, triggers, procedural languages, and complex data types..It was more or less the programmer's job to manage consistency but it ran like greased lightning.. Many new to the web and programming for that fact embraced the simplicity you point out and the perceived advantage in speed because they didn't understand the need for the relative advance feat
Re: (Score:3, Funny)
If you want speed, go for MongoDB. It doesn't use joins & it's webscale, plus it can even use /dev/nul as storage for ultra-high throughput.
Re: (Score:3)
If you want speed, go for MongoDB. It doesn't use joins & it's webscale, plus it can even use /dev/nul as storage for ultra-high throughput.
For those who haven't seen it, the parent is (I think) referring to this very amusing video [youtube.com].
Re: (Score:2)
But does it shard?
Anne McCaffery is that you?
Re: (Score:2)
Another reason I always felt that MySQL had a bigger footprint in the open source RDBMS market was because it was one of the first to have a native Windows version (1998). Postgres didn't get that until about 10 years ago. Of course that's not as big of a deal nowadays....
Re: (Score:3)
MySQL is simply easier to use and administer.
Having graduated from MySQL to PostgreSQL many years ago, that's easily the one thing that MySQL has over PostgreSQL.
I don't do a huge amount of database administration, but it's fair to say that every time I need to (say) modify the access of a role in PostgreSQL, I still have to manually verify that every ALTER command did what I think it did, because at least one of them didn't. Oh, and some of it is done with the data dictionary tables and some is done with pg_hba.conf.
That's not to say PostgreSQL is wo
Re:SQLite (Score:4, Interesting)
SQLite is fine for multiuser-read / singleuser-write. Also for built-in per-instance DBs in applications. Which covers a heck of a lot of use cases, online and not. Something else that's pretty awesome is it is trivial to compile SQLite right into an application. This serves both to make the application less complicated to install, and to ensure that the DB format, behavior and performance won't change when other parts of the host system change. Less opportunity for Apple / Linus / Microsoft / etc. to Break Your Shit(TM)
Within the Python2 environment, where I do a lot of my work, I use a convenient wrapper [fyngyrz.com] for SQLite (and another [fyngyrz.com] for PostgreSQL.)
Both DBs are very useful to me. I looked at MySQL and wasn't convinced there was any benefit to adding it to my toolbox, so... none of that. :)
Re:FireBird... enough said (Score:5, Interesting)
I happen to use and love FB, it's been rock-solid for me for over a decade now, but I've never pushed it for *performance* reasons. It's always been about the features: MVCC that always works (unlike Oracle's, and on-by-default unlike SQLServer's), transactional triggers (came in really handy), triggers that do what you intend (unlike SQLServer's), better temporary-table mechanisms than SQLServer, better stored procedures (selectable like a table-valued-function, but can read & write like a stored procedure). There's no equivalent to PostGIS, though, and there's no built-in replication method beyond shadow databases. And other stuff that a Wikipedia page would be better at explaining.
If you're serious about speed, I'd love to see benchmarks to back you up. If you're trolling, I hope readers will consider Firebird anyway, it really is a good DBMS.
Re: (Score:2)
So tell us why. I'm interested. Others will be too.
Re: (Score:2)
Does it do OpenGIS?
Re: (Score:2)
Open Source:
* Postgres over MySQL and its relatives, although the Percona fork is actually pretty good. Firebird sounds interesting.
Closed-source:
* Sybase over SQL Server over Oracle.
Re: (Score:2)
And we have like 3 expert DBAs maintaining it (who wrote books about Sybase), so it's not just me being useless at databases.
--Coder
Re: (Score:2, Informative)
That's because the PostgreSQL developers actually try to follow the ANSI SQL standards. MySQL and MSSQL? Not so much.
Re: (Score:3)
Re: (Score:2)
Re: (Score:2)