Re: Pushing the Limits

Cabbar Duzayak wrote:
> Hi,
>
> We have huge amount of data, and we are planning to use logical
> partitioning to divide it over multiple machines instances. We are
> planning to use Intel based machines and there is not much updates but
> mostly selects. The main table that constitutes this much of data has
> about 5 columns, and rows are about 50 bytes in size, and 3 columns in
> this table need to be indexed.
>
> So, what I wanted to learn is how much can we push it to the limits on
> a single machine with about 2 gig rams? Do you think PostGres can
> handle ~ 700-800 gigabyte on a single machine? And, is it OK to put
> this much data in a single table, or should we divide it over multiple
> tables? If that is the case, what would be the limit for a single
> table?
This may help... or then again :-)

We're running one much smaller database of some 200,000,000 records (as
well as several other PG databases). We've partitioned this into yearly
datasets (15 years) and have a clustered index on timestamp. (In terms
of volumes, we have several databases of up to 100 odd Gb on a variety
of boxes).

It's on a single 7200RPM SATA hard drive, P4 3.0Ghz & 2Gb ram. Works
pretty reasonably, with simple queries taking a few seconds & not many
typical extracts taking more than a minute or so. So yours is probably
reasonable.

I also have other databases on several systems, including an AMD64x2
system with striped raptor (10k rpm) drives for the database & 4Gb of
memory, & another A64 with 1Gb memory, as well as Xeon x2 Windows with
3.5Gb memory & 32bit Linux with 2.5Gb memory

I've used 64 & 32 bit Linux with Postgres compiled from scratch on both.

No real benchmarks, but some observations/suggestions:

Stick your data on the fastest RAID array you can afford. WD SATA
Raptors are a good cost effective approach (cheaper than SCSI).
Athlon 64's beat any Intel P4 options, esp under Linux. I have not tried
a Core2Duo system (but hope to very shortly :-)
64 bit Linux with 64 bit locally compiled Postgres is faster than either
Windows or 32 bit Linux (also locally compiled on Linux).
Use as much memory as you can afford, and ensure you configure Postgres
to use it.

Cheers,

Brent Wood