2

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!

2
  • I would suggest that you investigate partitioning - possibly by month! Here is an example of where a 25 minute query was turned into a 5 second one using partitioning - it was a kind of special case - data completely evenly balanced between partitions, but it looks to me like it might be a good fit for your use case!
    – Vérace
    Commented Sep 12, 2022 at 8:37
  • OK thanks for the advice and the link I'll look into it!
    – Franck
    Commented Sep 13, 2022 at 13:25

1 Answer 1

0

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.

5
  • Thank you very much for your reply! Yes, I agree we need a propre DBA but it's complicated in small organisations to have full time positions (I myself am a data analyst but also the AWS account manager, data engineer, data scientist and a bit of statistical process control and design of experiment expert all at the same time). We haven't found the right consultant yet either but I guess we will re-launch the search for a suitable profile because I'm not sure how long I can continue in this role...
    – Franck
    Commented Aug 31, 2022 at 7:19
  • The GUI of the statistics software is programmable, so it is in the form of an "add-in" (it is a SAS software called “JMP”) that I have set up with automatic pre-filters that are then translated by the software into WHERE clauses. I'm going to dig into this direction of incorrect indexes to understand why the current indexation doesn't seem to be well configured to help the speed of ad-hoc queries
    – Franck
    Commented Aug 31, 2022 at 7:19
  • @Franck No problem! Yea again, it depends on what those ad-hoc queries are actually doing. If they share any common fields in the predicates (JOIN, WHERE, or HAVING clauses) then that's possibly the fields you want to start your index(es) on. If there's absolutely no commonality between the predicates of any of the ad-hoc queries, then indexing becomes tougher. If you can find a way to commonize the predicates such as always including a timeframe filter, then you can cater the indexes a bit better to cover all ad-hoc queries to some degree. Best of luck!
    – J.D.
    Commented Aug 31, 2022 at 12:32
  • Also feel free to post a new question with a specific example query that's not performing good enough with your table and index definitions and with the EXPLAIN ANALYZE ideally, and people on here may be able to suggest more specific tuning advice to help you.
    – J.D.
    Commented Aug 31, 2022 at 12:34
  • 1
    OK thanks, I'll try to dig myself on a copy of the database in "sandbox" mode before calling for expertise
    – Franck
    Commented Aug 31, 2022 at 14:20

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.