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?

Improve this question
2
  • 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)
    – user1822
    Commented Dec 5, 2018 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
    Commented Dec 6, 2018 at 16:19

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.