PostgreSQL vs MySQL: an apples to oranges comparison

Author here.

Mysql - Model is in your code. PostgreSQL - Model is at least partially in your database.

Also your code can be at least partly in your database which is what makes this possible.

There is a HUGE mistake in the article in the assumption that WRT the model design, that the database always knows best.

I didn't say that. However if you read the entire O/R modelling series you will see in PostgreSQL it is possible to fully define your model in an OO-like way in your database, and if you do that then that model can be re-used across applications written in different development environments. We now have proof of concept PHP classes for integrating with LedgerSMB because of the fact that our model is in our db. This makes it very easy to write classes which interop across different languages.

The fact is you can decide where you want the line to be. PostgreSQL allows you to build interfaces which give you much more intelligent data models at every line.

These tools have complexity costs though. Use where appropriate.

Is it persistent storage or is it a turing complete theorem prover and why most both be in the same executable? Note I'm not claiming a "middleware" of a model is a bad idea, in fact its a great idea, it just doesn't belong in the persistant DB store anymore than it belongs in the filesystem layer.

Mike Stonebraker's example was: Create a db query to tell you what images (in your database) are pictures of sunsets taken within 20 miles of Sacramento.

His argument for code being in the database is that the last thing you want to do is select several thousand images and hand them over to the middleware or client for processing. Instead you need some way of having the database answer this and only send you back the ones you want. He suggests:

    select id
    from slides P, landmarks L S
    where sunset (P.picture) and
    contains (P.caption, L.name) and
    L.location |20| S.location and
    S.name = 'Sacramento';

The point here is that you have two good examples of why this approach can be important here: spacial queries, and filtering out images by content using image recognition algorithms. In this way, you aren't burdening your least scalable tier with transferring MB and MB of information back to a middlware so it can perform the processing and return only a few records to the client.

I think you missed the _reason_ why that matters.

If it's _just_ your app/code that's sending and retrieving data from tha database, you can pretty much do as you please.

If other code, especially other code written by other people needs to interact with that data, then explicit rules and agreements need to be made about exactly what "1890-02-30" means.

The argument in the article is that Postgres (and Oracle) have features that help in the multiple application interfacing with the same database, when compared to the MySQL and NoSQL end of the database spectrum.

It's not so much "the model" that's moved into the database, but the validation of the values stored by your model.

I think it's making a better argument than you imply. If you want to be able to store 30 Feb in your model, you'd better consider what might happen if you try and store that in a date column in your database. I'm pretty sure at least some versions of MySQL will happily let you insert that date, and "magically" return 02 (or 01) March when you query it. Is that the "expected behavior" of your Gravestone app?

No argument there. See my other posts in the Object/Relational modelling series for example.

A varchar named something like "DateAsEnscribed". With a related date field that you can search on with some well defined policy about what happens when gravestones have invalid dates enscribed them.

The problem is, you probably don't work out ou need that until you've got a million rows stored in a date column, and when you discover it, you then start asking ourself "I wonder how many of our dates have been auto-magically 'corrected' from accurate-but-invalid enscribed dates into valid-but-not-as-enscribed ones."

"There is a HUGE mistake in the article in the assumption that WRT the model design, that the database always knows best.

I didn't say that. However if you read the entire O/R modelling series you will see in PostgreSQL it is possible to fully define your model in an OO-like way in your database"

Perhaps the area of disagreement in our interpretations is that I'm thinking "the database knows best" as in the DBA gets the last word on what can be stored vs the DEV whereas I think you're defining the data definition in the DB as a DEV task, or maybe all DEVs should be both DBA and DEV, which I don't think will work very often but when it does work it's great.

"and if you do that then that model can be re-used across applications written in different development environments."

Again, would be great if its possible. Probably one very important part of the workflow would be not to allow the DEVs to code in a MVC framework, essentially VC only, or just vestigial M like anything goes and rely solely on the DB for all data modeling. Otherwise each environment will have a different, probably incompatible, model.

"PostgreSQL allows you to build interfaces which give you much more intelligent data models at every line." "In this way, you aren't burdening your least scalable tier" There's no free lunch, only tradeoffs. In your case at least in one example, it works great and I'd glad for you, there is no better proof than working code / working system. However in general for most situations I don't think it would work very well at all.