Why You Shouldn’t Create Your PostgreSQL Partitions with CREATE PARTITION

A deep dive into PostgreSQL locks, concurrency issues, and how we solved deadlocks in our production database.

1. Introduction: The Power of PostgreSQL Partitioning

When scaling large datasets in PostgreSQL, you inevitably reach a tipping point where traditional monolithic tables can no longer handle the load. That’s when partitioning steps in to save the day.

However, partitioning truly shines when your application lifecycle involves loading a bulk “release” of data where the records remain completely immutable (no subsequent UPDATE, INSERT, or DELETE operations) and are strictly queried by clients until the release is eventually discarded.

In my previous article, I discussed the wonders of PostgreSQL partitioning and how we shifted to this exact immutable “Snapshot” mindset to stabilize our data architecture at Clarity AI. But there is a hidden catch. While partitioning itself is a fantastic tool for this workflow, the way you create those partitions in a live, highly concurrent environment can make or break your entire system.

2. The First Attempt: Choosing the Simple Route

If you look at the PostgreSQL documentation, you’ll find two main ways to add a new partition to an existing partitioned table.

When our engineering team at Clarity AI first designed our automated data release process, we evaluated both methods. We ran thorough performance tests, specifically measuring the time it took to load over 1.6 million new rows into a parent table that already contained 3.2 million existing rows.

The results were close:

• The direct CREATE TABLE ... PARTITION OF approach took about 29.18 seconds.
• The alternative multi-step method (create standalone, copy data, attach) took about 23.32 seconds.

Since the time difference wasn’t massive for an offline data load process, our team initially concluded that it was better to go for the simpler, more straightforward option: creating the table directly as a partition.

SQL

-- The "Simple" Route
CREATE TABLE metrics_release_v2 
PARTITION OF operation_id -- Our release identifier 
FOR VALUES IN ('4458');

3. The Hidden Problem: Access Exclusive Locks and Deadlocks

What we didn’t realize was that the “simple” CREATE PARTITION route hides a dangerous concurrency trap. It fundamentally requires an ACCESS EXCLUSIVE lock on the parent table. In PostgreSQL, this is the heaviest lock available—it completely blocks all concurrent reads and writes.

What this mean is, if a user is executing a query involving some tables and the process that creates the partition is using the same tables you can eventually end with a deadlock

Here is how the perfect storm formed:

• Step 1: A user makes a standard API request involving a query that joins two related tables (Table A and Table B). To execute this, the database grants an AccessShareLock on Table A.
• Step 2: Concurrently, our background release process kicks in and attempts to create a new partition on Table B using the simple method. To do this, it requests an AccessExclusiveLock on Table B.
• Step 3: The user’s query now attempts to read from Table B to complete its join, but it is forced to wait because the release process is demanding an exclusive lock.
• Step 4: Meanwhile, the release process needs to check constraints (like Foreign Keys) between the new partition on Table B and the existing Table A. To validate these, it requests a lock on Table A. But it fails and is forced to wait, because the user’s query already holds an AccessShareLock there!

The result? A classic, catastrophic Deadlock!

But Wait, It Gets Worse: The Single Transaction Trap

While the foreign-key relation scenario described above is a notorious deadlock trap, you don’t even need related tables to run into this issue. The exact same catastrophic failure can occur if you attempt to execute CREATE PARTITION on multiple independent tables within a single database transaction.

In fact, this second scenario is exactly what happened to us.

To ensure consistency during our deployment, we thought we were following best practices by grouping the partition creations for several tables inside a single transaction. Instead, we were unknowingly building a deadlock time bomb.

Get Jorge Argente Ferrero’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Here is how our actual production outage unfolded:

• Step 1: Our background release process starts a transaction and successfully executes CREATE PARTITION on Table A. It is instantly granted an AccessExclusiveLock on Table A.
• Step 2: Concurrently, a user makes a standard API request. The database starts executing their query, which happens to access Table B first, granting the user’s query an AccessShareLock on Table B.
• Step 3: The user’s query now needs to read Table A to complete its operation. However, it is forced to wait because our release transaction is already holding the exclusive lock on Table A.
• Step 4: Meanwhile, our release transaction moves to the next statement in its execution block: CREATE PARTITION on Table B. To do this, it requests an AccessExclusiveLock on Table B. But it fails and is forced to wait, because the user’s query is sitting on an AccessShareLock for that exact table.

