{"id":1498,"date":"2018-01-19T19:22:54","date_gmt":"2018-01-19T19:22:54","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/mysql-and-postgresql-compared-page-3\/"},"modified":"2018-01-19T19:24:50","modified_gmt":"2018-01-19T19:24:50","slug":"mysql-and-postgresql-compared-page-3","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/mysql-and-postgresql-compared-page-3\/","title":{"rendered":"MySQL and PostgreSQL Compared Page 3"},"content":{"rendered":"
\n
\n
By Tim Perdue<\/div>\n
on July 30, 2000<\/div>\n<\/p><\/div>\n
\n

MySQL<\/h2>\n
\nThe numbers for MySQL ring true with what most people already know: it’s a
\nfast, although lightweight database that will probably serve well for the vast
\nmajority of web sites. However, if you plan on having a high-traffic site (say,
\ngreater than 500,000 pages per day), then forget MySQL as it can tend to fold
\nup and die under load. Anyone who has ever visited slashdot can attest to the
\nfragility of its setup (mod_perl and MySQL).<\/div>\n
\nBut again, the vast, vast majority of web sites fall well under the 15-pages
\nper second demonstrated by MySQL here. If you ever surpass a sustained 15
\npages per second,
\nyou’ll be delighted to fork over the cash for a bigger server or an Oracle
\nlicense.<\/div>\n

Wins<\/h2>\n
\nObviously, the advantage MySQL has over Postgres is performance. It also has
\nsome more powerful admin tools included in the distribution (mysqladmin allows
\nyou to watch processes and queries in-progress), like hot backup, a file
\ncorruption recovery tool and a couple others.<\/div>\n
\nI’m also a fan of MySQL’s command-line tools. You can see database and table
\nstructures using describe and show commands. Postgres’ commands are less
\nobvious ( d to show a list of tables for instance).<\/div>\n

Limitations<\/h2>\n
\nThe first thing you hear from hard-core database gurus is that MySQL lacks
\ntransactions, rollbacks, and subselects. You’ll really miss transactions if
\nyou’re trying to write a banking application, accounting application, or trying
\nto maintain some sort of counter that needs to increment linearly over time.
\nForget attempting any of those with released versions of MySQL (it should be
\nnoted that the unstable 3.23.x series of MySQL now includes transaction
\nsupport).<\/div>\n
\nFor many, if not most, web sites out there, MySQL’s limitations can be overcome
\nwith a little elbow grease on the part of the developer. The primary feature
\nyou’ll miss in MySQL is powerful subselect syntax that is present in almost
\nevery other production database. If I had a nickle for every time I could’ve
\nused subselects in MySQL, I’d be able to buy a case or two of beer. In other
\nwords, this missing feature can be a pain in the neck, but it can be overcome.<\/div>\n

Stability<\/h2>\n
\nMySQL loses points in the long-term stability department. Simply put, MySQL
\ngives up the ghost randomly and for no obvious reason after running for
\nsemi-long periods of time (say 30-60 days). Many developers will compile MySQL
\n“statically” for just that reason, and doing so has helped some people.<\/div>\n
\nThat problem again can be overcome with a good pager or a simple crontab entry
\nthat kills and restarts MySQL monthly. Not that I find that at all acceptable,
\nbut it is a solution.<\/div>\n
\nWhere MySQL loses points in the daemon robustness department, it makes up for
\nit by apparently never corrupting its data files. The last thing you want is
\nyour precious data files fouled randomly, and MySQL does well here. In over a
\nyear of running MySQL, I haven’t ever seen a single case of database
\nor index corruption. In the same timeframe, I have done 2 or 3 recoveries of a
\ncouple different Postgres databases. (Regardless, backups are always your
\nbest friend, as shown by the database fiasco here on PHPBuilder.)<\/div>\n<\/div>\n

<\/p>\n

\n
\u00ab Previous Page<\/a><\/div>\n
1<\/a> <\/div>\n
| <\/div>\n
2<\/a> <\/div>\n
| <\/div>\n
3<\/div>\n
| <\/div>\n
4<\/a> <\/div>\n
| <\/div>\n
5<\/a> <\/div>\n
Next Page \u00bb<\/a><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

By Tim Perdue on July 30, 2000 MySQL The numbers for MySQL ring true with what most people already know: it’s a fast, although lightweight database that will probably serve well for the vast majority of web sites. However, if you plan on having a high-traffic site (say, greater than… <\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1498","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1498","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/comments?post=1498"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1498\/revisions"}],"predecessor-version":[{"id":3342,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1498\/revisions\/3342"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}