pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartition management - best practices and avoid long access exclusive lock during partition creation
2+ messages / 2 participants
[nested] [flat]
* Partition management - best practices and avoid long access exclusive lock during partition creation
@ 2025-01-30 08:47 srinivasan s <srinioracledba7@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: srinivasan s @ 2025-01-30 08:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
Hope you are well.
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
<https://www.postgresql.org/docs/15/ddl-partitioning.html; 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 ? Please note at this time using pg_partman is very difficult,
as we have different naming conventions for partitioned tables and it is
not easy to change all the partitioned table names.
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)',
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Partition management - best practices and avoid long access exclusive lock during partition creation
@ 2025-01-30 14:01 Laurenz Albe <laurenz.albe@cybertec.at>
parent: srinivasan s <srinioracledba7@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2025-01-30 14:01 UTC (permalink / raw)
To: srinivasan s <srinioracledba7@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-01-30 14:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-30 08:47 Partition management - best practices and avoid long access exclusive lock during partition creation srinivasan s <srinioracledba7@gmail.com>
2025-01-30 14:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox