We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, search and aggregate these transactions, fast reads would be very nice.
We want to guarantee very fast writes, and adding indexes to cover some of the reads would slow down writes.
The good news is that we can afford stale data. I was considering to create a copy of the data to a read-optimized table (with indexes) every n minutes, which also allows for a periodical bulk-copy (thus limiting the number of ops?)
I'm looking for an opinion on whether this is a valid strategy. If you consider this a decent strategy, do you have implementation options pointers? If not, what are the alternatives?