The result is identical: Deadlock.

By trying to be “safe” and wrapping multiple partition creations into one single transaction, we were accumulating the heaviest possible locks across multiple tables at once. We inadvertently created a massive surface area for lock contention, guaranteeing a collision with our standard application traffic.

Our production error logs lit up with messages exactly like this to prove it:

Plaintext

Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected  
Detail: Process 1698 waits for AccessShareLock on relation... blocked by process 4683. Process 4683 waits for AccessExclusiveLock... blocked by process 1698.

4. The Second Attempt: The Safe Alternative

We needed a pivot. We couldn’t let our routine data releases bring down the client-facing APIs.

Our immediate fix — the strategy we are using right now in production — was simple but crucial: stop grouping operations. We broke up our release process so that every single CREATE PARTITION command is executed in its own, independent transaction. By releasing the ACCESS EXCLUSIVE lock on one table before trying to grab a lock on the next, we drastically reduced the surface area for lock contention and eliminated the deadlocks.

However, we know this is only a temporary band-aid. CREATE PARTITION still inherently requires that ACCESS EXCLUSIVE lock, meaning it still momentarily blocks all reads and writes on the specific table being partitioned.

To permanently solve this and achieve truly zero-downtime data releases, our roadmap for the coming months is to abandon the single-step CREATE PARTITION entirely. Instead, we are redesigning our release workflow around the second method recommended by PostgreSQL: the multi-step attachment process.

Here is what our upcoming architecture will look like:

1. Create an independent table: We will create a standard, standalone table with a CHECK constraint that perfectly matches the intended partition bounds.
2. Load the data: We will change our load processes to ingest all the new data directly into this independent table.
3. Attach the partition: Once the load is complete, an automated step will run an ALTER TABLE command, attaching the new table to the logical parent as a partition.

SQL

-- 1. Create a standalone table
CREATE TABLE metrics_release_v2 (
  -- INCLUDING ALL columns, indexes and constraints as in the parent table
);

-- Add the matching check constraint
ALTER TABLE metrics_release_v2 
  ADD CONSTRAINT operation_id_check CHECK (operation_id = 4458);
-- 2. Load the data into the standalone table
-- (e.g., using COPY or standard INSERTs)
-- 3. Attach the table as a partition
ALTER TABLE metrics_parent 
  ATTACH PARTITION metrics_release_v2 FOR VALUES IN (4458);

Why this is the holy grail: Here is the crucial technical detail behind this future roadmap item. The ATTACH PARTITION operation only requires a SHARE UPDATE EXCLUSIVE lock on the parent table. Unlike the ACCESS EXCLUSIVE lock, this lock does not block concurrent read operations. Once implemented, our clients will be able to continue querying the database completely uninterrupted while new partitions are seamlessly attached in the background.

5. Bonus: Safe Garbage Collection

It’s worth noting that this architecture shift isn’t just about creating partitions. Deleting them carries the exact same fundamental flaw.

If you try to drop a stale partition directly, PostgreSQL will once again acquire an ACCESS EXCLUSIVE lock on the parent table, putting you right back in the deadlock danger zone.

As part of our roadmap to eliminate all exclusive locks from our routine operations, we will also be modifying our garbage collector to follow a two-step, lock-safe process:

• First, DETACH the stale partition from the parent table. (This only takes a safe SHARE UPDATE EXCLUSIVE lock).
• Then, DROP the now-independent table completely.

SQL

-- Safe Garbage Collection Process (Upcoming)
ALTER TABLE metrics_parent DETACH PARTITION metrics_release_v1;
DROP TABLE metrics_release_v1;

6. Conclusion & Call to Action

In highly concurrent PostgreSQL databases, the “simplest” DDL commands can sometimes cause the biggest outages. When operating at scale, you have to look beyond just the syntax and deeply understand the locking behavior your commands trigger under the hood. Switching from CREATE PARTITION to ATTACH PARTITION eliminated our deadlocks entirely and ensured our platform remained 100% available during massive data releases.

Has a seemingly innocent DDL command ever caused a deadlock in your production database? Have you experienced this exact partitioning issue? Let me know in the comments below!