Thread: MySQL refugee interested in pgSQL

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.

Due to the hype over the feud, what I fear here of course is that I will
be told that "learning Real DBM requires a Ph.D in DBM", and/or that
"Knowing DBM is an in-born trait, please exit the gene pool immediatly". I
don't suspect that either is the case.

I am also very torn about the feud. I have been using MySQL without
incident for years doing fairly complicated things under load. I want to
know more about the things that ACID pundits appear to hold so dear to
their hearts and MySQL eschews. I really can't fathom most of them yet.
But at the same time I am cut very deep by the animous shown towards MySQL
and it's users on a personal level. It is as if, by entrusting data of any
kind to a MySQL database, I am clubbing baby seals somehow.

I have redrafted this message probably a dozen times now to omit my
instinctive defensiveness of MySQL, my abilities as a DBA, or as a future
DBA. I have come to feel as though being a Real Programmer involves being
a DBA. To write code involves managing data. Managing any volume of data
involves a database, which to the limits of my knowledge would be a
relational SQL database. Quit smirking. SQL is fully non-transparent and
the ability to access data in an SQL database (espescially once you
involve transactions, locking, foreign keys, etc etc) requires full
understanding of all of these. Thus to be a coder one must be a DBA, and
the vitriol directed towards the inadequacies of MySQL directly impunes my
22 years of programming experience and puts me on the defensive.

So perhaps SQL and it's inability to be black-boxed is itself the problem.
From a Semantic perspecive it sure is ugly, even more so when you leave
the realm of MySQL. I've seen banner ads featuring hybrid cheetah-snails
suggesting this as well.. but I don't know of a viable DBMS alternative (I
didn't click the banner ads) so I'm left in the position where writing
virtually any computer program apparently requires ACID compliant RDBMS
knowledge or else the seals have to die. :(

- - Jesse Thompson
Bend, OR

hi,

There's two things you need to understand to get through the learning curve:

• What's hard to understand is the industry standard 'theory' of SQL.
• Compiling from source or installing from the modules from the Linux based distributions

Both points haven't really so much to do with Postgres as it does with the prerequisite knowledge required of a DBA and systems administration of your OS.

The word "novel" is often confused with "hard". For example, this link
is an article I wrote about installing the procedural language plr and applying it to graphing hits on a firewall. Procedural languages, triggers, rules are what you need to learn about.

Download pgadmin3. It will help you "see" what you are doing (GUI's are good for that).


cheers


Robert Bernier


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.

Due to the hype over the feud, what I fear here of course is that I will
be told that "learning Real DBM requires a Ph.D in DBM", and/or that
"Knowing DBM is an in-born trait, please exit the gene pool immediatly". I
don't suspect that either is the case.

I am also very torn about the feud. I have been using MySQL without
incident for years doing fairly complicated things under load. I want to
know more about the things that ACID pundits appear to hold so dear to
their hearts and MySQL eschews. I really can't fathom most of them yet.
But at the same time I am cut very deep by the animous shown towards MySQL
and it's users on a personal level. It is as if, by entrusting data of any
kind to a MySQL database, I am clubbing baby seals somehow.

I have redrafted this message probably a dozen times now to omit my
instinctive defensiveness of MySQL, my abilities as a DBA, or as a future
DBA. I have come to feel as though being a Real Programmer involves being
a DBA. To write code involves managing data. Managing any volume of data
involves a database, which to the limits of my knowledge would be a
relational SQL database. Quit smirking. SQL is fully non-transparent and
the ability to access data in an SQL database (espescially once you
involve transactions, locking, foreign keys, etc etc) requires full
understanding of all of these. Thus to be a coder one must be a DBA, and
the vitriol directed towards the inadequacies of MySQL directly impunes my
22 years of programming experience and puts me on the defensive.

So perhaps SQL and it's inability to be black-boxed is itself the problem.
>From a Semantic perspecive it sure is ugly, even more so when you leave
the realm of MySQL. I've seen banner ads featuring hybrid cheetah-snails
suggesting this as well.. but I don't know of a viable DBMS alternative (I
didn't click the banner ads) so I'm left in the position where writing
virtually any computer program apparently requires ACID compliant RDBMS
knowledge or else the seals have to die. :(

- - Jesse Thompson
Bend, OR

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
 

Hi,

Having been a DBA and a database developer for years, I think you may be
overly concerned. Learning DBA tasks and issues, is a process like any
other in the tech world. My experience is people usually fail due to
halfhearted efforts, I doubt you will fall into that category.

Understanding the theory behind Relational DB's, SQL grammar and typical
DBA tasks --- these things apply regardless of which DB you want to use.
For Relational theory, obviously you'll want to check out some of the
writings of Codd, Date and others, but some DB magazines have columns on
non-vendor-specific SQL theory and issues.

In the process of clubbing baby seals, you have already become familiar
with some aspects of general DBA tasks, I'm sure. Reading tips for DBA's
of Sybase, DB2, Oracle and other DB's besides pgSQL can be helpful, too,
since issues tend to cross the DB boundaries.

As far as, learning pgSQL specific things - turning on different logging
options in the configuration and re-running sql statements to observe
the output can be helpful. GBorg http://gborg.postgresql.org/browse.php
  can help with tools for monitoring, administration, converting from
mySQL, developing queries, tutorials and more. Obviously, subscribing to
all the pg mailing lists is good, too...

Much of the effort invested in learning pg related skills, will pay off
well since those skills are transferable to proprietary DB's and after
learning pgSQL you will find learning those to be easy(er). I don't
think you have anything to worry about, other than applying yourself to
task.

BTW, I think that the clubbing baby seals thing is a great idea for an
advertising campaign. Friends don't let friends use [insert package name
here] software because it results in the clubbing of innocent baby seals
- Won't someone think of the seal babies...   :)

Rob

Jesse Thompson wrote:
> Hello, how do you do?
>
> I am a MySQL guy. I am interested in pgSQL. I have learned roughly
[snip lengthy concerns]
> knowledge or else the seals have to die. :(
>
> - - Jesse Thompson
> Bend, OR

Jesse,

> 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.

www.dbdebunk.com
-- the content is kind of fragmentary, but it's a starting point.  Mostly I'd
go with books:
"Pratical Issues In Database Management" by Fabian Pascal;
"The Essence of Databases"
"The Relational Model" by CJ Date,
and the very hefty canonical "Introduction to Data Management Systems" by
Date.

I don't think you need to worry about hostility -- if you want to learn
PostgreSQL, we're not going to be hostile.   We reserve our ire for
unrepentant MySQL users ;-)

Anyway, the whole relational thing is a long road of learning (I picked mine
up, on the job and in books, over 10 years) without an immediate payoff which
is, I think, why so many DBA's blow it off and satisfy themselves with
groping about in the dark.  Some points to keep in mind:

1) The relational model is a theory based on a tested mathematical model;
2) The SQL standard is an imperfect implementation of the relational model;
3) existing RDBMSes are imperfect implementations of the SQL standard;
4) The standard, standards compliance, and the general level of database
theory and relational education has been steeply declining since the mid
90's, due to apathy and clever marketing my certain database vendors.

> But at the same time I am cut very deep by the animous shown towards MySQL
> and it's users on a personal level. It is as if, by entrusting data of any
> kind to a MySQL database, I am clubbing baby seals somehow.

Of course you are!  <grin>

Seriously, a little history (and keep in mind that I haven't done a survey, so
take what I say about other people's opinions with a grain of salt)

1) When MySQL was starting out, they (the development team) saw PostgreSQL as
their chief rival, and thus took every possible opportunity to slander us in
the press, their documentation, and online forums.  While the MySQL strategy
has changed and some people have apologized, some of the people who so
virulently attacked PostgreSQL a few years ago are still in leadership
positions at MySQL.

2) From *our* perspective, MySQL is a technically inferior database buoyed up
by expensive marketing, and to add insult to injury is not really an open
source project.  As such, many PostgreSQL volunteers have the exact same fear
and resentment of MySQL that Linux advocates do of Windows.   It's not really
MySQL that we hate, so much as a marketplace that is all-too-willing to
reward inferior solutions in glossy packaging.

3) Many of use also see MySQL as symtomatic of the general decline of database
technology, including the reversion to old, bad database models and the
fragmenting of standards.   People who started programming in those old, bad,
days don't want to go back to them, and fear that they will be forced to use
MySQL in the future.

--
Josh Berkus
Aglio Database Solutions
San Francisco

> 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.
>

I got a lot of great information from "An Introduction to Database
Systems" by Date. That book does an excellent job of showing the value
in relational databases over other kinds of databases.

An important part of that is the ability to manipulate information into
the preferred form before sending it to the application. In relational
theory, the storage of the data isn't important because you can always
use a view or a stored procedure to get the data in the form you want.

Another important part is that when you perform an operation on a
relation, you get another relation back. A mathematical analogy would
be: if you multiply two numbers, you get a number. This is known as
"closure" of a set of values to a set of operations. This idea is
important because it allows the seperation of data storage and views, as
described above, as well as more complex manipulations of data. Back to
the analogy, think how long calculations would take if you multiplied
two numbers and didn't get a number back. You could still calculate, but
it would be much more difficult to break the problem down into simple
steps.

