I'm building a PostgreSQL database with the intent to host modelisation results for different projects.

Each project has a set of receptors points (~5000-10000 receptors) and have many results sets (could be as much as 100 000 results sets (here called scenarios results)).

Next figure is my actual (simplified) design: enter image description here

The problem with this design is that the table receptor_results is going to be extremely heavy. For a project with 5000 receptors and 50 000 scenarios, it will generate 250 000 000 rows!

Any suggestion on how I can avoid such thing ?

  • 2
    250 m rows is well within Postgres's capabilities. You haven't stated what kind of queries you will want to run, what sort of read/write ratio you might expect, whether there will be many concurrent users, etc. but without knowing any of this, properly indexed, there is no particular reason to fear 250m rows. – John Powell Jun 1 '16 at 16:27
  • There will be only a few users who will have access to the database, and I don't expect that there will be a heavy load on the database since there is normaly only 3-4 projects running at the same time and the results export will only be made once or twice per project. – kaycee Jun 1 '16 at 16:43
  • Sounds like the sort of data NetCDF was designed to handle. – Kirk Kuykendall Jun 1 '16 at 20:46
  • @KirkKuykendall, I'm not that much familiar with netcdf format but isnt this format much more oriented for local data stocking? We want to build a database to eventually use the data inside in a mobile cartography app. – kaycee Jun 1 '16 at 21:04
  • I don't think NetCDF works for mobile. Your project sounds interesting, could you elaborate on it a bit in your question? – Kirk Kuykendall Jun 1 '16 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.