I am specing out a database for my company and I need some advice. The
database in mind will consist of one table, with about 300 bytes per
record. The table will be getting two or three million inserts a week
and it would be nice if it could handle a sustained 30 to 50 a second.
The table will have (ACK!) 4 indexes (if anyone can explain to me how I
can get away with less please let me know)
The indexes will be
int8 (primary key)
int4 (group number)
timestamp (creation date)
int4 (customer id)
The customers want to be able to query their data whenever and have it
be snappy.
So here is my question:
* Is there an OS that is best suited for postgres. All things being
equal I would like to run this on Linux.
* Is there an architecture best suited for this. Should I shell out the
extra cash for a 64 bit box over a 32bit one. Quad Xeon or Quad Alpha?
Quad Alpha or Quad UltraSparc?
* Since most of what I am doing is inserts I will assume that the disk
will be my bottleneck over anything else. Is this correct?
* Will the 7.1 WAL save me, when it comes to insert times?
* I read something about disabling fsync() to speed up inserts. How
much will this speed things up? I would consider it as I COULD rebuild
lost data from my logs in the event of a system crash and one night a
year of panic on my part is worth saving $100,000 in drive arrays.
Oh, and if any of you SQL guru's are still reading I'll show you the
queries I will be running to see if I can really ditch an index.
select * from table where customer_id = ? and primary_key = ?::int8
select * from table where customer_id = ? and group_number = ?
select * from table where customer_id = ? and creation > ? and creation
< ?
Thanks for all your help,
Orion Henry
CTO TrustCommerce
orion@trustcommerce.com
