[CLUE-Tech] Meanness to my RDBMS

[grant]

On Thu, 19 Apr 2001, Dan Harris wrote:

> Yes, I've done everything from removing a RAID drive to physically cutting the
> network cable on many RDBMS installs.  I've had the best luck with recovery on
> Sybase (UNIX) and MSSQL (NT4.0).. Haven't done any disaster recovery exercises
> with pgsql yet, but WOW that's great to know.  I am currently in the midst of
> planning moving 20 million+ rows from MSSQL to PostgreSQL and that's encouraging
> to hear your results.
> 
> -Dan Harris
> 
Run 7.1 if at all possible.  Outer joins and write ahead logging are so
nice to have.

Expect a big performance bump, and the ability to scale to more
CPU's.  Administration is almost too easy.  I have not tried moving files
to seperate disk packs for performance yet, because so far, my CPU has
been the bottleneck.  If I get to an I/O bottleneck, I may try moving
files to seperate physical devices using symlinks, but I don't know if
that will cause any problems.

On my laptop, I can do in the 10,500 insert per minute range with a 366
Mhz, 96M memory, EIDE slow laptop drive, and 66Mhz bus.  The CPU gets
slammed to the wall, but it seems to play nice.  If I run other things,
the updates happen slower, but everything moves along at a decent pace.

Another thing to remember is that PostgreSQL uses a multiple version
concurrence locking system rather than row locking (or page locking in the
case of MSSQL) so, if you need to lock the row and have exclusive access,
you need to do a select for update.  Otherwise, your writes will never
block a read, your read will just get current data as of all committed
transactions when the transaction that does the reads starts.  Every time
you do a transaction, it snapshots the database, then you have consistent
data for the remainder of the transaction.  If you have 2 transactions
trying to read and update the same row without specifying locking, you can
theoretically get an "Unable to serialize" error if it can't figure out
how to apply both versions, or if your update would vioilate a constraint
(foreign key or something) of another transaction that ran while you were
not looking.