Re: MySQL refugee interested in pgSQL - Mailing list 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@jonathangardner.net