2

Our system uses inheritance on two levels:

  • master
    • child_level_1
      • child_level_2
      • child_level_2
      • child_level_2
    • child_level_1
      • child_level_2
      • child_level_2
    • child_level_1
      • child_level_2

The idea was to aggregate data by using the parent tables. But we realized that accessing the parent tables was way too slow to be useful.

We have around 500.000 tables right now and it would be nice if we could drop the parent tables to reduce that number a bit.

Is it possible to drop the inheritance from the master table? Or do we have to do it for every child?

The ideal solution would be one query that leaves us with only child_level_2 tables. They would still have all the columns that they inherited before.

Is that possible?

  • 2
    I think, you have to do it for every child table - but 500.000 tables is way too much. If you are using that for partitioning that won't scale at all. Especially older Postgres versions (before 10) couldn't real deal with such a high number of "partitions". And even with 11 this is still some kind of a bottleneck (although it got a lot better with declarative partitioning) – a_horse_with_no_name Dec 5 '18 at 13:18
  • Thanks for your response! I'm aware that it's not a good practice to have this many tables but unfortunately I wasn't involved in the design process. Wouldn't this be good use-case for postgres-xl (postgres-xl.org) though? – Jesse Dec 6 '18 at 16:19

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.