Managing large tables in postgresql: archiving? partioning? filtering indexes?

Question

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!

Answer 1

Unfortunately what you guys really need is a DBA. You've sound like you've done an excellent job so far based on the things you've described you have done, especially given the lack of formal technical training in the field. But proper maintenance will become a little harder as the data grows, especially if you guys continue to support ad-hoc queries.

Data size doesn't even matter so much when there are ad-hoc queries. Even a Table with a single row can be slow when the ad-hoc query is poorly crafted. If the statistical software can be altered to generate somewhat more predictable queries you'd be in better shape. E.g. a requirement in the statistical software that a timeframe for the data is one of the filters that are selected, with a max range of whatever's acceptable to the business's use cases (a few months at a time?) for example. This would allow for you to craft all of your indexes to include the timestamp column, since you now know it'll always be part of the query.

Reading data back from a Table should always be relatively quick when architected and indexed properly, and the amount of data being read at one time is comparatively small vs the entire size of the Table. I.e. it doesn't really matter how big the Table grows to, from a reading data back out of that Table perspective, if the data you're reading back out is of a reasonable size (which allows index seeking to work efficiently). Archiving generally isn't needed when the aforementioned is true. But ad-hoc querying complicates that.

So, there are a few different methodologies to archive data, as you've encountered so far. The simplest, for lack of a DBA available, may be to just create a copy of the Table, and store only the last X months of data that you need in it most of the time. You can setup some kind of job to routinely archive the data to the old Table. And your statistical software would need to be modified to know to look at the old archive Table whenever someone selects a date range that's outside the scope of the new Table. You may also be able to UNION the Tables back together in a View that can be efficiently queried too. That way you only have to query a single object, and perhaps minimal to no code needs to change.

You probably will find you'll eventually want to add more archive Tables (perhaps broken up by year or whatever timeframe makes sense), that'll all need to be maintained by your routine archiving process, so that even querying the archive data doesn't become sluggish. Or maybe that's tolerable to the business since it's not commonly queried.