We have a very big table where we keep a log of customer activity, there are about 5 of inserts per second, and we keep her information up to 6 months back. The problem is that many other applications are using this table (no updates on it, only to selects from many applications, the insert is done by a single process), heavy queries, despite all indexes, there are still timeouts, and the situation only gets worse. Most applications use only a portion of the columns, and I thought maybe build another table with fewer columns to make it lighter, and at the main table insertion, insert by trigger to the secondary table (maybe there is a way to make the insert without delay the main table insert?), then the other queries from other applications could run on the secondary table, that way:
1. I will not lose any insert to the main table as no one will lock it
2. Less likely to get timeouts on big queries that will run on the secondary table which has fewer columns and information.