Partition Lock Issue

From: Saravanan P K <pksaravanan(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Partition Lock Issue
Date: 2023-10-18 05:38:52
Message-ID: CAHueLmAhGo=OZBxExXbdhd6nDJyRqO+P2mU9YdRcCEXbkZ1+WQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a postgres database shared by two applications A and B. A is used
often (data centric) and B is used once in a while to create customers
(only for this purpose and nothing else) for Application A.

When a customer is created in application B, monthly partitions are created
for the next 3 years or so, mostly for the data tables used by application
A. Default partitions are created during the initial database deployment.

Assume, during the initial deployment of these applications, we have few
customers created in B and application A starts functioning as expected.
Application A gets data based on customers' usage and data is huge.

Here is the problem. A new customer has to be added in B (say after 6
months or so) and as part of the process, we create partitions for the next
3 years. At the same time, the data tables are used by application A for
select/edit or CRUD operations simultaneously. Due to shared/exclusive
access locks made by A, it makes the customer creation a little slower or
until the locks are released by various features that access data in A, the
customer could not be created immediately in B. Default partitions often
have the lock.

How do we get through this situation? Is there a better way for approaching
this partition behaviour. Customers could be created based on opportunity
and partitions have to be created for them, say for next few years.

Browse pgsql-general by date

  From Date Subject
Next Message Jehan-Guillaume de Rorthais 2023-10-18 07:44:53 Re: Postgresql HA cluster
Previous Message Hafeez Rahim 2023-10-18 03:18:32 Re: Index based search have issues with 11.20