Subject: Re: When to use PARTITION BY HASH?
To: Imre Samu <pella.samu@gmail.com>
Cc: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>,
 pgsql-performance@lists.postgresql.org
References: 
 <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
 <CAJnEWw=hPeMiunN=XEkJM55mh+ytEkTXvGGoW8kbeZ8O4-+23g@mail.gmail.com>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <1de60053-ac43-87f3-288a-55b3f31d67f7@sqlexec.com>
Date: Sun, 7 Jun 2020 07:41:28 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.17
MIME-Version: 1.0
In-Reply-To: 
 <CAJnEWw=hPeMiunN=XEkJM55mh+ytEkTXvGGoW8kbeZ8O4-+23g@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------8F93F1AAEBE53039DF0B9759"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------8F93F1AAEBE53039DF0B9759
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

The article referenced below assumes a worst case scenario for 
bulk-loading with hash partitioned tables.  It assumes that the values 
being inserted are in strict ascending or descending order with no gaps 
(like a sequence number incrementing by 1), thereby ensuring every 
partition is hit in order before repeating the process.  If the values 
being inserted are not strictly sequential with no gaps, then the 
performance is much better.  Obviously, what part of the tables and 
indexes are in memory has a lot to do with it as well.

Regards,
Michael Vitale

Imre Samu wrote on 6/5/2020 7:48 AM:
> > "Bulk loads ...",
>
> As I see - There is an interesting bulkload benchmark:
>
> "How Bulkload performance is affected by table partitioning in 
> PostgreSQL" by Beena Emerson (Enterprisedb, December 4, 2019 )
> /SUMMARY: This article covers how benchmark tests can be used to 
> demonstrate the effect of table partitioning on performance. Tests 
> using range- and hash-partitioned tables are compared and the reasons 
> for their different results are explained:
>                  1. Range partitions
>            2. Hash partitions
>                  3. Combination graphs
>                4. Explaining the behavior
>                  5. Conclusion/
> /
> /
> /"For the hash-partitioned table, the first value is inserted in the 
> first partition, the second number in the second partition and so on 
> till all the partitions are reached before it loops back to the first 
> partition again until all the data is exhausted. Thus it exhibits the 
> worst-case scenario where the partition is repeatedly switched for 
> every value inserted. As a result, the number of times the partition 
> is switched in a range-partitioned table is equal to the number of 
> partitions, while in a hash-partitioned table, the number of times the 
> partition has switched is equal to the amount of data being inserted. 
> This causes the massive difference in timing for the two partition 
> types."/
>
> https://www.enterprisedb.com/postgres-tutorials/how-bulkload-performance-affected-table-partitioning-postgresql
>
> Regards,
>  Imre
>

--------------8F93F1AAEBE53039DF0B9759
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html theme="default-light"><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head><body text="#000000">The article referenced below assumes a worst
 case scenario for bulk-loading with hash partitioned tables.  It 
assumes that the values being inserted are in strict ascending or 
descending order with no gaps (like a sequence number incrementing by 
1), thereby ensuring every partition is hit in order before repeating 
the process.  If the values being inserted are not strictly sequential 
with no gaps, then the performance is much better.  Obviously, what part
 of the tables and indexes are in memory has a lot to do with it as 
well.<br>
<br>
Regards,<br>
Michael Vitale<br>
<br>
<span>Imre Samu wrote on 6/5/2020 7:48 AM:</span><br>
<blockquote type="cite" 
cite="mid:CAJnEWw=hPeMiunN=XEkJM55mh+ytEkTXvGGoW8kbeZ8O4-+23g@mail.gmail.com">
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <div dir="ltr">&gt; "Bulk loads ...",<div><br></div><div>As I see - 
There is an interesting bulkload benchmark:    </div><div><br></div><div>"How
 Bulkload performance is affected by table partitioning in PostgreSQL" 
by Beena Emerson (Enterprisedb, December 4, 2019 )</div><div><i>SUMMARY:
 This article covers how benchmark tests can be used to demonstrate the 
effect of table partitioning on performance. Tests using range- and 
hash-partitioned tables are compared and the reasons for their different
 results are explained: <br>                 1. Range partitions<br>     
            2. Hash partitions<br>                 3. Combination graphs<br> 
                4. Explaining the behavior<br>                 5. 
Conclusion</i></div><div><i><br></i></div><div><i>"For the 
hash-partitioned table, the first value is inserted in the first 
partition, the second number in the second partition and so on till all 
the partitions are reached before it loops back to the first partition 
again until all the data is exhausted. Thus it exhibits the worst-case 
scenario where the partition is repeatedly switched for every value 
inserted. As a result, the number of times the partition is switched in a
 range-partitioned table is equal to the number of partitions, while in a
 hash-partitioned table, the number of times the partition has switched 
is equal to the amount of data being inserted. This causes the massive 
difference in timing for the two partition types."</i></div><div><br><div><a
 
href="https://www.enterprisedb.com/postgres-tutorials/how-bulkload-performance-affected-table-partitioning-postgresql"
 moz-do-not-send="true">https://www.enterprisedb.com/postgres-tutorials/how-bulkload-performance-affected-table-partitioning-postgresql</a><br></div><div><br></div></div><div>Regards,</div><div> Imre</div><div><br></div></div>
</blockquote>
</body></html>

--------------8F93F1AAEBE53039DF0B9759--