Now, whether this is accurate or not is a matter of opinion, but many
people consider PostgreSQL to be more correct regarding the relational
theory and therefore more able to realize the above benefits. I'm not
sure of the current state, but at least in the past, MySQL provided no
way to use the result of a "SELECT" other than to send it to the
application. It had no views, or subselects, or "table functions", or
stored procedures. So, in effect, you were required to select only from
tables that were physically stored on disk. You couldn't, for example,
select from the result of another "SELECT". That removes the benefits of
using a relational database.

Many people also consider PostgreSQL better at maintaining the
consistency inside the database rather than depending on the application
to check the consistency. A good example is that MySQL will accept
"2004-02-31" as a date (I think it thinks it's march 3rd or something
weird, depending on how you treat it), whereas PostgreSQL will report an
error and roll back the transaction. It's certainly possible to do all
consistency checking in the application, but it's easier for me (as a
developer) to rely on PostgreSQL's own strong data integrity protection
and durable transactions. The alternative is to constantly have to take
into account "what if the power went off right at this line of code?".
And if you don't take that into account, you could have a database that
looks correct to the database (i.e. it doesn't have any damaged pages
and all the tuples are accessible), but looks wrong to the application
(e.g. you have an order record but no customer record to associate with
it).

These are some of the primary reasons why I think that PostgreSQL (among
others) delivers more than MySQL.

Hope this is helpful,
    Jeff Davis

On Sun, 25 Apr 2004, 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.
>
> Due to the hype over the feud, what I fear here of course is that I will
> be told that "learning Real DBM requires a Ph.D in DBM", and/or that
> "Knowing DBM is an in-born trait, please exit the gene pool immediatly". I
> don't suspect that either is the case.
>
> I am also very torn about the feud. I have been using MySQL without
> incident for years doing fairly complicated things under load. I want to
> know more about the things that ACID pundits appear to hold so dear to
> their hearts and MySQL eschews. I really can't fathom most of them yet.
> But at the same time I am cut very deep by the animous shown towards MySQL
> and it's users on a personal level. It is as if, by entrusting data of any
> kind to a MySQL database, I am clubbing baby seals somehow.

As someone who uses both MySQL and PostgreSQL (although I greatly prefer
PostgreSQL for most tasks, MySQL does have its uses...)  I find the feud
fuelled mostly by rabid users on both sides with less understanding than
the typically quiet, experienced users from both sides.

However, I see more ignorance of basic SQL and relational theory from the
MySQL users who feel they are being attacked by PostgreSQL users
personally.

While I have serious issues with using MySQL for things like accounting,
finance, human resources, data warehousing, or anything where the numbers
and math HAVE to be right, I have no qualms about using it for things like
content management and that type of application.  In the past, it was
much faster than PostgreSQL at the kinds of simple things that content
management required.  Nowadays, PostgreSQL is much faster.

If you've learned SQL on MySQL, you'll likely need to re-learn some things
that MySQL let you do that weren't really the best or proper way of doing
things.  For a list of such things, go here:

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

For learning relational theory, I'd recommend "Data and Databases:
Concepts in Practice" By Joe Celko.  It's a more modern oriented read than
many of the larger and older tomes put out in the 70s and 80s, and covers
everything you really need to know about relational databases in about 350
pages.

For learning PostgreSQL, I'd highly recommend reading as much of the docs
as you can.  I personally set up an ht://dig search engine on my
workstation and used it to index the docs for postgresql, apache, php, and
a few other things so I can search them easily and quickly.

Read up on MVCC, why it's great, what to look out for when using a
database like PostgreSQL that uses an in-store MVCC with dead tuples
floating about / being created.

Read up on sequences, constraints, partial / functional indexes.

Use Btree indexes unless you're sure another type works better.  Most
usually don't, except the new, semi-experimental GiST types.

Wander about gborg.postgresql.org lotsa good stuff there.

Philosophically, PostgreSQL is built to handle hundreds or thousands of
clients at a time.  This design philosophy means it will beat few
competitors when running a single threaded benchmark.  however, as you
increase the number of threads hitting postgresql and whatever other
database, you should notice PostgreSQL slowing linearly, or nearly so, as
the threads increase in number, while most other databases will slow down
much more quickly.

And we don't club baby seals around here, but I have seen a few elephants
roasting dolphin meat for lunch.  :-)

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