{"id":1500,"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-4\/"},"modified":"2018-01-19T19:24:50","modified_gmt":"2018-01-19T19:24:50","slug":"mysql-and-postgresql-compared-page-4","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/mysql-and-postgresql-compared-page-4\/","title":{"rendered":"MySQL and PostgreSQL Compared Page 4"},"content":{"rendered":"
\n
\n
By Tim Perdue<\/div>\n
on July 30, 2000<\/div>\n<\/p><\/div>\n
\n

PostgreSQL<\/h2>\n
\nThe results for Postgres might surprise a few people, as Postgres has somewhat
\nof a negative reputation among some web developers (initial releases
\nof Postgres had widely-rumored issues in addition to laggard performance).
\nAccording to my experience, and these benchmarks, most of that reputation is
\nunfounded. In fact, it appears that PostgreSQL withstands up to 3 times the
\nload that MySQL can before throwing any errors — on the same hardware\/OS
\ncombination.<\/div>\n
\nPostgres happily chugs along at roughly 10 pages\/second, enough to serve about
\n400,000 pages\/day, assuming a regular traffic curve with the peak at 2x the
\nbottom. That’s an awful lot of pages and is far beyond what most people will
\nsee on their websites. In addition, most of the pages on your site will
\nnot<\/b> be as complex as the one in this test. As with MySQL, you’ll be happy
\nto pay for a hardware upgrade if you pass this ceiling. Because of Postgres’
\narchitecture, it could probably continue to scale up the more processors and
\nRAM you give it.<\/div>\n

Wins<\/h2>\n
\nWell, postgres has some extremely advances features when shown next to MySQL.
\nWhile I don’t use most of the features myself, they are available for the
\ntruly-hardcore developers out there. Many developers don’t even realize what
\nthey’re missing by not having some of these features available. <\/div>\n
\nAn example of where you should be using a transaction is if you are doing
\nmore than one update\/insert\/delete in a sequence. For instance, your script
\ninserts a new user into your user table, then also inserts a row in another
\ntable, and you update a flag somewhere else. In this case, if the first insert
\nsucceeds, but the second fails, what do you do? With Postgres, you could
\nRollback the entire operation and show an appropriate error. With MySQL,
\nyou would wind up in an invalid state, unless you program in a bunch of
\nlogic to handle the situation. In real-world use, most queries don’t fail
\nunless you’re a lousy programmer, and if the second query did fail, the
\nresults may not be dire (unless we’re talking about an accounting\/banking\/critical
\napplication where there can be no risk of incorrect data).<\/div>\n
\nAnyway, foreign-key support is now in Postgres 7.0+, which means that when you insert
\na row, the database can do some fairly impressive validation checks. Same if
\nyou delete a row – it just plain won’t let you delete a row if another table is
\ndepending on it. I love this idea and can envision rewriting entire websites
\njust to take advantage of this feature.<\/div>\n
\nTriggers and views are interesting and powerful tools that can be used in
\nPostgres, but not MySQL. I haven’t used either one, but I can think of a
\nhundred uses for Views if I were to redesign SourceForge from the ground up on
\nPostgres.<\/div>\n

Limitations<\/h2>\n
\nThe primary limitation with Postgres is not its performance (as most web sites
\nwill never run into that barrier), but hard-coded limits like the
\n8k row size limit (which probably dates back to its earliest days). When I
\ndesigned Geocrawler.com on Postgres, I had to segment large emails into 8k
\nchunks to work around this lame limitation. Also, by default, Postgres is
\ncompiled to only support 32 connections, which is not enough for a high-traffic
\nweb site, especially when you consider that postgres delivers each page much
\nmore slowly than MySQL.<\/div>\n
\nOne other limitation may bug a lot of PHP users – Postgres has no equivalent to
\nMySQL’s mysql_insertid() function call. That is, if you insert a row into a
\nMySQL database, MySQL will hand you back the primary key ID for that row. There
\nis an extremely round-about way of doing this in Postgres, but it’s a headache
\nand is probably slow if used a lot.<\/div>\n

Stability<\/h2>\n
\nPostgres will run smoothly for extended periods of time without trouble. My
\nPostgres 6.5.3 install has run for 90 days without blinking on my tired old
\nPowerMac 8500, while getting about 50-100,000 pages per day. And when postgres
\ngets loaded, it just bogs down, it doesn’t quit and give up the ghost under
\nstress.<\/div>\n
\nThe problem with Postgres is that when you do<\/b> have a problem with it,
\nit’s usually really bad. Like a fubar database file or, more commonly, a
\ncorrupted index (which can frequently be dropped\/rebuilt). I have encountered
\nother serious problems with older versions of postgres (6.4.x) where multiple
\nsame numbers were inserted into a primary key (something that should be
\nimpossible under any circumstance).<\/div>\n
\nThere have also been problems with Postgres where you can wind up with
\n“half-baked” indexes, tables, etc that you cannot drop or get rid of. I have
\nnot seen these yet on Postgres 7, but I haven’t used it enough to know.<\/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<\/a> <\/div>\n
| <\/div>\n
4<\/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 PostgreSQL The results for Postgres might surprise a few people, as Postgres has somewhat of a negative reputation among some web developers (initial releases of Postgres had widely-rumored issues in addition to laggard performance). According to my experience, and these benchmarks, most of… <\/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-1500","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1500","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=1500"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1500\/revisions"}],"predecessor-version":[{"id":3344,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1500\/revisions\/3344"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}