PGSQL or other DB? - Mailing list pgsql-general
| From | durumdara |
|---|---|
| Subject | PGSQL or other DB? |
| Date | |
| Msg-id | 498356DE.9060109@gmail.com Whole thread Raw |
| Responses |
Re: PGSQL or other DB?
(Sam Mason <sam@samason.me.uk>)
Re: PGSQL or other DB? (ries van Twisk <pg@rvt.dds.nl>) Re: PGSQL or other DB? (Tony Caduto <tony_caduto@amsoftwaredesign.com>) Re: PGSQL or other DB? (Tony Caduto <tony_caduto@amsoftwaredesign.com>) Re: PGSQL or other DB? (Greg Smith <gsmith@gregsmith.com>) Re: PGSQL or other DB? (Erik Jones <ejones@engineyard.com>) |
| List | pgsql-general |
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
- fast queries
- easy IDE to use (like pgadmin)
- the db server can handle many databases (webdb_nnn where nnn is an integer)
- 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.
- Quick and easy backup/restore system
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).
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.
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!
Please help me, because we need to determine which DB to use.
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 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.
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.
Thanks for your help:
dd
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
- fast queries
- easy IDE to use (like pgadmin)
- the db server can handle many databases (webdb_nnn where nnn is an integer)
- 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.
- Quick and easy backup/restore system
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).
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.
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!
Please help me, because we need to determine which DB to use.
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 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.
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.
Thanks for your help:
dd
pgsql-general by date: