{"id":1496,"date":"2018-01-19T19:22:54","date_gmt":"2018-01-19T19:22:54","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/mysql-and-postgresql-compared\/"},"modified":"2018-01-19T19:24:50","modified_gmt":"2018-01-19T19:24:50","slug":"mysql-and-postgresql-compared","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/mysql-and-postgresql-compared\/","title":{"rendered":"MySQL and PostgreSQL Compared"},"content":{"rendered":"
\n
\n
By Tim Perdue<\/div>\n
on July 30, 2000<\/div>\n<\/p><\/div>\n
\"picture<\/p>\n
\nWhich database do I use: Postgres or MySQL? This age-old question has plagued
\ndevelopers for, what, at least a couple years now. I’ve used both databases
\nextensively (MySQL for about one year and Postgres for about 2 years) and was
\ncurious if the performance differences between the two were as stark as the
\nMySQL website suggests.<\/div>\n
\nI had actually benchmarked the two databases back in September 1999 when we
\nwere starting to lay the groundwork for SourceForge. At the time, the
\nperformance difference was so stark that we had to go with MySQL even though I
\nhad always used Postgres for all my work. The rest of the developers were used
\nto MySQL and that pretty much cinched the decision.<\/div>\n
\nThis time around, rather than using some contrived benchmarking scheme, I
\nwanted to grab a “real life” web page from a “real” web site and see how it
\nperformed on the two databases. The page in question was the discussion forum
\non SourceForge. It involves some relatively straightforward joins of three
\ntables, each with 20-30,000 rows of data. It also involves some recursive
\nqueries to show nested messages, so the database is the true bottleneck on this
\npage, not PHP.<\/div>\n
\nTo get started, I dumped real data out of the production database, modified the
\ntable SQL and imported it all into MySQL 3.22.30 and PostgreSQL 7.0.2 on Red
\nHat Linux 6.2 and a VA Linux quad-xeon 4100 server with 1GB RAM.<\/div>\n
\nThe first problem I ran into was that Postgres has an arcane limit of 8k of data
\nper row. In a message board, you’re going to occasionally surpass 8k of data
\nin a row, and so postgres choked on the import. To get around this, I just
\ndropped out the “body” of the message and re-imported the data. The Postgres
\ndevelopment team is aware of this limitation and are fixing it in v7.1, and
\nthey also noted that you can recompile Postgres to support up to 32k,
\nalthough at a possible detriment to overall performance.<\/div>\n
\nAt this point, I ran into another small issue with Postgres – its “serial” data type (the
\nequivalent of MySQL’s auto_increment) creates a “sequence” which does not get
\ndropped when its parent table is dropped. So if you try to re-create the table,
\nyou’ll get a name conflict for this sequence. A lot of new users would be
\nconfused by this, so Postgres loses a couple points for that. Also, MySQL is
\n“smart” enough to increment its auto_increment value when you import data,
\nwhereas Postgres’ sequence does not get reset when you import data, causing all
\nnew inserts to fail.<\/div>\n<\/div>\n

<\/p>\n

\n
1<\/div>\n
| <\/div>\n
2<\/a> <\/div>\n
| <\/div>\n
3<\/a> <\/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 Which database do I use: Postgres or MySQL? This age-old question has plagued developers for, what, at least a couple years now. I’ve used both databases extensively (MySQL for about one year and Postgres for about 2 years) and was curious if the… <\/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-1496","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1496","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=1496"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1496\/revisions"}],"predecessor-version":[{"id":2254,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1496\/revisions\/2254"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1496"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1496"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}