Re: PGSQL or other DB?

On Fri, 30 Jan 2009, durumdara wrote:

> - quick (re)connect - because mod_python basically not store the database
> connections persistently

If this turns out to be a problem, you may want to use some sort of
connection pooling software in order to reduce this overhead.
http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling#Connection_Pooling_and_Acceleration
gives a quick intro to this topic with links to some good solutions here.

> This "one datadir" is seems to be not too good for us. We used DBISAM in our
> clients, and many times when we got some filesystem error, we can simply
> recover the tables - from the files.

It's quite easy for ISAM solutions to get corrupted. Barring a bad
hardware failure, this shouldn't ever happen to a PostgreSQL database.
The write-ahead log implementation nevers leaves you in a state where the
database can't recover itself after a crash or filesystem error. You
should consider this a positive thing--this whole class of problems that
you think you need a solution to, needing to fix corrupted database files
regularly, shouldn't ever happen in a proper database implementation.

The first two sections of
http://www.postgresql.org/docs/8.3/static/wal.html give a good
introduction to this topic.

Rolling back to a backup should only be necessary after a serious hardware
disaster--the sort of situation where it's unlikely you'll be able to
trust any of your individual tables even if you had the option of
repairing them one at a time.

> I tried the MySQL before I tried PG. InnoDB is seems to be "forcing
> transaction system on MyISAM". And boolean data type is missing (solved with
> enum?).
> I don't like it all, but it is seems to be fast with little tables, and it is
> separate the database files to another directories which thing I like.
> Possible it have many limitations what I don't saw in first time.

http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007
has a lot of commentary in this area. MySQL+MyISAM will have the same
sort of problems you should be trying to escape. MySQL+InnoDB would be
much better. In addition to missing features, the other thing you should
be careful about is making speed measurements with a low client
count--make sure you evaluate any database you're considering with a many
clients going at once as you can simulate.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD