brotherhood_of_LAN - 2:04 am on Dec 4, 2012 (gmt 0)

Are you using MySQL?

Version >=5.6 has some nice improvements regarding partitioning.

When using InnoDB you will want to use something like an auto-increment field for transactions (or perhaps userid & a timestamp in a partitioned table) as big tables with random inserts can get fragmented quickly and slows down SELECTs

The advantage of partitioning is you can specify particular partitions on all your SQL statements & reducing a lot of overhead on larger tables. If suitable for your spec, you can remove older partitions/transactions and put them into a more archive style table.

Agree with LiA RE: a running total table as it would be small and very fast...