Re: PGSQL or other DB?

On Fri, Jan 30, 2009 at 1:04 PM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> On Fri, Jan 30, 2009 at 08:37:02PM +0100, durumdara wrote:
>
>> - 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.
>
> Yes; have a read about MVCC:
>
> http://www.postgresql.org/docs/current/static/mvcc.html

Just because one uses MVCC doesn't mean it's free to add a new column
to a table. Adding a nullable column in pgsql is trivial. Adding an
index can be done concurrently. InnoDB supports MVCC, but MySQL's way
of doing things results in innodb tables being rewritten from scratch
for both of those for varying reasons, resulting in a locked table the
whole time.

Create index concurrently is a treasure to behold on large production
systems. I can't stress how important it is that I can create an
index on demand in the middle of the heaviest user activity on my
system and nothing hangs or locks from the user side waiting on it.
Same for adding a column.

>> - Quick and easy backup/restore system
>
> pg_dump works well, how quick it is depends on the amount of data you
> have obviously

More important that quick and easy is reliable, predictable, and
capable of being run on a live, working database (backup that is).
pg_dump definitely is those things.

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

Do you get a lot of filesystem errors? Or corrupted table errors.
Having table corruption be a common problem means something somewhere
is broken, whether it be your hardware or the db you're running. I've
been running pgsql for a long time, going back to the tailend of the
6.x era. I've never once had a corrupt table on a production server.
I am not alone.

> Use a proper backup system that takes consistent snapshots of your data,
> anything else will come back and bite you when you really don't want it
> to

I feel like I'm bathed with light and getting religious, but yes, this.

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

You need to get out of the habit of playing in your databases files.
Any database that requires you to routinely do that is broken by
design.

I get the feeling you've learned how to use a hammer (the file system
level manipulation thing) to fix a problem that's very nail like (file
corruption in mysql) and are looking for a place to use your hammer in
pgsql. Pgsql has no such nails sticking out of it.