Growth planning

At this point, performance is fine, though not spectacular, with a “normal” query of the currently 400GB database taking a couple of seconds. “Normal” in this case being defined as the most common SELECT query run against the data to plot the current and historical trends. Users are few in number (and will likely remain that way), and it can keep up with the insert rate without difficulty - data is processed and dumped to the database in 10 minute “chunks", and generally the run is only taking around 2 minutes, so there is a fair amount of overhead available there.

However, this database is growing at a rate of 14GB/day (as measured via looking at df stats), and the script owner is taking about wanting to process and pull in “all the historical data we have access to”, which would go back several years, not to mention the probable desire to keep things running into the foreseeable future. This amounts to a volume of data that is WAY beyond anything I have any experience with, especially since I am primarily a software engineer, not a DB admin (though administering a DB is often involved with my role, as the software I engineer often needs a data source). As such, I am looking for advice from people who have dealt with such large volumes of data as far as how I should architect things now, before it gets to out-of-hand, to best ensure optimal performance in the future.

Some additional data that may or may not be relevant:

- The workload is largely insert intensive. Every 10 minutes, 1Hz records are inserted for up to three channels from about 118 stations - so up to around 212,000 new records inserted every 10 minutes. In practice, the number is generally somewhat lower as not all stations actually have three channels of data, but the majority do.

- The largest SELECT workflow currently is a script that pulls all available data for ONE channel of each station (currently, I suspect that will change to all channels in the near future), and runs some post-processing machine learning algorithms on it. This script (written in R, if that makes a difference) currently takes around half an hour to run, and is run once every four hours. I would estimate about 50% of the run time is data retrieval and the rest doing its own thing. I am only responsible for integrating this script with the database, what it does with the data (and therefore how long that takes, as well as what data is needed), is up to my colleague. I have this script running on the same machine as the DB to minimize data transfer times.

- Other than the above processing script, workload is fairly light, with only one or two users occasionally pulling up graphs of the data for a handful of channels on a handful of stations at most (singe station/channel at a time). The time range of data needed for these graphs may vary from a few seconds (looking at a specific event, which may or may not be recent) to several years (looking at historical trends). As such, full-resolution data needs to be available quickly - that is, on a user-friendly time scale - for any period of time for which we have data.

- Some stats from the pg_stat_user_tables:

- and the data table definition: