Why PostgreSQL and not MySQL ?

Video shows basic problems with MySQL

Fun comparison :stuck_out_tongue:

Of course, for MySQL you can tweak some systemvarables like:

SET sql_mode = 'STRICT_ALL_TABLES';

so it’s not so black and white as this guy shows us.

[quote=218773:@Joost Rongen]Of course, for MySQL you can tweak some systemvarables like:

SET sql_mode = 'STRICT_ALL_TABLES';

so it’s not so black and white as this guy shows us.[/quote]
Actually, he did say that you can change the default settings so that MySQL behaves like a proper database.

yes he did, but overall he puts MySQL in the pillory

Having to change setting to make it behave right seems just nuts
IMHO It should be set that way by default and then you CAN turn those things OFF
That way it behaves as a proper database and you can make it more unsafe (like this video shows)

I agree with Norman except I don’t know why you should even have the option to turn those off.

Joost, I think the comparison was fair. I wouldn’t think, out of the box, that any of the issues that he demonstrated would exist. If we were designing a database, they wouldn’t, so it’s not unreasonable to paint MySQL as somewhat… broken.

Broken by design but I suspect thats what made it fast as heck way back when
It wasn’t doing what PostgreSQL was doing so it looked like it was super fast

I’d still avoid it for the dual license business since PostgreSQL has NO such ambiguity
This just confirms that choice was sound for other reasons

I think MySQL is a fine database:

  1. It’s easy to install.
  2. Absolutely everything can talk to it.
  3. Several choices for client applications to manage, query, analyze your data. For something that is technically “open source” it has extensive tooling around it.
  4. It’s fast and be configured to be even faster if you remove the constraints.

What you gain by PostgreSQL:

  1. Actual “free software” meaning do what you will with it. MySQL is free if the customer so happens to run it on their systems already and you are just accessing it… but you cannot deploy MySQL as part of your application. This sounds nasty but honestly who deploys database servers with their app. In the Enterprise large companies have teams who manage their databases. Smaller companies probably have a “database server” and have one or multiple data stores available. I don’t know that I like the premise of software just casually installing MySQL or PostgreSQL on my machine in the background anyway.
  2. Schema support is wicked awesome. You can literally shard your data across schemas (think database in a database) which can be used in creative ways.
  3. First class support for JSON although the latest MySQL has that now too. Your typical shared host is probably running the MySQL from 5 years ago though so that doesn’t help.
  4. Several more I can’t even think to list at the moment.

What you lose by PostgreSQL:

  1. I can’t think of anything.

Why MySQL?:

  1. You are already comfortable with it and don’t have time to waste to find new tooling.
  2. Your extremely niche programming environment does not support PostgreSQL.
  3. You are already a licensed Oracle user and want to use MySQL for data transformations or reporting.
  4. You are stuck with a host who does not offer anything else.

If you are one of those 4 you should use MySQL. If you are not then it does not make any sense to use it when PostgreSQL can do everything MySQL can, and more, for less.

PostgreSQL has lots of tools as well + then EnterpriseDB folks for support IF you ever need it
And NO funky ambiguous licensing as to whether you need a license or not
Its free for ANY purpose commercial or not

Yeah it’ll be nice when Xojo supports the same license. :slight_smile:

When phillip shuttleworth sets up a foundation with a few million to fund future development :slight_smile:

[quote]What you lose by PostgreSQL:

  1. I can’t think of anything.[/quote]
    PostgreSQL is case-sensitive. You have to do:
SELECT Username  FROM myTable WHERE UPPER(Username) = 'FRED'

otherwise it won’t find ‘Fred’. This slows it down a lot.

which is the correct behaviour given you are not using a strange case-insensitve collation.

not necessarily. you could use for example:

  • a functional Index
  • a case-insensitive comparison operator
  • a case-insensitive data type like citext

it all depends on your usecase. PostgreSQL is highly customisable and extensible.

I use CITEXT for most of my fields. I only use TEXT when the data inside is suppose to be case-sensitive, which isn’t often. Most PG experts will advise using TEXT over VARCHAR too, FYI.

or use LIKE & ilike which can still be optimized

And if you want to get really crazy you can use case-insensitive regular expressions: http://www.postgresql.org/docs/9.0/static/functions-matching.html

Crazy or not, it is a sophisticated technique which you might need one day.

I hear what you are all saying about using CITEXT and ILIKE, but I have found they still search EVERY record so are just as slow as UPPER(). If I use LIKE ‘fred%’ it will not match ‘Frederick’, and username ILIKE ‘fred’ is as slow as UPPER(username) = ‘FRED’ since they both have to convert every field. Yet, username = ‘fred’ is very fast, but it is case-sensitive. Defining a fields as CITEXT still does the field conversion in the search, so is not any faster.

This is only a problem with tables with millions of records, so in my case I keep a lowercase duplicate of the main field we need to search for very fast searching!

Index the column and they wont be scans
There are limits to how these can be optimized though
ilike with a leading wild card HAS to search every record
ilike without a leading wild card can jump to that starting position and proceed from there

the way you used upper() it can be optimized as it has to visit every row, change name to upper then compare

this is experience on a db that has a table with several billion rows