Hi Michael,
We have table having rows 2.5 millions records inserted and updated in each hour and another table having about 1 million records in an hour. WE have dedicated column added in table that have list of 1-500 and this column is used as partition key.
Idea behind the 500 partition is to store 1 year data in the table with partition key value change at every 20hrs.
As the data is huge in these tables we are approaching to partition and the list partition for performing the maintenance on these tables.
This is not a test as we have partition on these tables with oracle but as we migrate to postgres, we are enabling the feature in postgres as well.
As we want to see the impact from beginning 0 size and understanding the details for next 72 hrs under heavy load.
As I just executed the same environment with 100 partition on these tables, Run was running for 12 hrs with constant 70% RAM utilization and 50% cpu utilization.
So I am suspecting the number of partition is the issue behind the memory utilization.
Thanks & Regards,
Ishan Joshi
From: Michael Lewis <mlewis@entrata.com>
Sent: Wednesday, June 10, 2020 10:28 PM
To: Ishan Joshi <Ishan.Joshi@amdocs.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Postgres server 12.2 crash with process exited abnormally and possibly corrupted shared memory
How many rows did these tables have before partitioning? à We starts test with 0 rows in partition table.
Partitions are far from free and pruning is great but not guaranteed. How many total rows do you currently have or foresee having in the biggest table (all partitions combined, or without partitioning) within the next 1-3 years? 1 million? 100 million? 2 billion? You may have partitioned before it was prudent to do so is the point. Just because something can be done, doesn't mean it should be. What sort of key are you partitioning on?
Also, what do you mean about tests? There are some assumptions made about empty tables since stats may indicate they are empty when they are not. If your tests involve many empty tables, then it may give rather different performance than real life where there are few or no empty partitions.
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service
