We have a system that does some calculations for a network consisting of nodes, edges and endpoints. This data is stored in a Postgresql DB. Each of these tables have entries for different attributes (5-10) measured in 15min intervals and all of them have a column with a timestamp (ideally they should all should have entries for the same time at a specified timestep).

Would you consider it a good design decision to create an extra table with all timesteps in 15min intervals from e.g. 1970 00:00:00 ... 2100 23:45:00 and reference the indexes of this table instead of saving the timestep as timestamp in each of the tables? Or are there other, better alternatives?


Just for illustration we'll use a traffic simulation. We have a tables for topology; nodes (crossroads and bends) connected by edges (streets) and garages with a few base attributes like location, capacity, max speed,.... Then we have time series data for each of these nodes and edges, so for each of these edges and nodes we have measured data at 15 min intervals (# of cars, width of cars, etc..). That means around 35.000 entries per node, per edge, per endpoint etc... This means that there will be a LOT of data pretty quickly (depending on the network size of course) also if we also have multiple networks in the same DB/Schema this multiplies quickly.

We then use this data to extrapolate additional data or fill in the gaps (when we know the amount of cars and capacity we only need a few node/endpoint measurements to simulate the whole traffic flow). So we have at least the duplicate amount of tables (streets measured and streets simulated).

First of all a surrogate key series data would mean all entries (for a specific time step) would be exactly the same (sometimes measurements differ in the range of minutes) Second, even saving two bytes per entry would amount to quite some saving if we have enough nodes and edges.

Third, I guess the basic design has to be revised in the future anyway but for now I'm stuck with this design so I need to improve what I currently can (insertion for ex. is also quite a pain).

E.g. one year, network with 100 nodes, 100 edges, 50 endpoints, each 5 attributes in double precision: 35000*250*15*8 ~ 1GB double that for the simulated data and we already have 2GB of data for a relatively small network and one year of data without the timestamps.

Database redesign opportunity: What table design to use for this sensor data collection?
Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

  • You are not gaining much in terms of storage if you reference a "timestamp lookup table". A timestamp takes 8 bytes, an integer 4 bytes. With padding and alignment in the referencing table, I doubt you'll see a substantial storage reduction if you do so. I would just store the timestamp as it comes in
    – user1822
    Nov 21, 2022 at 17:03
  • What you're describing is not a time-series data anymore -- it is a surrogate key series data. What are you trying to achieve by not saving the timestamps along with the data? Presumably your "design decision" was based on some reasoning, so what was that reasoning?
    – mustaccio
    Nov 21, 2022 at 17:46
  • Just to clarify, you're concerned about 2 GB of data? Not 2 TB? That sounds... unwarranted.
    – mustaccio
    Nov 22, 2022 at 0:41
  • 1
    I'm not not concerned about these 2 GB but the next 1000 times 2GB as explained above; this is a small network and only one simulation for only one year. There will be multiple simulations for the same NW and year but different parameters or bigger NWs or more years. Also this 1GB of simulation data is sent to the server at once, so every once in a while (I hope there won't be too much parallel stuff in the beginning) there will be at least 1GB of Data sent to and inserted into the DB. Nov 22, 2022 at 8:25


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.