Comparing MySQL and PostgreSQL 2

Mr. Jax writes "6 years ago Mr Poet submitted the story Comparing MySQL and PostgreSQL. Since then both databases have evolved to wherever they are today. Are the points raised 6 years ago still valid? What has changed? Are there other things to consider since then (e.g. licensing)?" This is certainly a valid question since both databases have had to evolve with the times. Have these applications been specialized to fit a particular niche market or are they both still strong competitors? What does the horizon look like for the development of these programs, especially considering the recent MySQL partnership with SCO?

Comments

Been using MySQL, but...

[imbaczek]

...I'm starting to feel more and more drawn to check Postgres out. I've been mostly happy with MySQL though; I just want to see what I'm missing (VACUUM scares the shit out of me - but is it still there?)

Re:Get off it ScuttleMonkey

[ray-auch]

SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company

No, it's like MySQL _sold_ them something.

There are (I expect) a large number of people reaing this who believe that SCO is not a company you should do business with.

It would be interesting to know why MySQL did business with SCO, maybe on principle they turn no customer away, or maybe a need for money overrode. The latter case might be a legitimate concern for the community.

PostgreSQL is supreme A LOT

[michalf]

there is a short (decent) comparison at this url.
From my point of view (web application developer, Ozone framework author and the author of a few rich-content websites I can say for sure: I am more than happy to discover PostgreSQL. Why? More Oracle-like, transactions, nested transactions, views, sql-schema... I doubt MySQL 5.0 will come even close to the standard of PosgtreSQL.
Some can say MySQL is fast. No, it is not. When you run more than 100 users at once PostgreSQL is faster. MySQL has stupid table-locking mechanism that decreases performance significantly under high load.
I would say: PostgreSQL seems to be slower, is not perfectly optimized, but much better goals in its design were used. And one of the goals ic SQL conformance. MySQL is FAR from the SQL standard.
If you want to migrate from MySQL to e.g. Oracle - it is a pain. But PG is much closer to it.
IMHO PostgreSQL is an industry-standard database and we use it for almost every project now. We have used MySQL some time ago and believe me - the difference is huuuuuge. PG is a real database. MySQL seems like a table-managing-application ;-)

best regards - michal

Re:Another question

[Jerry]

PostgreSQL compares very well to Oracle.

I use PostgreSQL as a test database against which I write and test QT applications. I can switch an app between the two backends by changing only a few lines of code and recompiling, or I can build the switching capability into the app. Using PostgreSQL reduces the number of access licenses required for Oracle, or doesn't waste existing connections.

If I had my way I'd use PostgreSQL as the primary database, but some folks believe you've gotta pay money or the app isn't any good. As long as it's their money and not mine.

Re:I have a small problem...

[Anonymous Coward]

From my understanding of the below statement...

If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library.

The simple fact that I access MySQL via OBDC requires me to obtain a license before I may provide a commercial service.

Like before, It seems that I am still stuck.

But would Kroger co-release it?

[khasim]

Would a Kroger executive talk enthusiastically about your new "partnership" with them?

Usually, companies don't want to be seen publicly supporting nutcases who try to make a news story about buying some toothpaste.

SCO can have the press conferences it wants and tell everyone whatever they want ... but it changes when another company is quoted as saying anything more than "we sold them a license and we'll sell you one too!"

Coolest Feature of Postgresql

[daperdan]

I've used both databases for years and they both have their place. One thing that is rarely mentioned about Postgresql is the Table inheritance. I have yet to find this feature in another RDMS. Anyone know of a database out there that supports this feature?

It allows you to create a table that inherits the fields of a parent table. Each time you insert data into the child table the parent also gets a record. You can use regular expressions to select from all child tables. Very cool and useful.

MySQL webpage is full of bullshit

[typical]

