0

I am trying to find the best way to manage postgresql partitioned tables. I have two partitioned tables (one partition per day, based on column created_at existing on both tables), fridge_tagreading and fridge_tagantennareading. fridge_tagantennareading references fridge_tagreading through a composite foreign key (tag_reading_id, created_at).

Regularily i want to remove old partitions. To do so, my idea was to do the following (example if for removing data dated on 2024-05-03):

alter table fridge_tagantennareading detach partition fridge_tagantennareading_2024_may_03;
drop table fridge_tagantennareading_2024_may_03;


alter table fridge_tagreading detach partition fridge_tagreading_2024_may_03;
drop table fridge_tagreading_2024_may_03;

First two queries work like a charm. However, when running the third one i hit my statement_timeout (currently set to 10s). Looks like the query checking for fk violation on the referenced table takes too much time:

select fk."tag_reading_id", fk."created_at"
from "public"."fridge_tagantennareading" fk
join
    "public"."fridge_tagreading_2024_may_03" pk
    on (
        pk."id" operator(pg_catalog. =) fk."tag_reading_id"
        and pk."created_at" operator(pg_catalog. =) fk."created_at"
    )
where
    (
        (pk.created_at is not null)
        and (pk.created_at >= '2024-05-03 00:00:00+00'::timestamp with time zone)
        and (pk.created_at < '2024-05-04 00:00:00+00'::timestamp with time zone)
    )
    and (fk."tag_reading_id" is not null and fk."created_at" is not null)
;

My understanding is that this query takes too much time because postgres checks for all the partitions in the referenced table. I am surprised that postgres does not "propagate constraints", which would lead to a query only on the good partition of the referenced table (this would be very fast).

Is it a known limitation ? How would you deal with such a situation ? My only workaround so far seems so to drop the foreign key constraint...

Thanks

1 Answer 1

0

Yes, this is a known limitation. It has to be that way, because PostgreSQL has to make sure that foreign keys are not violated.

Since both of your partitioned tables seem to be partitioned on created_at, the simple workaround would be to define the foreign key between the partitions for the same day rather than on the partitioned table itself. That would give you the same guarantees, and detaching partitions will be much easier.

1
  • Ok thanks, it makes sense. I am gonna consider this option but this will probably complexify the partition creation tasks Commented May 6 at 13:10

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.