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

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).

Comments

Please...

[The-Ixian]

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

Re: Please...

[IBME]

Actually it is "Thereâ(TM)s less hassle with licensing, custom data types, table inheritance, a rules systems, and database events." Except for the average guy who just needs a db, ALTER SYSTEM to provide optimizations is way beyond my needs.

Re: Please...

[Anonymous Coward]

homer sez doh!

... FTFY.

Not rocket science

[Anonymous Coward]

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

Re:Not rocket science

[gmack]

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:Not rocket science

[Rei]

Postgres also has a lot of nice features. For example, I love table inheritance 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

[jellomizer]

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

[Anonymous Coward]

http://dev.mysql.com/doc/refman/5.7/en/replication-features.html

Re:Not rocket science

[Anonymous Coward]

From the GP link to the PostgreSQL documentation, you find this:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint.

This really kills it for me. No indexes allowed? Ugh.

Re:Not rocket science

[DougReed]

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:Not rocket science

[jdavidb]

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

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

Re:Not rocket science

[Penguinisto]

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 a separate product that I think you have to purchase.*

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

Re:Not rocket science

[Zontar The Mindless]

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.*

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

Doubt no more. Here ya go.

Re:Not rocket science

[Anonymous Coward]

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 a separate product

Try Percona, it works great for master-master clustering of MySQL. It's open source too.

This is the main reason why we use MySQL at work: Postgres is technically better but there are no decent clustering solutions for it. There are some 3rd party master-master replication solutions for Postgres, but they only replicate data, and are not really up to date.

Re:Not rocket science

[Qzukk]

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

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

PREPARE getuser AS SELECT * FROM user WHERE level = ANY ($1);
 
EXECUTE getuser ( '{administrator,manager}' );

(This works way better from a program than from the psql console - only problem is that if you're doing arbitrary strings, the escaping challenge moves from apostrophes to commas, but messing up a comma doesn't let a malicious user drop a table)

Second most favorite is DISTINCT ON () which makes an awesome poor man's window function for a very specific case. You want to see every customer's most recent invoice?

SELECT DISTINCT ON (customerid) * FROM invoice ORDER BY customerid, date DESC;

Which is about half as much work to write as

SELECT subq.* FROM (SELECT *, rank() OVER (PARTITION BY customerid ORDER BY date DESC) AS rank FROM invoice) subq WHERE subq.rank = 1;

It might even be half as much work for the database to process since there's no subquery, but I've never benchmarked it to see.

Re:Not rocket science

[gmack]

Compared to PostgreSQL that is just getting it now?

Most PostgreSQL solutions are addons and do the replication poorly and you can tell the PostgreSQL people get that given the number of abandoned and experimental projects. For the last project I used an HA cluster on(6 months ago), I tried several of the PostgreSQL options including the new experimental one (whose documentation was out of date) before giving up and going with MariaDB + Galera which is annoying to use since you have to prime the cluster if you restart all of it starting with the cluster item that has the most recent update (you try each one until it doesn't error). Starting the rest, and then restarting the original without the init command. I *hated* it, but at least it mostly works.

I'm hoping PostgreSQL has caught up by the time I ever need to do it again. The new idea they have looked promising, but the command interface was still changing too often to make it useful for me.

Re:Not rocket science

[gmack]

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

Re:Not rocket science

[guruevi]

PostgreSQL does (AFAIK) not have any native replication. It requires closed-source, commercial (expensive) plugins to do a decent job at replication, most of the open source attempts have so far (I looked a few years ago) failed or only done partial implements (statement or trigger based only, no multi-master, no failover, no load balancing).

From what I've read real replication which MariaDB/MSSQL/Oracle has won't work in the foreseeable future due to specific implementation issues within pgSQL and philosophical issues against it within the core development teams.

Re:Not rocket science

[guruevi]

I've done master-master replication since 5.0 with the free versions (before they were taken over by Oracle) and it has been documented since, not sure about before that period but I'm fairly sure it was possible to do in the late 4 versions as well.

Re:Not rocket science

[jafo]

I *LOVE* Postgres, and have since the '90s. But I've never set up replication on it except using DRBD. The replication story seems ... experimental? Every time I've looked at it, I couldn't even find which replication project to use (Slony seems to be the longest lasting, in retrospect, but I want to say there were always a half dozen of them around). I've been hoping that the newer Postgres versions with some built in replication that it would get better faster, but it seems like the replication is basically what we had in MySQL a decade ago, which disappoints me.

I've used MySQL, as I mentioned, and the replication was not bad. There was always one clear choice to do it, and it worked. Sure, master/master felt like a horrible hack (using even/odd autoincrement, for example), and sure sometimes I had to re-replicate the slave because somehow the slave would get wedged up on a record... But setting it up was fairly straightforward, and it has been around *FOREVER* and there is a clear choice. And it was really useful for a lot of cases, we set up a lot of clusters just for backups, so we could do the "mysqldump" on a slave so the locks wouldn't stall the master.

Recently, I wanted to convert a mail server that was using Postgres for sender address rewriting database, but never managed to get clustered, into something that was active/active replicated. This was our only system that we had no way continue service if the master went down. I ended up trying everything, starting with Postgres clustering, looking at I think Mongo, Cassandra, and eventually stumbled across something new: galera under either Percona or Maria (I forget, probably Percona). I had the cluster set up quickly, it is 3 way master, and seems to work great. Except if all 3 nodes go down, you need to take the stick to get it started, as mentioned above.

Usually, I will default to Postgres. But, I respect MySQL.

Re: Not rocket science

[Anonymous Coward]

You clearly don't know what you are talking about, PostgreSQL replication supports multi-master topology clusters using BDR (BI-directional replication).

Re:Not rocket science

[Basje]

https://mariadb.com/kb/en/mari...

Re: Not rocket science

[MrSome]

Is it still in BETA? If so, then I don't consider it ready for my production environment.

Re:Not rocket science

[Anonymous Coward]

Postgres is an excavator, while the other two are bobcats.

Obligatory xkcd : http://xkcd.com/325/

Re: Not rocket science

[gmack]

You clearly don't know what you are talking about, PostgreSQL replication supports multi-master topology clusters using BDR (BI-directional replication).

I really wanted BDR to work for the last project I needed a cluster for. The problem was that the the documentation for BDR hadn't been updated to the new changes they made and since the whole project was still in beta, there was no guarantee the some change they made would not break my setup.

Re: Not rocket science

[JoshuaPK]

It's not in BETA. It is a product that is fully supported (but still BSD licensed).

Re:Not rocket science

[JoshuaPK]

It has native "hot standby" replication- you can have one "write" database sever and many "read" servers, as the writes will replicate from the master to the slaves. As of version 9.5, a lot of the stuff will be baked in to the official Postgres to allow for multi-master replication. I reckon 9.6 will have it all the way there. For now, you can download Postgres BDR, which is a supported version of Postgres with multi-master replication.

Re:Not rocket science

[Anonymous Coward]

Replication is much more powerful in 9.4 and 9.5 now.

Re:Not rocket science

[Anonymous Coward]

Utter bullshit.

Re:Not rocket science

[MagicMerlin]

Postgres HS/SR *is* the master slave replication feature. It's better than anything mysql offers as long as that you are ok with the main caveat: it replicates the entire database cluster and the slaves must be the same version. The only separate tooling you'd need would be to do things like manage failover if the built in tools to manage that aren't working for you. Logical replication handles all other cases. BDR (http://bdr-project.org/docs/stable/index.html) lays on top of that to provide clustering. It's not quite there yet; most but not all of the necessary foundation have been moved to the core product which can make installation and setup a bear. But the underlying infrastructure is really well designed and will ultimately compete with commercial solutions both in terms of power and ease of use.

Re:Not rocket science

[Anonymous Coward]

Incorrect. PG has native replication. It can do so by shipping the write-ahead logs or by streaming. Does cascading replication too.

I won't use a DBMS I cannot pronounce.

[QuietLagoon]

I mean, really. That name looks like a random collection of letters. :)

Re:I won't use a DBMS I cannot pronounce.

[Anonymous Coward]

As silly as this sounds, this really does hold PostgreSQL back! I can't even ask someone if they've used it without trying to pronounce it 3 ways to see which way they might recognize it. Most people who decide on MySQL do so having never heard of PostgreSQL.

Re:I won't use a DBMS I cannot pronounce.

[flacco]

post-gres-cue-ell.

Now give it a try! :-)

I use it for everything except the stuff where sqlite is more appropriate.

Re:I won't use a DBMS I cannot pronounce.

[freeze128]

I always read it as "post gree see quell", which is the problem. Everyone knows what post is or means, but what the fuck is a gree?

Re:I won't use a DBMS I cannot pronounce.

[MachineShedFred]

Could easily be post-gres-quell as well.

And there's the issue.

Re:I won't use a DBMS I cannot pronounce.

[Anonymous Coward]

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:I won't use a DBMS I cannot pronounce.

[RavenLrD20k]

I've even heard it pronounced post-gres-kul.

Re:I won't use a DBMS I cannot pronounce.

[bytesex]

Everything's better than 'gimp' right?

Re:I won't use a DBMS I cannot pronounce.

[freeze128]

Damn, programmers are just way too clever for their own good.

Re:I won't use a DBMS I cannot pronounce.

[wardrich86]

It's like a Gru, but less hungry.

Re:I won't use a DBMS I cannot pronounce.

[PRMan]

I've always heard it as "Post gress see quill"

Re:I won't use a DBMS I cannot pronounce.

[Tablizer]

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:I won't use a DBMS I cannot pronounce.

[CrashNBrn]

They should of made querying the DB with regex a first-class citizen, and called it PostGrep.

Re:I won't use a DBMS I cannot pronounce.

[fredrated]

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:I won't use a DBMS I cannot pronounce.

[erapert]

Throwing my hat into the ring:
Postgresql: post-grey-ess-queue-ell
MySQL: my-ess-queue-ell

It's not a movie or a book and thus it's not "sequel". And I do not have a server which serves sequels therefore it's not "my-sequel-server" either.
There's no 'R' in the acronym and so it's not "squirrel".
Therefore, without any vowels to help guide pronunciation it must be pronounced letter-by-letter just as other such acronyms are: enn-ess-ay, see-eye-ay, yoo-ess-ay.
But really I think both names suck. I honestly would prefer if they named them both after a kind of rock or precious stone. It would kind of match the theme of certain gemstone-named programming languages.

MySQL => Marble || Musgravite
Postgresql => Pumice || Painite
SQL Server => Scoria || Serendibite

Re:I won't use a DBMS I cannot pronounce.

[Anomalyst]

WTF is a gree?

something that might eat you in the dark room of a twisty maze?

Re:I won't use a DBMS I cannot pronounce.

[Anonymous Coward]

Never pronounced SQL as "see-quell", just "ess-que-ell" since it's an acronym with no vowels. "Squall" would be a better phonetic pronunciation. Would you pronounce NSA as "nasa" or "neesa" or just "en-ess-ay"?

Re:I won't use a DBMS I cannot pronounce.

[Trogre]

Sounds rather similar to OpenOffice vs OpenOffice.org

Re:I won't use a DBMS I cannot pronounce.

[Anonymous Coward]

As long as we're being pedantic, SQL is not an acronym, it's an initialism.

Re:I won't use a DBMS I cannot pronounce.

[Anonymous Coward]

I don't know about you but postgresql's built-in regex support is good enough for me:
http://www.postgresql.org/docs...

Re:I won't use a DBMS I cannot pronounce.

[OffTheWallSoccer]

MySQL: my-ess-queue-ell

It's not a movie or a book and thus it's not "sequel". And I do not have a server which serves sequels therefore it's not "my-sequel-server" either.

I suppose you would be inclined to refer to MS-DOS as emm-ess-dee-oh-ess. And you certainly wouldn't have called it a "Das Box", because it wasn't a "box of das".

Get real, dude. A lot of us say "sequel" because we don't want to spell it out (verbally) as ess-queue-ell all day long.

Re:I won't use a DBMS I cannot pronounce.

[erapert]

There is no English word pronounced "doss".
There is an English word pronounced "sequel" which means something entirely different from a database.
Therefore I would say "em-ess-doss".

Re:I won't use a DBMS I cannot pronounce.

[Anonymous Coward]

> There is an English word pronounced "sequel" which means something entirely different from a database.

Correct. It also means Structured English QUery Language

This is why I always say "ess cue ell" for SQL, to be clear I'm not talking about SEQUEL.

PL/pgSQL

[nerdyalien]

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:PL/pgSQL

[flacco]

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

Re:PL/pgSQL

[QuietLagoon]

PL/pgSQL

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

Re:PL/pgSQL

[crackspackle]

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:PL/pgSQL

[Anonymous Coward]

That's just the abbreviated form. The full name is Procedural LlanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogochQL.

Re:PL/pgSQL

[Anonymous Coward]

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. Also one with less tooling, language features (like modularity and abstraction) and utility features (logging, monitoring, debugging, profiling).

And I was under the impression that using triggers is a great way to kill your app's performance, since they run per row and don't allow any batching. The fact that they run inside the original transaction is advantageous when you really need it, but will make for unnecessarily fat transactions when you don't (NOTIFY is a nice alternative then).

Re:PL/pgSQL

[Anonymous Coward]

But it's pronounced "Throatwobbler Mangrove."

Re:PL/pgSQL

[Tom]

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:PL/pgSQL

[Anonymous Coward]

+2 Troll! My hero!

Re:PL/pgSQL

[Pseudonym]

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: PL/pgSQL

[Anonymous Coward]

You can use python or perl if you prefer, although I found PL/pgSQL rediculously easy to pick up.
Triggering on a insert/update to perform heavy calculation can help avoid some of the problems of denormalising a db for efficency. Imagine it building a table that you could use a view for but would kill the db with having to do the calculation for every access. If its done in the app the app then has to get it correct and add the necessary statements in one transaction. Every app and user that does this then needs to do this correctly.
I follow the rule of thumb that if there is denormalised data in the db then the db should be responsible for maintaining it so things don't become inconsistent.

Re:PL/pgSQL

[turbidostato]

"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 implies that in order to insure data quality you need to control it -again, at the RDBM level, not the application's, since the same data can and should be accessed by other means.

Do you have business rules? Then sure, go build them on the application level (and make them reacheable by API means). But set data management rules at the data manager level where it makes most sense.

Re:PL/pgSQL

[guruevi]

The recent trend is away from doing stuff on the database side. Just give the client the raw data and let them calculate against it. a) Your systems are less loaded b) Data presentation client side is WAY more flexible.

Re:PL/pgSQL

[Aighearach]

PL/pgSQL

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

If you don't know, you don't need to know. Easy.

My favorite is PL/Ruby, and in the .com boom years I was using PL/Perl a lot.

PL/pgSQL is mostly compatible with the pSQL variants in the commercial databases. As a longtime postgres user I have no trouble writing basic procedures in Oracle or MS Sql Server. From a programmers perspective, postgres is the only open source database that supports traditional "database programming," and it does it using many more languages than the commercial ones. Combine functions/procedures with views, triggers, and rules, and you can do all of the old-school "big data" stuff, and with much of your business logic in a modern high level language of your choice.

There is even PL/R, for building statistical analysis right into the database.

People blather about scaling because postgres users tend to use different scaling strategies than MySQL or Sql Server users. Therefore, they worry that postgres doesn't do the thing they're used to doing. If instead they simply look into the scaling strategies in use, knowing that PosgreSQL is indeed used in large serious applications, then they'll be fine.

Twitter went through some legendary growing pains that they initially blamed on RubyOnRails that turned out in the end to have been caused by their lack of research into database scaling using postgres. So it is true that if you get to the point of having a really huge, large-scale database you will need at least one person on your technical team to spend at least a week learning about database scaling using whatever database you chose. If that sounds hard, you need to hire your first sysadmin, or admit your database is tiny and you don't need to scale it.

Re:PL/pgSQL

[Aighearach]

Just do less, and it is easier. What a perfect answer!

I have no problem with this. However, expect that many of us will continue to include features, including calculating values on the server side.

Re:PL/pgSQL

[hummassa]

And Ruby and R...

Re:PL/pgSQL

[hummassa]

How many "Thick server/ thin client" <--> "Thin server/ thick client" cycles in the last 30 years of computing? Four, five, maybe six? It would be interesting to determine the frequency, so we can always be prepared for the next wave... Anyway you can absolutely trust that in less then seven years you will be on the "thick server again", so you can polish your PL/pgSQL+PL/R when you see it coming.

Remembering: thick servers are much more efficient in terms of energy, heat and per-dollar-computing, but thick clients are much more interactive. Thick servers tend to centralize the data, which leads to sloppy crypto and security, which leads to "I want to keep all my data on my cabinet/table/person/skin? at all times", which leads to "where is the last backup", which leads to "oh, I'll just trust the cloud with this, because surely Google and Amazon replicate the data and keep timely backups" etc etc etc.

Re:PL/pgSQL

[CyDharttha]

I prefer to leave the business logic in the business layer. Separation of concerns, easier to scale, etc.

Re:PL/pgSQL

[Anonymous Coward]

The recent trend is away from doing stuff on the database side.

Oh, the trend! Well God forbid that I would think for myself and do what makes most sense based on merit, I should just copy whatever some bunch of hipster faggots think I should be doing (and of course completely rewrite it next week when they suddenly decree the exact opposite).

Speed an issue

[Anrego]

News to me. Possibly true, but I doubt that's the main reason for MySQLs popularity.

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.

Personally I prefer postgres, but I get the appeal of MySQL. It has a much warmer and fuzzier user experience.

Re:Speed an issue

[tibit]

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:Speed an issue

[Anonymous Coward]

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:Speed an issue

[Anonymous Coward]

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:Speed an issue

[crackspackle]

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 features Postgres provided, which today are even more numerous.

Re: Speed an issue

[Anonymous Coward]

So don't disable strict mode then! Why configure your db to allow errenous data and then complain that it did exactly that?

Re:Speed an issue

[Anonymous Coward]

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.

You mean like MySQL gold standard: "Hey, your disk filled up, I ignored it, and corrupted your entire database?"

Re:Speed an issue

[Hognoxious]

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: Speed an issue

[Anonymous Coward]

> Why configure your db to allow errenous data and then complain that it did exactly that?

I think the question most of us are asking is, "why design your db to allow erroneous data??"

Re:Speed an issue

[Major Blud]

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:Speed an issue

[binarstu]

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.

Re:Speed an issue

[Anonymous Coward]

But does it shard?

Re:Speed an issue

[Anomalyst]

But does it shard?

Anne McCaffery is that you?

Re: Speed an issue

[Anonymous Coward]

Strict mode is disabled by default. MySQL comes with an absolutely idiotic configuration out of the box.

Re:Speed an issue

[Pseudonym]

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 worse, of course; PostgreSQL lets you make fine distinctions which MySQL does not. But for every case that's likely to come up in practice, managing users and roles in MySQL is about ten times faster and simpler than in PostgreSQL.

Re: Speed an issue

[turbidostato]

"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:Speed an issue

[huge]

Hey, don't be mean! MySQL supports it as well

Re:Speed an issue

[16Chapel]

I've had MySQL servers run out of disk space many times (oops) and never had any data integrity problems....

Re:Speed an issue

[JoshuaPK]

Correction: MySQL used to be easier to use and administer.

Can you describe what makes MySQL easier to use and administer in present time (versus a few years ago)? I think nowadays they are on-par with each other for ease of use, especially if you're using Windows.

Re:Speed an issue

[JoshuaPK]

I know you were joking around, but in some (many?) cases, the document store mode of Postgres is faster than a comparable MongoDB installation.

Here is one such study.

Re:Speed an issue

[OffTheWallSoccer]

thank you for the link - I haven't laughed that hard in ages!

Re:Speed an issue

[Anonymous Coward]

I still have to manually verify that every ALTER command did what I think it did, because at least one of them didn't.

That's a problem with "what you think it did", not with the ALTER command.

But for every case that's likely to come up in practice, managing users and roles in MySQL is about ten times faster and simpler than in PostgreSQL.

