I am currently building a game that has a "City" entity. These cities generate and consume resources such as food variably over a period of time. I need to be able query the server often to find exactly how much food the city at any given point. These queries can take place multiple times per minute. There could also be 400,000 cities to track at a given time.

How would you handle tracking these resources? Would you do it in real time, keeping an instance of the city in memory on the server, with some sort of a snapshot in time of the resources, then computing the growth/consumption from that snapshot time for subsequent queries? Would you work exclusively with a database, using a similar "snapshoting" scheme? Maybe a mixture of the 2, caching recently queried cities in memory for a period of time?

There is also a lot of other data that each city needs to track. A player can queue units to build in a barrack. The armies available in the city will need to be updated as units complete.

I'm interested in everyone's input on where/when/how you'd manage the real time data.