Hi,

I hope some experts can shed some light on this.

I have a table that is very INSERT-heavy. Most of the days, it only
needs to accept data, almost like a running log. About 500,000 INSERTs
a day.

Because this is such a rapidly growing table, SELECTing from this is
a
huge pain. There are 24 columns, and the SELECTing needs to happen
from several of them, so we have 9 indexes.

These indexes mean that the performance of the table is pretty lousy.

So now I have two tables -- A and B. The structure of both is the
*same*.
Only, B has many indexes and is used for heavy duty SELECTs. On the
other hand, A only accepts heavy duty INSERTs, so has only one
primary key index.

In other words, A is only an INSERT table. Periodically,
say every 20 minutes or so, I would like to take all the new INSERTs
from table A and put them into B.

Is there any clever command to accomplish this? I'd rather not write
a
php script with the sql to take every single new record, and insert it
one
by one into table B.

I currently use Postgres but am amenable to using any database if it
supports this well.

Thanks for any pointers!!

LB

--
Conan O' Brien gets it right!
http://blogs.pcworld.com/tipsandtwea...es/004369.html

My question is why so many indexes?
How many different SELECTs for the INSERTED Table?
I guess you can create less and more effective Indexes.
Anyway, no detail and exact information provided. So, I(and perhaps
more experienced peoples) can't say any more practical
recommendations.

On May 12, 12:20 am, "L. Berger" <straightfwd... (AT) gmail (DOT) com> wrote:
Table A is a staging table. pretty common in some situations (I have
used it most in conversions where A and B might not match columns one
to one) including yours.

I do not know postgres, but it sounds like a server side batch job to
me. Does postgres have it's own job scheduler? If not, then you punt
to a OS scheduled job. Either way it is not a bad strategy.

Now also, why all the indices? Assuming not every select uses every
column, then you might consider a design where INSERTs happen to table
A and a batch job moves desired data to two or more reporting tables
where these tables are optimized for one or more of your client
SELECTs.
Then table A can stand with just the PK index, and tables B, C etc,
have their own PK index as well as other indices.

So the model is either
1. staging table A with main/working table B
2. main table A with report tables B,C, etc

you know more of your needs so take your pick.