13 ms·
MySQL vs PostgreSQL
- fkdjs 14y agoI would say this site is too nice to mysql. For stored procedures, mysql had those later whereas postgresql got those right early on. Mysql stored procedures suck donkey balls compared to postgres. Postgres in general just feels better designed, from the beginning. re: replication, slony is horrible yet they focus on that. The slony author says you can daisy chain things, but that's a setup nightmare. Also, slony's n^2 communication gives you consistency guarantees, something I'm pretty sure mysql can't do, but most people don't need that. I much prefer bucardo. It's simple, easier to configure, fewer guarantees, but replicates much faster. I just wouldn't run a bank on that. However, how many people design bank software.
- dlikhten 14y agoI read that entire site as: PostgresSQL has feature X, Y, Z MySQL had X, recently introduced Y, and does not always have Z. It's always MySQL catching up to postgres. And lots of it's db engines are not ACID. Its not the DB I thought it was.
- jeffdavis 14y agoI think it's a bad idea to evaluate by checklists of features you think you need. It's kind of like choosing where to live based on a checklist. I started with MySQL, then used MySQL and PostgreSQL for a while. Then, when I started to do more "real" projects, I just got so frustrated with MySQL in so many ways at once. It wasn't that MySQL couldn't do it, it's that it was frustrating at every turn. In my case, what caused me to drop MySQL almost entirely (around 2003 or 2004) was doing a few simple reports involving dates. Then I started using postgres and it was refreshingly consistent and flexible without so many caveats. And now I develop for postgresql, and the code is similarly consistent and flexible (and just all-around nice). I have had a long string of positive experiences with postgres. It's hard to wrap them up into a feature checklist. If something held you back from using postgres in the past, it's a good idea to watch the release notes to see if something new might solve that problem (or better yet, discuss on the lists so maybe it will be solved faster). But I tend to think that looking at long lists of features is a distraction.
- ken 14y agoLooks like the fragment ID was added to escape duplication detection. Previous discussion: http://news.ycombinator.com/item?id=328257 http://news.ycombinator.com/item?id=328257
- lazyjones 14y agoThey could add a check for identically rendered topic titles (including site name in parentheses) to avoid some of these.
- kyrra 14y agoThat won't work for pure javascript sites like Twitter.
- lazyjones 14y agoI am referring to the title that is generated and displayed on HN for the link (I don't know if it's done through scraping). Twitter has proper title tags on status pages by the way (e.g. https://twitter.com/wikileaks/status/235818483588407296 https://twitter.com/wikileaks/status/235818483588407296)
- bartonfink 14y agoThat title is supplied by the submitting user according to some informal recommendations. Every so often, there will be an argument over whether the submitted title is appropriate for the content. HN doesn't scrape anything for it.
- deleted 14y ago[deleted]
- Groxx 14y agoThat's from 1405 days ago. Duplicates can be submitted in a significantly shorter time-span, IIRC.
- kemiller 14y ago
- jfb 14y agoReading this makes me glad I don't ever have to interact with MySQL. PostgreSQL is bad enough.
- pygy_ 14y agoWhat is "bad enough" about Postgres?
- jfb 14y agoIt's SQL. Isn't that sufficient?
- pygy_ 14y agoIndeed, it's a shame that QUEL died. I recently daydreamed about Teaquel, a CoffeeScriptesque take on SQL. I'm afraid it would have little chance to take off, though... Anything else?
- atsaloli 14y agohtsql is a next-generation query language built on top of SQL - you can express in severals lines of htsql what would be a screenful of SQL. www.htsql.org
- justinsb 14y agoMangling the well-known quotation: SQL is the worst syntax, except all the others that have been tried. What are you comparing it to?
- pygy_ 14y agoQUEL was pretty clean. Far more consistent than SQL. It died, mostly because SQL was used by Oracle and IBM DB2 which were better marketed than Ingres. The DB world ended up standardizing on SQL because of this. Worse is better, once again. http://en.wikipedia.org/wiki/QUEL_query_languages http://en.wikipedia.org/wiki/QUEL_query_languages
- fleitz 14y agoCall me when MySQL has a working insert statement. One that doesn't randomly truncate your data, or allow insertion of nulls into not null columns.
- ceejayoz 14y agoYou mean like: SET GLOBAL sql_mode='TRADITIONAL'; ?
- twerquie 14y agoThat's like selling aftermarket airbags for your car.
- ceejayoz 14y agoIt's no more aftermarket than InnoDB support is.
- deleted 14y ago[deleted]
- einhverfr 14y agoBut individual apps can change that on the session level, right? So still no guarantees and you more or less have to audit every app connecting to your db to make sure it isn't tampering with the sql_mode if you value data integrity.
- ceejayoz 14y agoIndividual apps can do far more destructive stupid things than changing sql_mode.
- einhverfr 14y agoThe point though is that you lose a set of guarantees with MySQL that you don't lose with PostgreSQL. Yeah, computers make it possible to make more mistakes faster than any invention since handguns and tequila but when you lose the ability to guarantee that your declarative constraints are actually followed, you lose the ability to prove that certain types of errors are not being made. That's a big deal.
- sciurus 14y agoIf you're evaluating databases based on their replication capabilities, don't stop with what MySQL has built-in. Take a look at http://www.percona.com/live/mysql-conference-2012/sessions/how-evaluate-which-mysql-high-availability-solution-best-suits-you http://www.percona.com/live/mysql-conference-2012/sessions/h... The advice boils down to "You probably want to ignore the built-in replication and use Galera". You can get Galera integrated with an otherwise vanilla MySQL from http://codership.com/products/mysql_galera http://codership.com/products/mysql_galera or integrated with Percona's XtraDB fron http://www.percona.com/software/percona-xtradb-cluster/ http://www.percona.com/software/percona-xtradb-cluster/
- briandear 14y agoWhy bother? PostgreSQL is better.
- sciurus 14y agoPostgreSQL may not be better on all possible points of comparison. PostgreSQL replcation alone isn't comparable to MySQL with Galera. I don't know enough about the various extensions to PostgreSQL to know which ones could give you * Synchronous replication * Active-active multi-master topology * Read and write to any cluster node * Automatic membership control, failed nodes drop from the cluster * Automatic node joining * True parallel replication, on row level * Direct client connections in a reliable and perfomant manner. Keep in mind I'm just atarting to play with Galera, I haven't used it in production yet, but it's making my planned architecure much simpler to manage than the traditional MySQL or PostgreSQL replication approaches.
- baghali 14y agorepmgr[1] is a very good replication option for PostgreSQL [1]https://github.com/2ndQuadrant/repmgr https://github.com/2ndQuadrant/repmgr
- jvdongen 14y agoGood mention. A small correction though: repmgr is not so much an replication option for postgres as well as a handy toolkit for using Postgres's own replication features in a more convenient way.
- zheng 14y agoSlightly OT, but I'm going to reiterate a comment from the older submission, this is one of the best written comparisons of two similar technologies I've ever read. Generally you either get one-sided pieces or supposed "fair fight" pieces which slant the view towards whatever the author has chosen as the better solution for them. Those can be helpful, but something divorced from justifying a decision like this is amazing. So thanks to the writer(s)!
- 3amOpsGuy 14y agoI only skimmed the page, but i didn't find any references or stats to back up any claims in the text?
- nl 14y agoReally? Just about every single claim provides a reference. For example, the sentence MySQL 5.1 natively supports 9 storage engines links directly to the MySQL documentation where they are listed.
- dredmorbius 14y agoAnd the whole wiki (hit the homepage) is comparisons of things. Some are technologies, but it's more than just that. Pretty slick.
- AaronBBrown 14y agoOh bloody hell, yet another PostgreSQL vs MySQL battle ensues.
- sbov 14y agoDepending upon its definition, you can add a column to a PostgreSQL table without locking it. Maybe they changed it in later versions of MySQL, but adding a column to a table become so lengthy for some of our projects that we switched for that reason alone.
- boyter 14y agoIts one of the big reasons I am looking at switching. I have been dumping the table and reloading it in using a new name and the renaming the tables to get around this issue but its hardly ideal.
- AaronBBrown 14y agopt-online-schema-change should resolve that issue for you: http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html http://www.percona.com/doc/percona-toolkit/2.1/pt-online-sch...
- boyter 14y agoSeems the risks are such my approch works just as well, "pt-online-schema-change modifies data and structures. You should be careful with it, and test it before using it in production. You should also ensure that you have recoverable backups before using this tool." With that being the case what im doing is fine I guess.
- AaronBBrown 14y agoBecause there is a disclaimer, you are disregarding a thoroughly tested product that solves your exact problem? One which is released by one of the premier MySQL consultancies with the author of "the" MySQL book as the designer?
- boyter 14y agoI don't pay for support. So yes.
- magoon 14y agoXbox vs Playstation...
- billsix 14y agoJust pipe your data /dev/null to get that web-scale http://www.youtube.com/watch?v=b2F-DItXtZs http://www.youtube.com/watch?v=b2F-DItXtZs
- rburhum 14y agoA huge advantage of PostgreSQL over MySQL is PostGIS (the geospatial extension of PostGIS). I can't believe they don't even mention this.