I am going to create small system/website that will allow me to manage my stocks, and monitor the portfolio. I do not have much of it, but I cannot access the data from wherever I want and it is not so dynamic.

Beside the design itself I came across a problem of recreating a FIFO (first in first out) type of queue. Why that? Because if I buy and sell stock and I want to see the current value of my opened position that is how it should be calculated: the first stock I buy that is also the first that is out of the book when I sell. The solution below was made in PostgreSQL 9.1.

How does it work? For a given id_user = 3 and id_stock = 3 take all transactions that are opened, is_opened IS TRUE – that is a common part of two SELECTs joined by UNION statement. The difference between them is that the upper one sums up all the sell transactions, whereas the bottom one has only buy transactions.

In the next step, just for clarity, I pick all the important values. The next one is a window function that given the order by type and tr_date makes all the calculations. This way in new column, amount_left, we get a current number of a given stock we own.

From the performance point of view, assuming I have many users, many stock, and much more transactions. I have created two partial indexes on “opened” buy/sell transactions. By opened transaction I understand those transactions that have not been closed. Transaction will be marked as closed if after entering new sell transaction all amounts of previous sell/buy transactions sum up to 0. In other words all transactions are OUT. Below the code for the partial indexes.

CREATE INDEX CONCURRENTLY idx_trans_user_stock_f
ON transactions(id_user, id_stock)
WHERE is_opened IS TRUE AND type IS FALSE;
CREATE INDEX CONCURRENTLY idx_trans_user_stock_t
ON transactions(id_user, id_stock)
WHERE is_opened IS TRUE AND type IS TRUE;

I thought about making bitmap indexes, but as I found out they are not implemented in Postgres. Besides, doing two partial indexes that cover exclusive parts of table seems a reasonable idea.