Re: Need guidance on partioning

Hi Rui,
On 750 MB part, Matthew has clarified that he means to say that 750 million rows.
But all of your comments are insightful. I agree with all of your comments.

My tables are growing fast. My largest table size is 10gb with 130 Million rows. Database is still only used by operations team so far. It is going to roll out end customers soon.

No-one has started complaining about anything so far. I will not be surprising if someone start crying all of sudden.
My management has highly underestimates the database tasks. I am just listening to them with their priority order. Otherwise, my job will be at risk 🙂

Thank you,
Sarwar

________________________________
From: Rui DeSousa <rui(dot)desousa(at)icloud(dot)com>
Sent: Wednesday, May 22, 2024 1:58 PM
To: M Sarwar <sarwarmd02(at)outlook(dot)com>
Cc: Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)evernorth(dot)com>; srinivasan s <srinioracledba7(at)gmail(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Need guidance on partioning

On May 22, 2024, at 11:10 AM, M Sarwar <sarwarmd02(at)outlook(dot)com<mailto:sarwarmd02(at)outlook(dot)com>> wrote:

Is it a good idea to partition the table when it reaches 750 MB in size or 1 B rows or any other general rule?

750MB is not a really big table; Postgres default segment size is 1GB, I normally set it 256GB instead. 1GB segment size is holdover from signed 32bit filesystems where the largest file size was 2GB.

I’ve had tables that where hundreds of GB and with billions of records. In my experiences partitioning usually degrades performance unless it well planned out as there is overhead associated with partitioning.

I don’t think there is a hard and fast rule for when to partition. It really depending on the given issues at hand. One good reason to partition large tables is for table maintenance, i.e. aging out and archiving out large chunks of data. What is a large table? That might also depend on hardware; a big table on spinning rust might feel like small table on solid state.

There is also a limit to how many partitions one should create. Too many and performance tanks. I once inherited a system that partition by day, what a disaster, I completely eliminated the partitioning from system as it didn’t need it. Point is partitioning is a tool and you’ll know when you need it. As with a tool — if you have a hammer in hand and the fastener is a screw then that screw really start to look like nail.