Thinking of going back to MySql from PgSql. Your thoughts?

Rails
1

Just thinking out loud. about switching from pgsql to mysql. So far,
haven’t experienced any differences between the two databases. I’ve
been using mysql for the past few years. I like it more because am a
mac interface junkie and mysql has sequel pro. Not that this is a
determining factor. But pgsql also has navicat (in my opinion, not as
good as sequel pro).

The more important thing is, web hosts that I have spoken to have said
that pgsql takes up a considerable amount of RAM than mysql… More
RAM on a web host means more $$$.

Moreover, since I’ve switched from Symfony PHP. Most of my other web
projects will be hosted with a mysql db.

I am sure there may be other more significant differences. But for my
every day use as a web developer, I don’t really notice the internal
features that pgsql has over mysql.

What are you thoughts

2

They make Navicat for MySQL too. I love it, couldn’t do without it.

That being said, if you really can’t tell the difference between PGSQL
and MySQL then you’re probably better off with MySQL :slight_smile:

Most developers make the switch to PG because they realize they need
more advanced DBA features that MySQL doesn’t offer. If you’re not
missing these features, why bother?

My $0.02.

Cue PG fan trashing MySQL based on technical merits here

http://twitter.com/subimage

On Tue, Aug 31, 2010 at 5:43 PM, Christian F.

3

One has said that he/she had to switch to pgsql because of scaling
problems with mysql. Is this true? Just thinking of the future

4

Christian F. wrote:

Just thinking out loud. about switching from pgsql to mysql.

You would be making a huge mistake.

So far,
haven’t experienced any differences between the two databases.

That’s nearly unbelievable unless you haven’t been using either much, or
unless you’ve been using the most absolutely basic features.

I’ve
been using mysql for the past few years. I like it more because am a
mac interface junkie and mysql has sequel pro. Not that this is a
determining factor. But pgsql also has navicat (in my opinion, not as
good as sequel pro).

Don’t choose your DB server based on pretty graphical clients. MySQL is
far inferior to Postgres in virtually every respect. Postgres performs
better, lets you have referential integrity along with a fast storage
engine, and is more extensible (PostGIS would not be possible in MySQL,
for example). I think MySQL is still too brain-dead to scan table
indices backwards, and its procedural language (if you ever need it) is
so bad as to be nearly useless. Also, Postgres’ syntax adheres more
closely to the SQL standard.

MySQL’s advantages over Postgres include ease of setup and (so I’ve
heard) clustering…and that’s it. For most projects, it is a mistake
to choose MySQL over Postgres.

The more important thing is, web hosts that I have spoken to have said
that pgsql takes up a considerable amount of RAM than mysql…

Never heard this.

More
RAM on a web host means more $$$.

So configure your Postgres install to use less RAM. Or use Heroku,
which has Postgres ready configured.

Moreover, since I’ve switched from Symfony PHP. Most of my other web
projects will be hosted with a mysql db.

That’s just compounding the mistake. :slight_smile:

I am sure there may be other more significant differences. But for my
every day use as a web developer, I don’t really notice the internal
features that pgsql has over mysql.

What are you thoughts

You’ll notice the difference as soon as you try to do something
non-trivial. Stick with Postgres. It’s by far the better product. The
fact that the differences are under the hood doesn’t make them less
important. There are also no commercial entities causing uncertainty as
to its fate.

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

5

On Aug 31, 2010, at 5:43 PM, Christian F. wrote:

Just thinking out loud. about switching from pgsql to mysql. So far,

I wouldn’t.

haven’t experienced any differences between the two databases. I’ve

Try inserting an invalid date into each database. See what it does.
Unless it’s changed mysql will insert it as 0000-00-00. PostgreSQL will
give you an error. I think mysql will set a warning, but it will still
do the insert.

Create a VARCHAR(100) field. Now stuff 101 characters into it. MySQL
will truncate the data. PostgreSQL will give you an error.

You might be lucky and never run into these but they are a PITA when you
do.

I am sure there may be other more significant differences. But for my
every day use as a web developer, I don’t really notice the internal
features that pgsql has over mysql.

Not sure how old these are, but might be interesting…

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

Awhile back I had the exact same Rails site running on MySQL and
PostgreSQL. Database created via Rails migrations. Data inserted via
the same load scripts. Identical in every way. A query that joined two
tables of about 30K and 100K rows respectively took 30 seconds in MySQL.
It took <1 on PostgreSQL. I was floored. The reason? The conditions
were complex enough that MySQL wouldn’t use all the available indexes.
This is a fairly well known problem. And it can be a killer. What good
are creating indexes if it won’t use them?

(for the record I may have some of my numbers wrong, but it was an
incredible difference and the explain/analyze output clearly showed
mysql wasn’t picking up indexes that were available).

What are you thoughts

Don’t change. Write a letter to Sequel Pro and tell them you’d like
them to support PostgreSQL :slight_smile:

-philip

6

Thanks for the feedback.

@Philip, @Marnen, I will definitely review these points. Thanks for
passing the information.

@marnen I got an email from one of the web hosts I am communicating
with. They have said this “Yes, PostgreSQL can be installed with our
Managed VPS’s. Many use with cPanel but keep in mind it’s resource
intensive so RAM usage will definitely be higher then MySQL.”

@seth, the only thing I dont like about navicat is that it has to open
up a new window for various tasks. i.e. open the table or query view.

7

On Wednesday 01 September 2010, Christian F. wrote:

Just thinking out loud. about switching from pgsql to mysql. So far,
haven’t experienced any differences between the two databases. […]

As for the databases themselves: Add or delete a column to/from an
existing large table, say > 1M rows. Or add/remove an index. In
PostgreSQL these operations will all be well below a minute. In MySQL
I’ve experienced them to take so long as to become prohibitive.

Also, if anything goes wrong during your migration, PostgreSQL behaves
properly transactional. MySQL will leave you with a partially applied
migration where you have to sort out the mess manually.

Possibly it is a matter of taste or familiarity, but I think the
PostgreSQL reference docs are much better and particularly much better
organized. As a bonus, on Debian Linux I have all the command and SQL
language references as man pages.

The psql commandline tool, in my opinion, is far superior to mysql. For
one thing, auto-completion works.

The more important thing is, web hosts that I have spoken to have
said that pgsql takes up a considerable amount of RAM than mysql…
More RAM on a web host means more $$$.

I haven’t been there myself, as I mostly work on small-scale apps, but I
take it that properly configured for a high load, both DBMS take lots
and lots of RAM – and use it to improve performance. If you are
concerned about the RAM use, you ought to look into how these systems
are configured. Their default configurations are meant to work, but are
in no ways optimized for what you might be doing with your databases.

Michael

–
Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/