Re: PGSQL or other DB?

On Jan 30, 2009, at 2:37 PM, durumdara wrote:

> Dear PG Users!
>
> I don't want to make a heated debate with this subject, but I wanna
> ask about your experiences because we need to make a new special
> site and we wanna know, which DB is the best for this...
>
> This website will supports up to 200 corporations.
> The functions are similar, but because of many differents of these
> corps (datas to keep, way to working them), we wanna make many
> databases (one DB for one corp).
> The site uses one main database that handle all public, and shared
> services, and store the links to subdatabases.
> The website will works with apache/mod_python, and the each of the
> corp's services are handled with another python module (from another
> (separated) database). The main structure of the source code already
> wroted.
>
> But: we need to determine, which database we will use in the future
> for this project.
>
> The main viewpoints:
> - quick (re)connect - because mod_python basically not store the
> database connections persistently

I don`t know much about python except the animal but could use a SQL
proxy to solve this issue

>
> - fast queries

Define fast, but remember a DB was never designed to be fast. However
it sounds like for the coupld of companies you wouldn't have to much
problems there.

>
> - easy IDE to use (like pgadmin)

>
> - the db server can handle many databases (webdb_nnn where nnn is
> an integer)

I don't have experience with PG and hunderds of DB's, but I think it
shouldn't be a problem

>
> - I can add/modify a table, or a field to a table without "full
> lock" on the table (like DBISAM restructure). Like in FireBird,
> where the "add field" change only the table description. I don't
> know that PG supports this way of the DB modifying.

How big are these tables and how frequently do they change? If they
change really frequently then you have a problem with your design in
general.

>
> - Quick and easy backup/restore system

pg_dump/pg_restore are your friends. webmin makes this also easy if
you want a point and click interface.

>
>
> Another important thing that I don't understand (what as I saw) that
> the PostGreSQL is store the databases in one, unseparatable file
> set, in a directory named data.
> In another databases, like DBISAM, FireBird, MySQL, the databases
> are separated to another directories/files.
>
> 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.
> When we want to backup or restore one database, we can do it in two
> way: a.) archive all files b.) make sql dump from database.
>
> If a file get corrupted in a database, then we can restore the datas
> from files, and this filesystem error causes problems only for this
> database, not for all.
>
> I very fear from to keep all databases in one place, because if they
> are corrupted, possible more of them injured (if they are not
> separated).
> I cannot make filesystem based (hard) copy from one db (only SQL
> dump enabled).

Why would just one file get corrupt?? You can store your
tables(indexes in different places if you want to but if one get's
corrupt you need to change/check/replace hardware anyways. PostgreSQL
doesn't behave much like MySQL where all of a sudden tables get
corrupted and you need to repair them.

>
>
> Ok, I saw that pgsql supports "tablespaces", but as I saw, this
> function can hold only table datas in the another directory, and not
> the full database can separated with them.

Correct...

>
>
> Because I don't used one PGSQL with many databases (up to 200), I
> don't know, what happening, and which cases possible. But I think
> you have many experience with it. Please share it with me!

Don't expect yourself to just set one table and/or DB back. This is
plain wrong and you need to make appropriate backups. If you really
want to set a DB back as a table then use sqlite, but that defeats
your requirements of 'fast'.

>
>
> Please help me, because we need to determine which DB to use.

I think PG will do very well in your situation, but you have to set
your mind off using files

>
>
> I started the usage of the PG in prev. month, and I liked it except
> the way of the data storage (one data dir).

I don't know any DB (except may be some Object DB's and sqlite) where
you can do that properly, officially and ACID compliant.

>
>
> 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.

Don't want to debate MySQL but PG is a better option IMHO, it recovers
VERY well from crashes where you are worried about (I know, I live in
Ecuador where power goes down every week or so and it always recovered
perfectly).

>
>
> Please help me, which DB is good for us, and how to configure, and
> use PGSQL with these database-set which we need to use.

PostgreSQL is good for you as long as you set your mind away from
restoring a DB by replacing a fileset. pg_dump/pg_restore are your
friends. If you do care about restoring a DB up to a point in time you
can do WAL shipping.

>
>
> Thanks for your help:
> dd
>
>
>
>
>
>

regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: ries(at)vantwisk(dot)nl
web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196
SIP: +1-747-690-5133