Re: Survey: MySQL vs PostgreSQL for PHP

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 select. 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. Ilia FUDforum Core Developer ilia@prohost.org 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.