> 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
Is there a reason you can't use the timestamp data type? It's still 8 bytes, but you don't have to deal with the casting issues... > PostgreSQL also does not support ENUM type, although you can port your ENUMs > to PostgreSQL by using CONSTANTS. It's also worth noting that ENUM isn't part of the SQL standard (at least according to mysql's own docs) > 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. This is true.. but if you add in a good amount of updates (intranet type of app) then mysql will be much slower because it is locking the entire table. But for a lot of small websites it is 90% selects so mysql will be faster. > 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 I'm pretty sure that count(*) is optimized and if you've got an index on the columns you want to max/min then it will just look at the index and not the table... > 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 No, it doesn't. It can, but it doesn't have to. With the WAL (write ahead logging) stuff they've added in the 7.x series I am pretty sure fsync is disabled by default. > 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. Doesn't this contradict what you're saying below about table-level locking? If a thousand people all hit your page at the same time, mysql can only insert that row one at a time, right? > 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, > 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. Agreed... there's also the argument that a lot more ISP's have mysql support than postgresql support so that's a factor as well. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
