Skip site navigation (1) Skip section navigation (2)

Re: PGSQL or other DB?

From: ries van Twisk <pg(at)rvt(dot)dds(dot)nl>
To: durumdara <durumdara(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: PGSQL or other DB?
Date: 2009-01-30 20:21:34
Message-ID: 3527D7E5-5DF5-48A1-975D-833F5373F210@rvt.dds.nl (view raw or flat)
Thread:
Lists: pgsql-general
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






In response to

pgsql-general by date

Next:From: Merlin MoncureDate: 2009-01-30 20:32:45
Subject: Re: Pet Peeves?
Previous:From: Alan HodgsonDate: 2009-01-30 20:19:26
Subject: Re: PGSQL or other DB?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group