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.
