CODEWITHSUNDEEP
postgresqlpartitioning
ConanTheGerbil
ConanTheGerbil

Reputation: 777

Why does working on an individual POSTGRES partition effect the parent table?

I have a 24/7 postgres database in which I've partitioned some of the major tables to allow maintenance whilst data is still being loaded. Unfortunately changes to individual partitions still seem to have an impact on the parent table.

My table is defined something like -

CREATE TABLE tableA ( loadedTime TIMESTAMP, rawData CHARACTER(150))
PARTITION BY RANGE (loadedTime)

And the individual partitions - 

CREATE TABLE tableA_yyyymmdd PARTITION OF tableA FOR VALUES FROM () TO ()

Where ranges equate to individual days.

I have a process that inserts records 24/7 into the parent table tableA (not a specific partition), loadedTime always refers to the current time, so data is always being loaded into todays partition.

Why does changing the tablespace of some of the old partitions cause inserts into the current partition to time-out? My understanding is that the partitions are almost like separate tables and I should be able to work on the partitions without causing problems with the parent table - or have I misunderstood?

UPDATE - currently using postgres 10.5. I have a similar issue if I attempt to DETACH, VACUUM and ATTACH an old partition from the parent table. I can access the parent after detaching the partition, but the DETACH and ATTACH take a while and INSERTs on the parent timeout during the detach/attach steps.

Upvotes: 1

Views: 2282

Answers (1)

amitlan
amitlan

Reputation: 260

With Postgres 10.5, INSERT on parent table locks all partitions before determining which partition a given row should be inserted into. If you've locked one of the older partitions to change its table space, then the INSERT has to wait to get a lock on that partition, which might explain why it gets timed out. Postgres 11 has the same behavior, but Postgres 12 (in beta at the moment) fixes this so that INSERT on the parent table won't block the operations on the older partitions or vice versa, that is, if the INSERT only targets the newest partition.

ATTACH and DETACH commands lock the parent table. So if you're INSERTing into the parent table at the same time as attaching or detaching a partition, the former might get blocked until the latter finishes. ATTACH can take a while because it must scan the partition being attached to check if it doesn't contain any rows that violate the partition constraint. Again, Postgres 12 has improved matters such that ATTACH doesn't block concurrent INSERTs (and SELECT/UPDATE/DELETE).

To avoid the parent table being locked for a long time for this validation check, you can add a check constraint on the table to be attached that matches the required partition constraint before running the ATTACH command. With the check constraint in place, Postgres can skip the expensive ATTACH validation step, because the partition constraint would already be valid because the check constraint is valid.

Upvotes: 4

Related Questions