Extend pgbench partitioning to pgbench_history

From 4c8121d88aacb1f09236cb1a32a82dbf11c421be Mon Sep 17 00:00:00 2001
From: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Date: Thu, 30 Nov 2023 11:02:39 +0100
Subject: [PATCH] Include pgbench_history in partitioning method for pgbench

In case partitioning, make sure that pgbench_history is also partitioned with
the same criteria.

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
---
 doc/src/sgml/ref/pgbench.sgml | 10 +++----
 src/bin/pgbench/pgbench.c     | 55 +++++++++++++++++++++--------------
 2 files changed, 38 insertions(+), 27 deletions(-)

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 05d3f81619..4c02d2a61d 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -365,8 +365,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
       <term><option>--partition-method=<replaceable>NAME</replaceable></option></term>
       <listitem>
        <para>
-        Create a partitioned <literal>pgbench_accounts</literal> table with
-        <replaceable>NAME</replaceable> method.
+        Create partitioned <literal>pgbench_accounts</literal> and <literal>pgbench_history</literal>
+        tables with <replaceable>NAME</replaceable> method.
         Expected values are <literal>range</literal> or <literal>hash</literal>.
         This option requires that <option>--partitions</option> is set to non-zero.
         If unspecified, default is <literal>range</literal>.
@@ -378,9 +378,9 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
       <term><option>--partitions=<replaceable>NUM</replaceable></option></term>
       <listitem>
        <para>
-        Create a partitioned <literal>pgbench_accounts</literal> table with
-        <replaceable>NUM</replaceable> partitions of nearly equal size for
-        the scaled number of accounts.
+        Create partitioned <literal>pgbench_accounts</literal> and <literal>pgbench_history</literal>
+        tables with <replaceable>NUM</replaceable> partitions of nearly equal size for
+        the scaled number of accounts - and future history records.
         Default is <literal>0</literal>, meaning no partitioning.
        </para>
       </listitem>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 2e1650d0ad..677cbee5ae 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -217,8 +217,8 @@ char	   *tablespace = NULL;
 char	   *index_tablespace = NULL;
 
 /*
- * Number of "pgbench_accounts" partitions.  0 is the default and means no
- * partitioning.
+ * Number of "pgbench_accounts" and "pgbench_history" partitions.
+ * 0 is the default and means no partitioning.
  */
 static int	partitions = 0;
 
@@ -889,8 +889,10 @@ usage(void)
 		   "  --index-tablespace=TABLESPACE\n"
 		   "                           create indexes in the specified tablespace\n"
 		   "  --partition-method=(range|hash)\n"
-		   "                           partition pgbench_accounts with this method (default: range)\n"
-		   "  --partitions=NUM         partition pgbench_accounts into NUM parts (default: 0)\n"
+		   "                           partition pgbench_accounts and pgbench_history with this method"
+		   "                           (default: range)."
+		   "  --partitions=NUM         partition pgbench_accounts and pgbench_history into NUM parts"
+		   "                           (default: 0)\n"
 		   "  --tablespace=TABLESPACE  create tables in the specified tablespace\n"
 		   "  --unlogged-tables        create tables as unlogged tables\n"
 		   "\nOptions to select what to run:\n"
@@ -4697,20 +4699,22 @@ initDropTables(PGconn *con)
 }
 
 /*
- * Create "pgbench_accounts" partitions if needed.
+ * Create "pgbench_accounts" and/or "pgbench_history" partitions if needed.
  *
- * This is the larger table of pgbench default tpc-b like schema
- * with a known size, so we choose to partition it.
+ * "pgbench_accounts" is the larger table of pgbench default tpc-b like schema
+ * with a known size, so we choose to partition it. "pgbench_history" on the
+ * contrary is dynamically populated when pgbench runs, and we partition it
+ * based on the same criteria used in "pgbench_accounts".
  */
 static void
