postgresql

[Cole Tuininga]

On Fri, 2003-06-06 at 15:03, Erik Price wrote:
> * I have just started playing with Postgres (I used to use MySQL 
> exclusively) and I must say it is a really nice database, from the 
> application developer perspective.

I agree that it has some great features, but when we looked into using
it, we found some serious drawbacks to it.  If anybody has comments on
these, I'd be very interested to hear them.

1) Indexes require casting.  If you have a field (let's call it 'id')
that is a bigint, and you add an index to it, you have to cast your
number to a bigint or the index will not be used.  For example, with
mysql, I would do:

select * from tablename where id = 1;

However, in postgres, this will not make use of the index.  To use the
index, I need to do:

select * from tablename where id = 1::bigint;

This is a bit of a pain in the buttocks.  8)

2) Inconsistent performance.  We have a database with a normalized table
of first names, as well as a normalized table of last names.  Each table
has > 50,000 rows in it.  We used the sample fuzzy search module that
comes with the debian package to do some tests and the behavior was
pretty wildly inconsistent.  Some times a query would take 5 seconds,
sometimes it would take 30.  On the same machine.  

Initially we figured that the first shot at the query would be slower,
as there was no cached data.  However, sometimes the 5 second response
would be immediately after starting postgres, while the 30 second
response would be performed right after...

3) Messy permission configuration.  We've gotten to used to mysql's
"mysql" database for controlling access.  It's very nice, and as of yet,
postgres doesn't have anything comparable.

4) Database dumps.  The database dump tool that came with postgres
appeared to be plain 'ole broken.  It would crap out in the middle of a
dump.  If it didn't crap out, the dump file would often contain syntax
errors.

All of this being said, I have to say that postgres definitely impressed
me with a few things.  The range of features was pretty nice. 
Everything from stored procedures in multiple languages (including my
personal favorite - python), views, triggers, real cursors (from a
programming standpoint), and so on.  

For our purposes, it seemed to need a bit of maturation, but it seems
like it has some serious potential.

-- 
"Maybe I'll be able to get a job when I graduate..."
 -Linus Torvalds

Cole Tuininga
Lead Developer
Code Energy, Inc
colet at code-energy.com
PGP Key ID: 0x43E5755D