Slashdot Log In
MySQL And PostgreSQL Compared
Posted by
CmdrTaco
on Sun Jul 09, 2000 09:06 AM
from the allright-let's-take-this-one-on dept.
from the allright-let's-take-this-one-on dept.
unicron writes: "PHPBuilder has got an article MySQL and PostgreSQL Compared. " Everyone who has used these DBs knows the differences between them, and now that licensing isn't one of them, let's try to talk about where each excels and the other fails. I know people get almost as religious about their DBs as they do about OSs and programming languages, but let's try to get somewhere here and not just needlessly flame and rant, mmkay?
This discussion has been archived.
No new comments can be posted.
MySQL and PostgreSQL Compared
|
Log In/Create an Account
| Top
| 147 comments
(Spill at 50!) | Index Only
| Search Discussion
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
(1)
|
2
(1)
|
2
Tool for the Job (Score:5)
Compared to DBase III, both of these tools are excellent. I wish I'd had either one a decade ago when I started work doing clipper programming for a dog track related publishing company.
For the dog track application I would have preferred Postgres; the rollback support would be pretty compelling for an application like the one we were doing. For something where I was just kicking around a database (Which I've also done a lot of) MySQL would be perfect. MySQL would be ideal in something like the RHS Orchid Registry, for instance.
If application bigotry keeps you from choosing the right tool for a job, you will be a less valuable resource to those who employ you. Not too many people seem to "Get" this. People are often surprised that I will, on occasion, suggest that Microsoft products are the best tool for what they're trying to do. Usually those people asked me expecting a "Windows sucks use Linux" spiel, but if I think their situation warrants it (Inexperienced user, just wants to browse the web, word process and send E-Mail or wants to play games at all) I'll tell them to use Windows.
Re:Diferences? (Score:4)
These aren't trivial weaknesses, and says nothing about syntactical differences between MySQL and ANSI SQL 92. I use these features everyday at work. To impliment them in code is possible but would be a royal pain. TRANSACTIONS, for example, can be worked around by creating duplicates of tables and batch processing into them. If all the updates on all the tables goes ok then copy the dups over the original tables. Not practical if the original tables are huge, but can be done at night after backups are done.
It also listed functions which 'exist' only for compatibility (i.e., not functional)
After that list of don'ts I decided to check PostgreSQL, especially since TRANSACTIONS was missing and MySQL said it would require a 'completely different table layout than *MySQL* uses today.' I understand that they have employed a wrapper technique to supply a 'TRANSACTION'-like functionality. I've had experiences with extending fuctionality with wrappers and they are generally not a stable or acceptable solution.
PostgreSQL has everything MySQL is missing. It has a very powerful set of operators (std, numerical, time-interval, geometric) which can be evoked by name. It also has a similar set of fuctions which can be used to increase the functionality of the operators. Postgres alows attributes of an instance to be defined as fixed-length or variable-length multi-dimenstional arrays, either of base type or user-defined types. (Not exactly like Pick's AMV, but close). I seldom see folks mention a powerful feature of PostgreSQL that MySQL doesn't have - Inheritance.
From their documentation:
That's why I chose to PostgreSQL over MySQL.
Re:You know... (Score:5)
- He gave a very vague description of the layout of his database and the nature of the queries. Without that, it is impossible to reproduce his results.
- He did not describe his client/driver configuration, for all we know, they could have been programs running on the same machine as the DB server
- For database benchmarks such as TPC-C (you can see the stringent reporting specifications at www.tpc.org [tpc.org]) it is common to allow the driver program to warm up the database for a long amount of time (45 - 60 minutes seems to be common now, depending on the size of the database) then allow the database to reach steady state after at least 20 minutes. He does not mention whether he gave time for the database to stablize. Transient numbers are not a good indication of actual system performance.
- His reported statistics are lacking. Saying that PostgreSQL is 2 - 3 times faster than MySQL leaves a 50% uncertainty in his measurements. At times, he reports 2 - 3, and others he reports simply 3. In fact, I don't see any solid measured numbers for queries/second, transactions a second or some other metric.
Granted, most websites running either of these software packages are probably not going to see very heavy loads, but if you're deciding between two vendors, it's good to know exactly *what* you are comparing.Re:Flame (Score:5)
Reason to avoid MySQL. (Score:3)
Re:8K limit ? (Score:5)
> my sites all have whole articles, pictures, etc... into a MySQL database.
(Warning: DBA in a previous life - expect pain ;)
I really wouldn't suggest placing text articles, images or other large objects into a database tuple. Modern databases (including PostgreSQL; not sure about MySQL) have support for 'BLOBs' (Binary Large Objects), which allow you to associate very large files and objects with a particular tuple.
The BLOBs are stored as part of the database, but not within the relation itself. This works a *lot* faster than putting the images or text inline with the tuple, and is I suspect partly why the folks at PostgreSQL haven't yet fixed the 8kb limit.
He missed Stored Procedures! (Score:5)
The reviewer mentioned Postgres triggers, but didn't make it clear that triggers use a more general feature called stored procedures.
By coding lots of low-level functions as stored procedures that are stored _inside_ the database I can drastically reduce the number of database calls my application has to make.
The benchmarks he used can't show this (because he said himself that he doesn't use these features) but proper use of stored procedures should improve performance drastically while simplifying application code.
Re:Diferences? (Score:4)
Atomicity:
This means that transactions are either fully completed or never begun at all. Any updates that a transaction might make on a system are completed in its entirety. If any error ccurs during the transaction keeping it from completing all its steps then the DB is rolled back to its previous state before the transaction began. E.g. Let's say a transaction consists of money being removed from a checking account and stored in a savings account. If there is an error after the money is removed from the checking account but not stored in the savings account then the transaction can be rolled back to when the money was still in the checking account.
Consistency:
This means that the DB is always in a valid state after a transaction. Thus if an error occurs during a transaction, the DB can be rolled back to the last valid state. E.g. From the above example the total amount of money in both accounts is constant and is equal to the sum of both amounts. If an error occurs while transferring money from the checking account to the savings account then the total of both accounts is not consistent with the amounts actually in the accounts. This inconsistency is handled by rolling back the DB to the last consistent state.
Isolation:
Each transaction appears to the only one being carried out by the system at that time. If there are two transactions both performing the same function and both running at the same time they will be invisible to each other. This ensures that the system is consistent because if transactions do not run in isolation the they may access data from the DB that is inconsistent due to the fact that some other transaction is in the middle of performing its tasks. E.g. if my paycheck is being deposited in my checking account at the same time as when I am viewing an account summary I will not be able to see the changes being made to my account until the deposit transaction is completed. This stops unsavory things like seeing the total amount in the account remaining constant while the recent deposits field may already contain the deposit.
Durability:
Once changes have een made to the DB they are permanent. There are safeguards that will prevent loss of information even in the case of system failure. By logging the steps that each transaction takes the state of the system can be recreated even if the hardware has failed.
Here's an article [openacs.org] that explains why mySQL is not a real RDBMS since it's support for transactions are lacking. PostrgreSQL on the other hand supports transactions. For instance, people who use a DB that supports transactions don't have to lock the tables themselves when accessing the DB from their code instead stuff like that is handled by the DB.
PS: Also not all of SQL is supported by mySQL (e.g. Foreign Keys for specifying integrity constraints).
Re:You obviously missed the point (Score:3)
But the article itself discussed some of the more advanced database features provided by Postgres, which to me implies that it was addressing a wider audience than just the guestbook-writer's guild.
My real point is just that once you get beyond a database with just a few tables, the benefits provided by a commercial database are probably worth the money, even if you're not a mission-critical enterprise systems developer. However, I grant that you'd want something a bit cheaper than Oracle, which is why I mentioned SQL Anywhere.
My other point is that I suspect a lot of people who pick MySQL or Postgre because they're the only free/open tools available, and try to use them for something more than just a guestbook, may not realize what they're missing and how much unnecessary work they have to do just to take care of basic database plumbing issues and error handling.
In fact, the original post I replied to mentioned dBASE/Clipper, which is what really prompted me to respond, because I remember all too well having to deal with some of that basic plumbing with those products. I don't doubt that MySQL and Postgres are an improvement over xBase, but some of the limitations mentioned in the article are serious ones, and will mean that developers have to do more work than they otherwise would to get an error-free and stable system.
Re:You obviously missed the point (Score:3)
If I want to code a guestbook for a small website, create an on-line shop for the fanclub our obscure little rockband, I don't need all the extras of a commercial RDBMS. :p ) - But you can't expect me to be happy to pay big $$$ if I don't need that stuff.
Last time I worked with oracle, the actual cost of getting the oracle licenses in place was completely insane. But that cost was justified, because we needed our app to be entreprise-ready (but God knows it'll be DB2 next time such a project comes along
You missed the point, no more, no less :-) (nothing personal intenden ;-)
Okay... I'll do the stupid things first, then you shy people follow.
Re:You know... (Score:3)
A database that has been open and running for 2 minutes is not the same as a database that has been up for 30 days (the latter has been warmed up). It is how the server will actually be operating for a majority of its use.
The warm up is not, however, something that you would do when you start up the server processes. It's simply to get the system in a state like it would be in when you have it running for a long time. We're talking about two different things. :)
both have serious limitations (Score:3)
Nonsense. Many applications (including banking applications) that theoretically would be well served by transactions still run largely non-transactioned and use proven real-life approaches instead (transaction limits, nightly reconciliation) because trying to handle everything with transactions would create a huge bottleneck. As for data loss, that is more of an issue with database logging and replication, which you can have without having transactions.
Both MySQL and PostgreSQL have their strong points and their weak points. I think I have a slight preference for MySQL, not in spite of, but because of, its limited feature set; it keeps people from blindly using performance killing features that most applications just don't need; full SQL just makes it too easy to write something that will bring the whole database to a grinding halt.
Ultimately, the argument between MySQL and PostgreSQL is missing the point. The current crop of SQL-based databases (this includes Oracle and DB2) are just awful for modern applications: their APIs and data models are a poor match to what we really need (SQL was originally designed to allow managers to generate reports easily). The widespread use of "stored procedures" and various "object" features is a clear indication of that. Some alternatives are on the horizon. For now, we'll have to make do with what we have, I guess.
Re:You got it wrong! +3 Insighful?? (Score:3)
You can't just add transactions as an afterthought. MySQL just wrapped sleepycat with a SQL front end and called it transactions. So technically it is, but you'd be insane to use it in any serious setting.
sleepycat is not a good thing to build a client-server RDBMS on. (this isn't to say it doesn't have it's uses. sleepycat is great for simple inproc data storage. but this is going to be a disaster).
mysql was not designed for this kind of application, and it shows. mysql needs a complete redesign before it will be remotely competitive with commercial databases.
Transactions support (Score:3)
Even though Sybase hasn't open-sourced their Sybase-SQL server and Adaptive Server Enterprise (and they probably never will), I prefer using Sybase over both PostgresSQL and MySQL. Sybase SQL server 11.0.3 on Linux [sybase.com] is free for commercial use.
and no, I'm not affiliated, etc...I just like 'em
---
Interested in the Colorado Lottery?
Reliability versus features (Score:3)
This review also mentions the admin tools in passing. MySQL's backup strategy is very easy to explain to non-database guru's. Try explaining how to restore an Oracle backup to a non-DBA... You can even get a poor mans roll-forward working on MySQL and be able to explain how it works.
Operational ease is always my first concern. As long as you keep in mind MySQL is a poor mans solution, it is a very nice poor mans solution.
Sigh... Let the flamefest begin!
My Experiences with PostgreSQL (Score:3)
Re:both have serious limitations (Score:4)
Don't you think that your comments are more than a little unsubstatiated? Have you ever used an ATM? If so, you are using transactions. Have you ever paid with something using a credit cart? If so, you are using a transaction application. Have you ever made an insurance claim? If so, you again have used transactions.
While preventing dataloss is an important application for databases, it is not the only one. Instead, try creating a large, complicated system using no transactions. In order to execute most data manipulations, you would have to modify more than one table. Even something like a bank account transfer would access at least three tables. How would you do this using something with no transactions? You would lock the tables as you needed them. How would you do this with a transaction safe system? You would forget about locking the tables and commit or rollback, depending on the success of the action.
The fact is, for simple joins, MySQL will always be faster, and simpler. For more complicated joins, it's naive query optimization is incredibly limited, with joins involving more than seven tables, my experience has shown that MySQL just cannot optimize correctly, even PostgreSQL was even faster on some of my most complicated joins.
Simple insert/update actions likewise are much faster, but with the lack of foreign keys, you have to write select statements yourself to maintain referential integrety.
MySQL is faster for simple actions that wouldn't need foreign key checks, and complex manipulations. However, consider when you have to make a modification to a large number of tables. in MySQL, you would have to lock each table, blocking out any other access to the same data, while something like PostgreSQL allows concurrent access to the data.
You talk about the performance killing features of full SQL, they do indeed have a performance hit. These are overkill for glorified flat file databases, but for a normalised database, transactions give you an order of magnintude more speed.
Consider the ACID test, durability is only one fourth of the equation, atomicity, durablity and isolation make up the rest of the equation. These features not only make the system more stable, they also make life for a developer much easier. They also allow a far more robust, scalable and flexible system to be developed in a shorter amount of time. Every big website that uses a DMBS uses something with transactions, not because they are a performance killer, but because they are a more scalable solution.
At the low end, big databases are slower, but as the size of the system grows, MySQL gets slower faster, untill at Enterprise level, it becomes slower. Lest you think I don't know what I'm talking abount, I work for a fairly large e-commerce site as the senior programmer.
BTW: I do agress that SQL is not the ultimate solution, I wouldn't go so far as to say it's awful, though. Instead of bitching, why don't you design something better, I am designing something better, it's just a long way down the road.