Skip site navigation (1) Skip section navigation (2)

Re: MySQL refugee interested in pgSQL

From: Jonathan Gardner <jgardner(at)jonathangardner(dot)net>
To: "Jesse Thompson" <heckler(at)bendnet(dot)com>
Cc: pgsql-advocacy(at)postgresql(dot)org
Subject: Re: MySQL refugee interested in pgSQL
Date: 2004-04-29 20:41:07
Message-ID: 200404291341.07912.jgardner@jonathangardner.net (view raw or flat)
Thread:
Lists: pgsql-advocacy
On Sunday 25 April 2004 04:10 am, Jesse Thompson wrote:
> Hello, how do you do?
>
> I am a MySQL guy. I am interested in pgSQL. I have learned roughly
> everything I know about databases from using MySQL, and all that that
> implies. I would like to learn about PG and about "real" relational
> database theory. Links to any type of "PGsql for MySQL vets" and/or "Real
> relational database theory for MySQL vets" documents would be
> appreciated.
>

Take some time to sit down with the PostgreSQL documentation and read it 
through thoroughly. You won't be able to understand all the concepts on the 
first round, but you will know how the documentation is organized and where 
to find stuff.

I will tell you the things that I sorely miss whenever I have to use MySQL:

Transactions. Read through on everything in the documentation to do with 
transactions. They mean something entirely different in PostgreSQL. Look at 
serializable transactions as well. MySQL poo-poos transactions and only 
pays lip-service to it. Transactions are the foundation of PostgreSQL 
reliability.

Primary Keys / Foreign Keys / Constraint system: Learn what constraints are 
really easy to implement in PostgreSQL. This is another strong pillar of 
PostgreSQL. Start using them where appropriate. I like to really bolt down 
the constraints and then look for use cases where they need to be relaxed. 
Having strong constraints everywhere will ensure your data is always 
consistent.

Type system: Learn about types, how typecasts work, and how to use that to 
your advantage. The type system seems to bite people with poor performance 
when they aren't paying attention to it. But it is extremely powerful and 
when used properly, make PostgreSQL a dream to operate. The type system is 
the original reason why PostgreSQL was written in the first place.

When you master these concepts, everything else will start to fall in place. 
Another powerful feature are stored procedures, language hooks, and other 
things. I won't talk too much about these because the documentation does 
such a good job.

If you are in a company, it is best to pick someone to be the database 
administrator. They will take the time to become familiar with how 
PostgreSQL really works and how to tune it and such. You need at least one 
and probably only one of these experts. The rest of you can work pretty 
much independent of him. If you are alone, you will be responsible for the 
database like you are for the rest of the system.

Think of your database as a very powerful operating system, and start moving 
your business rules into it. People often build the database abstraction 
layer into the database itself because it can be done so efficiently.

As always, post your questions to the lists. They are extremely helpful and 
personal. I've learned most of my knowledge this way.

-- 
Jonathan Gardner
jgardner(at)jonathangardner(dot)net

In response to

pgsql-advocacy by date

Next:From: Rod TaylorDate: 2004-04-29 21:57:56
Subject: OSDL, PostgreSQL & Testing
Previous:From: Bruce MomjianDate: 2004-04-29 19:26:05
Subject: Re: What can we learn from MySQL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group