title: Why does subpartitions not increase insert speed performance in postgres?
body: <p>I have installed postgres 12 and made a test with partitions (shown below). My question is, why there is no significant performance gain inserting data, even with ¼ billion rows in the table, when comparing 5 partitions vs 5 partitions with 5 subpartitions.</p>
  <p>My goal is to import data fast in usually big tables with + ¼ billion rows, and I was of the impression, that having more partitions would reduce the size of the indexes and increase the speed of inserting data.</p>
  <p>This was the setup for the test:</p>
  <p><strong>Machine</strong>: Local pc, 16 GB ram</p>
  <p><strong>Postgres version</strong>: 12</p>
  <p><strong>Partition test</strong>: Table a)  5 hash partitions with 5 hash subpartitions. Table b) 5 hash partitions</p>
  <p><strong>Tablesetup</strong> (example with only 5 partitions)</p>
  <pre><code>CREATE TABLE public.only_5_partitions
  (
      id integer NOT NULL,
      title character varying COLLATE pg_catalog.&quot;default&quot;,
      project_id integer
  ) PARTITION BY HASH (id) ;
  </code></pre>
  <p>--INDEXES ON ALL 3 COLUMNS NOT ADDED IN THE CODE EXAMPLE, BUT THERE ARE INDEXES ON ALL 3.</p>
  <p>--THE PARTITIONS</p>
  <pre><code>CREATE TABLE public.only_5_partitions_0 PARTITION OF public.only_5_partitions
      FOR VALUES WITH (modulus 5, remainder 0)
      PARTITION BY HASH (id);
  
  CREATE TABLE public.only_5_partitions_1 PARTITION OF public.only_5_partitions
      FOR VALUES WITH (modulus 5, remainder 1)
      PARTITION BY HASH (id);
  
  CREATE TABLE public.only_5_partitions_2 PARTITION OF public.only_5_partitions
      FOR VALUES WITH (modulus 5, remainder 2)
      PARTITION BY HASH (id);
  
  CREATE TABLE public.only_5_partitions_3 PARTITION OF public.only_5_partitions
      FOR VALUES WITH (modulus 5, remainder 3)
      PARTITION BY HASH (id);
  
  CREATE TABLE public.only_5_partitions_4 PARTITION OF public.only_5_partitions
      FOR VALUES WITH (modulus 5, remainder 4)
      PARTITION BY HASH (id);
  </code></pre>
  <p><strong>Rows inserted</strong>: Generated rows with this sample code:</p>
  <pre><code>INSERT INTO tableb
  SELECT generate_series(1,10000000), 'someting new', generate_series(1,10000000);
  </code></pre>
  <p>As you can see from the test, inserting data in table A (only 5 partitions) more or less are the same as for table B. In some runs the small number of partitions even performed better.</p>
  <p>In the last insert, I increased the insert to 50 mio rows, to detect a performance change.</p>
  <p><a href="https://i.stack.imgur.com/NdJzE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NdJzE.png" alt="enter image description here" /></a></p>
  <p><a href="https://i.stack.imgur.com/zYVt9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zYVt9.png" alt="enter image description here" /></a></p>