PostgreSQL vs MySQL

Have you ever wondered why you should choose one open source database over another? What features would make the most sense for your Organization? Maybe you're a developer looking to learn a database and can't choose where to start?

The folks at PostgreSQL have put together a wiki, Why PostgreSQL instead of MySQL. It's, by no means, complete at this time but it is a good start. The wiki is not editable by the public but it is open for reading. The wiki entry compares PostgreSQL 8.1 and MySQL 5.0. Some of the points raised are:

• Data Integrity - MySQL has improved with a "strict mode"

• Database Engine Core - No comparison is complete without a bit of FUD: "It is worth observing that the database engine is part of the core of PostgreSQL, whereas InnoDB is a dual-licensed product currently licensed from Oracle Corporation. It's uncertain how Oracle may alter InnoDB in the future as they act in competition with MySQL AB, whereas PostgreSQL has no such conflict of interest."

• Foreign Keys - MySQL: "the CHECK clause is parsed but ignored by all storage engines"

• Speed - MySQL is faster but PostgreSQL is narrowing the gap

• Application Portability - sparse now but hopefully will grow

I hope this is frequently updated by the PostgreSQL community. It will make a great resource. It would be nice if the people at the PostgreSQL.org website would give a few MySQL developers write access to enter counterpoints to the details in the wiki. An alternative would be for MySQL to answer with their own wiki.

postgresql mysql comparison

Native UUIDs in PostgreSQL

Stefan Arentz posted an entry on his blog, about a hack he's done for PostgreSQL. He's added a native UUID to the database. This is pretty slick. Most of your users will never notice but it can help on databases that need it. He lists some advantages on the google code page:

Database Size - Using a native UUID type on a table that also has an index on the uuid column saves 47% space. This is good because there is less data on disk and in memory. Which really matters if you have potentially hundred of millions of records. The native UUID type uses 16 bytes of storage whereas a ascii uuid column uses 38 bytes.

Consistency - The native UUID type only accepts UUIDs that are well formatted. Of course you can also do this with a constraint check, but having a native type with a fixed format is more elegant in my opinion.

Transparent - Nobody is going to notice the difference. Because of the implicit typecasting you can use UUIDs as if they are ascii values.

postgresql uuid data types

Does anyone use PostgreSQL in Production?

The number of Oracle users is huge and Oracle has the numbers available. The number of EnterpriseDB users is smaller but EnterpriseDB tracks those also. I would like to know how many people are using PostgreSQL in production and what kind of systems they are running.

If you monitor the some of the Postgres email lists, such as General, Jobs, and, maybe the Docs group, you can get a basic feel of some usage but only from the more verbal and connected in the community.

Does Forrester Research or Gartner track open source database usage? I haven't seen anything.

I know the community tracks things like downloads but that is incredibly misleading for production usage. Do you know anyone that is surveying or tracking these kinds of stats?

LewisC

postgrsql usage research

GlassFish Benchmark Results Keep Climbing

woodjr over at the sun.blogs.com site that they ran a new SPECjAppServer 2004 with GlassFish v2 and got a better response than what Josh posted a while back. Check out the post,GlassFish Benchmark Results Keep Climbing. LewisC Tags: postgresql sun benchmark

MicroOLAP Database Designer for PostgreSQL ver.1.2.1released

MicroOLAP Database Designer is an easy CASE tool with intuitive graphical interface allowing you to build a clear and effective database structure visually, see the complete picture (diagram) representing all the tables, references between them, views, stored procedures and other objects. Then you can easily generate a physical database on a server, modify it according to any changes you made to the diagram using fast ALTER statements. You can get it for PostgreSQL and MySQL.

What's new:
[*] CHECK constraints expression Reverse improved a lot
[*] Model tags are treated case-insensitive now
[*] Disallow modification of columns and constraints referenced by Foreign Key
[+] Smart reference counter added to avoid name duplicates
[+] Ability to choose default folder for model files added
[+] SQL Reverse Engineering for CREATE statements added
[+] "Open last edited files at startup" feature added
[+] Interval backup/autosave
[+] "Leave current tool" options added
[+] "Quick hand tool" feature added
[+] Ability to select references or tables connected to selected table (Popup menu item)
[+] Enhanced reference visualization for selected tables added
[-] "Modify Database is trying to ALTER TYPE for timestamps with the same parameters" bug fixed
[-] "Modify Database is trying to DROP DEFAULT on SERIAL columns on PostgreSQL 8.2.x" bug fixed
[-] "Duplicate operation in Table Editor duplicates column and constraint ID" bug fixed
[-] "Table object context menues "SQL Preview" & "Format" open wrong Table Editor tabs" bug fixed
[-] "Error while reverse scalar types: index out of range(4)" bug fixed
[-] "Storage properties were fetched incorrectly" bug fixed
[-] "Impossible to define SETOF return of the SP in Stored Routine Editor" bug fixed
[-] "Copy/Paste is unavailable in rule actions memo of View Editor" bug fixed
[-] Problems with references numeration
[-] Bug with object search by partial string
[-] Scale Combobox is not updated on Actual Size (F5)

Check it out.