Partitioning and Foreign Keys

[openrijal]

Hello.

Is the foreignkey limitation only when you try to partition based on a column which is a foreignkey? Or is it like you cannot partition a table if any of its columns are involved in foreignkeys?

I'm using PostgreSQL and have followed all the methods described in the docs. When I add partition to a table with the foreignkey column as the column name, it prevents with foreignkey violation, again if I re-create the partition based on a date field which is not a foreignkey it also gives the same error.

I was just asking if I was doing something very wrong here?

[maxtepkeev]

Hi Nitesh,

Yes, currently table partitioning isn't supported for tables which have at least one foreign key and it doesn't matter whether you're going to partition on a column which is a foreign key or not. So currently the rule is, if table have at least one foreign key, partitioning won't work.

This is a PostgreSQL limitation and all the approaches which exist for supporting table partitioning with foreign keys (like the one described in #25) are just workarounds which have (sometimes significant) performance costs.

[maxtepkeev]

I believe this can be closed.