Forgive me if this idea has already been mentioned:
The basic function of a stockpile is to provide a buffer for resources so that local deficits and surpluses can be sorted out and balanced. This is the main purpose and it can be achieved with a simple empire level pile that clears itself at the end of a turn the excess labeled as wasted by inefficiency or alternatively sold for a given amount. That would be keeping things simple.
OTOH it would be nice if stockpiles could be used to buffer resource output and usage over time (i.e. turns), too. Especially if you have blockades or you are simply expanding and putting out colonies at a fast pace. If a stockpile carries over to the next turn without any limits or penalties then this creates the obvious problem of piling a huge reserve of resources and then ignoring the resource harvesting for the rest of the game.
This problem could be solved with maximum sizes capping the piles, but then we would have to think about what's a "good" maximum size and whether technologies, race picks, governments, etc. affected it and how. That's pretty much work and could quickly get complicated. Not to mention that whatever you set the max cap at there would be people complaining it's too low while other people complained it's too high.
However, I think that we could circumvent the entire problem by using a self-adjusting stockpile.
This would simply mean that the stockpile is deducted by X% at each turn. Because the depletion is a percentage ratio of the stockpile size and the pile is increased with a fixed surplus amount the net result is that your stockpile will automatically cap out and balance at the point where your per turn surplus equals the depletion ratio.
For example if the depletion rate is set at 10% and you have a turn surplus of 20 minerals (and assuming things stay that way) you would accumulate a stockpile of 200 minerals at which point your stockpile gets deducted by the exact amount that your mines bring in as a surplus. Thus your "cap" would in this particular situation be 200 minerals.
The beaty of this is that your stockpile limit would follow your resource output rate which would already be tied to your tech level and the size of your resource harvesting operations. When you build more mines your stockpiles will automatically grow, too. If you then turn around and switch those mines to industry your stockpiles will dwindle very fast taking care of the Huge Accumulated Stockpile problem.
Self-adjusting stockpiles would keep things very simple from the game engine point of view but still give you stockpiles that follow the game decisions you yourself make.
Also, if you don't like the depletion to be just wasted they could be thought of as sold to the private sector adding to your money income.