Postgres attach partition deadlocks

It is quite easy in busy database to cause deadlocks when dynamically attaching partitions, or hold the locks for too long. Let’s see why this occurs and how to attach partitions in optimal way.

Possible approaches

Lets start the db with:

 docker run -e POSTGRES_PASSWORD=postgres -e POSTGRES_USERNAME=postgres -p 5432:5432 postgres

Simply attaching a partition

Let’s create partitioned table with some indexes:

create table bird (
    id uuid,
    name text,
    color text,
    btype int
) PARTITION BY LIST (btype) 

CREATE INDEX idx1 ON bird(name);
CREATE INDEX idx2 ON bird(color);
CREATE INDEX idx3 ON bird(id);

Now we will create partition table, fill it with data, and attach:

create table bird1 (
    id uuid,
    name text,
    color text,
    btype int
)
alter table bird1 
   add constraint check1 check (btype in (1));

do $$
begin
    for r in 1..10000000 loop
        insert into bird1 values(gen_random_uuid(), r::text, r::text, 1);
    end loop;
end;
$$;

According to postgres docs, check constraint is a quick&simple way to improve performance.

it is recommended to avoid this scan by creating a CHECK constraint matching the expected partition constraint

And now we attach the partition:

ALTER TABLE bird ATTACH PARTITION bird1 for values in (1)

Attach command took 14s to finish! When attaching the partition, postgres adds indexes from parent to associated table.

And what is more, for the whole time we were holding ShareUpdateExclusiveLock on parent table (bird). This is not a major problem in this example, but keep in mind that in next section Foreign Keys will add significant lock contention! So we better keep this time at minimum!

We can add indexes also to our partition table, just before the attach.

CREATE INDEX idx1b1 ON bird1(name);
CREATE INDEX idx2b1 ON bird1(color);
CREATE INDEX idx3b1 ON bird1(id);

And now the attach is instantaneous! <15ms.
We won’t get rid of ShareUpdateExclusiveLock lock, but at least we will hold it for minimal duration.

Attaching a partition with foreign keys

This is a similar case to previous paragraph, but now we include foreign keys on our partition table referencing other tables.

create table bird_color (
    id text primary key,
    name text
);

create table bird_type (
    id int primary key,
    name text
);
alter table bird ADD constraint fk1 foreign key  (color) references bird_color (id);
alter table bird ADD CONSTRAINT fk2 foreign key  (btype) references bird_type (id);

Now we will crate a table to be attached and fill some data:

insert into bird_color values('blue', 'description');
insert into bird_type values(1, 'description');
create table bird2 (
    id uuid,
    name text,
    color text,
    btype int
);
insert into bird2 values(gen_random_uuid(), 'some_name', 'blue', 1);
alter table bird2 add constraint check2 check (btype in (1));

After we attach:

ALTER TABLE bird ATTACH PARTITION bird2 for values in (1);

This method works, but applying foreign keys when attaching prolongs our time when we hold the locks. And we need to hold some of them!

We hold locks on only on our partition + parent table, but also ShareRowExclusiveLock on tables referenced by foreign keys! So to attach properly, we need to catch all those locks. The probability of deadlock increases significantly. Some tables may have many foreign key constraints!

Like in previous chapter, we could try to add foreign keys before the attach. Postgres should figure out that there is no need to apply them again.

alter table bird2 ADD constraint fkb2_1 foreign key  (color) references bird_color (id);
alter table bird2 ADD CONSTRAINT fkb2_2 foreign key  (btype) references bird_type (id);

What we can observe is that we get speed advantage. We are back to <15ms attach, regardless of table size. Lets also check the locks:

Every table referenced by foreign key has AccessExclusiveLock! Even simple queries can deadlock with our attach command! This seems illogical. What happened?
Due to postgres internal handling of foreign keys’ triggers, it is like that for now. The whole thread is here:
https://www.postgresql.org/message-id/174130.1731282279%40sss.pgh.pa.us

Is it everything we can do? There is no golden solution?

We either:
- trade performance for less demanding locks, holding them for longer
- minimize attach time but with demanding locks that can cause deadlocks

Optimal attach procedure

There is a way to create perfect attach process! It requires some extra coding, but can be fully automated. It is best advised for every operation to be idempotent.

IMPORTANT: Parent table does not have foreign keys! Foreign keys are applied to each partition separately.

1. Create a new table.

   1. Don’t add any constraints yet

   2. Use “like” keyword to inherit columns from parent table

2. Fill the table with data

3. Add constraints

   1. Copy indexes from parent table, could be in single transaction.

      1. Skip unique indexes that belong to unique constraint and primary keys

      2. Skip if they already exist.

      3. Remember about unique name! Index names are global!

   2. Add foreign keys

      1. Add them one-by one, each in separate transaction

      2. Use “NOT VALID” and “VALIDATE” options to further optimize the process.
         "NOT VALID" KEYWORD

      3. If they exist, skip

   3. Copy unique constraints and primary keys from parent table

      1. Skip if already exists

      2. Remember about unique names! Index names that will be created to support the constraints must be unique globally!

   4. Add check constraint. This avoids scanning and locking parent table with more restrictive lock.

      1. Check constraint details

4. Attach partition.

   1. Remove check constraint

Steps 3 and 4 should have some retry logic applied.
There is very small possibility for foreign key constraint or attach to deadlock still. Foreign key needs some locks for both tables that it connects, also attach needs locks for parent and partition itself.

Thanks to this approach, we have full control of the process. Restrictive locks are held for minimum duration. Deadlock possibility is almost eliminated. Attach command finishes in few milliseconds, regardless of table size.

Optimal drop procedure

It is worth mentioning that dynamic table drop is also problematic. Here I have few points to remember about:

• Detach partition first. There is “CONCURRENTLY” option, if you don’t need a transaction with other operations, use it!

• Drop all foreign key constraints one by one, in separate transactions.

• Delete the table.