Storage do you mean the Disk storage space that is being used? or the data source size. As you mentioned You can query the PostgreSQL for the size of data source. You can take a read here to see how tableau uses the Disk
I dont think its easy to capture this for per site if that is what you want.