mysql and postgresql

roach

Im curently building a program that is using MySQL, it seems that every thing I need to do...triggers, row level locking, nested and sub queries etc...MySQL does not support.

Im now looking into going to Postgresql; it supports all these features. But, why would such a popular DB like Mysql not support some important features? Is Mysql not up to par with other DB'S?

Can someone give me the scoop on this, please. =)

-r

Sxooter

MySQL was designed as a fast data storage system for things like web pages and document management.

Postgresql descends from Ingress, a university project that was designed to be a transactional database from the beginning.

They had different objectives, so they have evolved in fairly different ways.

While transactions, triggers, rules, constraints et. al. are add ons that are still in the making for MySQL, they are core to the design of Postgresql.

They both have their places, but Postgresql is a better choice for financial / transactional databases.

MySQL is much easier to install and admin, so it has a solid advantage in deployment on things like free or nearly free public hosting sites. Postgresql is more commonly seen in corporate intranets and backend projects where the hosting is done internally.

[deleted]

"But, why would such a popular DB like Mysql not support some important features?"

MySQL started life as a small en fast alternative to all the clunky and expensive databases like oracle.

Unfortunately their efforts soon turned to just making their database faster instead of making it better. (their todo-list just grows, they only recently implemented row-level locking and UNION)

If you surf around their website all they mention is that mysql is fast. They even go so far as to say that all the functions, that you and I and probably the rest of the database-using world find so important, really only slow the database down and should therefore not be implemented. Consequently mysql is lightning-fast, but cannot even perform a simple sub-query.

MySQL is very popular mainly due to it's marketing campaign that cleverly targets those users who are clueless about what real databases can do.
For those people, speed is all that matters, just like so many people use windows because it is convenient, not because it is good.

"Is Mysql not up to par with other DB'S?"

'par' is just a glowing dot on MySQL's horizon, they are nowhere near it 🙂

MySQL know this, and will always fallback on their default arguments: MySQL is fast and MySQL is simple, and you don't need all that fancy stuff.

And MySQL really is fast, but only because it is so limited. It can't do anything and so it doesn't have to do anything. And the less work you have to do, the faster you can be at doing it. Ofcourse the work still has to be done and if you can't do it in the database you'll have to do it in your application, so your application just gets larger and slower.

Unfortunately most people have no clue about what real databases can do, and how much work and code it saves you.

Things like true PK/FK relations with cascading delete, transactions, triggers, functions, stored procedures, rules, checks, functional indexes, partial indexes...

Ofcourse there will be peope who say: "I don't need all of those", but those are usually also the people who have never had the pleasure of working with those features.

jeremuck

I've heard arguments that it's actually better to do the advanced database stuff in your app logic. Generally people share the database resources, whereas client resources are in less demand.

I'd be interested to see performance comparisons betwen faster database speed/ more app logic and database logic/ faster app speed.
If it's pretty much the same, I guess it's better to use the database.

MySQL doesn't fully support the SQL standard, correct? Does postgresql?

[deleted]

"MySQL is much easier to install and admin,"

That is one of mysql's arguments too, but what exactly is the difficult bit in installing postgresql compared to installing mysql?

Both databases are installed exactly the same way (from RPM or sourcecode the operations are the same) and both databases require you to execute one command to create initialize the tablespaces.

Once that is done there really is nothing extra to do for postgresql nor for mysql.

MySQL has administratoon tools, so does postgres
MySQL uses the GRANT syntax, so does postgres
MySQL has a commandline tool, so does postgres
MYSQL has mysqldump, postgres has pg_dump

The only drawback about postgresql compared to mysql is that postgres does not have a native windows build. Installing under windows requires that you use cygwin, which doesn't make it much more difficult, but does require extra work.

Sxooter

My experience with both (I'll admit I haven't used MySQL much in recent months) has been that Postgresql is easy to install, but it isn't usually "ready to go" when first installed. Most installations needs tweaking of the settings for things like sort memory, shared buffers etc... to get decent performance.

Postgresql needs regular vacuuming or it slows to a crawl and fills up disk space. It looks like with 7.4 there may be an auto-vacuum daemon as well as a native windows support.

MySQL also has finer grained security than Postgresql (i.e. table creation rights, etc.) but Postgresql 7.3 is introducing both schemas and a finer grained security mechanism, so that argument goes away.

For a long time, Postgresql WAS a harder install than MySQL, at least from source. When I first started running it, you had to create the destination directories as root, assign ownership to the source directory to postgresql, and build and install as the postgres super user. I think that requirement went away with 7.0.x or thereabouts.

With RedHat, the install of either is pretty darned easy, as you just install the RPMs and go from there.

To me, the difference between Postgresql and MySQL is that of a train (postgresql) and a pickup truck (MySQL). The pickup truck is great for running around town moving small loads, and the train is what you use for moving tonnes of stuff.

Sxooter

Originally posted by jeremuck
I've heard arguments that it's actually better to do the advanced database stuff in your app logic. Generally people share the database resources, whereas client resources are in less demand.

I'd be interested to see performance comparisons betwen faster database speed/ more app logic and database logic/ faster app speed.
If it's pretty much the same, I guess it's better to use the database.

MySQL doesn't fully support the SQL standard, correct? Does postgresql?

There are three issues here that are seperate from each other.

One is whether it is faster to do your advanced stuff in your app code or in the database, the next is whether or not you should let the database maintain data integrity by doing letting it do the advanced stuff.

The last is how well your database can scale under parallel load.

If the database has a procedural language that's as fast as your application language, then letting the database do the advanced work should be fasters, since the database can optimize it's access, and not have to transfer data to and from a client app for processing.

This is true because the database sits right on top of the data.

But more important is the issue of handling data integrity rules in app code versus in the database via constraints rules and triggers.

Imagine coding your sequences by hand. Maintaining the integrity of your data set should be the job of your database, PERIOD. If you try to do it in code, you HAVE to do it on a transactional system, and the overhead of locking and unlocking required to ensure your integrity can slow down a database quite quickly.

Which brings us to scaling to handle parallel load. Anytime you're writing a database driven app (or any app really) you should be asking yourself "What will happen if two or more people do this at the same time?"

MySQL seems to ignore that case, and focus on the high speed single user batch processing methodology.

In the immortal words of one of the core PGSQL developers, "Monty (the primary MySQL developer) just doesn't get it."