Slashdot Mirror
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).
Try my product... I guarantee you will be satisfied with it or your money back... here's how to order...
My eyes reflect the stars and a smile lights up my face.
Postgres is an excavator, while the other two are bobcats. For heavy lifting, the choice is obvious.
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.
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.
post-gres-cue-ell.
Now give it a try! :-)
I use it for everything except the stuff where sqlite is more appropriate.
pr0n - keeping monitor glass spotless since 1981.
'nuff said
MySQL has JetProfiler, Postgresql does not.
PostgreSQL has it. Makes development faster by an order of magnitude with a big database schema.
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.
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?
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.
I came to the datacenter drunk with a fake ID, don't you want to be just like me?
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?
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.
A successful API design takes a mixture of software design and pedagogy.
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.
I don't respond to AC's.
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.
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.
Like I'm going to trust a Dice "insights" page to tell me what DBMS to use...
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).
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.
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.
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.
Memsql.
That is all.
Could easily be post-gres-quell as well.
And there's the issue.
Slashdot still doesnâ(TM)t support Unicode after it was added to the HTML standard in 1997.
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?"
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.
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.
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.
I've even heard it pronounced post-gres-kul.
Everything's better than 'gimp' right?
Religion is what happens when nature strikes and groupthink goes wrong.
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.
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.
Damn, programmers are just way too clever for their own good.
So tell us why. I'm interested. Others will be too.
I've fallen off your lawn, and I can't get up.
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.
Confucius say, "Find worm in apple - bad. Find half a worm - worse."
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.
That's because the PostgreSQL developers actually try to follow the ANSI SQL standards. MySQL and MSSQL? Not so much.
> 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??"
It's like a Gru, but less hungry.
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.
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....
If you post as Anonymous Coward, don't expect a reply.
I've always heard it as "Post gress see quill"
Peter predicted that you would "deliberately forget" creation 2000 years ago...
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.
Table-ized A.I.
They should of made querying the DB with regex a first-class citizen, and called it PostGrep.
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.
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.
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
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.
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.
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
Postgres does JSON and NoSQL too. Really well in fact.
WTF is a gree?
something that might eat you in the dark room of a twisty maze?
There is no right to feel safe thru security vaudeville at the expense of everyone's freedom, privacy and tax money.
But does it shard?
Anne McCaffery is that you?
There is no right to feel safe thru security vaudeville at the expense of everyone's freedom, privacy and tax money.
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.
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.
sub f{($f)=@_;print"$f(q{$f});";}f(q{sub f{($f)=@_;print"$f(q{$f});";}f});
Does it do OpenGIS?
sub f{($f)=@_;print"$f(q{$f});";}f(q{sub f{($f)=@_;print"$f(q{$f});";}f});
Sounds rather similar to OpenOffice vs OpenOffice.org
"Nine times out of ten, starting a fire is not the best way to solve the problem." - my wife
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.
"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.
Comment removed based on user account deletion
Hey, don't be mean! MySQL supports it as well
-- Reality checks don't bounce.
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.
brandelf -t FreeBSD
No. http://tracker.firebirdsql.org...
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).
I've had MySQL servers run out of disk space many times (oops) and never had any data integrity problems....
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.
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.
Wow a VMS troll. How quaint.
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.
thank you for the link - I haven't laughed that hard in ages!
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.
sub f{($f)=@_;print"$f(q{$f});";}f(q{sub f{($f)=@_;print"$f(q{$f});";}f});
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".