> John, do you have any idea which parameters to change?
up the buffer size (I don't have the docs in front of me, I'm at a iinternet
cafe on wifi at the moment), set WAL_SYNC_TYPE (or whatevr it is) to fdatasync,
up the wal buffers some too, increase the sort buffer size significantly, yada
yada. don't go overboard, just reasonable. I usually use about 10000 main
buffers (80MB).
> I tried similar tests also against PSQL 8.0 as well. But the results
> are exactly same.
>
> My final usage is going to be exteremly high. I would need something
> like 1000 inserts/updates per sec. Do you think PSQL will scale?
watch your disk IO. `iostat -x sda -x sdb -x sdc 5` is your friend (replace
the sda b c with whatever your physical drives are called). keep the await
times under 100mS if you can. *never* use RAID5 for database volumes, use
raid1 or 10 (striped mirrors). With postgres 8, you can split your tablespaces
across several physical disk drives, this can help alot if you can balance your
disk IO. doing this requires some finesse and careful analysis of your
database usage patterns. postgres stats can help determine which tables are
getting the most insert/update activity, you want to balance these across the
available physical drives.
good database administrators who understand all this sort of tuning are a rare
breed, and in demand. I have a oracle DBA on staff who knows this stuff really
well for oracle... the same principles apply to postgres, but are less well
understood.