I've got a huge database I'm building for my company of adverse drug
reaction data dating back to 1969, totaling well over a million records,
and comprising several separate tables.
My question is this: how is PostgreSQL's performance for datasets this
large? I have a sampling of this data from 1990 to October 1997, about
900,000 records, that someone in my office put into Access. Bringing up
an individual table is fine, as long as we don't sort it first (which
takes about 5 minutes on a P-166 with 64 megs of RAM). In a query I
built, even after 30 minutes it hadn't finished running (it was at about
50%)! This is quite obviously way beyond Access's meager capabilities.
I've never built something this big in PostgreSQL (or anywhere else for
that matter). What kinds of advice can anyone give me who has built
something this large? Supposedly Oracle can handle this kind of thing,
but I don't have the time to get Oracle, get it installed and figure out
how to use it. I have PostgreSQL ready to go on my PPro 200 Linux server
right now, and I have to have this thing ready in a week or so!
Ultimately, this database will be used to build subsets of data based on
individual drugs -- say, a client wants all adverse experiences for
tagamet in 1995, and we will want to provide this data somehow on disk,
tape, or whatever.
Many thanks in advance for any and all advice.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy/
-----------------------------------------------------------------------
It is a hard matter, my fellow citizens, to argue with the belly,
since it has no ears.
-- Marcus Porcius Cato
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GAT dpu s:-- a C++++ UL++++$ P+ L+++ E W++ N+ o K- w--- O@ M@ !V PS+++
PE Y+ PGP- t++ 5- X+ R+@ tv b+++ DI+++ D+ G++ e>++ h+(---) r++ y++++
------END GEEK CODE BLOCK------