Slashdot Mirror

Postgres isn't a speed slouch either, if you're judging by the "butt dyno" with terms like "amazingly fast."

I like to use PostgreSQL because it adheres to the standards better. Plus, I don't have to deal with this list (infamous, for certain)

http://sql-info.de/mysql/gotchas.html

There's also a quote out there about one of the primary devs not knowing the difference between a PRIMARY KEY and a UNIQUE field. That is not the kind of leadership I want in a database.

MySql is the Windows 95 of the DBMS world.

Once the 5.x branch is regarded as production quality and the issues raised at http://sql-info.de/mysql/gotchas.html have been addressed then I'll consider it as something more than a toy. Until then, I'll stick with Postgresql.

The DBAs I worked with always told me "Postgres is better". But I tried it a good few years ago, couldn't install it, it didn't "just work", and I was not that good with Linux at the time, so I just moved on to the next thing - MySQL.
MySQL was good enough, and all the stuff that hardened DBAs said to me - "It doesn't do transactions", or "It handles NULLs wierdly", etc, just didn't apply.

But when I tried to do a query like this: SELECT * FROM foo where bar NOT IN (SELECT blib from wheee) - MySQL advised me that it "didn't do" "NOT IN" queries. I tried to work around it, but after trying all the JOINs I could, it just didn't seem like something that I could get round. (I wasted quite a long time trying to work around this, and although I'm sure that some really top DBAs out there can do it, I couldn't.)

So, mysqldump > mysql.dump, and then restore into Postgres. :%s/mysql_/pg_/g in all my PHP files. Change mysql_error to pg_last_error, and fiddle with pg_num_rows, and it all worked. Moreover, one huge query that took 25 seconds to complete in MySQL (lots of JOINS and nastiness) took about 1 second in Postgres.
I've never looked back. MySQL is now just coming to fill in all the gaps it's missing - but just go with Postgres. It's rather good.
No mention of SQL servers can go without the Gotchas: Mysql and Postgres. The worst MySQL is probably that it modifies data as you insert it without throwing an error. Yuk.

The DBAs I worked with always told me "Postgres is better". But I tried it a good few years ago, couldn't install it, it didn't "just work", and I was not that good with Linux at the time, so I just moved on to the next thing - MySQL.
MySQL was good enough, and all the stuff that hardened DBAs said to me - "It doesn't do transactions", or "It handles NULLs wierdly", etc, just didn't apply.

But when I tried to do a query like this: SELECT * FROM foo where bar NOT IN (SELECT blib from wheee) - MySQL advised me that it "didn't do" "NOT IN" queries. I tried to work around it, but after trying all the JOINs I could, it just didn't seem like something that I could get round. (I wasted quite a long time trying to work around this, and although I'm sure that some really top DBAs out there can do it, I couldn't.)

So, mysqldump > mysql.dump, and then restore into Postgres. :%s/mysql_/pg_/g in all my PHP files. Change mysql_error to pg_last_error, and fiddle with pg_num_rows, and it all worked. Moreover, one huge query that took 25 seconds to complete in MySQL (lots of JOINS and nastiness) took about 1 second in Postgres.
I've never looked back. MySQL is now just coming to fill in all the gaps it's missing - but just go with Postgres. It's rather good.
No mention of SQL servers can go without the Gotchas: Mysql and Postgres. The worst MySQL is probably that it modifies data as you insert it without throwing an error. Yuk.

I found this list to be particularly interesting reading. I happened to pick PostgreSQL rather than MySQL when I needed a DB; this list made me feel very glad that I did.

MySQL Gotchas

To remain fair, there is a similar list for postgresql, but to me they seem like far less serious problems.

PostgreSQL Gotchas

I found this list to be particularly interesting reading. I happened to pick PostgreSQL rather than MySQL when I needed a DB; this list made me feel very glad that I did.

MySQL Gotchas

To remain fair, there is a similar list for postgresql, but to me they seem like far less serious problems.

PostgreSQL Gotchas

Would you care to elaborate?

In SQL NULL represents the the absence of a value. In MySQL an explicit NULL may also represent the next value of a pseudo-sequence and an implicit NULL may represent an implicit default value (a zero or empty string) determined by MySQL. See here and this Wikipedia article for more info.

Why do think that this is better? In one case you call the function and then use the value, and in the other case you insert the row and then ask what the new PK is. I'm not sure how one is better than the other. Could you explain?

If you're wanting to know your next ID number before inserting data, you're screwed in MySQL. MySQL uses "auto_increment" fields that just add one to the previous row's value, while PostgreSQL uses what are called sequences which are guaranteed to always return a unique value, and you use those for your ID fields. In MySQL, there's no way of finding out the next value in an auto_increment sequence until you've committed a row to the database, while PostgreSQL lets you peek at the next value in the sequence.

Details please? I'm not trying to pick on you here. I'm just trying to figure out what your complaint is. The MySQL manual is huge, so you can't expect everyone to know what you're talking about.

Here you go. The old manual criticized foreign key constraints and transactions using bizarre reasoning.

For what it's worth, MySQL has some features that we really love, like the binary logs. We have yet to lose a single row because of database corruption.

The issue with MySQL isn't database corruption, it's data integrity. A lot of things in MySQL will happen and not give you any warnings, whereas other databases are very strict about giving you a warning or even refusing the statement with an error so you can respond to it in your code. There are an alarming number of conditions where MySQL inserts things like zeros into fields without telling a single thing. You can't fully trust MySQL.

hopfully then your big enough to know mysql is crap, and so is php. http://sql-info.de/mysql/gotchas.html oh yes i c lets insert a NULL when anything goes wrong. GREAT THINKING 99!!!

Great! Once 5.x is officially labeled as a production release and it has demonstrated over a period of at least several years that it no longer silenty munches data and has addressed the issues mentioned on http://sql-info.de/mysql/gotchas.html I'll give Mysql another look!

Until that time, I'll trust my data to real databases that place importance on the concept of data integrity and adhere (as much as reasonably possible) to the various sql standard publications.

To the O.P.: Provide some info - we're not mind-readers. Today's User Friendly is somehow appropriate.

How well normalized is the schema? Mostly reads? Writes? Both? 280,000 users? So what. Do you mean simultaneous users or are only 2 on at a time? Are they accessing a single 100 record table or lots of large tables? Are they indexed properly? What is the OS, memory, disk, processor...? How much processing is required of the DB vs. the front-end. Have you run into any specific problems that might indicate that a different db might be more appropriate. What have you tried and what was the result?

To the editors: Please reject Ask Slasdot questions from posters who can't be bothered to provide the most basic background info.

This is Slashdot. I would like to believe that the typical reader could be rather more technically erudite.

I really would like to see MySQL 5.0 compete and actually topple oracle in the market place.

Yeah, and you'd probably like a pony,, too.

Hmmm... have they fixed these yet though?

Enforcing referential integrity constraints can certainly slow things down, but I've always found it far preferable to data inconsistency. When I load a log file into a database I want to know that all or nothing arrived, then there's no screwing around trying to feed half the file back in later. This is never a problem with a full transactional RDBMS.

But you can also get around speed issues when you are sure of the data. Years ago I was an acting Postgres DBA - for a couple of years or so - and quickly discovered that to speed up data dump/load issues it was a relatively simple task to disable referential integrity enforcement before the load, and reenable afterward. This was especially useful when my manager accidentally messed up a single client data table with a single update and I was able to disable the table's triggers, drop it, load the morning's backup, and reenable the integrity enforcement.

Indeed, a database restore is typically of the order: Create the tables, load the data, turn on integrity. ~10G in 15 minutes (backup and restore) on now-5-year-old-hardware, not hard to do.

Oracle offers similar loading functionality. I've heard a very highly regarded DBA referring to it as ripping a hole in the side of the table, but basically it's a referential integrity bypass. Great stuff if you know what you're doing, and it can turn hours of loading into something better measured in minutes.

The integrity problems are specifically because of poor implementation of foreign key and other constraints. Read up at http://sql-info.de or a million other sites on the net. There are fundamental problems in their implementation. It doesn't matter if you are using transaction isolation or innodb tables, MySQL silently changes data in many many circumstances. This is bad.

My 'users' aren't touching the database design. The database developers are. Multiple developers. When one makes a change and goes off shift, the next guy working on the table should immediately know if something changes made by the previous developer have invalidated some new data/scheme he's implementing. A database should never silently accept errors. It should always flag someone and refuse to make (or appear to make) a bad change.

I don't know about your background, but a lot of MySQL users haven't a clue how a real database should be designed or what real data integrity is. I'm not bitching about MySQL not having features, I'm bitching about it's shoddy implementation of the features it already has. Foreign keys (in innodb) do not work right! Constraints do not work right! Many other basic features that MySQL claims to have do not work right!

I'll say it again: A database should protect your data. It should not silently change data it doesn't like, instead of aborting the transaction and throwing proper error message.

Postgres is also available for free. And it's designers appear to care about data integrity.

Perhaps the Postgresql guys will fix their list also? http://sql-info.de/postgresql/

Do a simple comparison of the two lists. The postgresql list has 1 item that can cause incorrect results or data corruption (RANDOM item). The other items either affect performance, list non-compliance with sql standards, or are applicable to versions of postgresql two major versions ago. Even then, there are a lot less items in the postgresql list then the mysql list.

Hint for moderators : the parent is karma whoring by pasting content of the MySQL gotchas page.

Or trolling ? Or both ?

Anyway, if somebody is using mysql in any kind of environment, he surely has already heard of this by now unless he's living in a Cave, thanks to all the postgresql zealots.

Perhaps the Postgresql guys will fix their list also?

http://sql-info.de/postgresql/

The same way that MySQL has the constraints feature on columns. But it also isn't implemented correctly. If you insert out-of-bounds data, MySQL silently changes it to a number that it likes. Features implemented half-assed like that aren't really good features.

If they implement views, triggers, and stored procedures in the same crappy way, MySQL is still going to be a crappy DBMS. It will have lots of broken features which will still make it an unsafe place to store data that you care about.

As far as a reference for just some of the problems with their foreign key implementation, and as pointed out elsewhere in the thread, and found here...: http://sql-info.de/mysql/referential-integrity.htm l%233_5

3. Foreign Keys and Referential Integrity Foreign keys are an essential part of any relational database. In MySQL's foreign key support has been added on through the InnoDB extension and is continually being improved. However some aspects of the foreign key implementation, especially in combination with other areas of functionality, may cause unexpected problems.

3.1. ALTER TABLE ... SET NOT NULL If a NOT NULL constraint is applied to a column, MySQL will set any rows containing NULL in that column to 0 (in integer or numeric columns) or '' ( empty string, in character columns). No warning is given.

In certain circumstances - particularly if the column contains character data - this may be quite practical, saving you an entire UPDATE tbl SET col = '' WHERE col IS NULL.

But - imagine the column is an integer foreign key. And the column it references does not contain a zero. Hmmm...

mysql> CREATE TABLE exmpl5 (
id INT NOT NULL,
val TEXT,
UNIQUE (id) ) TYPE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE exmpl6 (
id INT,
blah TEXT,
INDEX(id),
CONSTRAINT id_fkey FOREIGN KEY (id) REFERENCES exmpl5(id) ON DELETE NO ACTION ) TYPE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

INSERT INTO exmpl5 VALUES(1, 'test');
INSERT INTO exmpl6 VALUES(1, 'foo');
INSERT INTO exmpl6 VALUES(NULL, 'bar');
INSERT INTO exmpl6 VALUES(0, 'oops'); ERROR 1216: Cannot add a child row: a foreign key constraint fails

SELECT * FROM exmpl6;
| id | blah |
| 1 | foo |
| NULL | bar |
2 rows in set (0.00 sec)

So far so good - this proves the foreign key constraint is being taken seriously. Now the fun starts:

ALTER TABLE exmpl6 CHANGE id id INT NOT NULL
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 1

INSERT INTO exmpl6 VALUES(NULL, 'bar');
ERROR 1048: Column 'id' cannot be null

INSERT INTO exmpl6 VALUES(0, 'oops');
ERROR 1216: Cannot add a child row: a foreign key constraint fails

This is perfectly normal behaviour for a well-adjusted database. Now let's have another look at what the table contains:

select * from exmpl6;

| id | blah |
| 1 | foo |
| 0 | bar |
2 rows in set (0.00 sec)

I don't recall successfully inserting the zero in that second row - do you? Perhaps I secretly inserted a row into exmpl5 with 'id' set to 0?

SELECT * FROM exmpl6 e6 LEFT JOIN exmpl5 e5 ON e5.id=e6.id;

| id | blah | id | val |
| 1 | foo | 1 | test |
| 0 | bar | NULL | NULL |

Err, no. All I can think of is that the foreign key was arrested as a potential terrorist suspect while I was seeing what other databases did given the same set of queries.

(Note: MySQL 4.1.7 raises a warning after the ALTER TABLE statement above with the cryptic message Data truncated for column 'id' at row 2.)

I'd say this was a little more pertinent.

mysql> INSERT INTO phantom2 VALUES (128);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM phantom2;
+-----+
| id |
+-----+
| 127 |
+-----+
1 row in set (0.00 sec)

Wake me up when they fix this. Oh, and are they finally adding an "enterprise feature" called "subselect"?

I wonder if they will have thought of correcting a few pertinent errors in this new release ...

http://sql-info.de/mysql/gotchas.html : 31 mysql gotchas
http://sql-info.de/postgresql/postgres-gotchas.htm l : 13 postgresql gotchas

Which one would you rather use?

http://sql-info.de/mysql/gotchas.html : 31 mysql gotchas
http://sql-info.de/postgresql/postgres-gotchas.htm l : 13 postgresql gotchas

Which one would you rather use?

I would like to use this opportunity to say this: Kudos to Josh Berkus and the entire PostgreSQL team! I hope you are reading this. You have done an absolutely amazing job with your database. It is the only Free Software RDBMS that I believe can be compared with Oracle. This is hardly the first Free Software RDBMS I used but it is the first one that actually works as it should, respecting standards and relational model with full ACID support (*cough*cough*). This is also the fastest Free Software database when you are doing any serious inserts/updates-heavy transactions on large data sets with consistency being the top priority. Kudos! You guys rule! I believe that your project should be a de facto standard in Free Software world. Keep up the good work.

You have got to be kidding. Those "fatal flaws" are nothing compared to MySQL "gotchas." Talk about bias...

This is an interesting question. The databases you ask about don't have MySQL gotchas, that's for sure. Nor do they have PostgreSQL gotchas. They don't have SQLite gotchas either. Or Oracle gotchas, for that matter. But one thing is sure, trust me, they most certainly do have gotchas of their own. Do you know them? Can you work around them? Will they silently corrupt your data? Will it be easy to migrate your data to other RDBMS without changing your applications? How do they scale? Do they fully support SQL92? SQL99? Can you afford them not to? Are their transactions truly atomic? Is your data always guaranteed to be in a consistent state? Are the operations on your data isolated? Are the transactions durable? What is the developers' relation to the decades of scientific research and engineering experience in the field of relational database management systems? Do they fully understand it? Do they know why you need ACID? Or would they rather tell you that you don't? Those are the questions that you have to answer. When it comes to relational databases, it is always a question of which gotchas are you ready to face. And of course, as I have already written, you will be unable to answer that question without at least some basic understanding of relational algebra, set theory and predicate calculus. Those fields are essential to understand what the relational model is all about.

This is an interesting question. The databases you ask about don't have MySQL gotchas, that's for sure. Nor do they have PostgreSQL gotchas. They don't have SQLite gotchas either. Or Oracle gotchas, for that matter. But one thing is sure, trust me, they most certainly do have gotchas of their own. Do you know them? Can you work around them? Will they silently corrupt your data? Will it be easy to migrate your data to other RDBMS without changing your applications? How do they scale? Do they fully support SQL92? SQL99? Can you afford them not to? Are their transactions truly atomic? Is your data always guaranteed to be in a consistent state? Are the operations on your data isolated? Are the transactions durable? What is the developers' relation to the decades of scientific research and engineering experience in the field of relational database management systems? Do they fully understand it? Do they know why you need ACID? Or would they rather tell you that you don't? Those are the questions that you have to answer. When it comes to relational databases, it is always a question of which gotchas are you ready to face. And of course, as I have already written, you will be unable to answer that question without at least some basic understanding of relational algebra, set theory and predicate calculus. Those fields are essential to understand what the relational model is all about.

However, as a professional programmer (hah. even my cousin's dog is a professional programmer these days.) I find this kind of quasi-implementation of perfectly good if vague and previously ill-implemented standards with the excuse of "code quality" and "implementation efficiency" reprehensible when the excuses speak more of the implementors' ability to manage their own code and to pay attention to the things that actually matter efficiency-wise, than real issues. No one is going to care whether your database does a "SELECT 1+1 FROM implicit_temporary" in a microsecond or one and a half. What people actually care about, and what they get angry about, are things like those listed on the MySQL Gotchas page.

And don't get me started on the "sorry, we don't support subselects or IN joins or EXISTS clauses and our LEFT JOINs behave in a deviant manner; perhaps you should do the join inside the application!" garbage.

Sorry to vent my spleen on an unsuspecting target like this. I'm mostly just frustrated with people who refuse to think things through before jumping headfirst into implementation, as evidenced by the longstanding MySQL policy with regard to transactions, atomicity and locking policy.

what about postgresql?

That is a very good question, I don't know why has it been moderated as off-topic. Naturally it is useless to compare MySQL performance to MySQL performance ignoring any other options. (It is essentially the same tactic Micro$oft is doing all the time! Do we really want to parrot them?) First of all, there are MySQL gotchas and PostgreSQL gotchas, so you have to know whether the particular glitches are acceptable for you before you decide to use either RDBMS. Understanding the relational algebra, set theory and predicate calculus is essential to understand what the relational model is all about. Lack of this knowledge often leads to confusing tuples with OOP-style objects and other stupidity, so you will save a lot of time learning it first.

Now, the performance. Generally speaking MySQL is faster for a heavy load of simple read-only queries (like Slashdot) while PostgreSQL is faster for complex read-write queries (like a bank). Once you turn on the ACID support in MySQL it is no longer so fast, and it can really crawl because of row or even table (sic!) locking, a mistake avoided for decades by any advanced database. Here is another comparison. See also this recent thread on Slashdot. One of the best comparisons of Oracle, MySQL and PostgreSQL was done by the Computer division of Fermilab (Fermi National Accelerator Laboratory), this is a must-read.

There is a lot to read about it if you need more comarisons, but the general rule of thumb is that if you want lots of very simple read-only and very few read-write queries when the integrity of your data is not critical, you should probably choose MySQL. When you need that (or better) speed but the data is critical and you need ACID transactions which would severly slow down MySQL, try SQLite, the easiest choice there is, especially using Perl where you don't even need to install it (but just like with every other database, there are SQLite gotchas too, you need to be aware of them). If you need full ANSI SQL compatibility, ACID transactions, scalability and your data integrity is important, you should probably choose Oracle or PostgreSQL. There are also licensing issues. Oracle is proprietary. MySQL is GPL so you need to pay if you want to use it in any non-GPL software. PostgreSQL is released under a free-for-all BSD license. SQLite is public domain.

As you can see, there is no one-size-fits-all database. Every one has its strengths and weaknesses. The correct choice is a matter of trade-offs and finding out which database is optimal for your particular niche. Good luck.

what about postgresql?

That is a very good question, I don't know why has it been moderated as off-topic. Naturally it is useless to compare MySQL performance to MySQL performance ignoring any other options. (It is essentially the same tactic Micro$oft is doing all the time! Do we really want to parrot them?) First of all, there are MySQL gotchas and PostgreSQL gotchas, so you have to know whether the particular glitches are acceptable for you before you decide to use either RDBMS. Understanding the relational algebra, set theory and predicate calculus is essential to understand what the relational model is all about. Lack of this knowledge often leads to confusing tuples with OOP-style objects and other stupidity, so you will save a lot of time learning it first.

Now, the performance. Generally speaking MySQL is faster for a heavy load of simple read-only queries (like Slashdot) while PostgreSQL is faster for complex read-write queries (like a bank). Once you turn on the ACID support in MySQL it is no longer so fast, and it can really crawl because of row or even table (sic!) locking, a mistake avoided for decades by any advanced database. Here is another comparison. See also this recent thread on Slashdot. One of the best comparisons of Oracle, MySQL and PostgreSQL was done by the Computer division of Fermilab (Fermi National Accelerator Laboratory), this is a must-read.

There is a lot to read about it if you need more comarisons, but the general rule of thumb is that if you want lots of very simple read-only and very few read-write queries when the integrity of your data is not critical, you should probably choose MySQL. When you need that (or better) speed but the data is critical and you need ACID transactions which would severly slow down MySQL, try SQLite, the easiest choice there is, especially using Perl where you don't even need to install it (but just like with every other database, there are SQLite gotchas too, you need to be aware of them). If you need full ANSI SQL compatibility, ACID transactions, scalability and your data integrity is important, you should probably choose Oracle or PostgreSQL. There are also licensing issues. Oracle is proprietary. MySQL is GPL so you need to pay if you want to use it in any non-GPL software. PostgreSQL is released under a free-for-all BSD license. SQLite is public domain.

As you can see, there is no one-size-fits-all database. Every one has its strengths and weaknesses. The correct choice is a matter of trade-offs and finding out which database is optimal for your particular niche. Good luck.

First of all, this book seems like a nice rewrite of on-line documentation. It is even a good idea in principle, because building a database driven site with PHP and MySQL is indeed very quick, almost as quick as using Perl and SQLite, but as with every RDBMS there are gotchas. It is true for MySQL, true for PostgreSQL, true for SQLite and even for Oracle, because just like no system is secure, no database is perfect. You always have to know the gotchas to work around them, which is especially important when you want to write a portable database-independent application, which is always a good idea. Unfortunately, this book lacks many important informations about those issues, as it also lacks essential introduction to relational algebra, set theory and predicate calculus, which are important to understand the relational model and to know what the relational database is all about. Without such background, people tend to confuse the relational model with a SQL interface to the filesystem, or an object store, so the lack of such an introduction is the most important flaw of that book. Other than that, it is quite a nice rewrite of many HOWTOs available on-line, and it is always easier and quicker to read one book than to hunt countless websites. All in all, a nice book.

First of all, this book seems like a nice rewrite of on-line documentation. It is even a good idea in principle, because building a database driven site with PHP and MySQL is indeed very quick, almost as quick as using Perl and SQLite, but as with every RDBMS there are gotchas. It is true for MySQL, true for PostgreSQL, true for SQLite and even for Oracle, because just like no system is secure, no database is perfect. You always have to know the gotchas to work around them, which is especially important when you want to write a portable database-independent application, which is always a good idea. Unfortunately, this book lacks many important informations about those issues, as it also lacks essential introduction to relational algebra, set theory and predicate calculus, which are important to understand the relational model and to know what the relational database is all about. Without such background, people tend to confuse the relational model with a SQL interface to the filesystem, or an object store, so the lack of such an introduction is the most important flaw of that book. Other than that, it is quite a nice rewrite of many HOWTOs available on-line, and it is always easier and quicker to read one book than to hunt countless websites. All in all, a nice book.

Good lord, are you kidding? I would assume any reasonable organization that was accessing their database over a network would keep the webserver on a DMZ and the database server behind a firewall that's tightened up and only allows access to the database from the DMZ. Isn't this, uh, kinda obvious?

Hello? We are talking about people using MySQL. Seriously, what did you expect? Calm down, no one is running MySQL for any mission critical database, and no one has any mission critical database open to the world. We are talking about blogs here and small websites, but still I agree that using SQLite would be much better idea -- it's secure from the network, it has real ACID transactions, it is a real RDBMS. But people prefer MySQL running on Windows, what are you gonna do? Nothing. So calm down. It's not the end of the world, is it?

Maybe you just need to simma down now!

Fuck you, I don't have to waste my time on this shit so stop acting like a dick. If you are "genuinely curious about the technical differences between the two DBs" then great, start from reading these articles:

PostgreSQL vs MySQL: Which is better? by Ian Gilfillan
MySQL Gotchas by Ian Barwick
MySQL and PostgreSQL Wikipedia articles

Things you should know before you start reading the above articles, to fully understand what they talk about, and indeed before starting to use relational databases, to avoid common mistakes like confusing objects with tuples et cetera:

Relational model, transaction processing,
ACID, atomicity, consistency, isolation, durability,
relational algebra, predicate calculus, set theory.

Those Wikipedia articles are a very good start if you really want to know what databases are all about. First of all you have to understand that RDBMS is not an object store. This is the most common mistake. What you get from a database are not objects, but tuples. They don't have an identity and they are not real things that exist, but an information about your data. See this thread for wonderful explanation.

You must have some minimum knowledge about the set theory, predicate calculus and relational algebra to understand it, but once you do, you will have a much better understanding about relational databases and your data, including the importance of ACID features. It is really worth to invest a little time now ro learn the theory and save a lot of time in the future thanks to better understanding those concepts which can be confusing at first, for they don't map into the standard OOP model and standard data models very well. Good luck.

I am looking forward to hear from you if reading those articles was helpful. This is the standard introductory material that I recommend to my students, and I'd like to hear an opinion of someone who is not scared that some criticism will cause him troubles. By the way, sorry for my English.

I wonder how this is different from MySQL Cluster an in memory only DB.

I hate it when MySQL fanboys jump into threads like this only to show their ignorance of relational algebra and predicate calculus saying that no one should ever bother with PostgreSQL and ACID-compliance, because MySQL is somehow a "better tool for the job" in the "real world". People, please, I beg you, read this first: [1] [2] [3] [4] [5] [6] [7] before you post yet another misleading plug for your favorite toy. Thank you. A real relational database is more than just a data store with SQL frontend.

This is a great breakthrough in RDBMS development. Remember that you nead real atomic ACID transactions for this thing to work reliably. Watch out for the gotchas. Good luck.

PostgreSQL vs MySQL: Which is better?

And here is a 3-point summary for the impatient:

MySQL versus Postgresql (summary)

For lite uses, and many heavy ones, SQLite seems excellent. I haven't used it yet, but whoever writes for the project is an excellent communicator.

In my experience, most open source projects, and almost all commercial products, have a (maybe mostly unconscious) plan: "We will carefully measure how much hassle people will accept, and make sure we don't document anything more than enough to just barely keep people from rejecting us."

It's common to visit an open source project and find that, yes, they have a new version, but the manual is two years old. There are plenty of commericial projects that are the same way, like Netgear's FVS318. Their reference manual is for version 1.4, but the latest version is 2.4.

An advantage of open source projects is that they are usually far more honest than commercial projects. I love this from the PostgreSQL What's New page: "Although tested throughout our release cycle, the Windows port does not have the benefit of years of use in production environments that PostgreSQL has on Unix platforms and therefore should be treated with the same level of caution as you would a new product." Marketing people are generally so dishonest that they would not allow an honest statement like this.

MySQL is a non-standard implementation of SQL. That's a problem that's probably partly caused by not doing good documentation. If they had documented everything as they wrote MySQL, they might have seen what a mess they were making. Bad documentation obscures programming messes.

PostgreSQL has an elaborate documentation system, and the new features are very impressive.

Can anyone say WAPP instead of LAMP? Maybe.

Can anyone say database instead of data store? YES.

(Sorry for the broken link in my previous post.)

Can anyone say WAPP instead of LAMP? Maybe.

Can anyone say database instead of data store? YES.

Could anyone give an example of where you might want to use postgresql over mysql and vice versa?

Here it is.

I hate it when MySQL fanboys jump into threads like this only to show their ignorance of relational algebra and predicate calculus saying that no one should ever bother with PostgreSQL and ACID-compliance, because MySQL is somehow a "better tool for the job" in the "real world". We already have comments saying that, so people, please read this first: [1] [2] [3] [4] [5] [6] [7] before you post yet another misleading plug for your favorite toy. Thank you. A real relational database is more than just a data store with SQL frontend.

MySQL
PostgreSQL

MySQL
PostgreSQL

Great, but why should I use PostgreSQL when I already have a database, you might ask? Here's why.

A good design principle is: either do what you're told to do or tell us you didn't do it and why, but don't do something completely different.

Exactly. Compare and contrast with MySQL's behaviour.

• NULL inserted into a NOT NULL column silently alters the data to fit.
• VARCHAR values have trailing whitespace silently removed without asking.
• Dividing by zero is not an error.
• Inserting a value into a column that violates its constraints doesn't result in an error; MySQL guesses at the "correct" value instead. For example, limiting an integer column to 4 digits, and attempting to insert 99999 will result in 9999 being inserted without any error.
• If MySQL finds that it can't create certain table types, it simply ignores referential integrity.

That's why there are loads of people who point out that you can't trust MySQL for important data, or that it isn't a "real" database. A real database tells you when it fails, which is something that is necessary for trusting it with data integrity.

The key point here is that if you go to sea with only one clock, you can't tell whether it's telling you the right time.

Ahh... but a man with one clock always knows the time - but a man with two is never quite sure :).

right here

I'm lovin' it!

Then, they're only missing these

MySQL - The best. Somehow I know of no company willing to deploy this at a mission critical level.

Strange, isn't it?

You were thinking of this page. It convinced me to avoid MySQL completely.