You are correct (IANAL, but you're right). However, this is *not* what the MySQL people claim -- take a look at their commercial license webpage. The existence of this webpage is one major reason why I prefer to avoid MySQL. The people behind MySQL not have a Do No Evil stance. At least the postgres people aren't assholes.

Re:Get off it ScuttleMonkey

[entrylevel]

The jokes on SCO, they paid to use something in a product very few companies will buy.
 
More precisely:
The joke's on SCO, they paid for something that very few companies have to pay for, to use in a product that very few companies will buy. Additionally, for those companies already own UnixWare or OpenUNIX, MySQL AB already provides installation instructions and patches for them. Finally, for the exceptionally lazy, SCO themselves provide a GPL'd version for you to download for free!
 
More on-topic, isn't this story a dupe? Granted I wouldn't expect these editors to search back 6 years to find it, but I would at least expect them not to be 6 releases behind on the PostgreSQL version number! Oh wait...

Re:My point of view

[asdfghjklqwertyuiop]

4,1 has excellent relational support, it is damn near impossible to corrupt if db design is correct, and innodb is great. IIRC ./ has been running on mysql + innodb for years. It also support clustering "out of the box".

Excellent relational support? Please. Does 5.0 fix the issue where an insert of an invlalid date into a date field will instead insert '00-00-0000' into the field instead of aborting, like real databases do? And what the fuck is the 00-00-0000 default anyway? That isn't even a valid date unless mysql is running on a 13 month calendar! How about SQL standard comments? How about when you have a NOT NULL column and insert default values, does 5.0 abort or just totally ignore the constraint and insert a 0 or NULL?

Enterprise ready... excellent relational support... sure. They don't even have the relational database 101 stuff working yet.

Stored Procedures!

[matchboy]

Above all things... this is one of my favorite features of PostgreSQL. MySQL has nothing of the sort. Procedural Languages... yum. I recently showed on my blog how I could interact with an instance of DRb from inside of PostgreSQL. How cool is that? MySQL has nothing like this. I'd also guess that a majority of the applications that use MySQL could use SQLite instead and as soon as that becomes more popular, we'll see less MySQL usage.

Re:Mysql is very isp friendly

[schon]

Its just included in the default user account which is difficult if not impossible with posgresql unless you manually install it for each account.

Complete, utter bullshit.

PostgreSQL does *NOT* need to be "manually installed" for each user any more than MySQL does.

there are a ton of php and perl scripts and tools for users to use.

This is the reason why MySQL is more popular for ISPs - because there is a bunch of PHP code that runs only on it.

Its what ISP's prefer.

No, it's what ISPs offer. And they offer it because people ask for it.

Re:Another question

[dotgain]

Get over yourself. If a DBA fails to "recongnize their lack of skills", maybe they're doing just fine with the management tools. If his skills are so lacking he'll screw up his own database eventually.

Creating graphical tools to enable more people to do more things easier is a part of Microsoft's business model. SQL Server Enterprise Manager etc. are just examples of where they've succeeded here.

Personally I'd love to see an open source equivalent of these tools, the offerings I've looked at so far are unfortunately lacking.

And don't give me crap about being a click-and-drool reboot monkey. I'm sick of typing SQL to get things done, when I can grant permissions by picking users from a list and ticking the right boxes.

Did you use telnet to post your slashdot comment? No, you used a graphical browser. Because you don't want to type the http request, and the graphical browser presents the HTML to you in a way that is more natural and effective for you.

Seeing me use Enterprise Manager does not make my co-workers think that they could do my job just as easily. They do not end up thinking any "fool" can do it.

Re:Another question

[Anonymous Coward]

SQL server gets bashed on slashdot just because it's made by microsoft. If it's linux/FOSS oriented, then it's cool n stuff. This is the slashdot mindset.

Oracle is not only more complicated than SQL server, but much more expensive to buy/maintain/administer (than not only SQL server but pretty much every other DB I've used).

Price comparison between Oracle 10g Enterprise Ed vs SQL server 2000 Enterprised Ed; with management tools + advanced security features + business intelligence features (OLAP); for a 2 cpu box (which aren't dual core - because if they're dual core then double the oracle costs - only them counts cores as separate CPUs):

Oracle: 192000$
MS SQL: 39998$

Nearly 5 times the price (not just like 50% or such which would already be significant). At that price I'd expect to get a LOT more than I'm actually getting. Plus, Oracle on that same hardware won't actually be faster. Oracle may have a couple extra nice features, but nothing to warrant paying 5 times as much for. Not counting that for our Oracle DBs, you'll have to pay a lot for a competent DBA (senior oracle DBAs make a killing and are in shortage too).

SQL server routinely wins on all kind of benches against Oracle, it's usually (always?) FAR cheaper, much easier to use and administer, more widely known (so many MS certified kids know MS SQL vs the few who know oracle), SQL server is much better integrated with Visual Studio for internal apps (winforms or asp.net - using SqlClient) and everything, SQL server's enterprise manager is much better than oracle's tools, and it offers all most big corporations need (there's nothing in oracle we're really missing).

I'm sick of trolls who've never seen or tried SQL server and bash it for no reason at all. It's a VERY GOOD database, lots of big corporations use it (we do) and are very happy about it.

We've actually considered other databases as well (like Matisse), and there's LOTs of others we'd have picked over oracle if it wasn't for SQL server (Postgres included). Oracle just doesn't make sense to use in 99% of cases, it's just too fucking expensive.

extensions

[InsaneCreator]

After 4 years of using both PostgreSQL and MySQL, I'd say that one of the biggest differences between them is their extensibility.

If PG lacks a feature, you have a very good chance of finding a script or an extension which implements equivalent functionality. Materialized views, ordering by different locales and hierarchical queries are some examples of this.

On the other hand, if MySQL doesn't have a feature you need, you're pretty much screwed.

Re:Forget Interbase/Firebird

[Unordained]

I had mod points, and as a Firebird user, I was going to mod up the grandparent -- Firebird is very often ignored despite a host of positive features.

But as you had a bad experience, and you link to your problem list, I thought I'd respond instead. Better to answer questions than just mod up friendlies.

- Database path: Yes, firebird supports aliases. Our app doesn't use them, but they're there.
- ISQL: I hear Oracle's SQL*Plus isn't much better. However, I use isql every once in a while, and I have command-history, backspacing, etc. available to me. From what I hear, it's more of a function of the shell you use (around isql) than isql itself. If you set up your environment properly, isql and its ilk automatically get command-history. (That's what I'm told, note. Anyone who can explain this is welcome to. I'm not a sysadmin.)
- Never seen it freeze.
- Corruption: we've had exactly one database issue, where it seems a backup/restore script ran in the middle of the day, restoring the database to its state from 4 hours earlier. In 4 years of use, with somewhere around 60 users in a medical clinic/insurance/billing environment, we've had no corruption. Using forced-writes is important, however. The careful-write strategy is really, really reliable, but it still can't protect you from faulty hard drives or operating systems that refuse to send data to the disk in the order requested (cf. Windows). M1 Abrams tank story, anyone?
- IBDataPump and other third-party tools exist for some of the other features you're interested in. I'm not sure I know how even I feel about some things only being offered by third-parties. Oracle's tools suck enough people buy other products ... heck, why bother? Just develop a good RDBMS with a good API, and let others fight it out? (That's an open question.)

