Context
We are a very small company that measures a lot of data on the very complex products we make. And in this company there is no database administrator (nor IT people), so I have this role even though I am only a data analyst (on the statistician side of the field) so please excuse me if my question is trivial or stupid I’m not used of database optimization at all.
We have 200 million rows in our main table which weighs more than 250Gb and is stored on AWS RDS in Postgresql which corresponds to a production of 2 years and contains all of our measurement (every day measurement) and timestamp associated to them. The db increase is a bit exponential: it has been for example 100Gb more over only the last 6 months as the company grows and sells more products… For the moment we don't have too many performance concerns because I optimized the queries with indexes and use of subqueries as well as materialized views to run heavy calculation at night (summary of median, standard dev,… or even some linear regression. Calculated by lot for example, for 6 months with a refresh every month and concatenate to the current month datas refreshed every day) but this main table is still very heavy: it takes almost 1 hour to do just a row count on it! As soon as someone try to get datas without a proper SQL custom query, with our statistical software that can run queries with a GUI to build them, it takes ages to get results if I’m not here to write the script for them…
The problem
We are a bit afraid that our database will become unmanageable as it grows in size and need more and more AWS hardware resources whereas we really only need the last few months of data as day to day basis
Constraints
we are supposed to keep all our measurement data in case of a customer complaint or some rare requests from management to compare old and new data. So we can’t just drop old data
What has been tried
I tried to make a replica and then make it writable so that I could delete old data and keep only the last 6 months for example because I had seen that this was a strategy that some people were doing in MySQL. New data would have been synchronized with the replica db and this replica db would have been lighten with a drop of old datas. But it doesn't seem to be feasible in Postgresql as a read replica is really a read only replica I also tried to do some clustered indexes but it did not allow me to gain in performance but I have perhaps did something wrong in the setup
=> What solutions do you think are the most efficient and convenient in postgresql to handle very large tables: partitioned tables? filtered indexes? Others?
I imagine that we could also create a new database from a snapshot, keep it as an archive database and drop old values in our production database but how can we deal of that way in practice: make database archive every year? How can we deal in that case someone would like to get data over several years? And how to deal with the transitions of years? For example, we are in January 2023, we have archived 2022 and we still want to look at data for January 2023, December 2022 and November 2022?
Thanks for your help!