Multiple users (either an admin team, or advanced users who are trusted with (limited) SQL access), who all need to have the same permissions. Trivial in Postgres, just define a role with all the users in it and grant permissions to the role. In MySQL? Give them all a shared account, and make your security people cry, or manually give them all the same grants, and make sure you don't forget any when adding a new person to the team.

But of course, if you're enough of a kiddie to defend MySQL in the first place, you probably think a system with more than one user isn't "likely to come up in practice".

Re:Speed an issue

[Pseudonym]

That's a problem with "what you think it did", not with the ALTER command.

It could be both. In case it wasn't clear, I was saying that PostgreSQL's permission system is more powerful, so of course it's going to be harder to use and easier to get wrong. PostgreSQL lets you do more things, and consequently lets you do more things wrong.

Multiple users (either an admin team, or advanced users who are trusted with (limited) SQL access), who all need to have the same permissions.

Good point. That's not a case I've ever had to do, since every database I've ever co-admininstered was not only ever accessed by maintainers and middleware.

However, I will note, for the record, that you didn't defend splitting access permissions between the data dictionary and pg_hba.conf. Implementing IP-based restrictions in PostgreSQL is even more painful than it is in MySQL.

To claim that PostgreSQL is objectively better than MySQL (and it is!) is not to claim that every single thing in PostgreSQL is easier than it is in MySQL. I moved from MySQL to PostgreSQL probably ten years ago, and this is the only thing I find is less elegant. Thankfully, changing user permissions isn't a common occurrence in the majority of setups.

SQLite

[short]

'nuff said

Re:SQLite

[Anonymous Coward]

Inside your Android, yes. But multi-user? Not a chance!

Re:SQLite

[fyngyrz]

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 for SQLite (and another 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. :)

Why choose mysql? JetProfiler

[mveloso]

MySQL has JetProfiler, Postgresql does not.

Re:Why choose mysql? JetProfiler

[rla3rd]

and PostgreSQL has pgFouine http://pgfoundry.org/projects/...

Re:Why choose mysql? JetProfiler

[mveloso]

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:Why choose mysql? JetProfiler

[Galactic Dominator]

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.

Re:Why choose mysql? JetProfiler

[kriston]

If you're serious about profiling your database, you should be using the EXPLAIN command and analyzing what the query planner is up to.

I have worked virtual miracles on boggy PostgreSQL databases using EXPLAIN and careful index creation (with my choice of algorithm with three- and four-way column indices). MySQL just can't compete.

Re:Why choose mysql? JetProfiler

[Anonymous Coward]

pgFouine is dead for years. If you wanna do log parsing, pgbadger is the tool. If you want realtime query monitoring, give a try to POWA https://dalibo.github.io/powa/

Re:Why choose mysql? JetProfiler

[Anonymous Coward]

Is this post written in English?

Re:Why choose mysql? JetProfiler

[Anonymous Coward]

Thanks for the lol .. nice1

Transactional DDL

[Anonymous Coward]

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

Re:Transactional DDL

[phantomfive]

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.

Re:Transactional DDL

[dskoll]

This. Transactional DDL has saved our skins many times. We've had the *ahem* occasional buggy upgrade and knowing that we always have a valid schema is priceless. We either have the old schema or the new one, never some half-assed in-between version that requires a ton of manual fixups.

Comparing to MySQL, you will always lose

[PhrostyMcByte]

And not because MySQL is a better product, but because everyone thinks of MySQL as the database that isn't a very good choice for large projects. You should be comparing Postgres to SQL Server, Oracle, etc. -- the big guys.

Re:Comparing to MySQL, you will always lose

[Anonymous Coward]

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:Comparing to MySQL, you will always lose

[phorm]

Or often, MySQL is
"the database which likely isn't the best choice, but it is the one available on most VPS hosting providers" ...

Re:Comparing to MySQL, you will always lose

[Anonymous Coward]

It doesn't have to be available on "most" providers, just the one that you use - which you will of course select based on the features it provides.

Re:Comparing to MySQL, you will always lose

[Gavagai80]

Unless you're developing something others will use, in which case using a database that customers are much more likely to already have set up is a big advantage.

Re:Comparing to MySQL, you will always lose

[Big Hairy Ian]

I thought it died when Oracle took them over. Surprised to see it's still breathing

Re:Comparing to MySQL, you will always lose

[Anonymous Coward]

> You should be comparing Postgres to SQL Server, Oracle, etc. -- the big guys.

Postgres is sensibly cheaper, I've been told...

Re:Comparing to MySQL, you will always lose

[Anonymous Coward]

If you have "customers" then they can afford sensible fucking hosting.

Since when was speed a problem for PostgreSQL?

[Balial]

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?

[Anonymous Coward]

It was back in the day when being compared to MySQL running on its default MyISAM table structures, which had no referential integrity, thus there was no constraint checking. That makes for quite a substantial difference.

Until you find out you've been corrupting the data with incorrect queries for the past six months.

Re:Since when was speed a problem for PostgreSQL?

[Unordained]

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:Since when was speed a problem for PostgreSQL?

[Anonymous Coward]

Early on mysql was limited in the kinds of joins it could do and apps were designed to do the joins themselves. So this emphasized select speed, which was faster in mysql and didn't make use of postgres' abilities. Postgres also needed to be managed better for frequent updates. If you didn't vacuum often enough, rapidly updated tables would be slow to reference. Another issue is that people liked to make count(*) refernces, which were cached in mysql, but not in postgres, because with its multi concurrency design, count(*) could have several different values at the same time, depending on which rows were visible to your transaction.

Re:Since when was speed a problem for PostgreSQL?

[Unordained]

Early on mysql was limited in the kinds of joins it could do...
Can it do a FULL OUTER JOIN yet?

Re:Since when was speed a problem for PostgreSQL?

[Anonymous Coward]

Yup. Talked to a guy who said he could spare half an hour every evening fixing the inconsistencies that a DB without transactions would incur through a day.

I guesstimated the number of fixes he'd have to do (far too many), and explained I wouldn't help him find the inconsistencies, nor could I write a program to find them all.

Re:Since when was speed a problem for PostgreSQL?

[kbrannen]

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.

Re:Since when was speed a problem for PostgreSQL?

[Cramer]

In mysql, count() was 99% a lie. It never referenced the actual rows. It used indexes and "reference statistics" to answer the queries quickly, vs. correctly. I always found that infuriating. (it was one of a very long list of dumbass things mysql did -- and mostly still does)

Re:Since when was speed a problem for PostgreSQL?

[Anonymous Coward]

Speed was only a problem because it was being compared with MySQL.

But when you throw out things like data validation, transactions, and referential integrity you know things that people who actually work with databases use on a regular basis, of course it's MySQL is faster.

Now that MySQL has started paying lipservice to some of that; the difference in speed has dropped to near parity. And there's now a shit version of the enterprise ready features that are in PostgreSQL.

