> On Jul 11, 2021, at 17:36, Nagaraj Raj <nagaraj.sf@xxxxxxxxx> wrote: > > personally, I feel this design is very bad compared to other DB servers. Patches accepted. The issue is that in order to have a partition-set-wide unique index, the system would have to lock the unique index entries in *all* partitions, not just the target one. This logic does not currently exist, and it's not trivial to implement efficiently. > can I use some trigger on the partition table before inserting the call that function this one handle conflict? That doesn't handle the core problem, which is ensuring that two different sessions do not insert the same (billing_account_uid, ban) into two different partitions. That requires some kind of higher-level lock. The example you give isn't required; PostgreSQL will perfectly happily accept a unique constraint on (billing_account_uid, ban) on each partition, and handle attempts to insert a duplicate row correctly (either by returning an error or processing an ON CONFLICT) clause. What that does not prevent is a duplicate (billing_account_uid, ban) in two different partitions. There's another issue here, which is this design implies that once a particular (billing_account_uid, ban) row is created in the partitioned table, it is never deleted. This means older partitions are never dropped, which means the number of partitions in the table will row unbounded. This is not going to scale well as the number of partitions starts getting very large. You might consider, instead, hash-partitioning on one of billing_account_uid or ban, or reconsider if partitioning is the right solution here.