-createPartitions(PGconn *con)
+createPartitions(PGconn *con, const char *table)
 {
 	PQExpBufferData query;
 
 	/* we must have to create some partitions */
 	Assert(partitions > 0);
 
-	fprintf(stderr, "creating %d partitions...\n", partitions);
+	fprintf(stderr, "creating %d partitions for table %s ...\n", partitions, table);
 
 	initPQExpBuffer(&query);
 
@@ -4721,10 +4725,10 @@ createPartitions(PGconn *con)
 			int64		part_size = (naccounts * (int64) scale + partitions - 1) / partitions;
 
 			printfPQExpBuffer(&query,
-							  "create%s table pgbench_accounts_%d\n"
-							  "  partition of pgbench_accounts\n"
+							  "create%s table %s_%d\n"
+							  "  partition of %s\n"
 							  "  for values from (",
-							  unlogged_tables ? " unlogged" : "", p);
+							  unlogged_tables ? " unlogged" : "", table, p, table);
 
 			/*
 			 * For RANGE, we use open-ended partitions at the beginning and
@@ -4748,11 +4752,11 @@ createPartitions(PGconn *con)
 		}
 		else if (partition_method == PART_HASH)
 			printfPQExpBuffer(&query,
-							  "create%s table pgbench_accounts_%d\n"
-							  "  partition of pgbench_accounts\n"
+							  "create%s table %s_%d\n"
+							  "  partition of %s\n"
 							  "  for values with (modulus %d, remainder %d)",
-							  unlogged_tables ? " unlogged" : "", p,
-							  partitions, p - 1);
+							  unlogged_tables ? " unlogged" : "", table, p,
+							  table, partitions, p - 1);
 		else					/* cannot get there */
 			Assert(0);
 
@@ -4760,7 +4764,8 @@ createPartitions(PGconn *con)
 		 * Per ddlinfo in initCreateTables, fillfactor is needed on table
 		 * pgbench_accounts.
 		 */
-		appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
+		if (strcmp(table, "pgbench_accounts") == 0)
+			appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
 
 		executeStatement(con, query.data);
 	}
@@ -4836,9 +4841,9 @@ initCreateTables(PGconn *con)
 						  (scale >= SCALE_32BIT_THRESHOLD) ? ddl->bigcols : ddl->smcols);
 
 		/* Partition pgbench_accounts table */
-		if (partition_method != PART_NONE && strcmp(ddl->table, "pgbench_accounts") == 0)
-			appendPQExpBuffer(&query,
-							  " partition by %s (aid)", PARTITION_METHOD[partition_method]);
+		if (partition_method != PART_NONE && (
+			strcmp(ddl->table, "pgbench_accounts") == 0 || strcmp(ddl->table, "pgbench_history") == 0))
+			appendPQExpBuffer(&query, " partition by %s (aid)", PARTITION_METHOD[partition_method]);
 		else if (ddl->declare_fillfactor)
 		{
 			/* fillfactor is only expected on actual tables */
@@ -4860,7 +4865,10 @@ initCreateTables(PGconn *con)
 	termPQExpBuffer(&query);
 
 	if (partition_method != PART_NONE)
-		createPartitions(con);
+	{
+		createPartitions(con, "pgbench_accounts");
+		createPartitions(con, "pgbench_history");
+	}
 }
 
 /*
@@ -7225,7 +7233,10 @@ main(int argc, char **argv)
 		fprintf(stderr, "starting vacuum...");
 		tryExecuteStatement(con, "vacuum pgbench_branches");
 		tryExecuteStatement(con, "vacuum pgbench_tellers");
-		tryExecuteStatement(con, "truncate pgbench_history");
+		if (partitions > 0)
+			tryExecuteStatement(con, "truncate pgbench_history CASCADE");
+		else
+			tryExecuteStatement(con, "truncate pgbench_history");
 		fprintf(stderr, "end.\n");
 
 		if (do_vacuum_accounts)
-- 
2.43.0

From c8f684fbc8bbedcbf8016f6cef9ed3d822423d3d Mon Sep 17 00:00:00 2001
From: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
Date: Thu, 30 Nov 2023 11:02:39 +0100
Subject: [PATCH v2] Include pgbench_history in partitioning method for pgbench

If partitioning, make sure that pgbench_history is also partitioned with the
same criteria.

Signed-off-by: Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>
---
 doc/src/sgml/ref/pgbench.sgml | 10 +++----
 src/bin/pgbench/pgbench.c     | 55 +++++++++++++++++++++--------------
 2 files changed, 38 insertions(+), 27 deletions(-)

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 279bb0ad7d..6af59f9882 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -365,8 +365,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
       <term><option>--partition-method=<replaceable>NAME</replaceable></option></term>
       <listitem>
        <para>
-        Create a partitioned <literal>pgbench_accounts</literal> table with
-        <replaceable>NAME</replaceable> method.
+        Create partitioned <literal>pgbench_accounts</literal> and <literal>pgbench_history</literal>
+        tables with <replaceable>NAME</replaceable> method.
         Expected values are <literal>range</literal> or <literal>hash</literal>.
         This option requires that <option>--partitions</option> is set to non-zero.
         If unspecified, default is <literal>range</literal>.
@@ -378,9 +378,9 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
       <term><option>--partitions=<replaceable>NUM</replaceable></option></term>
       <listitem>
        <para>
-        Create a partitioned <literal>pgbench_accounts</literal> table with
-        <replaceable>NUM</replaceable> partitions of nearly equal size for
-        the scaled number of accounts.
+        Create partitioned <literal>pgbench_accounts</literal> and <literal>pgbench_history</literal>
+        tables with <replaceable>NUM</replaceable> partitions of nearly equal size for
+        the scaled number of accounts and future history records.
         Default is <literal>0</literal>, meaning no partitioning.
        </para>
       </listitem>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index af1f75257f..ef1a7e0ad1 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -217,8 +217,8 @@ char	   *tablespace = NULL;
 char	   *index_tablespace = NULL;
 
 /*
- * Number of "pgbench_accounts" partitions.  0 is the default and means no
- * partitioning.
+ * Number of "pgbench_accounts" and "pgbench_history" partitions.
+ * 0 is the default and means no partitioning.
  */
 static int	partitions = 0;
 
@@ -891,8 +891,10 @@ usage(void)
 		   "  --index-tablespace=TABLESPACE\n"
 		   "                           create indexes in the specified tablespace\n"
 		   "  --partition-method=(range|hash)\n"
-		   "                           partition pgbench_accounts with this method (default: range)\n"
-		   "  --partitions=NUM         partition pgbench_accounts into NUM parts (default: 0)\n"
+		   "                           partition pgbench_accounts and pgbench_history with this method"
+		   "                           (default: range)\n"
+		   "  --partitions=NUM         partition pgbench_accounts and pgbench_history into NUM parts"
+		   "                           (default: 0)\n"
 		   "  --tablespace=TABLESPACE  create tables in the specified tablespace\n"
 		   "  --unlogged-tables        create tables as unlogged tables\n"
 		   "\nOptions to select what to run:\n"
@@ -4729,20 +4731,22 @@ initDropTables(PGconn *con)
 }
 
 /*
- * Create "pgbench_accounts" partitions if needed.
+ * Create "pgbench_accounts" and/or "pgbench_history" partitions if needed.
  *
- * This is the larger table of pgbench default tpc-b like schema
- * with a known size, so we choose to partition it.
+ * "pgbench_accounts" is the larger table of pgbench default tpc-b like schema
+ * with a known size, so we choose to partition it. "pgbench_history" on the
+ * contrary is dynamically populated when pgbench runs, and we partition it
+ * based on the same criteria used in "pgbench_accounts".
  */
 static void