Feature-wise, and maybe target-audience-wise, Firebird and PostgreSQL are similar. Stored procedures, triggers, check constraints, MVCC (Postgresql seems to have copied MVCC off of Interbase, note), savepoints/nested (but not concurrent) sub-transactions, etc. It lacks a lot of the UDT (type) features of PostgreSQL (you can define domains, but not entirely new datatypes) -- note that Postgres was specifically designed with UDT's in mind. Firebird does support UDF (function) features though, and you can get some of the same flexibility that way if you're masochistic (save data in octet or blob fields and use UDF's to interpret the data). Pg also has neat SP language support, letting you write your SPs in a variety of languages -- Fb doesn't. Unlike Postgres, it's really easy to install, particularly on windows (that was a problem for Pg up until semi-recently) and it practically maintains itself. (Happily, the Pg team eventually got their vacuum, equivalent to Fb's sweep, to not take down the database, so Pg can now run 24/7 too.) Fyracle has been trying to make Firebird more Oracle-like in SP language support and some of Oracle's more interesting query abilities (CONNECT BY). Yes, I occasionally get feature-lust and look at other DBMS's. I don't need Oracle features, but Pg features would sometimes be nice. But I don't use Pg, so I don't know what annoyances it has that Pg users would be thinking about. Maybe it's all-around better, I don't know.

Both are really good projects, with their own strengths. I would say comparing Firebird and PostgreSQL is a much fairer comparison than Pg and MySQL or MySQL and Fb. Pg and Fb are more of a 'niche' comparison. MySQL has nowhere near the features of either of them, isn't nearly as safe, and just isn't designed with the same requirements in mind.

Every single experience I've had with MySQL has been one of "fixing" stuff for a MySQL user who just couldn't get things to work. Joins that wouldn't work (but should have), joins that were slow, data being eaten ... And then there's reliability ... ugh. MySQL just wasn't designed with data integrity in mind, while Pg and Fb were. "Foreign key constraints can be

SP's are only the beginning

[einhverfr]

For an enterprise system,you also need:

1) Views
2) Triggers
3) Integrity Enforcement (i.e. if you try to insert 1000000 into a numeric(4,2) column of your enterprise accounting app you should get an error and not have something inserted).

As your system gets large you may also want:

1) Table partitioning
2) Functional Indexes, i.e. create index on table foo (md5(bar))
3) Partial indexes (i.e. create index on table foo (bar) where open IS TRUE)

MySQL hardly offers all of these capabilities.

PostgreSQL 8.1 will offer all of them in usable forms.

BTW, for those interested, my site has a whitepapers section which has a MySQL to PostgreSQL migration guide.

Re:SP's are only the beginning

[einhverfr]

I don't think it will support things like table partiitoning though in any maintainable form.

For example, in PostgreSQL 8.1 I can do something like (pseudocode here):

create table master_table(
id serial,
date_entered date,
open bool,
state CHAR(2),
data text
);

create table arizona_table () INHERITS (master_table) CHECK state = 'AZ';

create rule arizona_rule ON insert to master_table where state ILIKE 'AZ' DO INSTEAD insert into arizona_table (id, date_entered, open, state, data) values (new.id, new.date_entered, new.open, 'AZ', new.data);

Now all all entries to master table with the state field ILIKE 'AZ' (i.e. AZ, Az, az, aZ) will have their dates entered into arizona_table instead. Furthermore, if I want to do something like:

SELECT * from master_table where state = 'AZ';

PostgreSQL 8.1 will only look in the child tables which allow state to equal AZ. If the system is well designed, it will only look in the table which holds Arizona records. This is important for data data warehousing solutions (which MySQL really doesn't even try to handle).

MySQL 5 may be useful when it comes out and will be a strong step in the right direction. However, many of these features take a long time to get right. I.e. PostgreSQL has had views and rules since 1995 I think.

Re:MySQL vs. Oracle

[Hiro Antagonist]

Other people have made similar comments, but I just feel the need to chime in.

Mind telling me how you're going to move ten terabytes of data around on a $10K server? With full replication (so you're going to need another server at a remote location), tens of millions of transactions every hour, with complete integrity checking and automatic failover to secondary and tertiary systems?

Because that's what McKesson does. I don't work there, just got a tour of the datacenter when I was looking for a job a few years ago, and the amount of data they push and prod is amazing. A 10K server, even with an assload of IDE disks in one huge raid, can't even come close to what you can do with a Sun Enterprise server tied to Fibrechannel disk arrays.

MySQL is for SOHO and small-business use, and depending on it for larger things is a recipe for trouble; where's the transaction and constraint checking in the current stable version? What about stored procedures (again, those are in beta)?

What's worse is that MySQL includes datatypes (like sets) that are, from the perspective of a relational model, completely incorrect, and this makes transitioning to a larger database much harder.

I use MySQL for simple jobs, and PostgreSQL when Real Work needs to be done.

My Grief

[Positronic]

I don't know the details about the internal workings of either one, but they both bug me.

For years I've hated MySQL's install process. Call me old fashioned but I just want to

./configure
make
make install

It makes upgrading and maintaining my servers really nice. MySQL is just an annoyance.

Today I tried PostgresSQL, and I was happy to see a typical install process. Then I finished and I was blown away that it requires a shell in order to run. I understand that daemons need user accounts, but it just doesn't seem secure to give a daemon a shell.

IMO MySQL is a pain, and PostgresSQL is a joke.

Re:Mysql is very isp friendly

[quanticle]

The thing is that all of the above processes require root access. If you're going with a 3rd party hosting service, that's something you won't have. This is what the grandparent poster was talking about. The reason that MySQL is so popular is that ISPs include it, and that those who need a database on their website are therefore "locked into" MySQL.

Slashdot uptime

[ttfkam]

There's more to uptime than the box(es) being up. Netcraft doesn't count when Slashdot has returned 500 status codes because the back-end (read: database) fell over. While it speaks volumes about the reliability of Apache over long periods of time, Netcraft uptimes mean didley in this case for MySQL.

Don't get me wrong. Slashdot's reliability has been steadily improving over the years. Perhaps this is an indication of the stability of MySQL improving over the same interval.

----

Also remember that older articles are archived -- removed from active database queries/updates and rendered to flat text files. Once again, it speaks highly of Apache rather than MySQL.

Re:MySQL vs. Oracle

[einhverfr]

3. MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.

Sure. This is one of Oracle's big downsides. But this hardly means that MySQL is the right tool for the job....

4. MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).

Under what kind of load? How many are complex queries, and how many are read-only? MySQL performs well with simple read-only queries, but does not do well under serious load with a mixture of complex queries and writes.

This makes MySQL perfect for content management and pretty piss poor for anything else.

5. MySQL's supposed gotchas pale in to comparison to Oracle's.

Hardly. Any "rdbms" that truncates your data silently and does not enforce CHECK constraints is not suitable for enterprise applications almost by definition. If you don't know why, I think you should read up on relational theory.

  When I first used MySQL BLOBs it simply worked. I opened up the administration programs and I could actually see the images in the database. It was so beautiful I wanted to cry. I can't count the number of times I went through Oracle BLOB/CLOB hell with different platforms. (Not just getting them in there, but actually getting them to work with third party applications which is the real pain.)

Again, if Oracle is too much of a PITA, look at EnterpriseDB. It is basically PostgreSQL with some additional features to make it work with Oracle-only applications. This means your Oracle apps just work, and your PostgreSQL apps just work. And you save a bundle or two of money.

Re:Another question

[NickFortune]

Compares very well to Oracle? In what metric?

Please, Oracle has a ton of features that just aren't there in PostgreSQL

"What metric" is the right question. But I'm not convinced that the best answer is "comparative length of feature lists". One man's feature is another man's bloat, after all.

I've been writing database apps for a living since 1984. I've worked on trading systems for stockbrokers and multinational merchant banks; I've worked for telecoms giants and for manufacturers. I can't think of a single oracle feature that I've ever needed to use that wasn't available in PostgreSQL.

Admittedly, this has a lot ot do with my style - I'm old school enough that I write my logic in C, C++ or Perl and use the database purely for storing and retrieving data. DBMS vendors (and some database researchers, to be fair) would like coders to do program purely with database packages. I've always though this a supremely boneheaded idea - I trust database designers to design databases, but not progamming langauges thank you. However, if that approach appeals, then you probably need a lot more features than I do.

But they ain't necessary, and it most assuredly is possible to write non-trivial real-world apps using the PostgreSQL feature set.

Re:Another question

[Craig Davison]

The only rows that would get sent back from the server are the ones you want; the temporary table exists server-side.

Yes, you could use a cursor instead, but to avoid problems if the table gets updated while your cursor is open, you'd either have to lock the table or deal with errors from the server.

Re:Sure there is a difference:

[einhverfr]

No. It is the difference between a bucket you *know* is empty and a bucket that contains an unknown.

Re:Mysql is very isp friendly

[electroniceric]

The simple reason people get started with MySQL is that it's popular. So there are more tutorials on how to get started, more books, more widgets and wizbangs that work with MySQL, etc. This also means there is a larger network of people to answer questions.

Contrary to popular opinion however, Postgres is now extremely easy to install and administer, either on Windows or *nix, has excellent resource use (i.e., on my Windows box, it's much more lightweight than MySQL), and it has an GUI admin tool right in the box. And because of its liberal licensing scheme (BSD), you can use it for anything you like. What of that makes it hard for ISPs to run Postgres? The only hard thing I can think of is finding people who know it.

I'll concede that postgres' user model is rather limited, but this is slated for substantial improvement (conversion to roles) in version 8.1 now in beta.

Postgres really has a rather extraordinary feature set: what other DBMS (open or closed source) has Perl and Ruby procedural languages (PL/Ruby), or an (admittedly incomplete) statistical procedure language (PL/R), custom aggregrates, Kerberos authentication? And in my experience you have all that stuff there if you need it (which as you correctly point out, most simple database driven pages do not), but it does get in your way when you don't use it, and it's there for you to grow with when you want it.

Re:Another question

[Anonymous Coward]

This page is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard.

Who cares if they support the standard to the letter or not? Standard conformance != useful performant scalable database.

In most of these cases the standard has been playing catch-up: they've only recently added features that T-SQL and Oracle's extensions have had for years, and with a different syntax. There's no one right set of syntax or programming methodology for these databases because they're all very different beasts.

Re:Solved: CREATE INDEX id1 ON T1 (lower(x));

[melted]

I know this, smartass.

Now try the same for Unicode (i.e. non English) strings. Your lower(x) doesn't work for them.

The first approach suggested by you has horrible select performance and leads to full table scans (that's on top of not working with international chars).

The second approach screws up INSERT performance, which would be OK if it worked well with Unicode strings. Which it doesn't.

So next time before you unleash your righteous criticism on someone, do some hands-on testing.

Re:Another question

[Saturn49]

I've recently ported an mid-size level enterprise application to both MySQL and Oracle from MS SQL. Amongst this was a data layer in C# and about 200 stored procedures. I have this to say:

Besides the null/empty string thing, converting to Oracle was a breeze. Oracle even provides a migration wizard, that, with a little tuning, converted most of the stored procedures automatically.

On the other hand, working with stored procedures in a beta version of MySQL 5.x was a huge pain. The utilities didn't support them well, and crashed constantly. I eventually gave up trying to use the included utilities and stuck with the console. I would think beta meant "most usable", but that was nowhere near the case.

The .NET connector was buggy as heck. Just getting a stored procedure to run took me two days due to a case sensitivity problem. The error messages were hugely cryptic (stored procedure not found actually created a message like "Invalid attempt to access a field before calling Read()"). I had to dig down into the code and make fixes myself to get a reliably working connector, since it didn't handle all whitespace between parameters in a stored procedure properly.

As mentioned elsewhere, MySQL has no problem inserting bogus data into fields, such as 00/00/00 00:00:00 for a date/time. That wouldn't be so bad, except it completely borks up the .NET connector when trying to read that data (it throws an exception and won't return any data.)

Now, some of this I'd rack up to it being a beta, which is fine. Beta software has bugs. BUT, the length of time it took to get those bugs recognized and fixed was over a month! (see bugs 9722 and 9668.) It took another 2 months before those fixes were included in a release. One was erroneously marked as duplicate, even though there is no other bug in the system (earlier or later) describing the same problem. Requests to find out what it was "duplicating" went ignored. I have no idea if that bug is actually fixed or not now. I'd expect a company with a product in beta to be responsive to people putting it through its paces, and acknowledge and fix bugs quickly. Neither happened.

The kicker was when we contacted MySQL to get some sort of developer license. We wanted to include the .NET connector in our general release, since our data layer compiled against it. Their own sales people were clueless as to how to go about such a thing and eventually concluded it wasn't possible. What sort of company makes a developer component that you can't license?

We finally gave up on MySQL. We were 90% done converting, ran up against the licensing wall and simple dropped the project. We may revisit when 5.x is released and stable if they actually have some sort of developer license for their COM/ODBC components.

Re:popularity

[Bloater]

> If you're trying to insert invlaid data, you're the only one to blame.

That would be fine if the database were compiled into the application and strongly type-checked by the compiler. Even small software can't be reliably checked by humans, and the database is connected to at runtime so the compiler can't really do it.

With schemas this can be done better. The compiler could know the types that the database supports and the software is compiled against the schema specification, then as you connect ot the database, the schema is checked for agreement and the compiler had assured the types (and the database author had provided classes to compile against). Then you can be *reasonably* safe, but shit can still happen (database version changes, nearly compatible competitor is substituted, etc). When that shit happens, you need to know that the database has its shovel ready. Its because of the loose connection between the application and database that the database must provide this assurance and it can't just be left to the application compiler to check everything at compile time.

PostgreSQL is "more relational"

[Anonymous Coward]

If anybody here has actually studied database theory, you know that the relational model allows you to do a lot of cool things. For instance:

#1 updateable views (these are like subroutines calls in programming language: they allow you to abstract and refactor)

#2 arbitrary database contraints (these let you say, for instance, that tuple X refers to a tuple in relation A OR relation B, but not both. or that the string in attribute Z must not contain spaces).

#3 real type constraints: can't store integers into a string attribute, and vice-versa

#4 user-defined types: need to store IP addresses, JPEGs, XML data, etc, and declare constraints.

#5 arbitrary relational expressions. for example, I'd like to join two tables and then project the result, then join the result with something else. Compare with arithmatic: most languages let you nest and group expressions arbitrarily: A + ((B*C+2) * D)

Once I design my database using relational principles, I have to find an actual physical database to implement it with. Since there are no relational databases today (except maybe Dataphor), I have to settle for an SQL database. Now. Which should I choose? Open source is usually more flexible, let's see how MySQL and PostgreSQL fare:

MySQL

#1: no updateable views. In fact, no views at all in any release version.

#2: No triggers to implement these.

#3: ugh, no

#4: no

#5: yes, finally they have sub-selects, which is a wordy way of writing out relational expressions, but that's the best we can do with SQL.

Let's see how PostgreSQL meets my needs:

#1: no updateable views, but you can intercept inserts and updates and send them to your own code.. good enough.

#2: simulated with triggers, we can do it.

#3: yes

#4: yes

#5: yes, sub-selects here too, and Postgres had them longer

So, if the relational model is a "10", and MySQL is a "2", then Postgres is at least a "3" or "4". I.e., MySQL is a SUBSET of PostgreSQL. Maybe if you're hacking together a blog or LiveJournal or something, you can away without having this stuff (heck, you can probably just use flat files). But for any real database app that involves money or requires accuracy, I'll stick PostgreSQL.

Remember folks, the purpose of a database is DATA INTEGRITY, not data storage. Once you figure this out, like Codd did in the 70's, you'll be a much better programmer, and you'll laugh at stuff like MySQL (just like Lisp programmers laugh at BASIC programmers).

Re:Another question

[grassbeetle]

No. He got it right the first time. Why on earth would you want your RDBMS vendor cramming their lousy procedural programming language down your throat? For the privilege of burning cycles for your application code on CPUs that you've paid your database vendor upwards of $10k per core for licenses? Or is it because your control-freak DBAs like the app code right up close to the data where they can micro-manage it. The only folks with a worse appreciation of programming languages and application design than sysadms are DBAs.

Finally, if you want to scale, getting your app code out of the DB is the best first step. Outside the database server you can throw cheap app servers at a problem if you need to. Growing your DB server is another beast altogether. Despite the IBM/Oracle propaganda, big grown-up businesses are very hesitant to cluster their databases. Not just the cost but for tuning and safety (the odds of bugs in this super-complex technology bringing them down). In general, you have one live DB server for an app and at least one failover. Growing that single DB server is a lot harder than throwing in a few more pizza boxes, or whatever.

MySQL data corruption

[DMNT]

I run quite a big forum, most active in its kind in Finland. One day in June the site just didn't let anyone sign in or show threads or messages. So I login:

[x@x forum]$ mysql -p x
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Didn't find any fields in table 'backup_yabbse_instant_messages'
Didn't find any fields in table 'backup_yabbse_log_activity'
Didn't find any fields in table 'backup_yabbse_log_errors'
Didn't find any fields in table 'yabbse_members'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 168027 to server version: 4.1.11-Debian_2woody1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from yabbse_members;
ERROR 1016 (HY000): Can't open file: 'yabbse_members.MYI' (errno: 145)
mysql>

After that I repaired my members table, losing all the rows. Luckily backups were recent enough, losing only 7 latest registered members (of some thousands).

After that, I occasionally get MySQL error messages from the e-mail warning system, but repair table helped and didn't lose data.

Re:Another question

[ajs]

"He got it right the first time. Why on earth would you want your RDBMS vendor cramming their lousy procedural programming language down your throat?"

Let's just stop right here. The answer to this question was settled sometime around the introduction of the electric lightbulb, and it has little to do with programming paradigm.

Logic in the database is almost always a bad idea. It introduces complexity in source code management, release engineering, application de-centralization, etc. The database's code optimization is also rarely on-par with external langauges. It's also essential at times. There is a fine line to be drawn between performance needs and software engineering needs. As a very, very rough metric, based only on personal experience, I would say that you should place code into the database only when that code reduces the amount of data that must be moved to the application by at least and order of magnitude. This goes for everything from the most complex stored procedure to the simplest sort (though be careful to be aware of when sorts are free because of storage implementation, and don't be afraid to put conditional logic into code based on storage engine).

Re:MySQL vs. PostgreSQL -- Real World

[timeTumbler]

PostgreSQL has come a long way in 6-9 months. Please take a look at today's SLONY replication, at commercial support *and value-add* companies like http://www.commandprompt.com/, and http://www.enterprisedb.com/. Replication is here...don't worry. And by the way, sounds like EnterpriseDB is putting replication in place between Oracle and PostgreSQL...could be interesting...

Re:MySQL vs. Oracle

[pushf popf]

Systems this large typically don't need a general purpose database, they need custom software designed specifically for the required tasks.

This is another fallacy that's been foisted on the business community: that if you have data, you need a database.

I actually designed a system similar to the one you mentioned. The initial specs called for called for importing, merging sorting and outputting 50 - 75 Gb of data each day.

Even with a 16 processor boxe and a SAN, Oracle took 22 hours to process 24 hours worth of data.

I finally convinced the company that just because they had a hammer, the problem wasn't necessarily a nail, rewrote the required functionality in a C application with NO DATABASE, and the sort/merge/output ran in less than 2 hours on an old leftover dual 500 Mhz Sun box we had laying around.

No staff of DBAs, no huge Oracle licence fees.

Although they never shared the savings information with me, it had to be over a million $/year.

Re:Mysql is very isp friendly

[ClayDowling]

PostgreSQL was held back for years by documentation that made the user account and security system seem arcane. I finally decided to bite the bullet and install PostgreSQL. It turns out that the security system isn't any more arcane than MySQL, the documentation just made it seem harder.

Personally I like the features of PostgreSQL better, probably because I've been working with databases professionally for roughly ten years. For a lot of situations though MySQL is not only adequate, but an ideal choice because you can usually count on people having access to it.

Re:popularity

[arkanes]

This is the single most common response to people pointing out MySQLs data validation issues, and I think it pretty much sums up both the attitude of the typical MySQL developer as well as the reason for MySQLs popularity: People who totally misunderstand both the theory and importance of databases. Amusingly, this same sector of people (because this argument essentially becomes "don't write buggy code, every") have produced some of the most atrociously written and flawed applications to ever be used on the Internet. Except for sendmail.

Re:Another question

[einhverfr]

I've never been entirely convinced by item three. Views are occasionally useful, but if you have a user interface that allows ad-hoc queries, they may be the only way to enforce data security. But if you're writing apps you may as well code the query directly. You'll need to change the code if the view changes, but apart from a few ultra-generic table based apps, that is always going to be true.

Never done any data warehousing, have you?

With updateable views, you can separate the physical data storage mechanism from the way it is presented to the application. Some cool things you can do here include:

1) Normalize your data storage, and denormalize your API.

2) Have several different applications see the database as if it were conforming to their own expectations.

(i..e I can run several different application off the same database even if I don;t have the source code to the app and they require different database schemas).