OK,
Thanks for the quick responses.
A bit more information.
We are in the business of gathering data for our clients. (We're a news
service). Subsequently, we do a lot of inserting and very rarely do
any deleting. (We periodically clear out results that are over 6 months
old.)
On a give day, we will insert around 100,000 records in total.
(Currently split across all the client tables).
A challenging part of the process is that we have to keep track of
previous content that may be similar. We CAN'T do this with a unique
index (don't ask, it would take too long to explain, but trust me, it
isn't possible). So, we have to query the table first and then compare
the results of that query to what we are inserting. SO, we probably do
close to 1 million queries, but then only make about 100,000 inserts.
The basic flow is 1) our system finds something it likes, 2) query the
table to see if something similar already exists, 3) if nothing similar
exists, insert.
While all this is going on, our clients are accessing our online
reporting system. This system makes a variety of count and record
requests from the database.
As I mentioned in our earlier post, we are attempting to decide if
Postgres will run faster/better/ with one big table, or a bunch of
smaller ones. It really doesn't make much difference to us, we just
want whatever structure will be faster.
Thanks,
-N