Scalability to very large databases under Linux

Dear Folks,

We have been running 6.4.2 under Linux on a dual Xeon box
with mostly good success. Our current dataset is 20 million records
of mostly numerical data (56 fields of floats [70%], ints [20%] and
a few text fields [10%]). The performance is acceptable, the only
problem so far has been the difficultly in floating type promotion
"confusing" the optimizer.

Our database is about to expans to 150 million records. Given
sufficient diskspace (e.g. a larger RAID array) will postgres
perform on this volume? Are there any gotchas? General comments
from people with experience with databases of this volume under
postgres?

The optimizer is overhauled in 6.5, due out in a day or two. I
recommend an upgrade. Optimizer should get less confused.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Dear Folks,

We have been running 6.4.2 under Linux on a dual Xeon box
with mostly good success. Our current dataset is 20 million records
of mostly numerical data (56 fields of floats [70%], ints [20%] and
a few text fields [10%]). The performance is acceptable, the only
problem so far has been the difficultly in floating type promotion
"confusing" the optimizer.

Our database is about to expans to 150 million records. Given
sufficient diskspace (e.g. a larger RAID array) will postgres
perform on this volume? Are there any gotchas? General comments
from people with experience with databases of this volume under
postgres?

The only problem I could see immediately is that the Linux ext2 filesystem has
a 2GB limit on any one file. Postgresql has functionality to get round that (as
was discussed a few months ago on the list) but it apparently causes problems
in some instances. I suggest you look through the archives for this one.

M Simms wrote on Tue, 15 Jun 1999 19:58:28 BST

Dear Folks,

We have been running 6.4.2 under Linux on a dual Xeon box
with mostly good success. Our current dataset is 20 million records
of mostly numerical data (56 fields of floats [70%], ints [20%] and
a few text fields [10%]). The performance is acceptable, the only
problem so far has been the difficultly in floating type promotion
"confusing" the optimizer.

Our database is about to expans to 150 million records. Given
sufficient diskspace (e.g. a larger RAID array) will postgres
perform on this volume? Are there any gotchas? General comments
from people with experience with databases of this volume under
postgres?

The only problem I could see immediately is that the Linux ext2 filesystem has
a 2GB limit on any one file. Postgresql has functionality to get round that (a
s
was discussed a few months ago on the list) but it apparently causes problems
in some instances. I suggest you look through the archives for this one.

There is one I know about, which was a fencepost-type bug with
the addressing in Linux. There was a patch posted in "hackers"
which I had to apply to get this to work in 6.4.2. Our current
database already exceeds the 2Gb limit.

--Martin