MySQL versus PostgreSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

MySQL versus PostgreSQL

Post by malcolmboston »

ok, just got mandrake 10 and when i finally get it working i will have postgreSQL as well as MySQL servers

now i have no intention of switching over from MySQL when building applications for clients, but i just wanted to know if there were any advantages to postgreSQL (im sure i read somewhere it supports many useful commands that mysql doesnt (yet))

i have quite a few personal applications which ive built such as personal organisers, site sorters, moneytracker etc etc which work fine on MySQL but my databases are growing and i was just wondering if its worth the switch?

i also believe theres a new law coming out / come out that stops anyone from selling anything that has MySQL in it, wouldnt this apply to us!?

Mal
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

does anyone here actually use postgreSQL?
how does it compare to MySQL (not mssql)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I'm sure you'll get an answer eventually, there are definitely a few people here using postgreSQL.

Mac
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

malcolmboston wrote:does anyone here actually use postgreSQL?
how does it compare to MySQL (not mssql)
I do.

It depends on which versions. 7.3.0+ has a substantial fix for an issue that really dragged things down in table creation - to the tune of a 400% improvement in one of my scripts.

So I'll just assume you mean a relatively new version of PGSQL.

In general, its slightly faster - depending on what you are doing - than mysql. It has better data retention (full ACID-compliance), and has fantastic support for stored procedures.

Of course, mysql-4 has similar features now. Its fairly snappy, has solid (but not full) acid-compliance, and has some support for a variation of stored procedures.

All in all, the difference - for programmers - is becoming very slight. When you add a abstraction layer in (for example, adodb), its almost impossible to tell them apart.

The key major remaining difference is deployed base. The number of webhosting companies offering MySQL is *substantially* higher than the number offering PGSQL. In fact, I'd say its close to 2-to-1.

There are slight differences in large number handling, booleans, blobs, sequences/auto_number fields, and stored procedures, but its very managable.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

yeah, as i mentioned i have no wis to develop PostgreSQL app's for clients i was just wondering whether or not it was worth porting my own large personal localhost apps to PostrgreSQL.

I dont think i'll bother though, Thanks for the advice
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

Not sure what the law is, but my guess is it stems from the microsoft bundling issues. You can't bundle the mysql db with the app, but you just tell the client to go and download/install it themselves. Then sell them the app as is...
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

lostboy wrote:Not sure what the law is, but my guess is it stems from the microsoft bundling issues. You can't bundle the mysql db with the app, but you just tell the client to go and download/install it themselves. Then sell them the app as is...
We werent talking about MS-SQL, we were talking about MySQL and Postgresql.

Neither has any 'licensing/bundling issues' of the type you describe.

Up until recently, mysql was the db-of-choice on most linux distro's because of its easy setup, and fast processing. However, postgres has seriously jumped ahead lately in both.
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

Its been a few years since I use postGres,
but a couple things come to mind.

Postgres required sequence tables versus auto increment
with a Mysql column. This forced me to use an object id (OID)
when referring to the last inserted id and ultimately for a primary key.

It became easier and more clear to recall mysql_insert_id() with PHP and set the column to auto increment.

Other than than the lack of a decent database admin tool at the time, Postgres worked out ok.
Post Reply