MySQL v. PostgreSQL

[cooop]

I realize that I may be inviting a flamewar here, but can anyone recommend one RDBMS over the other? Specifically, I am currently designing a social networking Web application a la Facebook (or MySpace if you prefer), and I need to prepare for hundreds of thousands of registered users/connections. From what I gather, MySQL is generally perceived as faster while PostgreSQL is thought to be more feature-rich and stable, although both are making up for their respective shortcomings. Can anyone endorse this quote I came across (http://www.osdbmigration.org:8080/osdb/performance/mysql_pg_performance): "...In general, MySQL will outperform when there are simple queries and few readers/writers. PostgreSQL will outperform under more demanding workloads." If this is the case, would it make more sense to go with PostgreSQL? Thanks in advance.

[Brad]

Anecdotal: Everyone in the professional community I've asked about this, from university professors to the lead engineer where I work, has highly recommended PostgreSQL over MySQL. The strong consensus is pretty amazing. All of these systems they've worked with, however, are moderately-to-very large and complex.

That said, I'm not versed enough with their respective features to give an objective analysis. I personally like PostgreSQL because I find its CLI easier to work with, but that's sure to vary by individual.

[ShadowOfGed]

Anecdotal: Everyone in the professional community I've asked about this, from university professors to the lead engineer where I work, has highly recommended PostgreSQL over MySQL. The strong consensus is pretty amazing. All of these systems they've worked with, however, are moderately-to-very large and complex.

That said, I'm not versed enough with their respective features to give an objective analysis. I personally like PostgreSQL because I find its CLI easier to work with, but that's sure to vary by individual.I'll chime in.

I strongly recommend PostgreSQL over MySQL for features. The only thing that MySQL does in a simpler way than PostgreSQL is full-text indexing. It's possible in PostgreSQL, but I've never tried to wrap my head around it (the add-on is called tsearch2, I believe). I'm working an internship this summer, and part of my project involves using a database. I started with MySQL because, well, it's what everyone uses at my company. My personal website is run on PostgreSQL, but I created that ages ago, so I thought I would give MySQL a shot again.

Well, that was a mistake. I'm back on PostgreSQL.

The reason? Look carefully through the MySQL manual, and you'll find absolute *gems* like this:
The CHECK clause is parsed but ignored…
And as if that weren't bad enough, you'll find elsewhere that if you use any storage engine other than InnoDB, which includes the default MyISAM, there's a nice note about foreign key constraints:
MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements.
EXCUSE ME?!

:mad: :eek: :no:

For a widely-deployed enterprise database, I am flabbergasted that these two *DATA INTEGRITY* features are left unimplemented in MySQL. If you look around their forums, people will say you can use triggers to implement CHECK constraints (and FOREIGN KEY constraints if you so desire), but compared to a native implementation, the performance hit is likely significant.

I found that if you poke deep into the MySQL database and its documentation, it seems to reek of an open-source project that adds features rapidly to look good, but never completely implements them. After finding those two gems, I just couldn't convince myself to trust any other purported features of MySQL to be fully or accurately implemented. What's the use in a thousand storage engines if your default engine doesn't allow you to fully implement SQL constraints?

Use PostgreSQL. Everything it claims to implement seems to be implemented. Also, with version 8 you can get point-in-time backup and recovery with minimal additional configuration. PostgreSQL seems more solid than MySQL, at least from what I've seen and read.

I'm done ranting.

:cancer:

[Gargoyle]

MySQL gives you plenty of options so it is more flexible. As ShadowOfGed pointed out, some aspects are implemented in some storage engines and not others. If you need Transactions and full data integrity, then you will have to use the InnoDB storage engine and take a hit on performance. If however, your application does not need transactions, then you will see very impressive speeds from the default MyISAM storage.

[thuh Freak]

theres a lot of non-compliance to sql in mysql. see here (http://sql-info.de/mysql/gotchas.html). but to be fair, postgresql doesn't fully comply (http://sql-info.de/postgresql/postgres-gotchas.html) either. imo, postgres' issues are less severe. i generally recommend it over mysql, and its my personal fav f/oss rdbms. wrt performance, i haven't read too much about that.

[cmeyer42]

I'm glad that this thread has started. It enables me to ask this question (no flames, PLEASE!!!) -- which of these (my.. , post...) is a more compatable "replacement" for MS Access? I ask this as I will have to take a DB course in college, and the professor is a MS fan, so this looks like a good time to ask. I don't want to have to install virtual pc to install access for commands that are "MS only".

[Banana]

That's easy.

Anything but Access. :D

Seriously though, I develop database using Access on the sideline at my work, and have always thought that I'd be better off with Filemaker Pro or other programs.

And I haven't even touched Filemaker before!

[colivigan]

which of these (my.. , post...) is a more compatable "replacement" for MS Access?
I don't think you can really compare them.

Sure, they're all "relational databases". But look at it this way. MS Access is a Cooper Mini with a fancy paint job, chrome bumpers, and fuzzy dice hanging from the rear-view mirror. MySQL and PostgreSQL are jet engines with no airframe.

From a database perspective, either one should be able to easily (with both hands tied behind its back) match anything that MS Access can do and more. But Access does have plenty of bells and whistles that you won't find in the dedicated database engines. You may have to add other tools.

[Banana]

And I recall some talk about using MS Access as front end to MySQL, so as long you know SQL, I'd imagine you can have near data interoperatability?

I've thought that MySQL was a fully packaged database, but I've never actually seen MySQL before, so I have no idea what their interface is like, though.

[rollercoaster375]

And I recall some talk about using MS Access as front end to MySQL, so as long you know SQL, I'd imagine you can have near data interoperatability?

I've thought that MySQL was a fully packeaged database, but I've never actually seen MySQL before, so I have no idea what their interface is like, though.
As with most Databases, it's CLI.

[thuh Freak]

mysql and postgresql are closer to microsoft sql server than to ms access. the database is hidden in a background process (often called a daemon in *nix). the user interface is a separate program from the actual database itself (as opposed to ms access, where data and UI is contained in the MDB file).

mysql and postgresql don't do vba, which is hwo you'd write access' ui. a lot of stuff is convertable, like tables and queries, but the UI type stuff is a different beast. there are front end/user interface tools for mysql and postgres, but you're probably not going to find some easy conversion tool.

there are also minor differences in sql implementation and in adherence to the standards.

[nassau]

so, how would one go about changing from MySQL to PgSQL? is it fairly easy to port the databse content and change the coding in (in my case) PHP?

can i simply replace mysql_fetch_assoc with pg_fetch_assoc etc and change the query formatting (if needed..) etc...?

[nassau]

anyone have any insight on my question right above this...?

[Brad]

It would require rewriting probably most of your queries, but it's possible. :p It really depends on the type and complexity of the queries you're using.

[nassau]

well, my queries use a customized template, so i'd only have to re-write the template in that case.

is the phpPGadmin similar to phpMyAdmin? i wasnät able to demo it on their website..

[JackBauer]

I figured I should chime in here. The first 3 replies provide good information on "real world" usage. I work as a consultant for a large defense company and for many of the reasons the first 3 replies list, this company would never use MySQL. If they wanted to use an open source DB it would definitely be Postgres. However, the IT department uses MySQL internally to store data that only their eyes would see and it's a fairly quick and dirty application that uses it.