PostgreSQL or MySQL??

redgorilla

Which one would you guys recommend using with PHP -- PostgreSQL or MySQL and why??

People are telling me that there are security issues with MySQL and therefore that they would go with PostgreSQL. What do you guys think?

Also, if you do recommend PostgreSQL, could you please recommend any books about how it functions with PHP. It seems there is a lot more information about PHP and how it works with MySQL, but not so much on how PHP works with PostgreSQL.

I really appreciate your help on this!!

syntoad

Although all I have ever used is MySQL here is my opinion, (and what I've heard) From what I understand newer versions of PostgreSQL are a lot faster than MySQL, but on the other hand postgres has had a bad history of corrupting data, while this isn't an issue as much anymore as it used to be, it is still something to think about.

The biggest reason I chose to make my site use MySQL was the fact that it is more widely used, If I ever decide to get a new web host I will be a lot more likely to find one that supports MySQL than PostgreSQL.

The probally best solution is just to use a good database abstraction layer, such as the one in PEAR, and try them both out for a while, to see which one you like best.

mrblom

I like both, but i prefer PostgreSQL as this is a "real" database. MySQL has A LOT of limitations compared to PG.

I like MySQL for its speed and simple use, but postgres has a lot more to offer, and I don't thinks it's far away from MySQL when it comes performance.

It all comes down to what your needs are. In most webprogramming MySQL would do just fine, but sometimes it's just not good enough.

When I said "real" I meant that it can be compared to for example databases like Oracleand msSQL in functionality.

Sxooter

The answer of course, is, a question.

What are you planning on doing with your database. Both MySQL and Postgresql have advantages over each other, plus a lot of overlap.

It's like asking if you should get a mainframe or use a server farm. They both are massively powerful, but they have very different strengths.

syntoad: Postgresql hasn't had any serious corruption issues since the 6.5 series came out over four years ago. the only time I've seen a postgresql database become corrupt since that time is when it is running on a machine with bad memory, hard drives, or CPUs. Something no database can overcome.

Back to the issue of which one. postgresql's advantages lie in it's extensibility. You can create your own datatypes, functions, triggers, rules, stored procedures, partial indexes, etc...

Since Postgresql's main strengths lay in it's greater complexity, doing simple tasks with Postgresql is not going to be any easier or harder than MySQL.

But, if you need to use a spherical coordinate system to map the surface of the earth, the data types to store the data and the functions to operate on them are built right in.

With this greater complexity comes a higher learning curve. If you want your queries to run fast, it takes more than just the create index statement like in MySQL.

Postgresql supports multi-byte character sets and localization standard, with no add ons. It also can ONLY operate as a transactional database. It doesn't work any other way.

It won't run as root, so, if someone finds an exploit in postgresql, they MAY be able to get your data in the database, but they don't own the box. Big difference, and one a lot of MySQL advocates (and even some postgresql advocates for that matter) don't get.

What I'd recommend is sit down and install both and play with them. If you have a friend who is a DBA and knows data design theory, have him help you out. You'll find that postgresql can run most all queries from books by folks like Celko with no problem. with MySQL, you will have to learn a different way to do it, as most of the fancy SQL in those books simply will not work. (i.e. anything with subselects, etc...)

To make up for these shortcomings, the MySQL team has made some very useful workarounds, to overcome problems like race condidiotns between selects and updates. What winds up happening is you learn the MySQL way, and then find out that no other database in existence does it this way, and you have to learn it all over when you migrate off of MySQL.

For this reason, and many others, I do NOT recommend MySQL for beginners, as you might learn quite a few bad habits.

john_smith

PostgreSQL (and other real RDBMS's) is a whole level above MySQL. However, for quick and simple stuff MySQL is easy enough; plus it's probably already installed. PG has a greater learning curve: functions, triggers, subselects, views, rules, transactions, cursors, and more. You'll also have to unlearn some sloppy practices acquired from using mysql.

I'm trying to switch from mysql to postgresql, but I'm having very serious speed problems with simple things like updates and selects. Take for example this query on two tables with 100K records each:

select t1.*
from table t1
join table2 t2
on t1.id=t2.t1_id
order by t1.name
limit 100
offset 100000;

I can't get it to take less than about 30 SECONDS! I've quadruple-checked indexes, etc. Hopefully I'll be able to find a solution and eat my words - I love everything else about PG over mysql 🙂.

ultraslacker

Two likely causes - you have not updated statistics or do not have an index on t1.name.

EXPLAIN output?

john_smith

I've done both. Plus try CLUSTER, multi-column indexes, cursors, ...

NOTHING improves the query's speed. Over on the PG general list they're telling me it's NECESSARY for PG to retrieve all rows and sort them.

QUERY PLAN

Limit (cost=19546.62..19546.87 rows=100 width=62) (actual time=20557.00..20558.00 rows=100 loops=1)
-> Sort (cost=19321.62..19571.32 rows=99881 width=62) (actual time=19775.00..20410.00 rows=90101 loops=1)
Sort Key: l.url
-> Hash Join (cost=2471.00..7662.54 rows=99881 width=62) (actual time=3013.00..12002.00 rows=100000 loops=1)
Hash Cond: ("outer".id = "inner".link_id)
-> Seq Scan on links l (cost=0.00..2444.81 rows=99881 width=42) (actual time=65.00..1790.00 rows=99881 loops=1)
-> Hash (cost=2221.00..2221.00 rows=100000 width=20) (actual time=2946.00..2946.00 rows=0 loops=1)
-> Seq Scan on stats s (cost=0.00..2221.00 rows=100000 width=20) (actual time=36.00..1936.00 rows=100000 loops=1)
Filter: (referrer_id = 1)

Total runtime: 20571.00 msec
(10 rows)

john_smith

Hrm, I guess MySQL does the same thing. It appears my problem is with LIMIT and OFFSET on large tables

http://archives.postgresql.org/pgsql-sql/2002-12/msg00250.php

Apparently rows keep getting retrieved until there are enough to satisfy the LIMIT. So...if the OFFSET is far into the table... I don't understand - I mean with a query like:

select url
from links
order by url
limit 10
offset 100000;

Why not use the url index to go to record 100000 and get the next 10, rather than getting ALL 100000 records, sorting them, then return the last 10?

Surely Google et al have HUGE tables - wonder how they handle paging results FAST.

ultraslacker

I see someone has already recommended sort mem.

Just out of curiosity, how does the query perform without ORDER BY? And did you cluster links on the index for url?

ultraslacker

Surely Google et al have HUGE tables - wonder how they handle paging results FAST.

The data isn't stored in tables, but in flat files distributed over NFS.

Really, for this query you do not need the overhead of a rdbms.

john_smith

Originally posted by ultraslacker
I see someone has already recommended sort mem.

Just out of curiosity, how does the query perform without ORDER BY? And did you cluster links on the index for url?

Changing some of the values in postgres.conf helped.

I've tried without ORDER BY as well as CLUSTER. Nothing really helps. Just something about LIMIT and OFFSET that it has to retrieve all rows up to the offset, then sort them. Seems to me there's got to be a way to optimize those sorts of queries (by the db itself - but of course I'm not a db-programming guru so... 😉 ).

At any rate, I've got the queries about as fast as MySQL so I'm happy 🙂. I'm playing around with this:

select url
from links
where id > 100000
order by url
limit 10
offset 100000;

which is VERY fast. For paging you could pass the first and last id's of records for previous/next pages. No idea how you'd go about jumping to pages (like from page 2 to page 25) without just taking a guess at what the starting id might be (since ids will become quite spread out after DELETE's).