-createPartitions(PGconn *con)
+createPartitions(PGconn *con, const char *table)
 {
 	PQExpBufferData query;
 
 	/* we must have to create some partitions */
 	Assert(partitions > 0);
 
-	fprintf(stderr, "creating %d partitions...\n", partitions);
+	fprintf(stderr, "creating %d partitions for table %s ...\n", partitions, table);
 
 	initPQExpBuffer(&query);
 
@@ -4753,10 +4757,10 @@ createPartitions(PGconn *con)
 			int64		part_size = (naccounts * (int64) scale + partitions - 1) / partitions;
 
 			printfPQExpBuffer(&query,
-							  "create%s table pgbench_accounts_%d\n"
-							  "  partition of pgbench_accounts\n"
+							  "create%s table %s_%d\n"
+							  "  partition of %s\n"
 							  "  for values from (",
-							  unlogged_tables ? " unlogged" : "", p);
+							  unlogged_tables ? " unlogged" : "", table, p, table);
 
 			/*
 			 * For RANGE, we use open-ended partitions at the beginning and
@@ -4780,11 +4784,11 @@ createPartitions(PGconn *con)
 		}
 		else if (partition_method == PART_HASH)
 			printfPQExpBuffer(&query,
-							  "create%s table pgbench_accounts_%d\n"
-							  "  partition of pgbench_accounts\n"
+							  "create%s table %s_%d\n"
+							  "  partition of %s\n"
 							  "  for values with (modulus %d, remainder %d)",
-							  unlogged_tables ? " unlogged" : "", p,
-							  partitions, p - 1);
+							  unlogged_tables ? " unlogged" : "", table, p,
+							  table, partitions, p - 1);
 		else					/* cannot get there */
 			Assert(0);
 
@@ -4792,7 +4796,8 @@ createPartitions(PGconn *con)
 		 * Per ddlinfo in initCreateTables, fillfactor is needed on table
 		 * pgbench_accounts.
 		 */
-		appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
+		if (strcmp(table, "pgbench_accounts") == 0)
+			appendPQExpBuffer(&query, " with (fillfactor=%d)", fillfactor);
 
 		executeStatement(con, query.data);
 	}
@@ -4868,9 +4873,9 @@ initCreateTables(PGconn *con)
 						  (scale >= SCALE_32BIT_THRESHOLD) ? ddl->bigcols : ddl->smcols);
 
 		/* Partition pgbench_accounts table */
-		if (partition_method != PART_NONE && strcmp(ddl->table, "pgbench_accounts") == 0)
-			appendPQExpBuffer(&query,
-							  " partition by %s (aid)", PARTITION_METHOD[partition_method]);
+		if (partition_method != PART_NONE && (
+			strcmp(ddl->table, "pgbench_accounts") == 0 || strcmp(ddl->table, "pgbench_history") == 0))
+			appendPQExpBuffer(&query, " partition by %s (aid)", PARTITION_METHOD[partition_method]);
 		else if (ddl->declare_fillfactor)
 		{
 			/* fillfactor is only expected on actual tables */
@@ -4892,7 +4897,10 @@ initCreateTables(PGconn *con)
 	termPQExpBuffer(&query);
 
 	if (partition_method != PART_NONE)
-		createPartitions(con);
+	{
+		createPartitions(con, "pgbench_accounts");
+		createPartitions(con, "pgbench_history");
+	}
 }
 
 /*
@@ -7258,7 +7266,10 @@ main(int argc, char **argv)
 		fprintf(stderr, "starting vacuum...");
 		tryExecuteStatement(con, "vacuum pgbench_branches");
 		tryExecuteStatement(con, "vacuum pgbench_tellers");
-		tryExecuteStatement(con, "truncate pgbench_history");
+		if (partitions > 0)
+			tryExecuteStatement(con, "truncate pgbench_history CASCADE");
+		else
+			tryExecuteStatement(con, "truncate pgbench_history");
 		fprintf(stderr, "end.\n");
 
 		if (do_vacuum_accounts)
-- 
2.43.0