I've recently had the 'pleasure' of porting a large MySQL application to 
support PostgreSQL, through that experience I've gained some insight into the 
differences between the two as well as how they are supported by PHP.

First allow me to point out that MySQL support in PHP is a lot more mature 
then PostgreSQL support. This can be seen through several factors, such as 
larger number of functions, which do more as well as the fact that only 
recently (php 4.2.0) did PostgreSQL interface functions got renamed to a more 
standard convention that MySQL functions already used since 3.0 days.
For example, while using MySQL you can easily get properties of a field by 
simply using the mysql_field_type() function, pg_* has nothing similar. The 
only alternative is to run a query on the PostgreSQL internal tables, which 
contain the properties of a column. The biggest annoyance I've come across is 
the fact that while using PostgreSQL with PHP is that when you fetch a row 
you must specify the number of the result, while in MySQL, that is handled 
internally by PHP for you. This means that your PHP scripts must track the 
row numbers themselves.

PostgreSQL also has a number of things that it lacks when compared to MySQL. 
For example, it does not support unsigned integers, which forces you to use 
int8 to store timestamps, which will eventually reach 4.2 billion. In case 
you are wondering what is the big deal, int4 or int8, besides 4 more bytes of 
memory, well on x86 processors, which are 32 bit, 32 bit numbers are handled 
faster then their 64 bit counterparts. Now a days the performance drop as the 
result is quite small, but in the end everything adds up.
PostgreSQL also does not support ENUM type, although you can port your ENUMs 
to PostgreSQL by using CONSTANTS.

Now we come to the actual database speed itself. In this regard in most 
applications MySQL is MUCH faster probably because it has to do allot less 
work then PostgreSQL does. For example, lets analyze the most common action 
performed in a database system, a SELECT. When you do a select in MySQL, 
MySQL internally locks the table for the duration of the select. PostgreSQL 
on the other hand does a row level lock, internally, for every row you 
PostgreSQL also does not optimize count(),max() and min() queries, which in 
MySQL are instant regardless of the table size since they are cached 
internally. PostgreSQL on the other hand needs to go every single row in the 
table. However, I should note that PostgreSQL developers I spoke to, told me 
that they plan to add this kind of functionality in the next release of 
PostgreSQL, whenever that happens. PostgreSQL also syncs any inserted or 
updated to disk right away to ensure that you don't loose any data should the 
computer crash on the other hand MySQL keeps memory buffers and will often 
not sync to disk right away to avoid disk IO. PostgreSQL offers greater data 
security, which would be important in a shopping cart, however it looses much 
speed in this approach compared to MySQL's approach which is ideal for 
programs where fast inserts are critical, like a web counter for example.

PostgreSQL does have a number of advantages that should not be overlooked and 
are certainly very important. Such as views, that allow you to create 
'virtual tables' who's data is a combination of a complex join, which 
simplifies your selects to select * from table1_table2_view;
PostgreSQL supports triggers and stored procedures which can be coded in perl, 
python, plsql and C. These triggers and functions can clean up  not to 
mention speed up you code by moving various database code from PHP to 
PostgreSQL. PostgreSQL also supports various table locking implementations, 
which include row level locking. The benefit of using it is what while a 
certain row in a table is locked other users who do not require this row can 
still read from a table. In MySQL once a table is locked to write, no other 
user can read from the very same table until the lock is released. This in 
particular makes PostgreSQL much more scalable then MySQL. Unlike MySQL, 
PostgreSQL supports transactions with rollbacks, which allow you to actually 
make a bunch of queries and then with a single commit apply them to the 
database. The cool thing is that you can actually undo bad query by canceling 
the transaction.

Bottom line is that both MySQL and PostgreSQL have their 'markets'. IMHO in 
most cases MySQL is a simpler, faster and easier solution to use. However, if 
you require 100% data integrity and are dealing with sensitive data and in 
those case probably can spend a little more or hardware PostgreSQL should be 
your tool of choice.

FUDforum Core Developer

On July 5, 2002 04:59 pm, Lazor, Ed wrote:
> How many here feel PostgreSQL has surpassed MySQL as the better backend for
> PHP?  This would be based on performance (speed, scalability, etc.) and
> features.
> -Ed
> ***************************************************************************
>* This message is intended for the sole use of the individual and entity to
> whom it is addressed, and may contain information that is privileged,
> confidential and exempt from disclosure under applicable law.  If you are
> not the intended addressee, nor authorized to receive for the intended
> addressee, you are hereby notified that you may not use, copy, disclose or
> distribute to anyone the message or any information contained in the
> message.  If you have received this message in error, please immediately
> advise the sender by reply email and delete the message.  Thank you very
> much.

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to