-1

I'm designing a database for tracking stock transactions & real-time portfolio holdings. The system should be able to pull historical position on a custom time-period basis (e.g. end of day holdings). My current design includes a transaction table and a real-time position table, when each transaction is booked into the database it will automatically trigger the update of the real-time position table. I'm planning to use PostgreSQL and the TimescaleDB extension for the transaction table.

I'm kind of confused about how to implement the function of historical holdings, as the historical holdings as a certain timestamp t can be derived by aggregating the transactions with timestamp <=t together. Should I use a separate table to record the historical holdings or simply do the aggregating? I am also considering use binary files to store snapshots of real-time positions at end of each day to support the historical position look-up.

I have little experience with Database design thus any advice/help is appreciated.

ra1n
  • 61
  • 1

1 Answers1

1

This question is lacking detail, so my answer is general.

One thing you could do is have two tables: one for the detailed data and one for the aggregation. Then you calculate one record for the latter from the former every day. Use partitioning for the detail table to be able to get rid of old data easily.

You can also use the same (partitioned) table for both, if the data structure allows it. Then you calculate a new aggregated record every day, drop the partition for that day and extend the partition boundary for the “aggregated” partition.

Consider carefully if you need the TimescaleDB extension. If it offers benefits you know you need, go for it. If not, do without it. It is always nice to have few dependencies. Just because you are storing time series data doesn't mean you need TimescaleDB.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263