MySQL vs PostgreSQL

[vol7ron]

I think a lot of users aren't familiar with Postgres, which is why I see a lack of use. -- That and turnkey web solutions seem to default to the PHP/MySQL combination, which users don't know or want to modify.

While I can't do anything about #2 for those that don't know much about editing their applications, I can address the unfamiliarity.

There are three good sites I think people might be interested in:
» Pro PostgreSQL over MySQL
» Wiki - MySQL vs PostgreSQL
» Benchmark comparison of MySQL vs PostgreSQL


Obviously, because the first link is a site that is for PostgreSQL, you may think that it'd be a little bias about PostgreSQL's capabilities over MySQL's; however, after reading the article, I can't say that for sure. It addresses both MySQL's and PostgreSQL's benefits over one another. The truest statement was probably in saying: "Benchmarks are very difficult to do well; creating truly comparable benchmarks is a complex art." They also address that the only true benchmarks is to have both databases optimized and tuned. Regardless, the better the hardware you throw at the matter, the better PostgreSQL results, unlike MySQL which doesn't scale nicely.

The Wiki is a little less biased and even still, seems to proclaim Postgres as being the better option. The third link was actually taken from the Wiki page, I just wanted to make sure people saw this. One thing to pay attention to is the numbers on the graph, even though MySQL seems to degrade over connections, it seems like the numbers are still higher than Postgres's -- I didn't read the 3rd article fully for lack of time.

Final thought: Old tests showed that MySQL was the faster database, but since then users noticed that Postgres had pessimistic configurations and when optimized it performed better (the latest versions of PgSQL is a little more optimistic). Additionally, the data integrity of Postgres is much better due to the preprocessing, unlike MySQL where data problems do exist.


Hope this helps,
vol7ron :Þ


Ü




Edit:
I suppose either no one cares, or no one is reading or thinks anything about the links.

What it comes down to is Pg is faster, more reliable, more feature rich, and open sourced with regular updates and new features.

All-in-all, even if you use MySQL predominantly, I'm hoping to spark some interest for users to start playing around with Postgres and stored procedures. If x10 installs the newest version, 8.3, then you will be happy to see Full Text Search capabilities installed with it.

[port5900]

I was looking at PostgreSQL just for the hell of using and learning to use it. It does not seem to be such a huge switch from mySOL to PostgreSQL. But the 1 problem and onlymproblem I had was,..... no web host I used supported PostgreSQL. and thanks for the documentation, i did read it.

[vol7ron]

...no web host I used supported PostgreSQL

That is what drove me to x10, because I, like you, could not find one. Now I've found several, but I've grown fond of the owner and the support staff here, so how could I leave?

Part of the reason I posted some of those links is because I've read a lot of comparisons between the two, but the comparisons were between like Pg 7 and I think a beta version of MySQL 4.x or something.

Regardless, both have come a long way. But in my opinion, MySQL is more of an MS Access type of database, while Postgres is more of a SQL Server. There is this free software from ESM called SQL Mgmt Studio Lite (the Lite means less features and free), but it still is great and reminds me of using Oracle again. I was even able to produce the SQL for my tables again, once I had them constructed (called: reverse-engineering).

I came from a MS world, using Oracle and coding C++ and then the .NETs, and then I was forced to use Linux and Perl at work. At first I dreaded it, but once I realized I started using Firefox more than IE, I was hooked. Perl became my primary web language and Pg my favored free database, not because I use them now (cuz the tools to use the others are there), but because they actually are more beneficial and I was just oblivious to that before.

[hezuo]

Thanks a lot for sharing. I'm a newbie and this kind of discussion always help me find out more about what bd engine i should use. I've always been told that PgSQL is better, but i have no idea the difference depends on so many factors. Thanks for the share!

[vol7ron]

Not a problem. We here don't have much control over the database configurations themselves, but there are so many settings to tweak (kind of like a computer) to speed it up if we did. Most of them involve how big the storage size is for memory calls. You don't need a big block size if your database is all small integers.

Although we can't really optimize the backend of the database, there are other feature that give Pg a big edge. One that stands out is it's stored procedure feature, which allows you to pretty much code programs into the database. This is good because those functions you do all the time perform faster. These functions might involve querying/joining two tables, or changing the format of a date, or producing some sort of special ID. The sky is the limit.

Just a forewarning, make sure to VACUUM Postgres regularly. This is a good feature that reduces the bloat, reindexes, and partially auto-optimizes the database. Users here at x10 have the ability to VACUUM their databases. One time a query started taking 15-20secs to perform and I realized I hadn't VACUUMed it in almost half a year; after I did, everything was running instantly. Things like that happen to MySQL too, but users have no way to clean it up, unless they create new tables and dump the data into them -- this is a problem for auto_incrementing field, which you don't have control over.

[LHVWB]

Interesting, I have personally always used MySQL, but I looked up PostgreSQL when I x10 started supporting it. To see if there was any point, Most things I read said PostgreSQL was slower but had better features, so I didn't see any point in changing. Although I use SMF 2, and integrate all my scripts into that, its supports MySQL and PostgreSQL so I could change over fairly easily.

Do you know exactly which version of PostgreSQL x10 is running? If its 7.x then the MySQL might be faster anyway.

[vol7ron]

x10 is running 8.1 I believe. I'm not exactly sure what version of Linux they're running, but I know PostgreSQL 8.1 is part of the Red Hat package. And if you go into Postgres, in the SQL interface you can just type "Select version();" and it'll pop up the version

I'm trying to get them to upgrade to 8.3. Both 8.2/8.3 have made some changes since 8.1, but the most notable is the Full Text Search capability in 8.3. Basically, it gives you a platform to store parsed text documents (.xml, .html, .txt, possibly even PDFs) and the ability to search the database.

Essentially it is a preconstructed search structure for you, much like how Google stores its data. It even comes with canonical libraries - the part of the algorithm that says when someone types "whisperers" it'll find "whisper and whisperer" and maybe even whisp. It also has libraries of words that shouldn't be matched (if you choose) like "the, a, is, be, etc." I'm particularly interested in this because I would like to enable an advanced search function for my site that doesn't rely on flat text files, or a third party engine like Google -- despite the stock ownership I have in the company ;)


----------------------------------------

Another thing that people may not know is that PostgreSQL was originally designed by the makers of the Ingres database. Ingres had commercial and government support and after it's rise it gave way to Sybase and MS SQL Server, as well as a few other hatchlings.

Well another project that the owner of Ingres started was PostgreSQL, which is kind of how it got it's name: post-Ingres => Postgres. Postgres was the original name, then they tacked on the SQL part just for familiarity and to rival MySQL.

-- just a few tidbits of useless information --
I actually appreciated learning this, because I had heard about Ingres before because it used to be big commercially and sectors of the govt even used it - Postgres, on the other hand, seemed new to me when I first learned of it and I didn't know if Postgres could be trusted. After learning about the affiliation between the two, that's when I think I actually gave Postgres a shot.

[vol7ron]

Here's also a helpful link for the PHP programmers:

http://www.onlamp.com/pub/a/onlamp/2...ostgresql.html

[thaob]

thanks for the info. I'm a newbie. I'm not familiar with PostgreSQL, but I will try it.

[anotherdv]

I am glad to some of you talking about PostgreSQL. I have been using PostgreSQL personally for about 5 years. And finally I am using it professionally. I have been using Oracle for over 12 years. I read an article once that said PostgreSQL was the open source version of Oracle. That was a funny statement, but I understand where it was coming from. The database features of tablespaces, archive logs, schemas, table partitioning, on-line backups, exporting and importing, stored procedures & triggers, two phase commit, declarative referential integrity, and it adherence to the SQL standard have really made it a candidate for an enterprise class database. Yes, MySQL has grown as well and if you use the InnoDB storage engine for your tables, then you get a database that can actually be reliable.

What will be interesting will be what happens to MySQL with Oracle's acquisition of Sun, who bought MySQL in 2008. But then of course InnoDB has been owned by Oracle for the last three years anyway.