agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Laurenz Albe <laurenz.albe@cybertec.at>
To: srinivasan s <srinioracledba7@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Partition management - best practices and avoid long access exclusive lock during partition creation
Date: Thu, 30 Jan 2025 15:01:53 +0100
Message-ID: <a47bb3ef2a8d178fb01120d6cd677537a6657b67.camel@cybertec.at> (raw)
In-Reply-To: <CAOEeMcU4mSZb-BySSHoZ2WiHy1PMpe3TvuhqQuB-c1wjt+50Og@mail.gmail.com>
References: <CAOEeMcU4mSZb-BySSHoZ2WiHy1PMpe3TvuhqQuB-c1wjt+50Og@mail.gmail.com>
On Thu, 2025-01-30 at 14:17 +0530, srinivasan s wrote:
> I am looking forward to some suggestions to avoid exclusive lock during the partition
> creation in postgresql 15. Currently we have a simple monthly range partition setup
> on a table.
>
> I Scheduled a pg_cron job, which runs every month and checks if we have a partition
> available for next six months and creates necessary partitions.
>
> The command used to create the partition is given below, unfortunately this is
> causing a huge exclusive lock for a long time and blocking other sessions causing a
> resource crunch on the system. this is very busy system 24/7 very difficult to find
> a maintenance window
>
> CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)', partition_name, table_name, DATE(start_date), DATE(end_date)
>
> I was going through the documentation PostgreSQL: Documentation:
> 15: 5.11. Table Partitioning and there was note by creating a table, adding a
> check constraint & attaching to the parent table minimizes the lock during partition
> maintenance. something like below ? looking forward to the suggestions from
> partitioning experts. the change that I am making to help to avoid such huge locks
> or other suggestions ?
>
> EXECUTE format('CREATE TABLE IF NOT EXISTS %I (LIKE %I INCLUDING DEFAULTS INCLUDING CONSTRAINTS)',
> partition_name, table_name);
> -- Add the CHECK constraint with the dynamic name
> EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I CHECK (created_at >= DATE %L AND created_at < DATE %L)',
> partition_name, constraint_name, start_date, end_date);
> -- Attach the partition
> EXECUTE format('ALTER TABLE %I ATTACH PARTITION %I FOR VALUES FROM (%L) TO (%L)',
Yes, that should only take a SHARE UPDATE EXCLUSIVE lock, which won't conflict with
SELECT or data modifications.
But I am surprised that the original statement is a problem. Sore, it takes a higher
lock, but only for a very short time. Perhaps you have long-running transactions all
the time. If yes, that's a problem you should work on.
Yours,
Laurenz Albe
view thread (2+ messages)
Message-ID: <a47bb3ef2a8d178fb01120d6cd677537a6657b67.camel@cybertec.at>
Permalink: ../a47bb3ef2a8d178fb01120d6cd677537a6657b67.camel@cybertec.at/
Also on: postgresql.org/message-id/a47bb3ef2a8d178fb01120d6cd677537a6657b67.camel@cybertec.at
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, srinioracledba7@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partition management - best practices and avoid long access exclusive lock during partition creation
In-Reply-To: <a47bb3ef2a8d178fb01120d6cd677537a6657b67.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox