1

I am using Postgres 13.

I have a table (accounts) with around 100 million records, that is (hash) partitioned in to 50 tables (based on the user_id)

This is then further (range) partitioned into 14 tables each, based on the day of transaction.

Every day, I need to create partitioned tables for the hash partitioned tables account_1, account_2, etc - for the 15th day in advance. So in total 50 tables each day.

The process though needs to lock the main parent table accounts (access exclusive) - for it to be able to create a table. This process though takes ~30 seconds - meaning no transactions will be stored during that period. Although the tables would be empty then, it is probably the checks that it needs to do with the current entries in accounts table before it creates the (empty) partitions - that is taking time?

Is there a way I could make the process faster, such that the downtime is minimum? Can I lock the individual hash partitioned tables say accounts_1 and accounts_2 instead of the parent accounts table?

Another way I could think of is to create the table first with the constraint, and then attach the partition (expecting this to be faster). Is that a viable solution or are there other ways?

user1583803
  • 424
  • 2
  • 6
  • 16
  • 2
    you can first create the table, then use `alter table attach partition` which will not lock the table –  Jan 04 '22 at 15:42
  • There is no good reason for this to take 30 seconds. So the first step could be to figure out why it does. Is it waiting on other locks? Also, the lock required on the root table to add a sub-partition table is only an AccessShareLock, which should not interfere with much of anything. – jjanes Jan 04 '22 at 20:36
  • Without knowing the cause of the slowness, we can't know if any particular thing will fix it. You can always try it and see. – jjanes Jan 04 '22 at 20:36
  • @jjanes At the moment, we are locking the table before proceeding with the partition creation. So I do not expect it to be waiting on other locks. What I could think of is that the default partition has around ~25 million rows that is not cleaned up yet. Could it be that before creating the partition, it checks the default partitions for existence of entries that could be part of the new partition? – user1583803 Jan 05 '22 at 12:19
  • @a_horse_with_no_name From the documentation: `Attaching a partition acquires a SHARE UPDATE EXCLUSIVE lock on the parent table, in addition to the ACCESS EXCLUSIVE locks on the table being attached and on the default partition (if any).` It does lock the table as per this or am I understanding it wrong? – user1583803 Jan 05 '22 at 12:25
  • @jjanes As per the docs, it does need to scan the default table. But it also says `This scan, like the scan of the new partition, can be avoided if an appropriate CHECK constraint is present.` Not sure though where this constraint needs to be added, but seems like this should speed it up if the cause for slowness is the size of the default tables – user1583803 Jan 05 '22 at 12:27
  • 2
    @user1583803: a `SHARE UPDATE EXCLUSIVE` doesn't really lock the table. It only prevents concurrent DDL on that table - DML (insert, update, delete, select) will still be possible. –  Jan 05 '22 at 12:34
  • I think you have found the problem, the default partitions. If you add a check constraint to the default partitions to force all their dates to be NULL or at least all in the past or distant future, that should fix it. – jjanes Jan 05 '22 at 15:09

0 Answers0