Re:Since when was speed a problem for PostgreSQL?

[dskoll]

Pg ancient (like version 6 or so) was pretty nasty; a VACUUM more or less locked up the system. But that hasn't been true for ages now.

Re:Since when was speed a problem for PostgreSQL?

[Anonymous Coward]

well, union gets you full outer, but intersections, etc, probably require temp tables

Re:Since when was speed a problem for PostgreSQL?

[gerald.edward.butler]

What? Union and full outer join are not even close to the same thing.

Re:Since when was speed a problem for PostgreSQL?

[Anonymous Coward]

SELECT * FROM a LEFT JOIN b ON whatever
UNION ALL
SELECT * FROM a RIGHT JOIN b ON whatever WHERE a IS NULL

This is going to be a nice discussion

[trybywrench]

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.

Re:This is going to be a nice discussion

[quantaman]

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:This is going to be a nice discussion

[jedidiah]

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

Re:This is going to be a nice discussion

[Voyager529]

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:This is going to be a nice discussion

[Anonymous Coward]

head_exploding.gif

Re:This is going to be a nice discussion

[Anonymous Coward]

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

Bah.

MySQL is so bad, Larry Ellison gives it away for free.

Re:This is going to be a nice discussion

[Anonymous Coward]

VI VI VI - The Editor of the Beast!

Re:This is going to be a nice discussion

[phantomfive]

When I use postgresql, every once in a while I learn about some feature that is kind of nice, and might make my life easier (though sometimes at the cost of compatibility).
When I use MySQL, every once in a while, I learn about a feature (*bug*) that is kind of annoying, and definitely makes things more complicated (and sometimes at the cost of compatibility).
There is no question in my mind which to use. If a hosting platform only supports MySQL, I won't use that hosting platform.

Re:This is going to be a nice discussion

[Anonymous Coward]

No vim wins you incentive clod.

Re:This is going to be a nice discussion

[Pseudonym]

Nope, the answer is Perl.

Re:This is going to be a nice discussion

[rastos1]

Perl, isn't the answer. Perl is the problem.

SQL Server, thanks

[DogDude]

SQL Server is free up to 10 GB. After that, I'll probably still pay for SQL Server. MySQL and MariaDB are toys, and there's nothing that PostGre offers that SQL Server doesn't.

Re:SQL Server, thanks

[t33jster]

Operating system independence? I get that it may not be important TO YOU, but you're going to have a hard time downloading those SQL Server binaries for any Linux distro.

Oracle is free up to 10GB as well. You just don't get any of the patches or support, but who needs em? Relational data isn't all that valuable anyhow.

Re:SQL Server, thanks

[jedidiah]

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:SQL Server, thanks

[i_ate_god]

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

Re:SQL Server, thanks

[labnet]

Except the 10k licensing fees....

Re:SQL Server, thanks

[johannesg]

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

Re:SQL Server, thanks

[DogDude]

If you're changing server OS's in the middle of an implementation, then you've got much bigger problems than what kind of database you're using.

Re:SQL Server, thanks

[Anonymous Coward]

Well, the others don't need Windows.

Re:SQL Server, thanks

[Anonymous Coward]

It's worse than that. 2014 Enterprise costs $7,128 per core .

Re:SQL Server, thanks

[Hognoxious]

Right. Because it's totally impossible that you might be building something that you're intending to sell to more than one customer.

Re:SQL Server, thanks

[DrStrangluv]

I'm a Sql Server guy myself (I spent a brief period as #1 user by rep within the sql-server tag on Stack Overflow back in 2009), but Postgresql does offer some nice language features missing in Sql Server. It also has table inheritance and for larger servers can save you a LOT of licensing costs. It performs pretty well these days, too. I agree that MySql is toy, though. Still no windowing functions after 10+ years as part of the ansi standard, awful handling of NULLs, and no FULL JOINs are just three of the many reasons that MySql is and has been for some time only the 2nd best open source DB in most categories. The only reason it's popular today is because of the self-perpetuating nature of popularity. People like it because it's what they've known, and it's what's been available.

Re:SQL Server, thanks

[Anonymous Coward]

License-free operation is a feature the PostGreSQL has that SQL server doesn't.

Re:SQL Server, thanks

[PRMan]

Plus, EVERYTHING else is terribly slow after using SQL Server.

Re:SQL Server, thanks

[kimhanse]

Ease of install.

Sane handling of trailing spaces in varchar.

A better shell with a great help function build in.

Re:SQL Server, thanks

[Tablizer]

The "Express" SQL-Server version is limited by more than just capacity. Last I checked, it's limited to processing on one core and lacks the automatic backup scheduling feature. Regular file backups are not sufficient for such database files unless you switch off all updates temporarily during backup file copy (to avoid orphaned data pointers). You can script the process-shutdown/copy-files/startup steps, but if something goes wrong, you have a downed database to manually restart.

Re:SQL Server, thanks

[coder111]

Last time I had to deal with MSSQL, I found PostgreSQL to be faster on same queries/datasets. Sometimes significantly so.

YMMV

--Coder

Re:SQL Server, thanks

[Anonymous Coward]

Operating system independence? I get that it may not be important TO YOU, but you're going to have a hard time downloading those SQL Server binaries for any Linux distro.

One word: Sybase.

Re:SQL Server, thanks

[Anonymous Coward]

there's nothing that PostGre offers that SQL Server doesn't.

Functioning PHP support? (FreeTDS doesn't support prepared statements)

Re:SQL Server, thanks

[Pseudonym]

Hang on a sec, lemme check...

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

Sorry, you were saying?

Re:SQL Server, thanks

[Dog-Cow]

You just provided evidence to backup the GPs point. Did you think you were providing a counter-example?

Re:SQL Server, thanks

[hawk]

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:SQL Server, thanks

[Anonymous Coward]

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

Your dates are off, kid; Unix has had security issues a lot longer than 25 years. Where do you think the word "rootkit" came from?

Re:SQL Server, thanks

[Pseudonym]

I think I misunderstood the point that the GP was trying to make. The phrase "how truly trivial that limit is" could be taken several ways.

Re:SQL Server, thanks

[Anonymous Coward]

SQL Server is free up to 10 GB. After that, I'll probably still pay for SQL Server. MySQL and MariaDB are toys, and there's nothing that PostGre offers that SQL Server doesn't.

No licensing headaches.

Re:SQL Server, thanks

[Anonymous Coward]

The only reason it's popular today is because of the self-perpetuating nature of popularity. People like it because it's what they've known, and it's what's been available.

They like it because it did read-only replication and key-sharding long before Postgres. While Postgres implemented far more of 80s database ideas and did so at higher quality, they dragged their feet about late 90s ideas like clustering, trying to get it just perfect supposedly, and the web moved on with MySQL. That is why you see it everywhere: it was the only option for a site with too many queries per second to be served by a single machine. Meanwhile Postgres's stubborn-greybeard choice of BSD license meant Greenplum made progress on clustering but could release their Postgres-based work as proprietary software, so the progress withered to irrelevance on a fork and didn't benefit the community. It is the same story with Linux vs BSD on embedded systems: the more restrictive license is actually better for companies in the long run, because when companies act in their interest they are structurally capable of considering only the short term.

Meanwhile even the open source effort may not have considered the long term enough. Today I heard Facebook serves live traffic out of Hadoop, not just batch queries, so I'm not sure these older styles of clustering are relevant any more. I haven't heard much about Postgres clusters. Maybe they're good. :/ but already you can get Hadoop-like interface backed by Bigtable, and new distributed transactional databases like Spanner sound very different from and more powerful than the replication style of MySQL and Postgres.

Re:SQL Server, thanks

[KingMotley]

Express is limited to 4 cores, and you are correct that there is no automatic backup scheduling feature, but most backup software (not a straight file copy) does a shadow copy and should work just fine.

Re:SQL Server, thanks

[Anonymous Coward]

Plus, EVERYTHING else is terribly slow after using SQL Server.

Yeah. It's so fun waiting fucking five minutes for SQL Management Studio to even open in the first place, then take several seconds just to open a new query window, several seconds to drill down the tree menu to actually see the schema... The few fractions of a second it takes to do all that with PostgreSQL and phpPgAdmin are so slow in comparison.

Re:SQL Server, thanks

[Anonymous Coward]

, and there's nothing that PostGre offers that SQL Server doesn't

I much prefer postgresql's syntax - where I can do SELECT (fancy stuff here) AS newcolname from TABLEA order by newcolname.

SQL Server makes me do:
SELECT (fancy stuff here) AS newcolname from TABLEA order by (fancy stuff here)
Or wrap the select in another select so that I can refer to the inner select's newcolname in the outer select's order by ... Either way it's uglier.

Stuff like regex, CIDR data types is easier in postgresql.

SQL Server has plenty of other annoyances too. So much so that if I need to do some data analysis I often dump stuff from SQL Server and load it to postgresql.

I only use SQL Server because of work related reasons.

Re:SQL Server, thanks

[Anonymous Coward]

I don't like SQL Server but can't you script backups in express edition? Just use the transact sql BACKUP command. https://msdn.microsoft.com/en-us/library/ms186865.aspx

Once you've got the script written you can schedule it.

I personally script it instead of using the built-in "maintenance plans" crap. It's much easier to do the things I want that way. The maintenance plans stuff tends to run things in async which makes it hard to do: backup the database, test the backup by doing a restore if previous step was ok, compress the backup if the test restore was ok, rename the backup if the compress was OK, etc.

Perhaps there is a way to do it but it wasn't obvious. From what I see with maintenance plans you can get job B to run after job A, but job B will start after job A starts, not after job A ends successfully.

Re:SQL Server, thanks

[JoshuaPK]

I'll leave this here. It's an interesting series of videos that explain features of Postgres that SQL Server doesn't have. It also explains why many developers are moving from SQL Server to Postgres.

Pluralsight Tekpub Postgres videos

Re:SQL Server, thanks

[Anonymous Coward]

Uh those videos compare vs MySQL they don't compare vs MSSQL.

Re:SQL Server, thanks

[Tablizer]

Shadow copy does not work right on those database files.

There's more to databases than SQL

[Anonymous Coward]

"According to developer David Bolton, here's why PostgreSQL is worth a second look for your database-management needs" - nope, it's only for some of your database needs.

Re:There's more to databases than SQL

[tomhath]

Postgres does JSON and NoSQL too. Really well in fact.

RowID

[Anonymous Coward]

Postgres has rowids. This allows for more generic programming. MySQL does not. You have to forcibly add an integer id to each table which can be suckful.

FireBird... enough said

[Anonymous Coward]

Seriously, Firebird destroys all these other databases in terms of performance, space, and quality.

Too bad "the system" (aka dumbass "momentum") is against it. :/ Wake up, morons, you are morons.

I applaud those modding me down with no actual experience. You are smart! Real smart! (?) Slashdot is not great when it comes to realizing true skill. Too bad for you, I guess. WTF

Re:FireBird... enough said

[Unordained]

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:FireBird... enough said

[fyngyrz]

So tell us why. I'm interested. Others will be too.

Re:FireBird... enough said

[Pseudonym]

Does it do OpenGIS?

Re:FireBird... enough said

[Unordained]

No. http://tracker.firebirdsql.org...

Re:FireBird... enough said

[Anonymous Coward]

Databases should be set up and managed by DBAs or Contractors. Things like "what's on by default" are not something a developer should care about with a database. The database is installed, set-up, and tuned accordingly. If a feature is needed, and exists, it is turned on even if the default is off.

Re:FireBird... enough said

[Anonymous Coward]

Also, I do think Firebird is nice. It's small, rather full-featured, easy to manage, and easily embeddable which means that even Application Developers can get some use out of it.

However, it's poorly documented and has fallen into a fair bit of obscurity. With the way a lot of applications are being developed these days, the lack of decent tooling for Firebird is a problem.

You can get around that by using Delphi or C++Builder, to an extent, but not everyone is willing to do that.

I am interested in it as an embedded Database, though, since Microsoft has Deprecated SQL-CE and removed all of the tooling from the Visual Studio IDE.

update-ability

[vulcanrob]

I started with postgres years ago but I had some bad updates (on Debian) that broke some installations so I dumped it for mysql and haven't had a problem since. Perhaps an unfair, n=1 criticism, but there it is.

Re:update-ability

[Anonymous Coward]

It requires a full dump and restore for upgrades between major and minor versions. Sucks, and is a pain for a big database (time consuming) but it gives them much more flexibility in making changes to the on-disk structure.

Fortunately, recent versions (9.x) have improved this some: http://www.postgresql.org/docs/current/static/pgupgrade.html

Dice spam

[grimmjeeper]

Like I'm going to trust a Dice "insights" page to tell me what DBMS to use...

Real Database

[Anonymous Coward]

As a DBA; I would choose Postgres. Simply put Postgres has always strived to be a true RDBMS database where as MySQL was create aa a simple tool to solve a single problem. I don't consider MySQL a true RDBMS. Is MySQL ACID compliance? sure, now it is -- that is if configured correctly but the lack of it was historically promoted as a feature. Poor execution engine; given a complex queries MySQL falls apart where as Postgres handle the complex queries with ease. SQL compliance? MySQL is a disaster when it comes to compliance were as Postgres is more SQL complaint then even commercial databases like Oracle, SQL Server, etc.

I would compare Postgres to Oracle, MySQL, etc. The only reason to go with Oracle over Postgres is for Oracle features (or large company support) that are not currently available in Postgres yet. i.e OracleRAC, Exadata, Parrell Query (a planned feature for Postgres 9.6), etc.

Re:Real Database

[Damouze]

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:Real Database

[coder111]

Eh? Sybase? I work with it now, and it's the biggest piece of slow unfinished half-assed buggy shit database I've ever had to deal with.

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

Syntax

[Anonymous Coward]

My only gripe with PostgreSQL are the parts of the syntax that really differ from MySQL vs MSSQL. I mean, I can keep in mind the differences between the latter, but they both seem very similar compared with PostgreSQL.

Re:Syntax

[Anonymous Coward]

That's because the PostgreSQL developers actually try to follow the ANSI SQL standards. MySQL and MSSQL? Not so much.

ahem

[kelemvor4]

Memsql.

That is all.

MySQL might be a better choice

[Anonymous Coward]

Even if MySQL is inferior RDBMS then it still might be (and probably is) a better choice for most applications for several reasons:
- better support, just compare number of questions of MySQL and PostgreSQL on StackOverflow. Most MySQL issues can be solved by using Google 'I'm feeling lucky'
- your friends know MySQL,
- if some software uses RDBMS, it's probably MySQL. You might be left with a choice to learn about one or two sql servers,
- most applications don't need advanced features provided by PostgreSQL,
- there are less freelancers who know PostgreSQL than freelancers who know MySQL. They might charge more too.

Re:MySQL might be a better choice

[Anonymous Coward]

So by using the "100 million flies eat shit, so it must be good!" argument, you are convinced, and are trying to convince others, that shit is good to eat?

Happy PostgreSQL user

[johannesg]

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:Happy PostgreSQL user

[lgw]

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

Re:Happy PostgreSQL user

[Gramie2]

> their C interface was the stuff of nightmares

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

Re:Happy PostgreSQL user

[StormReaver]

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.

Re:Happy PostgreSQL user

[neoform]

If MySQL isn't even something you consider, how could you possibly know that it's slow compared to PG?

Re:Happy PostgreSQL user

[johannesg]

Back in the day it didn't, so we mostly built our systems around hot failover clusters that work on the OS level. We also have our own replication solution running (this is part of the application level code, using Postgres triggers) which transfers the data on the fly through various security barriers for the purpose of remote viewing. This is not just simple replication though; it wouldn't work with any off-the-shelf solution anyway.

We haven't really looked at the built-in replication since we already have working solutions that we are happy with, but it seems to be getting more capable with each successive Postgres release.

Re:Happy PostgreSQL user

[Anonymous Coward]

Then there's OCI, which added a nice little feature in 11g databases that causes any OCI-using application to drop the connection and require restarting if you accidentally select an XMLDATA column.

Re:Happy PostgreSQL user

[ebvwfbw]

Glad it's improved. Maybe it's worth another look now. I remember in the past I upgraded a system and the next version of postgres wouldn't touch the old files. I had to get another machine, load it with the previous stuff, dump it and then move it and load. That took a few days. Fortunately it wasn't important to be online during that time. I'd still like those few days of my life back. Along with the time I've lost due to spammer bullshit. They owe me a few years.

OTOH, I've never lost anything with mysql. It's been rock solid. Don't need the vacuum bullshit either. Maybe they've done away with that too.

Postgres isn't so great

[tribeca.kaji]

I'm currently working on a large CRUD application and we've selected postgres as the db of choice. The organization uses both oracle and mysql for other applications. I really don't like the fact that the only difference between users and roles is a --no-password flag. If you're deploying to aws you have to be very careful to setup your local master user with similar privs as the aws account. While automating builds, we've had some major hiccups scripting the schema creation/cleanup due to the fact that only a table 'owner' has the privs to delete a table unless explicitly granted. Put simply, just pay attention to the privs the aws master user has. Don't over engineer your roles and users. I prefer DBeaver over PGAdmin. Dropping a schema requires all users to be disconnected which means that you'll need to execute a snippet of sql to disconnect everyone. Over time one becomes accustomed to these nuances but they are a pain to deal with at the beginning.

Butchered SQL

[Sam36]

The issue I quickly realized with mysql when I tried to replace an aging IBM DB2 data warehouse (with a total of 10 billion rows) was that Mysql only supported a small subset of the SQL standard. This quickly lead to almost all of our normal queries for BI not even being able to be ported. After trying to make it work anyway I eventually gave up. I had recently started using postgresql in my home server set up since I didn't know when direction Debian was going to take with the whole mysql/maridb debacle. So I convinced my boss to scrap the mysql idea and try again with Postgres. I was amazed at how well the SQL standard was supported by postgres. It also had a far superior query planner that mopped the floor compared to mysql performance for any query with more than a couple of joins. I also like how postgres isn't owned by any one entity like mysql. It is all I use now.

Speed improvements...

[Anonymous Coward]

Speed is less of an issue because of hardware improvements. Every component, from the storage array, to the bus, to the CPU, is exponentially faster now than it was just a few short years ago. Postgres has benefited from improvements in processing and storage speed; not from design improvements.

Re:Speed improvements...

[Anonymous Coward]

Postgres has benefited from improvements in processing and storage speed; not from design improvements.

I'm sure all the developers who've worked on performance improvements will be interested to hear you tell them that none of their work actually happened.

Who supports kerberos?

[Anonymous Coward]

Setting up user access can be a pain.

Galera Clustering

[darkain]

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 without disrupting the rest of the cluster.

Re:Galera Clustering

[Unordained]

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.

Re:Galera Clustering

[rev0lt]

If you're using Galera, I'd recommend you to read this - https://aphyr.com/posts/327-ca... . Depending on your application requirements, you may have unwanted behaviour from your cluster.

Re:Galera Clustering

[MrSome]

If you're using Galera, I'd recommend you to read this - https://aphyr.com/posts/327-ca... . Depending on your application requirements, you may have unwanted behaviour from your cluster.

And then read this, after that, as it is a response from Percona about the first article . https://www.percona.com/blog/2015/09/17/clarification-call-maybe-mariadb-galera-cluster/

Re:Galera Clustering

[JoshuaPK]

Yes. The company 2ndQuadrant created something called Postgres BDR, and it is designed specifically for geographically diverse master-master replication.

Here are more details.

Re:Galera Clustering

[rev0lt]

Still, the problem exists, and their documentation is wrong. I'm not nitpicking, I do maintain a legacy Galera cluster and this are the kind of issues you can't easily control on a prebuilt application or with eg. ORMs.

Already a 'feature'

[Anonymous Coward]

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

It's been a 'feature' since the beginning: journald - the new syslog database management system. Moderate speed and nonzero scalability finally put syslog on the DBMS map.

The convenient binary file format ensures security by preventing the unwashed from viewing logs with only a cat, head, tail, or less.

All JavaScript

[kanibalv]

So, you have a Development, and you use Javascript (React, Angular, etc) for FrontEnd, NodeJS as BackEnd and Postgres with functions en PLV8, basically, you have to learn one language mainly .. JAVASCRIPT Easy and powerfull... PD: Complex calculation must be stores on a function on Postgres but not in Javascript.

Re:All JavaScript

[tomhath]

Bullshit. Postgresql can store data in JSON and you can write functions in Javascript (or even better, Python).

Re:All JavaScript

[tomhath]

Slightly misread GP, but limiting yourself to Javascript is foolish. Learn SQL, it's awesome. And Python, it's even more awesome. Javascript is for noobs.

Different use cases

[orlanz]

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:Different use cases

[MrSome]

MariaDB and MySQL are basically the same thing.

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

Re:Different use cases

[Anonymous Coward]

> But [Postgres] needs enterprise level care and maintenance processes just like the others. You can use it on small projects, but its really over kill.

I *happily* use Postgres on my small project.

The distro-provided config file was 100% acceptable for my needs. I haven't had to think about my DB in three years: it has Just Worked(TM), quietly in the background for all that time.

If you're starting a greenfield project, or have a lot of latitude on an existing one, run -don't walk- away from MySQL towards Postgres. It doesn't silently corrupt your data through "helpful" type coercion. It supports UTF-8 out of the box. (Did you know that MySQL's "utf8" encoding is *not* UTF8? You want the "utf8mb4" encoding.) It doesn't let clients override server settings that are essential to correct interpretation of input data and inadvertently corrupt the data they're inserting. What's more, it's user documentation is *great*. Postgres is just *better*.

Re:Different use cases

[Dog-Cow]

I don't think you understand the GP's point.

Re:Different use cases

[MrSome]

Oh...I understand his point.

He was trying to bring sanity to an endless discussion over a click bait article by expressing his opinion on how the different options stack up.

I humorously referenced the original article's intent, which states that we should all switch to PostGres because:

1. A few specific types of indexing.
2. Table Inheritance
3. JSONb

If that's all the author could come up with... then we are splitting hairs and speaking to the minority of the population.

I now feel dirty by having spent so much time discussing a horrible article that dice created solely to cause a stir.

Also great on BananaPi

[Anonymous Coward]

I replaced mysql with postgresql on an owncloud project running on a banana pi and it was the best solution I could find. Things actually flowed with postgresql. I was very pleased and started to consider it for everything else.

Where's the SJW angle?

[Anonymous Coward]

Who cares which database is best.

Tell us the really important stuff - are there enough women using databases, and if not, what plans are in place to increase those numbers?

Exactly which replication?

[coder111]

Depends on what kind of replication you need.

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...

--Coder

Re:Exactly which replication?

[Billly Gates]

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:Exactly which replication?

[JoshuaPK]

Did you ever try the Postgres built-in streaming replication? Kevin Grittner used to oversee the legal case databases for Wisconsin state courts. He has indicated several times that they've had better luck with Posgres replication than with SQL-Server replication.

