Re: Partitioning options - Mailing list pgsql-general

On Thu, Feb 8, 2024 at 10:25 PM Justin <zzzzz.graf@gmail.com> wrote:

Hi Sud,

Would not look at HASH partitioning as it is very expensive to add or subtract the number of partitions.

Would probably look at a nested partitioning using  customer ID using range or list of IDs then  by transaction date,  Its easy to add  partitions and balance the partitions segments.

 

 I'll not do that because, then, when getting rid of obsolete data, you must delete a huge number of records, and vacuum each partition.

if partitioning by date, you will ease greatly the cleaning, by just getting rid of obsolete partitions which is quite speedy.( no delete, no vacuum, no index updates, ...)

Marc

Keep in mind that SELECT queries being used on the partition must  use the partitioning KEY in the WHERE clause of the query or performance will suffer. 

Suggest doing a query analysis before deploying partition to confirm the queries WHERE clauses matched the planned partition rule.  I suggest that 80% of the queries of the executed queries must match the partition rule if not don't deploy partitioning or change  all the queries in the application to match the partition rule
 

On Thu, Feb 8, 2024 at 3:51 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:

Out of curiosity, As OP mentioned that there will be Joins and also filters on column Customer_id column , so why don't you think that subpartition by customer_id will be a good option? I understand List subpartition may not be an option considering the new customer_ids gets added slowly in the future(and default list may not be allowed) and also OP mentioned, there is skewed distribution of data for customer_id column. However what is the problem if OP will opt for HASH subpartition on customer_id in this situation?

It doesn't really gain you much, given you would be hashing it, the customers are unevenly distributed, and OP talked about filtering on the customer_id column. A hash partition would just be a lot more work and complexity for us humans and for Postgres. Partitioning for the sake of partitioning is not a good thing. Yes, smaller tables are better, but they have to be smaller targeted tables.

sud wrote:

130GB of storage space as we verified using the "pg_relation_size" function, for a sample data set.

You might also want to closely examine your schema. At that scale, every byte saved per row can add up.

Cheers,

Greg