An Introduction to PostgreSQL

MySQL is much more commonly provided by web hosts. PostgreSQL is a much more mature product. Apparently, MySQL is fast when concurrent access levels are low, and when there are many more reads than writes. On the other hand, it exhibits low scalability with increasing loads and write/read ratios.

PostgreSQL is relatively slow at low concurrency levels, but scales well with increasing load levels, while providing enough isolation between concurrent accesses to avoid slowdowns at high write/read ratios. It goes on to link to a number of performance comparisons because these things are very sensitive to conditions.

So if your decision factor is, “which is faster?” Then the answer is “it depends on the usage”. If it really matters, test your application against both.”

And if you really, really care, you get in two DBAs (one who specializes in each database) and get them to tune the crap out of the databases, and then choose. It’s astonishing how expensive good DBAs are, and they are worth every cent.

When it matters.

Which it probably doesn’t, so just pick whichever database you like the sound of and go with it; better performance can be bought with more RAM and CPU, and more appropriate database design, and clever stored procedure tricks and so on – and all of that is cheaper and easier for random-website-X than agonizing over which to pick, MySQL or PostgreSQL, and specialist tuning from expensive DBAs.

PostgreSQL database is Open Source product and available without cost. Postgres, developed originally in the UC Berkeley Computer Science Department, pioneered many of the object-relational concepts now becoming available in some commercial databases.

It provides SQL92/SQL99 language support, transactions, referential integrity, stored procedures and type extensibility.

PostgreSQL is an open-source descendant of this original Berkeley code.

Postgres Pros:

• Transactions
• Foreign keys ( via refint )
• Triggers
• Subselects
• Views ( mostly )
• User-defined datatypes
• User-defined functions in a variety of languages: sql, c, pl/pgsql, pl/tcl
• Sequences
• Proper date handling

PostgreSQL feels quite a bit like Oracle. There is no SHOW TABLES, it’s \dt (IIRC). To quit it’s not QUIT or EXIT, it’s \q.

PostgreSQL didn’t have built in replication until recently.

Postgres doesn’t support ‘UPDATE a,b SET’ syntax. This would need to be translated into:

UPDATE a SET a.id=b.id FROM b WHERE a.f2 = b.f2; to work on Postgres.

Postgres does not provide a way to order columns inside the db.

Altering columns:

ALTER TABLE a ALTER COLUMN b TYPE integer;

ALTER TABLE a ALTER COLUMN b SET NOT NULL;

Postgres has no auto_increment option. Instead, use the type ‘serial’ For example:

CREATE TABLE a (

b INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT

);

would become

CREATE TABLE a (

b SERIAL PRIMARY KEY

);

plain INDEX’s cannot be added during table creation, Instead, you must issue a second query:

CREATE INDEX indexname ON tablename(columnname);

the syntax for defining constraints (such as UNIQUE or FOREIGN KEY) varies between the two databases.

For example, in your table definition if you had:

UNIQUE INDEX a1 (f1, f2) this would be changed to:

CONSTRAINT a1 UNIQUE (f1, f2) to be compatible with Postgres. Note that Postgres creates indices by default for

UNIQUE and FOREIGN KEY constraints.

There are many data types that do not exist in Postgres that you may be used to using in MySQL.

These include: blob, tinyint, integer unsigned

http://www.postgresql.org/docs/9.0/static/

Source: Codewebber