Transactional DDL

[Captain Damnit]

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, MySQL away. Otherwise, use PostgreSQL. You'll sleep better.

SQLite and PostgreSQL

[Art3x]

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

[shocking]

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.

VMS is better than Unix - Banks use it

[Anonymous Coward]

A nail and a hammer open a paint can faster than anything!

Always use the fast tool, not the right tool.

E

Re:VMS is better than Unix - Banks use it

[ebvwfbw]

Wow a VMS troll. How quaint.

What do you mean second look

[Anonymous Coward]

PostgreSQL is the first choice.

When I see MySQL i immediately figure it's a sloppy, crap design by someone who don't know shit about databases and didn't bother to research.

i have yet to be wrong.

Re:What do you mean second look

[Anonymous Coward]

When I see MySQL i immediately figure it's a sloppy, crap design by someone who don't know shit about databases and didn't bother to research.
i have yet to be wrong.

Nope, because you are right.

PostgreSQL: optimized for SunOS4

[mveloso]

You know a tool is not for you when its defaults are set so it'll still run on SunOS 4.

Seriously, if you want to screw yourself by choosing a database that has poor tooling and is opaque, choose PostgresSQL. Because when you do, you know that you have the big RDBMS balls and you can walk into any open-source conference with a feeling of superiority - because you just spent weeks figuring out why your specific workload performs like crap using nothing but your pubic hairs and some logging.

For everyone else, save yourself months of headache and use MySQL.

Re: PostgreSQL: optimized for SunOS4

[Anonymous Coward]

Try adding a column to a large table with traffic on MySQL. Takes forever.

In PG unless you give it a default value it's instant. This alone saves hours of headaches.

Re:PostgreSQL: optimized for SunOS4

[Anonymous Coward]

And you're going to have years of headaches dealing with the limitations of MySQL, like the shit transaction manager and integrity checking.

Been there, done that, written thousands of lines of code and wasted who knows how many hours debugging crap because a naive developer put their commit/update in the wrong part of the transaction sequence. Then there's the whole data-integrity of having to run multiple transactions for a sequence of work. All because someone was too damn lazy pick the right database for the job.

The database doesn't have to be something to difficult to code around, but MySQL is so damn limited if you're smart you'll put an additional layer of code between the business layer and data access layer to make it easier to manage multiple transactions and create rollback transactions if necessary. I've never had to create a rollback transaction on the likes of Oracle, DB2, SQL Server, or PostgreSQL. MySQL on the other hand, fuuuck; even had to do that even on a crappy little CRUD application.

Re:PostgreSQL: optimized for SunOS4

[Galactic Dominator]

My RDBMS balls clang/llvm when I walk, boy.

If it's taking you weeks to figure out how to profile Postgres, that's a you problem. Works for everyone else.

Comment removed

[account_deleted]

Comment removed based on user account deletion

Availability/ease-of-configuration

[phorm]

Using MySQL as your DB is like using your smartphone as your camera. It's not that it's the best - in fact sometimes it might suck - but it's fairly easy to setup and use, is common, and it's there when you need in (available in most distributions and in VPS solutions etc).

MariaDB, same devs, just fixed & better (no My

[Anonymous Coward]

The same Developers developed MariaDB that created MySQL. This history is well known. What is not as well known is that they were prevented from fixing problems with MySQL and one of the first things they did when they left Sun-Oracle was to fix all the things that they were not allowed to fix because Oracle, the company, wanted everyone to license Oracle the RDBMS.

Why this is not advertised, publicized, discussed more is beyond me. If you want to compare Apples to Apples, you have to consider all the items fixed in MariaDB that made it faster, more stable and better, else it's not a good comparison.

No experience with PostgreSQL. But with experience with both MariaDB and MySQL (as well as DB2, Oracle, and other SQLs) you would be crazy just to blindly pay the licensing fees for Oracle without considering MariaDB first.

If someone says it won't scale, don't believe them. And beware of made up excuses by those that want to stay with Oracle anyway. Just say no.